I need some help with a BAQ for a dashboard. I cannot get a field to populate even though there is data there. I am missing something within the query that is preventing it from populating.
Below is the SQL code: I just a point in the right direction.
select distinct
[TranGLC].[Company] as [TranGLC_Company],
[TranGLC].[FiscalYear] as [TranGLC_FiscalYear],
[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
[TranGLC].[BookID] as [TranGLC_BookID],
[SubQuery3].[GLJrnHed_EnteredBy] as [GLJrnHed_EnteredBy],
[TranGLC].[CreateDate] as [TranGLC_CreateDate],
[TranGLC].[SegValue1] as [TranGLC_SegValue1],
[TranGLC].[GLAccount] as [TranGLC_GLAccount],
[TranGLC].[GLAcctContext] as [TranGLC_GLAcctContext],
[TranGLC].[TranDate] as [TranGLC_TranDate],
[TranGLC].[CreditAmount] as [TranGLC_CreditAmount],
[TranGLC].[DebitAmount] as [TranGLC_DebitAmount],
[TranGLC].[JournalCode] as [TranGLC_JournalCode],
[TranGLC].[JournalNum] as [TranGLC_JournalNum],
[TranGLC].[JournalLine] as [TranGLC_JournalLine],
[GLJrnDtl].[Posted] as [GLJrnDtl_Posted],
[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
[GLJrnDtl].[PostedBy] as [GLJrnDtl_PostedBy],
(case when SubQuery3.GLJrnHed_EnteredBy = GLJrnDtl.PostedBy then āTrueā else āFalseā end) as [Calculated_SameUser]
from Erp.TranGLC as TranGLC
inner join Erp.GLJrnDtl as GLJrnDtl on
TranGLC.Company = GLJrnDtl.Company
and TranGLC.CorrAccUID = GLJrnDtl.CorrAccUID
left outer join (select
[GLJrnHed].[Company] as [GLJrnHed_Company],
[GLJrnHed].[BookID] as [GLJrnHed_BookID],
[GLJrnHed].[CreateDate] as [GLJrnHed_CreateDate],
[GLJrnHed].[EnteredBy] as [GLJrnHed_EnteredBy],
[GLJrnHed].[Description] as [GLJrnHed_Description],
[GLJrnHed].[FiscalPeriod] as [GLJrnHed_FiscalPeriod],
[GLJrnHed].[FiscalYear] as [GLJrnHed_FiscalYear],
[GLJrnHed].[GroupID] as [GLJrnHed_GroupID],
[GLJrnHed].[JournalCode] as [GLJrnHed_JournalCode],
[GLJrnHed].[JournalNum] as [GLJrnHed_JournalNum],
[GLJrnHed].[Posted] as [GLJrnHed_Posted]
from Erp.GLJrnHed as GLJrnHed) as SubQuery3 on
GLJrnDtl.Company = SubQuery3.GLJrnHed_Company
and GLJrnDtl.FiscalYear = SubQuery3.GLJrnHed_FiscalYear
and GLJrnDtl.GroupID = SubQuery3.GLJrnHed_GroupID
where (TranGLC.TranDate >= ā1/1/2018ā and TranGLC.JournalCode = āCRā)
order by TranGLC.Company, TranGLC.FiscalYear, GLJrnDtl.FiscalPeriod, TranGLC.CreateDate, TranGLC.TranDate, GLJrnDtl.PostedDate, TranGLC.JournalNum, TranGLC.JournalLine
Iād suggest sharing a screen shot of the designer itself. My first guess is the inner join is keeping it from displaying records, basically both tables have to have a matching record or it will not show.
Mostlike that you need to make a relation to the Key1, Key2, etcā¦ fields in Tran GLC. And definitely want to but a table criteria on TranGLC.RelatedFile
Edit. Make a BAQ of just the TranGLC, and youāll see that many of the fields you are using are blank.
I am pulling Journal code CR (Cash Receipts). The related files are CashHead and CashDtl. I need GLJrnDtl to show who posted the CR to the GL. Nothing in the CashHead shows who entered it or created the header unless I add CashGrp and once I do that, it gives me garbage.
I have tried several different scenarios and I am close but not quite there. Below is the query and the fields I have related. I can get the exact number of Rows I need but it will not populate the entered by for each row.
Below is the sql. I have tried moving the GJrnHed to an Inner Subquery as well as tried to create it as a UnionAll it will provide some of the entered by from the table.
select distinct
[TranGLC].[Company] as [TranGLC_Company],
[TranGLC].[FiscalYear] as [TranGLC_FiscalYear],
[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
[TranGLC].[CreateDate] as [TranGLC_CreateDate],
[GLJrnHed].[EnteredBy] as [GLJrnHed_EnteredBy],
[TranGLC].[RelatedToFile] as [TranGLC_RelatedToFile],
[GLJrnHed].[GroupID] as [GLJrnHed_GroupID],
[TranGLC].[BookID] as [TranGLC_BookID],
[TranGLC].[SegValue1] as [TranGLC_SegValue1],
[TranGLC].[GLAccount] as [TranGLC_GLAccount],
[GLJrnDtl].[Description] as [GLJrnDtl_Description],
[TranGLC].[GLAcctContext] as [TranGLC_GLAcctContext],
[GLJrnDtl].[JEDate] as [GLJrnDtl_JEDate],
[TranGLC].[CreditAmount] as [TranGLC_CreditAmount],
[TranGLC].[DebitAmount] as [TranGLC_DebitAmount],
[TranGLC].[JournalCode] as [TranGLC_JournalCode],
[TranGLC].[JournalNum] as [TranGLC_JournalNum],
[TranGLC].[JournalLine] as [TranGLC_JournalLine],
[GLJrnDtl].[Posted] as [GLJrnDtl_Posted],
[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
[GLJrnDtl].[PostedBy] as [GLJrnDtl_PostedBy]
from Erp.TranGLC as TranGLC
inner join Erp.GLJrnDtl as GLJrnDtl on
TranGLC.Company = GLJrnDtl.Company
and TranGLC.CorrAccUID = GLJrnDtl.CorrAccUID
left outer join Erp.GLJrnHed as GLJrnHed on
TranGLC.Company = GLJrnHed.Company
and TranGLC.BookID = GLJrnHed.BookID
and TranGLC.FiscalYear = GLJrnHed.FiscalYear
and TranGLC.JournalNum = GLJrnHed.JournalNum
where (TranGLC.FiscalYear >= 2018 and TranGLC.JournalCode = āCRā)
and TranGLC.RelatedToFile = āCashDtlā or TranGLC.RelatedToFile = āCashHeadā
order by TranGLC.Company, TranGLC.FiscalYear, GLJrnDtl.FiscalPeriod, TranGLC.JournalNum, TranGLC.JournalLine, TranGLC.CreateDate, GLJrnDtl.JEDate, GLJrnDtl.PostedDate
I am open to suggestions but bear in mind I have to see each GL transaction for the GL Code CR (Cash Receipt). I know this comes from the CashHead and CashDtl Tables. But getting my query to pull in the entered by or each transaction has been the challenge.
Ah I confused myself again. Just move your head link to be under (and connected only to) the JrnDtl. Also, make it an inner join (you cant have a detail without a head anyway)
Iād try again and make sure you didnt get anything confused. There is no way you would not return the head, if you are returning details.Judging from the inner join you have on details, you must be returning them.
There in lies my problem. I need to show that whomever created the CR transaction was not the one who approved it. So Posted By <> Entered by or created by. Each try I pull in a different table that should get me data, I get zero results.
