Lookup if Code in one Module exists in another Module

Moehling
Moehling
in Modeling

I have two staging modules: 1) Account Master, and 2) Account Ledger

 

What I am looking to do is populate accounts from the the account master where the accounts exist in the ledger. I don't want to populate ALL the item in the account ledger since there are items which are no longer used, or that are "helper" items for JD Edwards, and not live account strings.

 

The formula I am looking to write should check to see if the [Account Master] ID exists in the [Account Ledger], and if it does, then I want these items to populate the list hierarchies related to the account string.

 

2) Account Ledger

Moehling_7-1660926809976.png

Line Items mapped to the same list as the Account Master shown below

Moehling_2-1660925879387.png

 

1) Account Master - The Account Master is dimensionalized by the red outlined code (which is the same code shown in the above module: "GLAID". I can successfully map to the Account Ledger here, but I want to to lookup the modules the other way around (For example: does code "00004571" exist in the Account Ledger module shown above? account master ID's can be found in the Account Ledger line item: GLAID)

Moehling_6-1660926703720.png

 

"Exists in F0911" should check to see if the Account Master CODE exists in the Account Ledger

 

I would really appreciate help on this, I cannot seem to figure it out...

 

Tagged:

Best Answer

  • ankit_cheeni
    ankit_cheeni
    Answer ✓

    Hi @Moehling 

     

    I am assuming Account Master is dimensioned by Flat F0901. I used Country in the example:

    Here are the modules:

    ankit_cheeni_0-1660928191940.png

     

    There are two solutions:

    1. Add a 'TRUE' boolean formatter line item that is always true in Ledger. Then using ANY function on this line item basis Country should serve the purpose
    2. Create a text field in Master and do a FIRSTNONBLANK on Country Text basis country line item (both in ledger). It will give you text for countries that have at least one instance. Doing an Isblank should conver it in boolean

    I would prefer 1 over 2. Here is the blueprint of Ledger and Master:

    ankit_cheeni_2-1660928406301.png

     

    ankit_cheeni_1-1660928378301.png

     

     

Answers

Categories