cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ZaneZ
Advocate II
Advocate II

Lookup relationship between three data sources

Hi Everyone!

 

Scope is three SharePoint lists:

- Products

- Ingredients (has lookup column Products)

- Frequently asked questions (has two lookup columns Products and Ingredients)

Idea is that one question can be related to multiple products and multiple ingredients at the same time. Users would like to improve their working process when adding new questions to the list, so that they don't have to memorize relationship between products and ingredients. For example, when user edits FAQ form and selects one or more ingredients -> then all related products should auto-populated/pre-selected for him.

 

So far have tried several formulas with choice fields, but lookup turns out to be more challenging especially because of three list relationship. 

 

Would like to ask your help and fresh piece of thought on this one.

Any hint is appreciated 🙂

3 REPLIES 3
v-monli-msft
Community Support
Community Support

Hi @ZaneZ ,

 

My understanding is that you want to know how to set the default selected items of a combo box inside Form control that is a lookup column in SharePoint. If my undestanding is correct, then you should add all 3 lists as data sources in the same app. And set the DefaultSelectedItems property of combo box control to something like below:

{
        Id: LookUp(ListA, Title = "xxx", ID), 
        Value: "xxx"
    }

This lookup field get the information from ListA's Title field. This formula means that set the default selected items to the "xxx" of ListA. 

 

More details about setting a default value for a LookUp field in PowerApps, please check the following thread:

https://powerusers.microsoft.com/t5/General-Discussion/Save-Filtered-Lookup-Value-in-List-Custom-For...

Please also check and see if the following blog would help in your scenario:

https://powerapps.microsoft.com/en-us/blog/default-values-for-complex-sharepoint-types/

 

Best regards,

Mona

 

 

 

Community Support Team _ Mona Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-monli-msft ,

 

Many thanks for your tips! Resources you shared turned out to be really helpful.

This formula now retrieves two values inside combo-box "Product" which is SharePoint lookup column of QuestionAnswer (QA) list.

Filter(Choices(QA.Product),Value = "EXAMPLE SHINE SHAMPOO" Or Value = "EXAMPLE CC CREAM")

Question now is how to make it filter dynamic values?

So that combo-box Product shows all Products that are assigned to selected ingredient.

Relationship between ingredients and products are defined inside Ingredient (INCI) SharePoint list. Combo-boxes are located inside QA list form. Hope that makes sense.

 

Did came up with formula which shows all Products assigned to selected ingredient -> which is exactly as needed, but...

LookUp(INCI,'Ingredient Name' in DataCardValue5.Selected.Value,FoundIn)

...problem here is that it works only inside empty comb-box which is outside SharePoint connector Form. When pasted inside the combo-box Product it gives error "The function LookUp has some invalid arguments". 

 

Have feeling that combo-box Product doesn't recognize Ingredient (INCI) list. All three lists have been added as datasources inside PowerApp. 

 

Any thoughts?

 

 

 

 

Anonymous
Not applicable

Hi @ZaneZ I'm going to bump this in the hopes some fresh eyes can provide additional insight

 

@Anonymous 

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,347)