Help with multiple condition IF formula

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered

Hi,

I am trying to create a formula field  for a multiple leveled IF question so I can automatically set the status of an instrument in regards to when it should be returned. The bellow Table is a brake down of the different status and the conditions I am trying to use for these statuses. The areas in italics are existing fields.

Install Date "" + Serial Number "" = Requested

Install Date "" + Serial Number "xxxxxxxx" = Assigned

Install Date "Grater Than Today" + Contract End Date "> 2 weeks in the future" = Installed

Contract End Date  "<2 weeks in the Future" + Returned Date "" = Expiring

Contract End Date "Between current Date > 2 weeks in the future" + Returned Date ""= Book Collection

Returned Date "has Date" = Returned

PO "XXXXXXx" = Sold

Contract End Date "beyond 2 weeks ago" + Returned Date "" + PO "" = Over due

I know this should be possible by combining IF and ISNULL functions but not sure of the Syntax to start 

Photo of James

James

  • 172 Points 100 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
to check if most fields are blank, you can use IsNull (  ).  But for text fields you need to use a check to see if its equal to "", ie quotes with nothing in them.

I think this will give you an idea of the Syntax.

IF(


Isnull([Install date]) and Trim([Serial number)="", "Requested",

IsNull([Install Date]) and Trim([Serial Number]) <>"", "Assigned",


[Install Date] > Today() and [Contract End Date] > Today() + Days(14), "Installed",

[Contract End Date]   < today() + days(14) and Isnull([Returned Date]), "Expiring",

[Contract End Date] >Today() and [Contract date]< today() + days(14) and IsNull([Returned Date]), "Book Collection",

not IsNull([Returned Date]), "Returned",

Trim([PO])<>"", "Sold",



[Contract End Date] > Today()-Days(14) and isNull(Returned Date]) and Trim([PO])="", "Over due")

Photo of James

James

  • 172 Points 100 badge 2x thumb
Thank you and it is starting to work.

I made some changes to the formula but not quote there on some of the variants and i cant work out why;

I am not seeing any "Installed" as these are showing as "expiring".

"Book collection" is before the expiry date not on or after Company Owned end date passes.It is also showing the "Book Colection period as "Over Due"

Any Ideas? I have pasted the latest version of the formula for you below. The returned and PO is working really well.


If(

IsNull([Install Date]) and Trim([Serial Number])="", "Requested", IsNull([Install Date]) and Trim([Serial Number]) <>"", "Assigned",

[Install Date] > Today() and [Company Owned End] < Today() - Days(14), "Installed",

[Company Owned End]   > Today() + Days(14) and IsNull([Return Date]), "Expiring",

[Company Owned End] >Today() and [Company Owned End]< Today() + Days(14) and IsNull([Return Date]), "Book Collection",

not IsNull([Return Date]), "Returned",

Trim([PO])<>"", "Sold",

[Company Owned End] < Today()+ Days(14) and IsNull([Return Date]) and Trim([PO])="", "Over due")
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
My advice is the following.

The IF statement will process the tests in the sequence they are listed, and it will return the first one that is true.  So, for any which are not calculating correctly, you need to just deal with them systematically one by one.  Choose the first example that you want to fix.  Take note of the values in the fields on he record that is incorrect.  Manually do each test and see what the formula is calculating to.  You may find that the sequence of the tests need to be changed.

For me to help you with an example of one that is incorrect, I will need to know what is in each field which is being tested, as I cannot see your data.
 
There is no shortcut to solving the issues one by one. Start with the first one that is not correct, and get that 1 working, and then move on to the next one.
Photo of James

James

  • 172 Points 100 badge 2x thumb
Thank you now working really well
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 59,768 Points 50k badge 2x thumb
Thx for letting me know!
Photo of oliver mah

oliver mah

  • 870 Points 500 badge 2x thumb
what if I wanted the record to change color?  for example, if the expiry date has past, record turns gray...