Sorting blank date records in a number of fields

  • 0
  • 1
  • Question
  • Updated 3 weeks ago
  • Answered
I have 3-4 date fields that have a large number of blank records and I wish to sort on all these fields in order, oldest to newest.  I don't want the blanks to come up first.  I've seen instructions here to create a new field next to the existing one and use a formula to push blank fields to the bottom by setting a date for them far out in the future. Do I really have to create 4 new fields in my table to just eliminate the blanks when I sort?  If so, do I show both of these fields in my tables all the time so I can input dates to my original field and then have the second field reflect the formula work?  This all seems very clumsy, if so.  I hope there's an easier solution... 

Thx, Mark

PS  What should the foumula look like if I have to do this...new to the formula use.
Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb

Posted 3 weeks ago

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

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Yes, if you want to change the sort, you will need to have an extra field for each field that needs to have an alternate sort. However, there is no need to have the extra sort field take up a column in the report. It can just be used for the sort.

However, it could be that you want the user to have the ability to click on any of the 3-4 column headings to sort, and in that case then you would need double columns.

Would it work to have a Dynamic filter to show or hide if the date is blank? Then you would not need to have duplicate sort fields.

The formula would be

IF(IsNull([my date field]), Date(2099,12,31), [my date field])
Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb
How do I create/add this dynamic filter using this formula?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
It would be a Formual Text field like

If[IsNull([my date field]), “Blank", " not Blank")

Unless you wanted a different formula to check all the date fields.
Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb
So I need 1 additional field only if I wanted to hide all the blank date fields I'd want hidden?  How would I do that one, formula-wise?  The date fields I'd like to hide (actually, looks like there's only two) I'm talking about in my "List All" report (main report table) are called "APPT? 19", "APPT 19".  Thank you!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
If(
[IsNull([APPT? 19]), “Blank",
[IsNull([APPT 19]), "Blank", 
" not Blank") 


That will filter out (or include only) if either of those fields are blank.

Mark

 
(Edited)
Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb
When I put in this formula, the first parentheses (right after If) is highlighted in yellow and the note when hovering over it is "Field[IsNull([APP? 19] is not identified" 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
I was on an iPad and I think the wrong quotes got in here.

If(
[IsNull([APPT? 19]), "Blank", 
[IsNull([APPT 19]), "Blank", 
" not Blank") 

If that does not work, then please post your complete formula and the complete error message.


Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb

Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb

Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
Sorry

If(
IsNull([APPT? 19]), "Blank", 
IsNull([APPT 19]), "Blank", 
" not Blank") 

In future, please actually copy and paste the text of the formula and not a screen shot as screen shots are not editable.

Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb
Sorry, will copy/paste in future.  So, that worked! Thank you!  Now I have to sort on that column first, then on APPT? 19 and APPT 19 next?  Also, is there a formula change that would be show "not blank" if *either* of the records had a date?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
No, use that field as a dynamic filter on the left side of the report.  It will instantly filter your report.

As for various permutations, try this enhanced formula

If(
IsNull([APPT? 19]) and IsNull([APPT 19]), "Both Blank", 
IsNull([APPT 19]), "APPT 19 Blank",
IsNull([APPT? 19]), "APPT? 19 Blank", 
" none Blank") 


Photo of Mark Glevicky

Mark Glevicky

  • 416 Points 250 badge 2x thumb
GREAT!  Thank you so much.  I may need to add another field, but looks like I can figure that out from here.  I very much appreciate your help!  Mark
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 57,418 Points 50k badge 2x thumb
OK great,.
I know it seems like a slog at first to get your first formuals working and figure out what a Dynamic filter is, but everything is much easier the 2nd time when you need to do something similar.