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
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...
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.
@AliceW when you say pre-populated values, you mean not all values have an established relationship with the related records?
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.
@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
Hi @AhmedSalih , sorry, should have mentioned that: a model-driven app.
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