Dynamics 365 F&O/AX Dynamics 365 CE / CRM Dynamics GP Business Central / NAV UG Dynamics SL Power Platform Fabric UG MS Copilot UG

[Rod Carlson]

Hello,

I would like to automatically assign GL-Master Account strings the values for aaTrxDimID, aaTrxDim, and aaTrxDimCode and was very close but have gotten to a bottle neck. 

I found the following query on this web site. However, it returns far more rows than what is shown on the “Account Access To Transaction Dimension Codes” screen. 

Has anyone had experience with this kind of project, and more specifically, what should be eliminated from this query to match the screen?

The table-finder tool from eOne Solutions is quite helpful showing the tables behind the screen but not the query behind the screen.

Any ideas?

http://mahmoudsaadi.blogspot.com/2014/03/analytical-accounting-gl-accounts-and.html

SELECT aaAcctClassID ,
aaAccountClass ,
aaTrxDimID ,
ACTINDX ,
ACTNUMST ,
Master_TB ,
aaTrxDim ,
aaTrxDimCode
FROM ( SELECT A.aaAcctClassID ,
A.aaAccountClass ,
B.aaTrxDimID ,
C.ACTINDX ,
D.ACTNUMST ,
CASE F.TPCLBLNC
WHEN 0 THEN ‘Debit’
WHEN 1 THEN ‘Credit’
ELSE ‘ ‘
END AS Master_TB ,
E.aaTrxDim ,
E.aaTrxDimCode
FROM AAG00201 AS A
INNER JOIN AAG00202 AS B ON A.aaAcctClassID = B.aaAcctClassID
LEFT OUTER JOIN AAG00200L AS C ON B.aaAcctClassID = C.aaAcctClassID
LEFT OUTER JOIN GL00105 AS D ON C.ACTINDX = D.ACTINDX
LEFT OUTER JOIN GL00100 AS F ON D.ACTINDX = F.ACTINDX
LEFT OUTER JOIN ( SELECT A.aaTrxDimID ,
A.aaTrxDim ,
B.aaTrxDimCode
FROM AAG00400 AS A
INNER JOIN AAG00401 AS B ON A.aaTrxDimID = B.aaTrxDimID
) AS E ON B.aaTrxDimID = E.aaTrxDimID
) AS AA

——————————
Rod Carlson
Information Technology – Software Developer
YMCA of Metro Chicago
Chicago IL
——————————

[Rod Carlson]

One step closer…. Now, I need to research the ramifications and best practices to insert into AAG02001

On my own, I think I answered the simpler question of joining the tables together as follows:

SELECT distinct AAG00400.aaTrxDimID
,AAG00400.aaTrxDim
,AAG00401.aaTrxDimCode
,AAG00202.aaAcctClassID
,AAG02001.ACTINDX
,GL00105.ACTNUMST
— , AAG00201.*
FROM AAG00400
INNER JOIN AAG00401 ON AAG00400.aaTrxDimID = AAG00401.aaTrxDimID
left outer join AAG00202 on AAG00401.aaTrxDimID = AAG00202.aaTrxDimID
left outer join AAG02001 on AAG02001.aaTrxDimCodeID = AAG00401.aaTrxDimCodeID and AAG02001.aaTrxDimID = AAG00401.aaTrxDimID
–LEFT OUTER JOIN AAG00200 ON AAG00200.aaAcctClassID = AAG00200.aaAcctClassID
–INNER JOIN GL00100 ON AAG00200.ACTINDX = GL00100.ACTINDX
LEFT OUTER JOIN GL00105 ON AAG02001.ACTINDX = GL00105.ACTINDX
order by GL00105.ACTNUMST

——————————
Rod Carlson
Information Technology – Software Developer
YMCA of Metro Chicago
Chicago IL
————————————————————————-