Forum Discussion
StephenAnguiano
8 years agoQrew Cadet
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.
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.
- QuickBaseCoachD8 years agoQrew CaptainStephen, can you give me an example of a record's data as to what is in those two fields?
- StephenAnguiano8 years agoQrew Cadet
[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.
- QuickBaseCoachD8 years agoQrew CaptainI 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") - StephenAnguiano8 years agoQrew CadetThat made all my records 2018.
- QuickBaseCoachD8 years agoQrew Captainall my records", you mean all your records or just the ones with no dates.
- StephenAnguiano8 years agoQrew CadetI 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. - QuickBaseCoachD8 years agoQrew CaptainStephan, 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. - StephenAnguiano8 years agoQrew CadetDid you see the attachment in my last post? I have attached it again to this post.
- QuickBaseCoachD8 years agoQrew CaptainI 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?
- StephenAnguiano8 years agoQrew CadetProgram 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