The business object GeneralJournalEntryLines contains the GL transaction lines of Exact Online. For performance tuning reasons, we are changing our scripts that copy data from Exact Online to our on-premise database to only include changes where possible instead of a full copy.
However, a query on GeneralJournalEntries joined with GeneralJournalEntryLines sometimes returns a null value in CreatorFullName and ModifiedFullName.
I've tried to reproduce new rows with this problem but those work fine.
Is this a database corruption? Or a join fault on my side?
The Exact Online query is:
use <DIVISION CODE>
select GE.Created GE_Created
,GE.Division GE_Division
,GE.EntryID GE_EntryID
,GE.EntryNumber GE_EntryNumber
,GE.FinancialPeriod GE_FinancialPeriod
,GE.FinancialYear GE_FinancialYear
,GE.JournalCode GE_JournalCode
,GE.Modified GE_Modified
,GE.Reversal GE_Reversal
,GE.Status GE_Status
,GE.Type GE_Type
,GL.AccountCode GL_AccountCode
,GL.AmountDC GL_AmountDC
,GL.AmountVATDC GL_AmountVATDC
,GL.AssetCode GL_AssetCode
,GL.CostCenter GL_CostCenter
,GL.CostUnit GL_CostUnit
,GL.CreatorFullName GL_CreatorFullName
,GL.Date GL_Date
,GL.Description GL_Description
,GL.GLAccountCode GL_GLAccountCode
,GL.LineNumber GL_LineNumber
,GL.ModifierFullName GL_ModifierFullName
,GL.OurRef GL_OurRef
,GL.ProjectCode GL_ProjectCode
,GL.Quantity GL_Quantity
,GL.VATBaseAmountDC GL_VATBaseAmountDC
,GL.VATCode GL_VATCode
,GL.VATPercentage GL_VATPercentage
,GL.VATType GL_VATType
from ExactOnlineREST..GeneralJournalEntries GE
inner join ExactOnlineREST..GeneralJournalEntryLines GL
on GE.EntryID=GL.EntryID
where GE.Status <> 50
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201501 and GE.Modified > '09/01/2017 14:03:09' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201502 and GE.Modified > '09/01/2017 14:03:09' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201503 and GE.Modified > '09/01/2017 14:03:09' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201504 and GE.Modified > '09/01/2017 14:03:09' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201505 and GE.Modified > '09/01/2017 14:03:08' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201506 and GE.Modified > '09/01/2017 14:03:08' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201507 and GE.Modified > '09/01/2017 14:27:27' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201508 and GE.Modified > '09/01/2017 14:27:27' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201509 and GE.Modified > '09/01/2017 14:27:27' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201510 and GE.Modified > '09/01/2017 14:27:26' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201511 and GE.Modified > '09/01/2017 14:27:27' ) )
or ( GE.Status = 50 and ( (GE.FinancialYear*100) + GE.FinancialPeriod = 201512 and GE.Modified > '09/01/2017 14:27:26' ) )
local export results as "D:\Invantive\data\uit_EOL\40570GeneralJournals.csv" format csv
local exit
When using the query:
select *
from ExactOnlineREST..GeneralJournalEntryLines GL
where gl.modifierfullname is null or gl.creatorfullname is null
it seems that all rows where the modifiedfullname or creatorfullname is missing have an associated user GUID from a short list. It are also (based on creation date) all records which are of considerable age.
It seems that Exact Online APIs internally make a left outer join with the users table, for which no longer active users return no information.