Discussions

Expand all | Collapse all

How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

  • 1.  How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-04-2017 05:13
    I am using row colorization formulas, mostly based on a date/time field, which works. However, I need to add a condition regarding a text field, which would need to override the colorization rule based on the date formulas. I have attempted to create a line item for each date formula combined with the text formula. It doesn't seem to be working. Here is what I have so far:

    If(
    not IsNull([Time Complete]), "",
    ([ETA:])>Now(),"#08ef08", 
    ([ETA:])<Now() and ToDays([ETA:]-Now())>-1, "#f9fc03",
    ToDays([ETA:]-Now())<0,"#fd3110",
    IsNull([ETA:]),"#00f6ff",
    (Trim([Van Driver]))<>"" and ([ETA:])>Now(),"#00f6ff")

    The last line is the only one not working.


  • 2.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-04-2017 11:13
    Are your conditions listed in their priority?
    The IF statement evaluates the tests in the order they are liste and stops at the first one which is true.


  • 3.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-04-2017 16:28
    I just moved it around, and it still isn't showing the blank value in blue over the ETA condition in green.


  • 4.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-04-2017 22:19
    Can you post you current formula, and tell me which line of the IF is not triggering and what the values are in each of the fields in that line?


  • 5.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-04-2017 22:20
    Ie, for an example Record which is not working.


  • 6.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-04-2017 22:25
    I started to get closer. I changed to this, but now the blue is overriding the green:
    If(
    not IsNull([Time Complete]), "",
    (Trim([Van Driver]))<>"" and ([ETA:])>Now() or (([ETA:])<Now() and ToDays([ETA:]-Now())>-1) or ToDays([ETA:]-Now())<0,"#00f6ff",
    ([ETA:])>Now(),"#08ef08",
    ([ETA:])<Now() and ToDays([ETA:]-Now())>-1, "#f9fc03",
    ToDays([ETA:]-Now())<0,"#fd3110",
    IsNull([ETA:]),"#00f6ff")

    The line that was not triggering until I moved everything into one string was this one:
    (Trim([Van Driver]))<>"" and ([ETA:])>Now() 

    I was going to create a separate line for each, but then I decided to string it all together with 'or'. I am trying to make the rows green, red, or yellow based on the time in the 'ETA' field. However, I need the row to display blue if there is no ETA or if the van driver has not been assigned. I can't get the part to work to show blue when the van driver has not been assigned.


  • 7.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-04-2017 23:11
    i suggest not using the ORs, as for debugging, its best to just list the conditions and results separately.

    For the blue when the van driver is blank, is there also an eta condition too? Or is it enough to be blue when the van driver is blank. Right now you have an additional test for the eta.


  • 8.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-04-2017 23:43
    The ETA should be blank for all of the cases. Here is the formula that works before I add the condition regarding the van driver.


    If(
    not IsNull([Time Complete]), "",
    ([ETA:])>Now(),"#08ef08",
    ([ETA:])<Now() and ToDays([ETA:]-Now())>-1, "#f9fc03",
    ToDays([ETA:]-Now())<0,"#fd3110",
    IsNull([ETA:]),"#00f6ff")

    Here is how it looks. The row in blue shows that the eta is missing. I also need the blue to override the other colors if the van driver is missing.

    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1645455/RackMultipart20170904-74473-1xr7qrh-Screen_Shot_2017-09-04_at_7.39.50_PM_inline.png?1504568448">

    This is the code that I attempted to add at both the beginning and end of the script. 

    (Trim([Van Driver]))<>"" and ([ETA:])>Now() 

    Needless to say... It didn't work :(


  • 9.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-04-2017 23:53
    What field type is Van Driver? If it is a User field type, it needs to be tested with IsNull.


  • 10.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-05-2017 00:21
    Also, if Van Driver is a text field,  why not just use

    Trim([Van Driver])<>"","#00f6ff",


  • 11.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-05-2017 00:39
    Then I get this:
    ">https://d2r1vs3d9006ap.cloudfront.net/s3_images/1645488/RackMultipart20170905-96016-1motk5w-Screen_Shot_2017-09-04_at_8.37.10_PM_inline.png?1504571918">

    Not only is the field with no Van Driver not blue, but everything else that shouldn't be - is!


  • 12.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-05-2017 06:38
    I figured it out!

    If(
    not IsNull([Time Complete]), "",
    (Trim([Van Driver])="" and ([ETA:])>Now()), "#00f6ff",
    (Trim([Van Driver])="" and ([ETA:])<Now() and ToDays([ETA:]-Now())>-1),"#00f6ff",
    (Trim([Van Driver])="" and ToDays([ETA:]-Now())<0),"#00f6ff",
    ([ETA:])>Now(),"#08ef08", 
    ([ETA:])<Now() and ToDays([ETA:]-Now())>-1, "#f9fc03",
    ToDays([ETA:]-Now())<0,"#fd3110",
    IsNull([ETA:]),"#00f6ff")


  • 13.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Posted 09-05-2017 11:48
    Congratulations.
    What I often do when using those hex color codes is to add a comment

    (Trim([Van Driver])="" and ([ETA:])>Now()), "#00f6ff", // blue

    You can add a comment by starting a line with // or even suffixing a line with //, like I did above.

    That way when you go back to look at your formula you will remember what those color codes mean.


  • 14.  RE: How can I put 2 conditions (text and numeric) as independent conditions, and then combined?

    Bronze
    Contributor
    Posted 09-05-2017 21:37
    Great idea! Thank You!