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”.
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
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”.
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.
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.
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:
&“” uses an undocumented, implicit functionality, unless the Seatable programmers tell my that this is by design. I’d rather use documented, explicit methods.
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.