Want Sort #s to Adjust Based on Sibling Records -- What Should I Learn?

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I want to learn how to make my projects database do the following:

A report shows projects sorted from low to high by a 'sort number' that the user has already entered manually

THEN, a user wants to move up or down a project to higher or lower on the list -- meaning a user can 'bump up' or 'bump down' a project on his list and the sort number for all other projects will adjust accordingly.

This means I'll need a sort number field to:

  • adjusts sort # by +1 when
    • a new project has been added with a lower sort number, or
    • a project previously having a higher sort number has been changed to have a lower sort number
  • adjusts sort # by -1 when
    • a project with a lower sort number has been removed, or
    • a project previously having a lower sort number has been changed to have a higher sort number
    I believe that, natively, QuickBase can't adjust record values based on siblings, but I believe I can learn things that will allow the above functionality.

    I know almost nothing about programming, but want to learn whatever it takes to do this.

    Where should I focus? What sorts of things will help me learn how to do this?

    Thank you!
    Photo of Amber


    • 590 Points 500 badge 2x thumb

    Posted 3 years ago

    • 0
    • 1
    I suggest that you make a numeric field on your report called [Sort]. Set its field properties to uncheck "treat blank as zero". In other words, treat blank as null.

     But then create a field like this one called [Sort for report sorting]

    The formula numeric formula would be


    So the effect of that is that users will want to get stuff to the top of the list and will rank 1 2 3 4 etc.  But we want the blanks to sort at the bottom.  So the [Sort] field is exposed to users, but the real sort for the report is done on the [Sort for report sorting] field.