Forum Discussion

MarkGlevicky's avatar
MarkGlevicky
Qrew Cadet
7 years ago

Sorting blank date records in a number of fields

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.
Tables and fields

14 Replies

Show More
  • MarkGlevicky's avatar
    MarkGlevicky
    Qrew Cadet
    7 years ago
    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?
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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") 


  • MarkGlevicky's avatar
    MarkGlevicky
    Qrew Cadet
    7 years ago
    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
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    7 years ago
    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.