Skip to main content

Dear community,


can two unrelated conditions live in the same formula? Do they need to connected by the same first argument?


I want "Sample Availability to read “Shop only” when there is no value in “SignOutIcon”



In writing it would be:


If SignOutIcon contains no value, return “Shop only”. If SignOutDate contains a value and ReturnDate contains no value, return “not in stock”, otherwise “available”



With first argument the same, it would be


If SignOutIcon contains no value, return “Shop only”. If SignOutIcon contains a value and SignOutDate contains a value and ReturnDate contains no value, return “not in stock”, otherwise “available”



I used “&” but this returned a mess:


IF(AND({SignOutDate},NOT({SignOutComment})),“not in stock”,“available”) & IF(NOT({SignOutIcon}), “Shop only”, “available”)




Could anyone give me a hint? Any help highly appreciated.


Best, Sisi


^that written as a formula would be:


IF(

   NOT({SignOutIcon}),

   "Shop only",

   IF(

      AND({SignOutIcon}, {SignOutDate}, NOT({ReturnDate})),

      "not in stock",

      "available"

   )

)

^that written as a formula would be:


IF(

   NOT({SignOutIcon}),

   "Shop only",

   IF(

      AND({SignOutIcon}, {SignOutDate}, NOT({ReturnDate})),

      "not in stock",

      "available"

   )

)



This is a great example of how to write a formula! First clarify the logic in English (or whatever your native language is), then translate it.



If SignOutIcon contains no value, return “Shop only”.





  • If translates into the IF function.


  • SignOutIcon contains no value translates into NOT({SignOutIcon})


  • return “Shop only” translates in to the second parameter of the IF function.



If SignOutIcon contains a value and SignOutDate contains a value and ReturnDate contains no value,




  • This If translates into a nested IF function.

  • The and translates into the AND function.


  • contains a value translates into the field name itself (except for numbers that might be zero)


  • return “not in stock”, otherwise “available” translate into the second and third parameters of the nested IF function.


The rest is dealing with pesky grammar of making sure that you have parenthesis and commas in the proper places.



This is a great example of how to write a formula! First clarify the logic in English (or whatever your native language is), then translate it.



If SignOutIcon contains no value, return “Shop only”.





  • If translates into the IF function.


  • SignOutIcon contains no value translates into NOT({SignOutIcon})


  • return “Shop only” translates in to the second parameter of the IF function.



If SignOutIcon contains a value and SignOutDate contains a value and ReturnDate contains no value,




  • This If translates into a nested IF function.

  • The and translates into the AND function.


  • contains a value translates into the field name itself (except for numbers that might be zero)


  • return “not in stock”, otherwise “available” translate into the second and third parameters of the nested IF function.


The rest is dealing with pesky grammar of making sure that you have parenthesis and commas in the proper places.



Thank you Kuovonne, this will definitely help me the next time I am trying to write a formula. Much appreciated!


Thank you Kuovonne, this will definitely help me the next time I am trying to write a formula. Much appreciated!


Dear Kamille,


thank you very much for your help. It all makes very much sense to me - only airtable replies that it is an invalid formula when I paste it in. I have already tried removing blanks and replacing quotation marks but without success. Any hints that could help me fix this would be highly appreciated.


Thank you!

Dear Kamille,


thank you very much for your help. It all makes very much sense to me - only airtable replies that it is an invalid formula when I paste it in. I have already tried removing blanks and replacing quotation marks but without success. Any hints that could help me fix this would be highly appreciated.


Thank you!



There is a missing comma at the end of the second line.


There is a missing comma at the end of the second line.


Good catch. I edited my formula to reflect that.

Good catch. I edited my formula to reflect that.


Thank you both so much. Now it worked, fantastic!

Reply


Terms & Conditions