Using a Form connected to 1 table but needing to display description from another table

[ACrow]

Hello,

I've got a Form connected to a table in a Canvas App. This table only has numerical data. 

I've got a second table which has the number, description and Variable.

I'd like for the dropdown which is an allowed values to display the number and description from the second table based off the Variable column. Is this possible?

Table 1: 

 The form is displaying this field:

Instype

8

Table 2:

Number	Description	Variable
1	Medicaid	Insurance Types
2	Medicaid Managed Care	Insurance Types
3	Medicare	Insurance Types
7	Other	Insurance Types
8	None	Insurance Types
99	No Response	Insurance Types
0	No School	Precollege
1	1st Grade	Precollege
2	2nd Grade	Precollege
3	3rd Grade	Precollege

 In this case I'd like the dropdown field to display: 8 None

[BCBuizer]

HI @ACrow ,

Try setting the Items property of the ComboBox to 'Table 2', the DisplayFields property to ["Description"] and the Update property of the DataCard to DataCardValueX.Selected.Number (where X needs to be replaced with the right number to match the name of the ComboBox).

[ACrow]

This got me 1 step closer...TY! However I'm getting an error and too many records 🙂

This is my code...

Items: ForAll(Distinct(CODES,Number & Description),{Result: ThisRecord.Value})

Update: DataCardValue102_1.Selected.Result 

I couldn't use Number like you suggested as it didn't recognize it so I used Result.

1. I get an error that The Value '8 None' cannot be converted to a number.

2. It is now displaying all the data from Table 2. I need it to only display the data from the Variable (Insurance Type). 

Your help is really appreciated!

[BCBuizer]

Hi @ACrow ,

May I ask why you are using the Distinct function? This strips exactly the data that we're trying to retrieve from CODES ('Table 2') and indeed it returns different column names.

Further more, because you are using the string concatenation operator (&), the value you are trying to save to the data source no longer matches the required type (text instead of number).

In case you wish to display a concatenation of the Number and Description columns to the user, set the Items property of DataCardValue102_1 to the below. This also includes a filter to only show items wher the Variable equals "Insurance Types":

AddColumns(
   Filter(
      CODES,
      Variable = "Insurance Types"
   ),
   "DisplayCodes",
   Number & " - " & Description
)

Set the DisplayFields and SearchFields properties to:

["DisplayCodes"]

Finally, set the Update property of the datacard to:

DataCardValue102_1.Selected.Number

[ACrow]

I had been using the Distinct because of all the duplicate answers. 

OK, I've made all the changes you suggested and I no longer receive the errors. However, the data is showing

1-Medicaid which is the first in the list but I need it to read (when in View and Edit mode) the 8-None which is the data from Table 1.  Thanks again for your help!

[BCBuizer]

Hi @ACrow ,

So the value saved to the data source should always be 8? Or should that be the default selected option?

[ACrow]

The value changes depending on what they select. Its an allowed values field so they have to chose from 10 choices and when in view their choice should show up and when entering New all the choices should be available as well as when in Edit. 

[BCBuizer]

Hi @ACrow ,

Got it.

Set the DefaultSelectedItems of DataCardValue102_1 to:

{Number: Parent.Default}

[ACrow]

Hi!

Is this the DefaultSelectedItems:

Error reads: Expected Text Value

Thanks again...I really appreciate your patience and help.

[BCBuizer]

Hi @ACrow ,

Alright, then let's try this instead for the DefaultSelectedItems property:

Parent.Default & " - " & LookUp(CODES, Number = Parent.Default)

It should replicate the text values.