Using SQL Child Table to populate dropdown lists to create a key value pair

[tonykiefer]

I'm working on populating a drop down list from a SQL child table.  Populating the table isn't a problem.  The issue come from trying to implement a key value pair.  The data table structure...

Currently the drop down list uses the USR_ITEM_DESC.

The the USR_ITEM_TYPE should be saved to the main table(I'm struggling with this part as I am not able to figure it out).

Also...when retrieving the data the user should only ever see the USR_ITEM_DESC.

The datacard and dropdown list are set up as so...

DataCard...

 DropDown List...

I'm trying to figure out how to implement this strategy and include the USR_ITEM_TYPE so the Key/Value Pair is USR_ITEM_DESC/USR_ITEM_TYPE.

I am trying to avoid using this technique...

"Table({Text:"",Val:Blank()},{Text: "Item", Val:0},{Text: "BOM", Val:1},{Text: "Service", Val:2})" and saving the value as dropdown1.selectedtext.val.

Has anyone figured out how to do this without manually creating a table in the Items property?

[RandyHayes]

@tonykiefer 

You already have what you want in your dropdown!

The fact that you have USR_AX_ITEM_TYPE as the Items property means that all of the columns are available in the selected record.

So, if you have "Service" selected in the dropdown and you reference, yourDropDown.Selected.USR_ITEM_TYPE you will get 2 from it.

[timl]

@tonykiefer 

To save the USR_ITEM_TYPE value to your main table, you would amend the Update property of the card to reference the USR_ITEM_TYPE. Therefore, you would change the highlighted part here to:

ItemType.Selected.USR_ITEM_TYPE

[tonykiefer]

Hi @RandyHayes,

Currently the USR_ITEM_TYPE is saved to the db.  The USR_ITEM_TYPE is saved to the main table as such.

This particular record has a value of 1 so it should show BOM

USR_ITEM_TYPE is the backend value(Val) that gets saved and retrieved but the end user should only see the translated USR_ITEM_DESC description(Key).  Currently the USR_ITEM_TYPE is not being referenced as it should as I don't know how to set these two SQL columns up as a Key/Value pair without doing it manually as illustrated above.

Also...if it's helpful to know...the "USR_AX_ITEM_TYPE" table will have nothing written to is.  It's merely a child lookup table used to store the key/value pairs.  The main table will store all the information as the Value portion of the pairing.

I'm hoping this information will further illustrate my dilemma.

[RandyHayes · Accepted solution]

@tonykiefer 

Yes, so, you are saying that you want to store the USR_ITEM_TYPE in the data record when you submit.  So, again, the USR_ITEM_TYPE is already in the Items property of your dropdown, you simply need to specify this in your Update property of the DataCard - ItemType_Dropdown.Selected.USR_ITEM_TYPE

You will need to change the Default property of your Dropdown to the following:

   LookUp(USR_AX_ITEM_TYPE, USR_ITEM_TYPE=Parent.Default, USR_ITEM_DESC)

That should give you what you are looking for.

[tonykiefer]

Hi @RandyHayes...

Yes!  This is exactly what I'm looking for.  Now here is the final question.

Which is the preferred method of handling this.  Using a table as I did...

Using the "USR_AX_ITEM_TYPE" table to reference the two columns "USR_ITEM_TYPE" and "USR_ITEM_DESC" as key/value pairs...

Or manually creating the table as such...

Table({Text:"",Val:Blank()},{Text: "Item", Val:0},{Text: "BOM", Val:1},{Text: "Service", Val:2})

Take into consideration that If an app has many dropdowns that need populated the same way...would multiple reference tables connections slow or adversely affect performance?

Which would you prefer?

[RandyHayes]

@tonykiefer 

Well, from a pure performance standpoint, a static list is always going to have better performance, but it will really be hard to discern the performance gained over the other the list method.

The real question goes to maintainability of your app.  What happens when the list needs to change?  Someone needs to go into the app and make the changes.  It is much more preferable to have the list outside of the app so that it can be changed more easily.  

However, the choice is really dependent on the data rather than a preference.  For example, if you have a list of months of the year, you can pretty much assure that it will not change and that you can just write it into the Items formula rather than a list.  If you have a list of items that "might" change in years to come...this is the worst because that means when it does change, yourself or someone else needs to figure it out again where to change it.  And of course, if it changes often, then the list is absolutely the best.

[tonykiefer]

Hi Randy,

Thanks for your help with this issue.  I ran into a situation where there will be a lot of items in some of the dropdown boxes.  To make life easier for the end-user I am going to replace it with a combo box.  Combo Boxes are searchable 🙂

For the dropdown box the Default property lookup is...

"LookUp(USR_AX_ITEM_TYPE, USR_ITEM_TYPE=Parent.Default, USR_ITEM_DESC)"

For the combo box the DefaultSelectedItems lookup is the same...

"LookUp(USR_AX_ITEM_TYPE, USR_ITEM_TYPE=Parent.Default, USR_ITEM_DESC)" however this doesn't work.

The combo box setup is...

It seems very peculiar to me that the lookup used for the dropdown does not work for the combo box...

It looks like I have the combo box properties set up correctly.  Any thoughts?

[RandyHayes]

@tonykiefer 

Comboboxes are way different than Dropdowns. 

For a combobox, you MUST provide a record or table (if you have multi-select on).  That record MUST match the exact schema of your Items property and contain the values that you want selected.  Simply providing a text value will not do anything (as you are seeing).

Change your formula to the following in the DefaultSelectedItems property:

    LookUp(USR_AX_ITEM_TYPE, USR_ITEM_TYPE=Parent.Default)

As long as you keep your Items property as USR_AX_ITEM_TYPE then this lookup will return the matching record from the datasource based on the Parent.Default.  AND, that record will match the schema of the Items property and you should have your matched and selected item.

[tonykiefer]

Hi Randy,

The table("USR_PRODUCT_TYPE") setup for this combo box is...

The value for USR_PRODUCT_TYPE is saved in the main table as the USR_CODE but it is the USR_ITEM_DESC that needs to be visible for the end user.

Previously I tried this...

LookUp(USR_PRODUCT_TYPE,USR_CODE=Parent.Default)

and it returns the USR_CODE.  Which is fine...but sometimes the user code isn't verbose.

I have to use the USR_CODE as it matches parent.default, in order to return the USR_DESCRIPTION.