Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Suggested answer

Statement to use Lookup table when retrieving data

(0) ShareShare
ReportReport
Posted on by 32

I have a customized lookup table like this:

Country Notes
Germany German notes
Italy Italian notes

What i want is that my report will fill the node "Notes" with notes depending on from which country the customer is.

For example, if the CountryRegionID for for the customer is GER, i want it to check it in the lookup table, and take the source from "Notes" that is in the same row as Germany. How would this be possible? can it be solved by using an expression?

  • Suggested answer
    Arcadi  Profile Picture
    Arcadi 786 on at
    RE: Statement to use Lookup table when retrieving data

    Declare your table registry in the maping of your XYZ_YourTable, then use the formula (on a new field and it the same level of where you have the CountryRegionID):

    FIRSTORNULL(
        FILTER(
            XYZ_YourTable,
            XYZ_YourTable.Country = @.CountryRegionId)).Notes

  • thesar Profile Picture
    thesar 32 on at
    RE: Statement to use Lookup table when retrieving data

    This will be on PackingSlip level, will be used as a Pro Forma for Customs. But i need the notes to be different depending on country. So the solution i cam of thinking is having a lookup table with the key being the country and the notes being the data i am needing. so if i am sending a pro forma for a customer from germany then i want the ER to take the german notes from the lookup table and insert it to my Note field in the model mapping.

    Still havent found a good way though.

  • Martin Dráb Profile Picture
    Martin Dráb 230,934 Most Valuable Professional on at
    RE: Statement to use Lookup table when retrieving data

    One option is not duplicating the value are merely displaying it from the setup table (by using a join or a display method).

    If you want to copy the value, think about at what time it should happen. If immediately when you change the country, modifiedField() method on the table is a good choice.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Congratulations to the January Top 10 leaders!

Check out the January community rock stars...

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,111 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 230,934 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans