Our accounts dept. like to use Excel to analyse data in NAV. They do this via SQL views which are then linked into Excel and can be refreshed in Excel.
I have been asked to produce a view (basically a SQL query) for each sales invoice including customer name, document number etc but also the Net, VAT and Gross figures (a column for each).
I am trying to work this using the Customer Ledger Entry table, but I’m not sure where to get each of the figures from (ie which tables). I’m currently using Sales (LCY) as the Net figure, then getting the VAT from VAT Entry table and the Gross from Detailed Cust. Ledg. Entry filtered on Entry Type being one of the following:
1, 3, 4, 5, 6, 7, 8, 9, 12, 13, 14, 15, 16, 17
I got this filter from the FlowField expression for the Amount (LCY) field in the Customer Ledger Entry table.
Please can someone clarify that this is the correct way to get these figures, or is there an altogether more simple way of doing this?