DropDown with linked data - Control data vs. Display data

[Disubert]

Hi there, 

I'm newbie in PowerApps built however several ACCESS applications.

Currently I'm struggling (since hours) to find the good way to put a dropdown field on my PowerApps to control one of my Lookup columns.

Architecture:

tblRegion

regID | regName

tblCity 

citID | citName | citRegID_Ref (Lookup)

The EditForm is connected to the tblCity table.

With the combobox control on my PowerApp I'd like the user let choose the Region by its name coming from the (linked) tblRegion but controlling the citRegID_Ref value of the tblCity.

In Access this was distinguished between the following properties of the control:

- Control Source (in this case tblCity.citRegID_Ref) and the

- Row Source (in this case would be a SELECT statement with the citID as first column)

I hope I was clear - but I'm sure that it's a kind of newbie question. Maybe that obvious that I missed it 🙂

[Nicci]

Hello! Just to clarify the question of what your looking to do, you want a combobox that lists the regName from the tblRegion list but after selecting it, when you save you take the selected regID and store it in citiRegID_Ref column on tblCity which is what the form is pointing to?

[Disubert]

Yes, Nicci, exactly.
The values shall be selected from the tblRegions (visible to the user the regName) and its regID shall be stored in the citRegID_Ref field.
I ask myself if I've to get the values with lookup and write with the patch function or if there is a easier, more enhanced way (as i.e. in Access with the control source and the row source which could be controlled separately).

[Nicci]

Ok so here is how to do it:

Step 1: Assuming you are using the form built in for the tblCity database click the data card for the citRegID_Ref it prebuilt and add a combobox to that data card, when prompted point the combobox to the other table, tblRegion.

Step 2: Delete the text field that the citRegID_Ref datacard came with, you dont need it. There will be 2 errors when you delete it, the update error and something to do with the height. You can resolve the height error in this step, just replace the old text field name you deleted with the new combobox name and leave everything after the dot that is there. We will come back to the update error.

Step 3: For the Items property of the combobox set it as follows: 

Distinct(tblRegion, regName)

Also when you click the combobox on the right there is a properties section and under 'Fields' click edit and just make sure its set to single and primary text and searchfield is set to 'Value'. Now your combobox should show all the regnames to choose from.

Step 4: Lets fix the update. So on the datacard select the update property and remove everything that is there and instead replace it with this:

LookUp(tblRegion, regName = Comboboxregname.Selected.Value).regID

Step 5: For the submit/send button you dont need to do a manual patch, just simply do this:

SubmitForm(FormName);

 The reason you can use the submit form is because the datacard is already pointing to the correct column on the table the form is meant for, all you're doing is changing the update property of the datacard to manually tell it what to store there based on the combobox we added.

If this helped you resolve your question please give me a thumbs up and accept it as a solution. Thank you so much!

[Disubert]

Thanks a lot Nicci, will report back the success of the development asap (sitting currently in the train with a really bad connection ^^)

[Disubert]

Hi Nicci,

I followed your guide and was able to fill the combobox with the values I wanted (list of regnames) but failed with step 4. Power Apps tells me that there is an error in the Update formula of the data card. The last part of the formula is underlined red:

.regID

Don't know if this matters, but the in the combobox 'fields' property is set to single but the primary text as also the SearchField is empty (and I can't change it).

What do I wrong? Is there any other value of the datacard or combobox that I need to adapt the data card accept the .regID?

Maybe this might be relevant after some reading: the column I try to update is of type "Lookup" in Sharepoint. Do I have to change the Update property according to a complex column?

On top: I suppose the Default property I've to change too to show the value of the dropdown?

[Disubert · Accepted solution]

After doing some reading and trial and error I was able to figure out the following solution:

On the data card:

Update: 

{Value:LookUp(tblRegion, regName=cboReg.Selected.Value).regID, Id: LookUp(tblRegion, regName=cboReg.Selected.Value).regID } 

Default:

{Value:LookUp(tblRegion, regID=ThisItem.citRegID_Ref.Value).regName, ID:ThisItem.citRegID_Ref} ​

Thanks a lot for your help! It helped a lot getting me on this way!