Discussions

Expand all | Collapse all

Help with multiple condition IF formula

  • 1.  Help with multiple condition IF formula

    Posted 10-03-2016 16:34

    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 



  • 2.  RE: Help with multiple condition IF formula

    Posted 10-03-2016 17:43
    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")



  • 3.  RE: Help with multiple condition IF formula

    Posted 10-06-2016 14:30
    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")


  • 4.  RE: Help with multiple condition IF formula

    Posted 10-06-2016 14:39
    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.


  • 5.  RE: Help with multiple condition IF formula

    Posted 10-31-2016 14:54
    Thank you now working really well


  • 6.  RE: Help with multiple condition IF formula

    Posted 10-31-2016 15:01
    Thx for letting me know!


  • 7.  RE: Help with multiple condition IF formula

    Posted 05-31-2017 22:36
    what if I wanted the record to change color?  for example, if the expiry date has past, record turns gray...