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,