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

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


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