Updating script with specific dates

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered

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")

Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb

Posted 11 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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")
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,928 Points 50k badge 2x thumb
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")
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,898 Points 50k badge 2x thumb
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?
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,898 Points 50k badge 2x thumb
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])))
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
This works perfectly!!! 

Thanks
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 52,898 Points 50k badge 2x thumb
Great to hear, .... Upward and onward ho!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,884 Points 20k badge 2x thumb
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"
)