Multicondition select / join

AppSheet AppSheet Q&A
,
1

Hello.

My goal is to create a virtual column that contains a value from another table: DWC Accounts W Products[Account ID]

Below you can see that I’m saying “If secondary account = true” (Which means the OEM is Cadillac), return account ID where DWC Accounts W Products[BAC Code] = ThisRow BAC AND DWC Accounts W Products[OEM]=“Cadillac” AND DI DWC Accounts W Products[GM Product Name] = thisRow product name. If secondary account = false, execute the same statement, but OEM = “GM” Essentially, I’m trying to join based on a 3 condition select.

When I run the below, I get Cannot compare List with Text in (DI DWC Accounts W Products[BAC Code] = [_THISROW].[bac])

Here is my current Code

IF([is Secondary Acct]=TRUE,
    SELECT(
            DI DWC Accounts W Products[Account ID],
                AND(
                     DI DWC Accounts W Products[BAC Code]=[_THISROW].[bac],DI DWC Accounts W Products[OEM]="Cadillac",DI DWC Accounts W Products[GM Product Name]=[_THISROW].[product name]
                    )
          ),
    SELECT(
            DI DWC Accounts W Products[Account ID],
                 AND(
                     DI DWC Accounts W Products[BAC Code]=[_THISROW].[bac],DI DWC Accounts W Products[OEM]="GM",DI DWC Accounts W Products[GM Product Name]=[_THISROW].[product name]
                     )
           )
)
2

SELECT() - AppSheet Help

3

Can you point me in the right dfirecton of what I should do there?

4

@Steve I tried using the distinct only part of select, but i still get compare list with number err

IF([is Secondary Acct]=TRUE,
    SELECT(
            DI DWC Accounts W  Products[Account ID],
                AND(
                     DI DWC Accounts W  Products[BAC Code]=[_THISROW].[bac],
                     DI DWC Accounts W  Products[OEM]="Cadillac",
                     DI DWC Accounts W  Products[GM Product Name]=[_THISROW].[product name]
                    ),TRUE
          ),
    SELECT(
            DI DWC Accounts W  Products[Account ID],
                 AND(
                     DI DWC Accounts W  Products[BAC Code]=[_THISROW].[bac],
                     DI DWC Accounts W Products[OEM]="GM",
                     DI DWC Accounts W Products[GM Product Name]=[_THISROW].[product name]
                     ),TRUE
           )
)
5

Construct a list from table-column references - AppSheet Help

1 Like
6

Column value expressions - AppSheet Help

1 Like
7

Unfortunately, I’m still not sure how to proceed with this.

8 
IF([is Secondary Acct]=TRUE,
    SELECT(
            DI DWC Accounts W  Products[Account ID],
                AND(
                     DI DWC Accounts W  Products[BAC Code]=([_THISROW]=[bac]),
                     DI DWC Accounts W  Products[OEM]="Cadillac",
                     DI DWC Accounts W  Products[GM Product Name]=([_THISROW]=[product name])
                    )
          ),
    SELECT(
            DI DWC Accounts W  Products[Account ID],
                 AND(
                     DI DWC Accounts W  Products[BAC Code]=([_THISROW]=[bac]),
                     DI DWC Accounts W Products[OEM]="GM",
                     DI DWC Accounts W Products[GM Product Name]=([_THISROW]=[product name])
                     )
           )
)

I realized I had my syntax incorrect, but this now returns: Cannot compare List with Yes/No in (DI DWC Accounts W Products[BAC Code] = ([_THISROW] = [bac]))

9

Read the help docs I provided.