Where can I find the fields within Sage for my layouts?
As you know there are a number of fields within Sage that are used to create layouts and reports. It’s difficult to know where the fields from Sage are kept when customising or building a layout from scratch.
This blog is going to help you find the most common fields used on a Sales Order Processing Invoice and how to look for them within Report Designer.
How do I find the fields I need for my Layout?
Here at itas, we would always recommend using or amending an existing default layout. Here you can get an idea for the fields that need to be included in your layout and which tables the fields are kept in. So, you can check which table the field is from two ways:
- Hovering over the field brings up an information box about the field. The first part of the Variable name is the table it is saved in and the second part is the variable name.
- Selecting the field and looking at the Properties menu on the right-hand side of the screen. You can then see the same variable information in the expression field.
Where is everything pulled from?
The fields are kept in tables within the Sage 200 Company Database in SQL. This is where Sage 200 saves all the information that you enter such as Customer or Order details. So for example, our SOP Invoice uses more than one table to pull through the information required. In our example of the SOP Invoice, there are ten tables that pull through the data.
Where can I find these fields within Sage?
A lot of information pulled through on the Invoices are entered or amended in the Sales Ledger either by creating or amending a customer account. As shown below the information is pulled through from multiple tabs – Company, Trading, and Payment.
You can view Sales Order Document Number and Document Date through the Sales Order itself. These fields are generated as a result of the order being created.
The Invoice Document Number and Document Date are entered when creating the invoice. You can view this through the Sales Order by clicking on Invoices at the bottom of the View Order Window. The window that pops up then lists invoices for that Sales Order which also gives you the Invoice Number and Invoice Date which is pulled through on the Document. Here you can print the invoice.
The code, description and VAT code come from within the Stock Item and pull through onto the Sales Order when the user selects the item. The quantity and unit is therefore determined through the Sales Order. Gross Net is pulled through from the line total field on the Sales Order or Invoice.
Now you have a bit more of an idea about where fields are within Sage and how to find them through Report Designer – why not try it yourself? You can have a go at our e-course for report designer or try designing your own invoice layout. Also, take a look at our blog on ‘The Anatomy of a Perfect Invoice‘ which gives you the low down on what you need to include on your invoices.
Holly is friendly,outgoing and at the heart of the itas team. She is our resident Excelerator and Iman expert, who loves solving those tricky cases! A bit of a film buff, Holly enjoys nothing more than a good trip to the cinema – popcorn and all!