Updating script with specific dates

  • 0
  • 1
  • Question
  • Updated 1 year 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 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 65,290 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

  • 65,290 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

  • 63,880 Points 50k badge 2x thumb
Stephen, can you give me an example of a record's data as to what is in those two fields?
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb

[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.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

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

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
That made all my records 2018.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 63,880 Points 50k badge 2x thumb
"all my records",  you mean all your records or just the ones with no dates.
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
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.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 63,880 Points 50k badge 2x thumb
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.
Photo of Stephen Anguiano

Stephen Anguiano

  • 394 Points 250 badge 2x thumb
Did you see the attachment in my last post? I have attached it again to this post.

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 63,880 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

  • 63,880 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

  • 63,880 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

  • 30,044 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"
)