what is wrong with this scenario?

[helenyeliu]

HI all,I am using LOOKUP("abc","CLEANESGLeaders","Company Name","Sector") to retrieve sector when  the company name in Table CLEANESGLeaders is equal to abc, LOOKUP function should return TEXT data type. right? I set column data type to TEXT. but I got the following violation,

Column Type 'Text' has an invalid data validation constraint '="text value"'.

Could someone tell me what is wrong with this?

THanks,

Helen

[Aurelien]

Hi @helenyeliu 

Can you check the "Data validation" section and the "Computed value" section in your column settings ?

You may have an unnecessary content due to table generation.

Also, I suspect the column "Sector" is not a type Text in the table CLEANESGLeaders. Could it be possible ?

[helenyeliu]

Thank you, Aurelien.  The computed value section is blank. On googlesheet, Sector column only has text input. 

Helen

[Aurelien]

can you share a screenshot of the error, and what is around ? it may help

[helenyeliu]

Thank you, Aurelien,

Here is the screenshot.

[Steve]

Why do you have that expression in Valid If? I suspect it would be more appropriate in Initial value.

Define App formulas and Initial values - AppSheet Help

Check form input validity (Valid_If) - AppSheet Help

[helenyeliu]

Thank you, Steve.

I would like to display sector value when the company is "abc", let me check your suggestion. thanks!

Helen

[Aurelien]

Hi @helenyeliu 

I think there is a little confusion about how this works 🙂

FIRST:

Let's say you have a table of companies. You mentionned earlier CLEANESGLeaders.

This one has columns key (type Text),  company name (type Text), sector (type Text).

THEN:

Let's say you have another table TEST, and you wish to retrieve the sector of a company picked from here.

In this table TEST, you will have columns:

- key (type Text),

- company (type Ref, and Source Table: CLEANESGLeaders)

- sector (type Text)

MAGIC 1:

Now, you wish to retrieve the sector with an expression. This will be, in the sector formula field:

[company].[Sector]

This way, every sector will be automatically calculated for each company.

Please note: using a virtual column instead of a real column may be more relevant here.

Use virtual columns 

MAGIC 2:

Now, you wish to display sector value only when the company is "abc", then will need to type in the Show_If condition of the sector column, as shown below, the expression:

[Company]="abc"

This way, the column sector will be displayed only if company = "abc".

Please note: "abc" is here the id value of the company name you wish to display. As shown below, this refers to the key-column of the table. In my example, this id value is named "key".

For reference, about key_columns:

Data: The Essentials 

Columns: The Essentials 

[helenyeliu]

HI Aurelien,

Magic 2 is more relevant to my case, let me try it out. Thanks so much for your suggestion. 

Thanks,

Helen

[helenyeliu]

HI Aurelien,

  The screenshot I gave you is simplified, which might be misleading.

what I want to do is that 

In table Test, 

I have key_test (Text)

           company_test (text)

          Sector_test(text)

another table CLEANESGLeaders

  key (text)

  company name(text)

  sector (text)

what I want to do is that

  when company_test ( single value as input) matches one of "company name" from CLEANESGLeaders, and assign its sector to the sector_test.

for column sector_test, I set data type to ref, the ref table is CLEANESGLeaders

in show, I want to check [CLEANESGLeaders].[Company Name] = company_test

in Auto Compute, set formula  [CLEANESGLeaders].[Sector]

but I got the complaint, saying [CLEANESGLeaders] column is not found, what could be wrong?

Is this correct setup?

Thanks much!

Helen

[Aurelien · Accepted solution]

Error loks normal to me, according to your description. 

Can you try:

==> company_test: Type Text

==> sector_test:

- Type Text

- Formula expression: 

LOOKUP([Company Name], "CLEANESGLeaders", "company name", "sector ")

- Show_If expression: 

IN([Company Name], CLEANESGLeaders[Company Name])

Please note:

[CLEANESGLeaders].[Sector] could not work. CLEANESGLeaders is the table name. The square brackets are for column names. As it is, it's looking for a column in your table Test, that would be named "CLEANESGLeaders"...which is not.

Generally, this kind of structure: [columnNameA].[columnName2] means:

- columnNameA is of type Ref, and refers to another table TABLE2

- columnName2 is a column in the table TABLE2.

I encourage you to read these to better understand:

References between tables 

Dereference expressions 

[helenyeliu]

Hi Aurelien,

  Thanks so much, it works.

Helen