How to bind simple ID in Form with other table lookup Drop Down

[TickboxPhil]

Hi, this subject (bind simple ID in Form with other table lookup Drop Down) is a common situation, quite a few threads here but different variations so have gone back to basic mock up for clarity of best practice in PA. In SQL (or any DB or similar Data Source), not SharePoint lookups.

Keeping to a Form (have had better success with Patch direct but want to know if this is even possible in Forms, which are very handy), I have a few Fields and then add the StatusID as a Card at the bottom. Default properties are then set, which need to be adjusted for SQL, as the Drop Down is empty and not binding properly:

As usual, the StatusID is the PK of another SQL Table which contains the StatusName text ("Approved", "Referred" etc) which needs to be shown to users (whilst the StatusID itself is bound to the Form's Table tbl_Time of course).

ThisItem of the Form is a single record of Item: LookUp('[testing].[tbl_Time]', TimeID = _intTimeID) to override the fact the Browse Screen uses a SQL View to combine the Names with IDs whereas Edit Form needs a direct Table only. But that shouldn't matter too much, the same should apply to any ThisItem.

I can make various suggestions gleaned from this forum to get the Drop Down to display lookup items, but so far not successfully both displaying text and binding ID on submit (I know the Update on the Card should likely be changed to ID for example). But no point second guessing combinations - can somebody who has achieved this please highlight the best setup/changes to get this common requirement?

[Drrickryp]

Hi @TickboxPhil 

This is a common situation that I encounter in all of my apps. My standard practice is to select a record in a Gallery containing the Parent table and then navigate to the edit form. Inside of the edit form,  I use a simple textinput box control with the default property set to 

Gallery1.Selected.ID

for the foreign key. I either hide this column or set its display property to disable so that the user cannot change it. 

[TickboxPhil]

Hi @Drrickryp sounds good but I can't see where you bind the Drop Down - if I do as you say, I just end up with a Text Input box with the ID in it, whether it comes from a navigated Gallery, Variable or whatever. Then what? Also, I don't think you can put an extra control into the Form (if you select the Form and then insert a control, it still ends up outside the Form). So are you saying your Text Input control is added via the Form dialogue, and not converted into the "Allowed Values" Drop Down, but that somehow you can then add a Drop Down and bind it to this Text Input box?

[Drrickryp]

@TickboxPhil 

A child table should have a number field that is the ID from the parent table. This field is referred to as the foreign key of the child table. The ID field of the parent table is called the primary key of the table and all tables should have a unique primary key.

[TickboxPhil]

Don't worry, I know what a FK is and a PK is @Drrickryp and it's all set up - my screenshot is pretty clear about what to achieve and I have responded to your suggestion to clarify it does not solve the control binding issue yet. Please tell us how exactly your additional Text Input box relates to the required Drop Down control - so far it is on a screen doing nothing, and you haven't answered my question as to whether I put it there through the Form dialogue or outside of that, or exactly which is the best way to reconfigure the default settings of the Drop Down shown (to a Text Input box or direct or whatever).

[TickboxPhil · Accepted solution]

Ok, just to make some quick progress, I can change the AllowedValues of the Card to be the lookup / Primary Key / Parent Table ID values:

[@'[testing].[tbl_StatusTime]']
//DataSourceInfo([@'[testing].[tbl_Time]'], DataSourceInfo.AllowedValues, "StatusID")

Then change the Value of the Drop Down to be the text part, eg StatusName. This is similar to Access, WinForms etc, where you hide the ID but still refer to the record. 

Then change the Update of the Card to the StatusID.

Bingo! Dropdown shows all the StatusNames, and updates the StatusID according to selection.

The only fly in the ointment is that when you go back to Edit, the DropDown reverts back to the first AllowedValue (and saves that if done, which is not good). Which is odd, because the Default for both the Card and the Drop Down (as Parent.Default) is ThisItem.StatusID, ie of the actual record, not the lookup, which should be fine. Will look at how to resolve that, but think I'm on the right track. Somehow it seems we have to manually force the correct selection of ID. TBC.

CODA: Got the final bit concluded with help of MS Support. I'd already read Drop Downs referred to whole record, not just ID fields (like Access, WinForms etc) but in PA, it's just the text/value specifically that needs to match on Default loading. So in this case, set the Default of the DD (not Card) to:

LookUp('[testing].[tbl_StatusTime]', ThisRecord.StatusID = ThisItem.StatusID).StatusName

Job done, now displays correct values, as well as allowing updates etc! Hope this helps somebody else in SQL/DB (and has restored my faith in PA).

[Drrickryp]

Hi @TickboxPhil ,

I think you are getting somewhere.  I like to troubleshoot using a datatable instead of dropdown to hold your parent table data.  Just set it to the same Items property as the dropdown and you will be able to follow what is happening.   You may be resetting the data in the dropdown somehow so that it is going back to the top of the table.