Forum Discussion

JamesJames's avatar
JamesJames
Qrew Cadet
9 years ago

Help with multiple condition IF formula

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 

Formulas and functions
Tables and fields
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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")

  • JamesJames's avatar
    JamesJames
    Qrew Cadet
    9 years ago
    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")
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    9 years ago
    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.
  • olivermah's avatar
    olivermah
    Qrew Assistant Captain
    8 years ago
    what if I wanted the record to change color?  for example, if the expiry date has past, record turns gray...