Select a column row value based on unique value in both the tables

[madhukarb]

Hi ,

I have 2 tables Raw Data and Process List. Based on 3 columns values of  Raw Data, i need a column value of  Process List tabel. I used SELECT(Master Process List[Site Director], AND([SITE]= [GOC Site Details],
[PODS]=[POD],[Sub POD]=[Sub Pods]),TRUE). This formula works fine when i am using only 2 conditions it doesn't work when i am using more than 3 conditions.

[MultiTech]

Appster says.....

______________________________________________________________

Hello,

It seems you're encountering an issue with the SELECT() function in AppSheet when you introduce more than two conditions. This can sometimes happen if there's a slight mistake in the syntax or if there's an issue with the data in the columns you're trying to match against each other. Let's work on addressing this issue.

First off, let's ensure the syntax for the SELECT() function is correct. The correct usage of your formula with three conditions should look like this:

SELECT([Process List][Site Director], AND(
  [SITE]=[_THISROW].[GOC Site Details],
  [PODS]=[_THISROW].[POD],
  [Sub POD]=[_THISROW].[Sub Pods]
), TRUE)

Notice that I have added [_THISROW] before the column names from the 'Raw Data' table.

This is to explicitly refer to the row in 'Raw Data' from which AppSheet should compare the values in 'Process List'. Often, without this distinction, AppSheet might not understand which table's rows to compare against.

If you've already verified that the formula has the correct syntax, then the next step is to ensure that the data is consistent and properly matched between the two tables. Here's a quick checklist:

1. Confirm that data types for the columns [SITE], [GOC Site Details], [PODS], [POD], [Sub POD], and [Sub Pods] are consistent between the two tables. For example, if [SITE] is of type Text in 'Raw Data', [GOC Site Details] should also be of type Text in 'Process List'.
   
2. Verify that the values in the columns from both tables exactly match. Any difference, even if it's just a matter of text case or additional spaces, can cause the condition to fail.
   
3. Check if all rows in 'Process List' have unique combinations of [SITE], [PODS], and [Sub POD]. If there are duplicates, SELECT() might be returning multiple 'Site Director' values where you only want one.

If you've gone through these steps and the issue persists, it may be time to review the structure of your data or consider a different approach. Instead of using a SELECT() function, which is computation-heavy, you might make use of slices or virtual columns to efficiently link data between tables.

Please try the suggestions and let me know if any of these resolve the issue. If you're still having trouble, don't hesitate to submit a question directly to Matt in the Answer Portal for further assistance.

Best regards,
Appster

________________________________________________________________

Use the free light version of Appster here
Access the full version here

[hien_nguyen]

Wow @madhukarb ,

I am very surprised if you are a staff of Google but You don't know how to write the correct syntax in the function Select().

Your Expression must add [_Thisrow]. for the second column in the condition of Select function

SELECT(
Master Process List[Site Director], 
AND([SITE]= [_Thisrow].[GOC Site Details],
[PODS]= [_Thisrow].[POD],
[Sub POD]= [_Thisrow].[Sub Pods]),
TRUE)

Regards,

Hien Nguyen

---