Including extra field on invoice

[rookers]

Hi
We have several custom fields for products set up, which work just fine.

How can I get a custom field, for a purchased product, to be included on the invoice.

I have crystal reports and am familiar with editing the reports, but of course custom fields do not seem to be available to be used in reports.

Any advice useful.

[Jan]

The variable's are defined in the Variable table and the values are in the User defined properties table, so you have to add these tables to the report definition and then you can use those values in the report.

I would recommend using a left join to make sure orders that don't have that particular variable are still included.

Regards,

[rookers]

Hi there
Thanks for this info - this is very exciting news. Can I just squeeze a little more info out of you so I can get this working.
I am looking at invoices.rpt. Sub-report, is invoice-detail. How do I link to these new tables that you mentioned.

I see orderdetail has product reference in it - but I can't work out what need to be linked to what. Or do I need to bring the product table in there too?

Any info useful!

[fergusw]

Jez, Jan's your best bet re: Crystal reports, however I can offer a bit more on the database query side of things.

Firstly though, you'll want to get the "nID" value of the userdefinedvariable/property you need from the "Variable" table. Scan through it until you find the name of said variable (in sName field) and record the nID value.

Secondly, to get the custom field property value for a product you need a query like this

SELECT sValue FROM UserDefinedProperties WHERE sContentID = [prodref] AND nVariableId = [nID] AND nContentLevel = 2

In the above line you would replace [prodref] with product reference and [nID] with the ID retrieved in the first step.

This will return the custom property value for that particular product.

[rookers]

Many thanks for that info - very useful. On another note I have emailed you regarding something else if you get a sec.

[Jan]

Double click on the subreport to open it and then go to Database / Add database to report.

The visual linking expert will appear and you can add your two tables and join them together (remember to use the left join). You won't be able to add all of the conditions but add the ones you can and close the dialog.

Now go to Database / Show SQL Query and add the additional clauses.

I think I said before but the variable cannot have 'allow use parent' ticked because when that option is ticked, the database rows are not added, the program just works out what the values should be but this means you can't get at the data in Crystal.

I hope that helps, this is one of the most complex things you can do with Sellerdeck and Crystal reports.