Updating script with specific dates | Discussions

Expand all | Collapse all

Updating script with specific dates

Stephen Anguiano01-25-2018 15:44

Stephen Anguiano01-25-2018 21:10

QuickBaseCoach Dev./Training01-25-2018 21:13

  • 1.  Updating script with specific dates

    Posted 01-18-2018 15:19

    How would I rewrite the script to account for both [Program approved] and [Permanently Rejected] fields to be specific for dates below? 

    June2016 - May2017=2016

    June2017 ? May2018=2017

    June2018 ? May2019=2018

    My current script is working but I need to account for 2018:

    If([Customer - Utility]="ABC Energy" and (Date(2017,5,31)>=[Program approved] or Date(2017,5,31)>=[Permanently Rejected]),"2016","2017")



  • 2.  RE: Updating script with specific dates

    Posted 01-18-2018 15:33
    If(

    [Customer - Utility]="ABC Energy" and (Date(2018,5,31)>=[Program approved] or Date(2018,5,31)>=[Permanently Rejected]), "2018",

    [Customer - Utility]="ABC Energy" and (Date(2017,5,31)>=[Program approved] or Date(2017,5,31)>=[Permanently Rejected]), "2017", "2016")


  • 3.  RE: Updating script with specific dates

    Posted 01-23-2018 21:19
    My requirements have changed and know not dependent on Customer. 

    So will the following still work if I write :

    If(Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",

    Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")

    Just curious if [Program approved] is blank/null does the formula think it is greater than 12.31.2017?

    Thanks.


  • 4.  RE: Updating script with specific dates

    Posted 01-24-2018 00:25
    That looks like it will work,  but I don't think that the condition will be true if the dates are null.  Hence it will probably calculate to 2017.

    I suggest that you specifically test for null dates and decide what you want to output for that situations.

    IF(
    IsNull([Program approved]) and Isnull([Permanently Rejected]), "say something",

    Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",

    Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")


  • 5.  RE: Updating script with specific dates

    Posted 01-25-2018 14:03
    This is the logic I decided to use:

    If(Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",
    Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")


    But it is making everything "2018". I know I have about 400 records that should be "2018" currently.


  • 6.  RE: Updating script with specific dates

    Posted 01-25-2018 14:23
    Stephen, can you give me an example of a record's data as to what is in those two fields?


  • 7.  RE: Updating script with specific dates

    Posted 01-25-2018 15:36

    [Program approved] and [Permanently Rejected] are set up as Date field types. Both fields cannot be populated at the same time, so it�s either one or the other to determine the year.

     

    The biggest issue I see so far is when I create a report based on year projects for 2018 will show up as �blank� while other will show �2016� or �2017�, but I know it because those 2018 projects have yet to be marked with a calendar date.

     I would like it to default to the current year of �2018� if it is NULL.



  • 8.  RE: Updating script with specific dates

    Posted 01-25-2018 15:38
    I had previously posted above how to test for null.

    F(
    IsNull([Program approved]) and Isnull([Permanently Rejected]), "say something",

    Date(2017,12,31)>=[Program approved] or Date(2017,12,31)>=[Permanently Rejected],"2018",

    Date(2016,12,31)>=[Program approved] or Date(2016,12,31)>=[Permanently Rejected],"2016","2017")


  • 9.  RE: Updating script with specific dates

    Posted 01-25-2018 15:44
    That made all my records 2018.


  • 10.  RE: Updating script with specific dates

    Posted 01-25-2018 15:47
    all my records",  you mean all your records or just the ones with no dates.


  • 11.  RE: Updating script with specific dates

    Posted 01-25-2018 16:01
    I mean "all" of them are now 2018. 

    I did use this logic:
    IF(not IsNull([Program approved]), ToText(Year([Program approved])),not IsNull([Permanently Rejected]), ToText(Year([Permanently Rejected])))
    It works fine, but in my reports, it would show the following on the filter of the left hand of the report.



  • 12.  RE: Updating script with specific dates

    Posted 01-25-2018 16:08
    Stephan, I'm, really not understanding your question.

    what does this mean

    It works fine, but in my reports, it would show the following on the filter of the left hand of the report.


  • 13.  RE: Updating script with specific dates

    Posted 01-25-2018 16:16
    Did you see the attachment in my last post? I have attached it again to this post.



  • 14.  RE: Updating script with specific dates

    Posted 01-25-2018 16:20
    I see a Dynamic Filter on a field called Program Year.  I don't know what that field represents in your data.  What is your question?


  • 15.  RE: Updating script with specific dates

    Posted 01-25-2018 16:32
    Program Year is determined by either [Program approved] or [Permanently Rejected]. Do you see the Dynamic Filter it has "Blank" for a year? It is because [Program approved] or [Pernanently Rejected] is "Null".

    Will this script below fix that "Blank" in my Dynamic Filter?

    IF(not IsNull([Program approved]), ToText(Year([Program approved])),not IsNull([Permanently Rejected]), ToText(Year([Permanently Rejected]))),
    IF(
    IsNull([Program approved]) and Isnull([Permanently Rejected]), "2018")

    Thanks


  • 16.  RE: Updating script with specific dates

    Posted 01-25-2018 16:55
    I would write i a bit differently, bit yes, tat shoiuld eliomiate blamnks

    IF(

    IsNull([Program approved]) and Isnull([Permanently Rejected]), "2018",

    not IsNull([Program approved]), ToText(Year([Program approved])),

    not IsNull([Permanently Rejected]), ToText(Year([Permanently Rejected])))


  • 17.  RE: Updating script with specific dates

    Posted 01-25-2018 21:10
    This works perfectly!!! 

    Thanks


  • 18.  RE: Updating script with specific dates

    Posted 01-25-2018 21:13
    Great to hear, .... Upward and onward ho!


  • 19.  RE: Updating script with specific dates

    Top
    Contributor
    Posted 01-25-2018 14:16
    I think this is what you want but I did not test it:

     var Date CriticalDate = Min([Program approved], [Permanently Rejected]);
     
     If($CriticalDate <= Date(2016,12,31), "2016",
        $CriticalDate <= Date(2017,12,31), "2017",
        $CriticalDate <= Date(2018,12,31), "2018",
        $CriticalDate <= Date(2019,12,31), "2019"
    )