Crystal and Extracting data

[jacy]

Hi There,
I have crystal reports 8.5 and have managed to extract some data from it such as short description and price. What i actually want to extract into the same report is my cost price as well which i have set up as a custom property under each product.
I know in the access database the custom properties are under Sstring1 but i have 4 custom properties cost price, manufacturer,vendor & Vendor number and i cant seem to get just the cost price to show in the report. all 4 custom properties show up. if i go to field/add data in crystal to add just the custom properties Sstring doesn't show up at all.

Any ideas on how to show this?

Cheers,

Jacy

[Jan]

The product properties table holds all sorts of information - components, choice, prices, HTML variables etc. The 'type' field tells you which type of thing you have in each row. HTML Variables have a type of 3. So you need to join in rows of type 3 only. Also products only have a row in the product property table if they actually have an entry for an HTML variable so you need to use an outer join to stop products with no HTML variables from being left out of the report.

The values for HTML variables in product properties are

type 3
nValue1 [HTML Variables].[nVariableID]
sString1 Value
bFlag1 Is a filename?
bFlag2 Use contents of file (uploads file otherwise)
bFlag3 Is it searchable?
bFlag4 Use as an HTML variable via CUSTOMVAR

If you get stuck, the catalog detail report shows product properties so you could have a look at how it does it.

Regards,

[jacy]

Hi Jan,
Thanks for replying.
After 5 hours and lots of cursing I have managed to add Vendor to my report. I cannot however, add Manuf acturer number or Cost Price. the formula i have used is
{ProductProperties.nValue1} = 22 and
{HTMLVariables.sHTMLVarName} = "VENDOR" and
{HTMLVariables.nVariableID} = 22

if i insert a new field into the report called cost price and use the same formula, changing 22 to 23 and Vendor to cost price, crystal will change the values in the vendor column but will not add the info to the new column
I need to have the folowing columns showing Man Number, Short Description, Vendor, Vendor Number.

Any help as usual is greatly appreciated.

Cheers,


Jacy

[Jan]

Hi Jacy,

For every HTML variable that you want to add you have to add a copy of the product properties table to the report.

Call each one the name of the variable that you are trying to extract and use the appropriate criteria to pick out the records that you need (so join on product reference, type and var ID). To make life really easy for yourself, create a formula and call it the name of the actually thing you are extracting from the table (eg cost price) and then you can concentrate on the formatting rather than trying to remember which field does what.

You only need to join in the HTML variables table if you think that the VAR id might change (which can happen if you import and export your catalog) - personally I would leave that table out of the join and just make sure that the ID didn't change - you could actually add a formula with the ID in it and use that formula in place of '22' so that if it did change then you would just have to change the value of that variable.

Thinking about it you probably have the HTML variables table in there because the catalog summary report includes it, but it includes it to allow the name of the variable to be shown.

Regards,

[jacy]

Hiya Jan,
I thought if anyone knew the answer it would be you!!!

Thank You very much. I'm going to try it now.

Now off to bed for you!! You must be tired after watching your boys beat us in the Rugby Union


Cheers & Thanks for your help I really appreciate it.

Jacy

[Jan]

I missed the whole rugby thing actually - the only inkling was a really frustrated assistant in a motorway service station who kept asking customers what the score was and couldn't believe it when people kept saying 'what rugby'.

Cheers,