Help

cast single-select field as string

Topic Labels: Formulas
Solved
Jump to Solution
3012 6
cancel
Showing results for 
Search instead for 
Did you mean: 
Eli_Kent
7 - App Architect
7 - App Architect

Hi community.

I have a single-select field called Sector in a table called Projects. I'm trying to cast this field to a string.

The reason I'm trying to do this is that I'm trying to build an automation, in which I will need a conditional step that will check this stringified single-select field to a multi-select field in another table to see if the multi-select field contains the the selected value in the single-select field.

Thus far, I've created a formula field in Projects with various formulas (or formulae, for the sticklers out there), including:

- ARRAYJOIN(Sector)

-Sector

-LEFT(Sector)

No matter what, when I go to the automation it won't let me choose the field because AT says "Cannot assign list of string to a string".

Please help!!!

Thank you!

1 Solution

Accepted Solutions
Eli_Kent
7 - App Architect
7 - App Architect

I was able to make it work by, in the conditional logic step of the automation, choosing the multi-select contractor[Sector] field and then "flatten", and checking if it contains the single-select project[Sector] field. It also worked when I compared contractor[Sector] to the formula field project[Sector_String}. It turned out not to matter if the formula was =Sector or =Concat(Sector).

Here's what the conditional step looks like.

Eli_Kent_0-1698194731274.png

In the end, this prodded me to learn enough scripting to write a script that does this job for me, which really was the best case scenario, anyway.

I appreciate your help, @Alexey_Gusev.

See Solution in Thread

6 Replies 6

Hi,
you think in a right direction, try to use operator that converts all arguments to strings and adds them,  CONCATENATE(Sector)
also, you can just add it to empty string: (attention, that's not a double quote, that's two single quotes, beginning and end of a string):
    '' & Sector

Hey @Alexey_Gusev . thanks for chiming in. unfortunately, when i did that, i got the same results. couldn't select it because "Cannot assign list of string to a string". frustrating. seems like it shouldn't be this difficult.

I'm trying to learn enough scripting now to be able to script it. i'm vaguely familiar with python, so the learning curve isn't too terrible. the record.getCellValueAsString function should come in handy

Sorry, I didn't notice that you compare with a multi-select field. The value of multi-select field is array of strings (or list of strings, as it called in Python, if I'm not mistaken). I tried to test your scenario and was able to compare, and make it work, when I add formula field with CONCATENATE in a table containing multi-select field, to convert a list to a string.
Regarding Sector field, you can compare it directly, but it might produce error for empty values. Here, you should decide. The condition 'Y contains X' is always true when X is empty, so if you want the opposite, find nothing when Sector value is empty, you should adjust the formula with 'emptiness check',  like:

IF(Sector, CONCATENATE(Sector), '-')

 

Hi again Alexey.

Hmmmm... I tried that, but still didn't work. As soon as I have time, I'll breakdown what I'm doing step by step and send your way.

Eli_Kent
7 - App Architect
7 - App Architect

I was able to make it work by, in the conditional logic step of the automation, choosing the multi-select contractor[Sector] field and then "flatten", and checking if it contains the single-select project[Sector] field. It also worked when I compared contractor[Sector] to the formula field project[Sector_String}. It turned out not to matter if the formula was =Sector or =Concat(Sector).

Here's what the conditional step looks like.

Eli_Kent_0-1698194731274.png

In the end, this prodded me to learn enough scripting to write a script that does this job for me, which really was the best case scenario, anyway.

I appreciate your help, @Alexey_Gusev.

I can say that Airtable is a perfect sandbox to learn JS. I learned its basics here having Pascal/VBA background many years ago.
you can start here , also check available examples and refer to API:

Alexey_Gusev_0-1697994200230.png

In general, you need to understand arrays, objects and very basics of async/await

Since JS received important new functions with ES6, you can learn some things to make coding much easier:  
array-helpers, arrow-functions. (optional: destructuring assignment)