Building a dependent dropdown based app using seperate data tables

[AKG123]

Hi guys,

I am currently trying to build an app that starts with a dropdown populated with all of my field services cases. Once a case is selected from the dropdown, I have a second dropdown that has all of the work orders that belong to the previously selected case, so essentially the work orders dropdown is dependent on which item is selected in the cases dropdown. The cases table and the work orders table are separate. I am aware that I can add a lookup column to the cases table that creates a relationship between the work orders table to the cases table, but I am not sure how I use that functionality when creating the app using the dependent dropdowns. I've attached a screenshot of the customer cases app I created without the work order functionality that I am trying to add. I have the "work order" dropdown selected with my "parental control" being the dropdown but no data appears in the matching field even though the relationship exists. I apologize in advance for any confusion.

Thanks for the help,

Powerapps novice

[v-qiaqi-msft]

Hi @AKG123,

Do you want to filter the order dropdown based on the case selected from the case dropdown?

Could you please tell me that if there is a primary key between case list and order number list?

Tell me that how you set the Items property of the Dropdown1?

For example, there is an caseID column in both case list and order number list, then we could retrieve order number based on the caseID from the case list.

You should set the items property of the Dropdown4 as below:

Filter('Work Orders',caseID=LookUp(CaseList,CaseNumber=Dropdown1.Selected.CaseNumber).caseID).WorkNumber)

[AKG123]

Hi @v-qiaqi-msft thank you for the prompt response. 

Yes, essentially I am trying to filter the order dropdown based on the case selected from the case dropdown. As of right now there is no primary key between the two but I know a N:1 relationship exists. Right now I have the items property of Dropdown1 set to the "Cases" table. 

It seems that creating a caseID column in both tables will be the easiest solution. Is there a way to create a caseID column that is updated every time a new case is generated?

[v-qiaqi-msft]

Hi @AKG123,

Absolutely you can. However it is difficult for you to re-configure the caseID for the existing records.

Firstly, you should create a Number/Text column in your data sourece to save the caseID, then I will stronglu suggest you generate an app based on your case list, which will bring you a complete app including a Gallery, a display form and an edit form.

Once you select an existing record from the gallery, it will navigate you to the display form where you could click the edit icon to edit the record, where set the Default property of the TextInput control corresponding to the caseID as 

ThisItem.ID

The above is based on you have unique case numbers in your case list.

[AKG123]

Understood. I have an existing 1:N relationship between my cases table and work orders table, is it possible to use that existing relationship to create the filtered dropdown without adding a primary key to each table? 

Thanks again, the assistance is much appreciated.