Skip to main content

Notifications

Power Apps - Microsoft Dataverse
Answered

Automatic LOOKUP between tables?

(0) ShareShare
ReportReport
Posted on by 276

Hi everyone,

Surely I'm not the first to ask this. Is there a way to automatically bring in rows in a related table, without the user manually selecting something in a LOOKUP field?

Hear me out: Table 'SHOP' has two columns: 'Shop Name' and 'Product Name'. The user enters 'Shop A' and 'Apple' on one line.

Then I have table 'PRODUCTS', which also has two columns: 'Product Name' and 'Color'. The user enters 'Apple' and 'Green' on one line.

I'd like to bring in (as a column or a sub-grid in a Form/View on 'SHOP' the column 'Color'. So to have

'Shop A', 'Apple', and 'Green'. 

I know, use 'LOOKUP' for the SHOP 'Product Name'. The problem is, this data can be bulk-loaded... 

In a regular Power BI dataset, I'd just create a connection between the tables. 

Does anyone know how to do this, please?

Thanks,

Alice

Categories:
  • Verified answer
    AliceW Profile Picture
    AliceW 276 on at
    Re: Automatic LOOKUP between tables?

    Ok, I found the solution!

    Step 1: create that relationship column between the two tables.

    Step 2: in the Dataflow query bringing in the table in the 'many' part of the relationship, in the second, mapping step before 'Publishing', you indicate the Primary Column to also be copied in the column from Step 1. 

     

    Two warnings:

    1. Be careful you use the Primary Columns in the two tables. The Primary Key don't work for this...

    2. If you have more lines in the table in the 'many' side of the relationship, lines that can't find a LOOKUP value, these lines will be marked as errors in the refresh report. Myself, I can live with this. 

     

    So happy about this solution! It enables the building of a proper database...

  • AliceW Profile Picture
    AliceW 276 on at
    Re: Automatic LOOKUP between tables?

    I mean I'm bringing in the rows of the 'SHOP' table via a Dataflow. They are already filled in when the user sees them in the App.

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 on at
    Re: Automatic LOOKUP between tables?

    @AliceW when you say pre-populated values, you mean not all values have an established relationship with the related records?

  • AliceW Profile Picture
    AliceW 276 on at
    Re: Automatic LOOKUP between tables?

    Unfortunately, the Sub-Grid only seems to work if the user manually selects the 'Green' value in a field; it's not pre-completed based on an already filled in column.

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 on at
    Re: Automatic LOOKUP between tables?

    @AliceW, in Model Driven-Apps you can use the SubGrid control to acheive this requirement in your Table's Forms. This will help show the related records when you have relationship in place via Lookup columns. (Note, you have to have relationships)

     


     

    If my reply helped you, please give a 👍 If it solved your issue, please give a 👍 & accept it as the Solution to help other community members find it more.

    Visit my Blog: ahmedsalih.blog

    Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

     

     

     

  • AliceW Profile Picture
    AliceW 276 on at
    Re: Automatic LOOKUP between tables?

    Hi @AhmedSalih , sorry, should have mentioned that: a model-driven app.

  • AhmedSalih Profile Picture
    AhmedSalih 6,678 on at
    Re: Automatic LOOKUP between tables?

    Hello, @AliceW, you can achieve this in Power Apps but first, are you trying to build a Canvas app or Model-Driven app?

     


     

    If my reply helped you, please give a 👍 If it solved your issue, please give a 👍 & accept it as the Solution to help other community members find it more.

    Visit my Blog: ahmedsalih.blog

    Visit my YouTube Channel: https://www.youtube.com/@powerplatformplace/videos

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

Microsoft Kickstarter Events…

Register for Microsoft Kickstarter Events…

Announcing Our 2025 Season 1 Super Users!

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

Announcing Forum Attachment Improvements!

We're excited to announce that attachments for replies in forums and improved…

Leaderboard

#1
WarrenBelz Profile Picture

WarrenBelz 145,422

#2
RandyHayes Profile Picture

RandyHayes 76,287

#3
Pstork1 Profile Picture

Pstork1 64,711

Leaderboard

Featured topics