Automatic LOOKUP between tables?

(0) Share

Report

Posted on by AliceW

286

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:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (1)

AhmedSalih 6,678 Moderator on at

Like (1)

Report

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

Was this reply helpful? Yes No
AliceW 286 on at

Like (0)

Report

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

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

@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

Was this reply helpful? Yes No
AliceW 286 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
AhmedSalih 6,678 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
AliceW 286 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Verified answer

AliceW 286 on at

Like (0)

Report

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...

Was this reply helpful? Yes No