Could someone help me please? I have very limited knowledge of Sage Report Designer (although I have managed to follow directions posted on here before with success), but I am sure that this is easy as pie for many of you.
We have order books with consecutive numbers, which I duly enter when raising sales invoices. However I really need these order numbers to show up on the customer statements. Could anyone tell me how to add this field please?
In addition, I would like to be able to run a report which could show me if I have any gaps in my consecutive order numbers, but I can't work out how to add this column to e.g. the invoice list. Could anyone help please?
The easiest is to use the "Update using Order number" however, you don't then have the invoice number and you have issues in the transition.
You can link in the invoice header table to the audit header table linking the inv_ref field in the Audit Header to the invoice_number field in the invoice header.
There is an issue with one being text and the other being a number and so at some point they added INV_REF_NUMERIC which is the field you need to link to INVOICE_NUMBER of course if the invoice has been deleted, or the ref refers to something that doesn't exist it is possible that transactions would be missed off the statement, so I suggest you user a "parent outer join" so that if there is no matching record the rest of the record will still be returned and the item printed albeit without a sales order number from the invoice table.
__________________
For just about anything Sage :- switch to renting, pay-as-you-go sage support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me.
Thanks so much for the prompt reply. I might have over-stated my knowledge of Sage Report Designer, I have literally only ever followed "step by step" instructions, other than that it's all very bewildering.
I definitely need to see both order number AND invoice number, but I could use just the numerics from the order number if this makes things easier.
Would you be able to lead me through the method please, or perhaps suggest how I could go about learning Sage Report Designer myself?
This shows how all the tables of data are linked to each other. It allows you to define what tables you can see and how those tables relate to each other.
Open the join editor You need to add the Invoice header table to this You do it by dragging it INVOICE from the list of tables onto the work area. Then you need to link it You do this by finding the fields that link in each table and then dragging one field and dropping it onto the other field I would move the tables so they are close to each other first You should now see a line linking the 2 tables to each other if you click on the line you should see its properties here it explicitly states which field is linked to which. change the join property to a parent outer join Change the join properties by selecting parent outer in the drop down box When you link tables together, you normally exclude data that does not link, so records that have no matching data are excluded the parent outer join is a non standard join which says give me the parent data even if there is nothing in this child table. So if there is no data in the invoicing module relating to this entry eg if it is a previous payment on account that has not been allocated then it still gives you the parent record but with nothing in the child table. You can now close the join editor and go back to the main report designer. In here you can now see the invoice header table INVOICE and in there is the Sales Order Number field ORDER_NUMBER Drag this field INVOICE.ORDER_NUMBER and drop it on your report where you want it to appear.
-- Edited by BruceDenney on Tuesday 13th of January 2015 11:59:47 AM
__________________
For just about anything Sage :- switch to renting, pay-as-you-go sage support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me.
I have I believe followed them to the letter, but I am not finding INVOICE.ORDER_NUMBER in the invoice header table. (NB: I have gone into Customer Statement > Edit to get to the Report Designer.)
Was I right to link INV_REF in Audit Header to INVOICE_NUMBER in Invoice Header per your first post? Or should one of these be Order number?
I used the full name of the field including the tablename TABLE_NAME.FIELD_NAME
the field you are looking for is ORDER_NUMBER
__________________
For just about anything Sage :- switch to renting, pay-as-you-go sage support, sagecover, upgrades, application integration, reports, layouts, analysis or any other help making life with sage easier/less time consuming Contact me.
