Issue with lookup columns cascading dropdowns formula !!

[Anonymous]

Hi Guys,

Some urgent help is required!!!

I am having the below 3 lists and having some serious issue in cascading the values.

Sites: Title

Departments: Title,Host

BookingForm: Site(lookup to Sites),Department(lookup to Department),SessionDate,SessionTime

SitesDropDown: Connected to Sites list and working fine.

DepartmentDropDown: Has to be connected to the Bookingform based on the Site selected from the SitesDropDownbox. Since, both of these are lookup fields i am having trouble in getting the values of the Departments list.

[RandyHayes]

@Anonymous 

First - Are you still having the issue?

If so, can you describe more about what issue you are having?

Are you stating that you are trying to populate the items of the Department DropDown control and having problems?

Is this from existing records or creating new ones?  Is there some logic that connects the lookups or is it simply that the Booking Form has two lookups and you want dropdown lists for them.  

Sorry not completley understanding the problem at hand - or at least the logic to it.  So, if you can provide more details, that would help.

[Anonymous]

@RandyHayes ,

Many thanks for taking your time to reply to my query.

Yes, I am still having trouble in populating the dropdown with the Department values. Attached is my detailed issue.

Thanks in advance.

[Anonymous]

To put it in more simple words, I dont see the choice/lookup column names in the Dropdown configurations in powerapp. I have 2 lookup columns and a choice column in the same datasource. But the value in the below screenshot is not listing any of them.

The datasource list columns are:

[RandyHayes]

@Anonymous 

So, it appears you're trying to lookup the values from the Customer Immersion Department for your DropDown based on the corresponding records in the Customer Immersion Sessions that match the SiteLocation chosen in the Site dropdown.  If that's correct then...

For your DDSiteLocation:

   Items property :  SortByColumns(Sites, "Title", Ascending)

   Value property : Title

For your DDImmersionDepartment:

  Items property : Filter('Customer Immersion Department', ID in Filter('Customer Immersion Sessions', SiteLocation1.Value=DDSiteLocation.Selected.Value).ID)

For your DDSessionDate - you already have that one.

For your DDSessionTime - not sure what you meant by "based on all the above values selected"

Let's see if the formula above gets you anywhere. 

One thing to try if you run into problems getting the correct values with DropDown lists is to "force" the columns you really want for it.  So, the above formula would be:

 ShowColumns(Filter('Customer Immersion Department', ID in Filter('Customer Immersion Sessions', SiteLocation1.Value=DDSiteLocation.Selected.Value).ID), "Title", "ID")

This would give your dropdown the proper values for Title and ID should you need it.  

You might not run into this with this scenario - but, it's good to have in your back pocket.

I hope this is clear and helpful.

[Anonymous]

 @RandyHayes 

Thanks for your quick response. I did tried using the formulas you have shared. The DDSiteLocation works just fine. But the  DDDepartments is not populating with any values. Attached is the screenshot for better understanding.

For your DDSiteLocation: Works fine

   Items property:  SortByColumns(Sites, "Title", Ascending)

   Value property: Title

For your DDImmersionDepartment: No values in the dropdown

  Items property: Filter('Customer Immersion Department', ID in Filter('Customer Immersion Sessions', SiteLocation1.Value=DDSiteLocation.Selected.Value).ID)

As per my understanding of the above formula, the purple ID refers to the ID in the Department. But the Green ID refers to the ID in the Sessions list. However, there is no relation between these two IDs. Please see the image below.

For your DDSessionDate - you already have that one. (Yes, I tested this one with dummy values in the DDdepartments and it works just fine)

DDSessionDate: SortByColumns(Filter('Customer Immersion Sessions',SiteLocation1.Value=DDSiteLocation.Selected.Value,ImmersionDepartments.Value=DDDepartment.Selected.Value).CalSessionDate,"CalSessionDate",Ascending)

For your DDSessionTime - not sure what you meant by "based on all the above values selected"

I meant that the DDSessionTime should be populated by “SessionTime” choice field from Sessions list based on the DDSitelocation,DDdepartment and the DDsessiondate selected by the user in the PowerApp.

This also should be the same formula as Sessiondate, but the formula is only working for single line of text but not for choice fields/lookups.

SortByColumns(Filter('Customer Immersion Sessions',SiteLocation1.Value=DDSiteLocation.Selected.Value,ImmersionDepartments.Value=DDDepartment.Selected.Value, CalSessionDate=DDSessionDate.Selected.Value).Session Time," Session_x0020_Time ",Ascending)

If only you could help me figure out a way to display the choice/lookup values in the dropdown of Powerapps, it would be really helpful. Thanks.

[RandyHayes · Accepted solution]

@Anonymous 

So, I needed to take a little more "focus" time on your scenario, so I recreated the data lists and the App to go quicker.  Let's see if this helps you...responses in red below.

---

For your DDSiteLocation: Works fine Okay - one so far...

   Items property:  SortByColumns(Sites, "Title", Ascending)

   Value property: Title

For your DDImmersionDepartment: No values in the dropdown

  Items property: Let's change to this...

ShowColumns(Filter('Customer Immersion Department' , ID in (Filter(AddColumns('Customer Immersion Sessions', "ImmDepID", ImmersionDepartments.Id), SiteLocation1.Value=DDSiteLocation.Selected.Value).ImmDepID)), "Title")

As per my understanding of the above formula, the purple ID refers to the ID in the Department. But the Green ID refers to the ID in the Sessions list. However, there is no relation between these two IDs. Please see the image below.  Yes, I missed the fact that you had no connection to the list in that way.  Was clearer after I threw your scenario together into an App.

I noticed a discrepancy in your Sessions list...there is a "Sales & Service", but a "Business Sales & Service" in your Department list!

For your DDSessionDate - you already have that one. (Yes, I tested this one with dummy values in the DDdepartments and it works just fine) Okay...another good one...

DDSessionDate: SortByColumns(Filter('Customer Immersion Sessions',SiteLocation1.Value=DDSiteLocation.Selected.Value,ImmersionDepartments.Value=DDDepartment.Selected.Value).CalSessionDate,"CalSessionDate",Ascending)

For your DDSessionTime - not sure what you meant by "based on all the above values selected"

I meant that the DDSessionTime should be populated by “SessionTime” choice field from Sessions list based on the DDSitelocation,DDdepartment and the DDsessiondate selected by the user in the PowerApp.

This also should be the same formula as Sessiondate, but the formula is only working for single line of text but not for choice fields/lookups.

SortByColumns(Filter('Customer Immersion Sessions',SiteLocation1.Value=DDSiteLocation.Selected.Value,ImmersionDepartments.Value=DDDepartment.Selected.Value, CalSessionDate=DDSessionDate.Selected.Value).Session Time," Session_x0020_Time ",Ascending)

Change your formula to this:

ShowColumns(AddColumns(Filter('Customer Immersion Sessions', SiteLocation1.Value=DDSitelocation.Selected.Value && ImmersionDepartments.Value=DDDepartment.Selected.Value && CalSessionDate=DDSessionDate.Selected.Value), "SessTime", SessionTime.Value), "SessTime")

If only you could help me figure out a way to display the choice/lookup values in the dropdown of Powerapps, it would be really helpful. Thanks.

---

Give these a try and see if that helps.

[Anonymous]

@RandyHayes 

I cant thank you enough for helping me solve this issue. I was trying to fix this issue for almost 2 days but headed nowhere and with your help, I could fix it in seconds. You are a legend!!!! Thanks heaps.