Why is this formula not working

SeaTable Cloud enterprise

Am using a formula to add gender to a column:
ifs( {Anrede P1} = “Gospodine”, “muško”, {Anrede P1} = “Gospođo”, “žensko”)
I am getting a #N/A error instead of the result. What is wrong with my formula? (It is not the " " because I checked them before implementing)

Second issue with the same formular
By the way, if I ask it to check the type, instead of “string” it comes back as “number”

Extra info:
The logic is that the column “Anrede P1” will contain either Gospodine or Gospođo.
If it contains Gospodine, then in the “Gender” column (where the formula is) it will return “muško”.
If it contains Gospođo, then in the “Gender” column it will return “žensko”.

For clarity, the language is Bosnian. Gospodine = Mr., Gospođo = Mrs. and muško = male, and žensko = female. So, if “Anrede P1” = Mr, then in the Gender column insert “male”.

1 Like

Try ’ instead of "

You are missing an “else” clause. If none of your conditions apply, #N/A appears.

My way of handling this (using new lines in formulas is good practice IMO):

ifs(
{Anrede P1} = “Gospodine”, “muško”,
{Anrede P1} = “Gospođo”, “žensko”),
True(),"Empty string or whatever should appear"
)

Note that True() is a function returning a Boolean. Otherwise, you get a different error.

Oh yeah, and you are using totally strange typographic double quotes, so the above will not work with copy & paste. You can actually see the difference when you use the code markup in your forum post. This version should work:

ifs(
{Anrede P1} = "Gospodine", "muško",
{Anrede P1} = "Gospođo", "žensko"),
True(),"Empty string or whatever should appear"
)

But the quotes were only part of your problem - I hope

Hi, that made no difference

Hi,

The logic is that the column “Andrede P1” will contain either Gospodine or Gospođo.
If it contains Gospodine, then in the “Gender” column (where the formula is) it will return “muško”.
If it contains Gospođo, then in the “Gender” column it will return “žensko”.

For clarity, the language is Bosnian. Gospodine = Mr., Gospođo = Mrs. and muško = male, and žensko = female. So, if “Anrede P1” = Mr, then in the Gender column insert “male”.

What I have noticed is that the column with the formula is detected as number and not as text. Something in the formula is causing this.

add

&""

this will make the column format to text

i use it for my formulas and it works
for example

ifs({column name}="text",{column name}="text" )&""

Hi Hossein,

Your solution worked. The column type was redetected as a string and now works. Thanks so much!

1 Like

Woops, sorry, I had an extra bracket included:

ifs(
{Anrede P1} = "Gospodine", "muško",
{Anrede P1} = "Gospođo", "žensko",
True(),"Empty string or whatever should appear"
)
1 Like

The effect is actually the same as with the True() “else” solution: Both make sure that the result is always a string. I’ll stick with the explicit “else” condition with True(), because that feels less like a hack.

Hi Abaer, Thanks for your correction. But the real issue was the fact that the column was being recognised as a number and not as a text string (plus I also made a mistake with “Gospodine” - it should have been “Gospodin”). @Hossein 's additional use of &“” caused SeaTable to recognise the column correctly.

1 Like

Not wanting to have the last world or be right (because both solutions work), I’ll just add the knowledge I acquired in long, frustrating sessions to prevent others from the same fate in more complex scenarios:

Both methods make sure that the column only contains values of one type (String). How I understand it, the format detection takes the data in the first row it encounters. I just made a quick test with a formula that creates number, date, boolean and string values.

ifs(
{Name}="A",True(),
{Name}="B",12,
{Name}="C","Hello world",
{Name}="D",today()
)

Try putting this formula column into a new table, create 5 lines, and put “A” to “E” into the “Name” column. Just by re-sorting the rows (pull one of them to the #1 position), you can get Seatable to recognize different data types for that column - and create weird effects on the other types. And it still produces an error for the entry “E”.

So while both methods make sure that the value is always a string and guarantee string detection regardless of row order and content, my personal learnings from this are:

  1. &“” uses an undocumented, implicit functionality, unless the Seatable programmers tell my that this is by design. I’d rather use documented, explicit methods.
  2. It works only (?) with strings. As soon as you need to make sure that the column only contains numbers or dates, you might come back to this discussion.
1 Like

Good idea. It is very important that this information is now available to be found when others need it.

1 Like

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.