Statement - Join Editor

[Rigspek]

Good day from Indonesia; I'm not new to Sage but it is very difficult to get Sage Support out here given the time difference. We are running Sage 50 Accounts professional 2011 (v17); (I note the site registration only goes to v16).

I'm trying to edit customer statements and in particular trying to add the 'customer_order_number' held on the invoice table. I''ve managed to get the report to print the order number by using the join editor but the invoice outstanding balances are being increased by huge amounts. Our base currency is USD. We use other foreign currencies and wondering if this has anything to do with it. My knowledge of the report designer is weak to say the least. If there are any 'more user friendly' reporting programs that can be used with Sage, I'd be delighted to hear about them.

Any help is very much appreciated
David

[brucedenney]

The customer order number is not in the Audit Tables, so you need to link the INVREF field to the Invoice Number in the Invoice Table so you can user the Order ref out of the invoice table.

[FHarris]

Hello, I'm trying to do a similar thing, to insert the customer order number into the customer statement.

I have linked the INVREF field in the Audit Split Table to Invoice Number in the Invoice Table, but when I preview the report I either have a screen that hangs for ages or I get "No data was returned for this report".

I have tried linking to the Audit Header table instead and also changed the direction of the link, but I'm not sure what I'm doing wrong.

Your help and advice would be much appreciated.

[brucedenney]

The "inv ref" for some transactions is blank, so lots of records will not match to anything and be omited unless you use the correct "parent" join, there is also a risk that some transactions such as sales receipts might match to valid invoice numbers, so you need to be very sure of your data if you want to try and create this link.

If sage times out, it also returns the nothing found error. Try running it on the demo data first to eliminate the chance of timing with large volumes of data.

If you omit the join all together, sage will print every invoice for each audit trail transaction, which will hang for ever.

Try attaching your layout to your post and I could have a look