Lookup multiple columns in another table | Return single value

[WalterWong]

Hi

I searched Appsheet Q&A and tried to follow some solutions without success. Sorry to trouble again.

Source table has the following columns and no Ref columns,

(A) Source[Submit ID]                Text field type | Key | Initial Value: UNIQUEID()

(B) Source[Name]                       Enum field type    /*** data selected by user ***/

(C) Source[OrderNumber]         Text field type      /*** data entered by user ***/

(D) Source[Type]                         Text field type      /*** computed by formula ***/

(E) Source[SubType]                  Text field type      /***computed by formula ***/

(F) Source[Design]                      Enum field type    /***Suggested value: SomeTable[All_design_codes] ***/

Target table has the following columns and no Ref columns,

(G) [Target]RecordID                   Text field type | Key | Initial Value: UNIQUEID()

(H) Target[Name]                        Text field type      /*** reference data ***/

(I) Target[Type]                            Text field type      /*** reference data ***/

(J) Target[SubType]                    Text field type      /*** reference data ***/

(K) Target[Design]                       Text field type      /*** a design code from SomeTable[All_design_codes] ***/

I want to search Target table (H)(I)(J), to find the matching row against Source table (B)(D)(E), return (K) into the initial value of (F) --- to pre-select a value in (F).

I tried to use below expression without success. Nothing return to (F) Source[Design] - enum column type.

ANY(

      SELECT( Target[Design],

            AND (

           [Name] = [_THISROW].[Name],
           [Type] = [_THISROW].[Type],
           [Sub Type] = [_THISROW].[Sub Type]

           ),

      TRUE)
)

Thanks,

[Joseph_Seddik]

Hello,

Your (F) column's suggested values field expects a list, not a single value. Remove ANY() from your expression.

[WalterWong]

Hi @Joseph_Seddik 

Thanks for your reply.

I tried your suggestion. I got this error message.

"The type of the Initial Value does not match the column type."

(F) Source[Design] is Enum field type....when i remove ANY(), it causes the above error.

Please advise.

[WalterWong]

Hi @Joseph_Seddik 

If this helps...screenshot from real data.

[Joseph_Seddik · Accepted solution]

Hi Walter,

The expression is for the suggested values field, as I read it.

---

@WalterWong wrote:

(F) Source[Design]  Enum field type  /***Suggested value: SomeTable[All_design_codes] ***/

If you want to put it in the Initial value, then I don't see anything wrong in your current expression, as far as I can see. If it is not giving you the expected result, then you might want to check if the corresponding values to be matched duly exist in the two tables. See what the expression assistant is giving you.

[WalterWong]

Hi @Joseph_Seddik 

It works today. Somehow changes to expressions was not updating in the app. 

For the benefit of others, this is what I did 

- create a form from Source_table

- populate an Enum field in the form with values from Some_table

- pre-selected an option in the Enum field by matching user input from 3 columns in the form with a Lookup_table

1. Enum Field Suggested Value

    = Some_table[List_of_Reference_Values_for_dropdown]   

    /*** This populates the dropdown list with all reference values ***/

2. Enum Field Initial Value = 

    ANY(

          SELECT( Lookup_table [Target_column_to_be_returned],

                AND (

                [Source_table_column_1] = [_THISROW].[Matching_column_X_ in_Lookup_table],
                [Source_table_column_2] = [_THISROW].[Matching_column_Y_in_Lookup_table],
                [Source_table_column_3] = [_THISROW].[Matching_column_Z_in_Lookup_table]

                ),

           TRUE)
    )

   /*** This finds the matching row in Lookup_table and returns a target value as the pre-selected value in the Enum field ***/

Hope this helps the next person, as I believe this is a fairly common use case.

Thanks!