This is the SQL code I currently use via the Invantive Control for Excel, linked with our Exact Online DB.
As you can see in the code, in front of the line of the second select case I would like to enter 4 blank columns. The way it's coded right now doesn't work of course, but it was just a try-out. :)
<pre>select date
, InvoiceNumber
, AccountCode
, AccountName
, YourRef
, GLAccountCode
, GLAccountDescription
, CostUnit
, CostUnitDescription
, ProjectCode
, ProjectDescription
, Description
, AmountDC
, AmountFC
, CostCenter
, FinancialPeriod
, JournalDescription
, substr(GLAccountCode, 1, 1) type
, case
when substr(GLAccountCode, 1, 1) = '6'
then 'KOST'
else 'OPBRENGST'
end
pl
<<HERE>>
, case
when substr(GLAccountCode, 1, 1) = '6'
then AmountDC
else 0
end
debet
, case
when substr(GLAccountCode, 1, 1) = '6'
then 0
else AmountDC
end
credit
, AmountDC dc2
from TransactionLines
where FinancialYear = 2017 and JournalCode >='600'
order by date<code>
Second part of my question: can I add excel formulas into these blank columns, via the model editor? This could be useful so everytime when synchronizing with Exact Online, these formulas are not erased, but refreshed together with the data.
When running on Invantive Control, you need to change both the SQL as well as make sure that your layout allows for additional columns.
First of all insert:
, null COLUMNNAME1
, null COLUMNNAME2
, null COLUMNNAME3
, null COLUMNNAME4
where you need it in the column list.
Then choose 'Refresh' with Fields tab in the model editor.
On an application of 'Synchronize' button, you will see that the columns where necessary move to the right. But...
when you have an Excel range defined in Presentation tab in the model editor for the block, you will need to resize the Excel range to accomodate the 4 new columns. For instance by changing your layout range to include the 4 new columns.