Skip to main content
Solved

cast single-select field as string

  • October 21, 2023
  • 6 replies
  • 155 views
E
Forum|alt.badge.img+13

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!

Best answer by Eli_Kent

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.

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.

1 Attachments

6 replies

Alexey_Gusev
Forum|alt.badge.img+25

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

E
Forum|alt.badge.img+13
  • Eli_Kent
  • Author
  • Inspiring
  • October 21, 2023

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

Alexey_Gusev
Forum|alt.badge.img+25

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), '-')

 

E
Forum|alt.badge.img+13
  • Eli_Kent
  • Author
  • Inspiring
  • October 23, 2023

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.

E
Forum|alt.badge.img+13
  • Eli_Kent
  • Author
  • Inspiring
  • Answer
  • October 25, 2023

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.

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.

Alexey_Gusev
Alexey_Gusev
Forum|alt.badge.img+25

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.

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:

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)

E
Terms & ConditionsAccessibility statement