How do I create dynamic line numbers for the number of related records?

  • 0
  • 1
  • Question
  • Updated 5 years ago
  • Answered

I have a list of records, called Billing Items, with the same Related record, called Property. What I need is the ability to assign a line number in sequential order to each Active Billing Item within a Property (billing items have start/end dates that determine if the item is active or not). In simplistic terms I have something like this today:

  • Property A
    • Billing Item a
    • Billing Item b
    • Billing Item c

What I am trying to achieve:

  • Property A
    1. Billing Item a
    2. Billing Item b
    3. Billing Item c

I need the line number to be a formula that creates a sequential list always starting with #1 for just active billing items. For example, if billing item b becomes inactive, then the list would be:

  • Property A
    1. Billing Item a
    2. Billing Item c

I'm not sure of how to construct the formula, but using 'Date Created' might be a solution. That would work for figuring out line #1(If [date created] = [Property - min date created], 1, ... The last active billing item could adopt the 'total # Active Billing Items' using a max date created. From there I am stuck. If I could assign a line number for each active item that was created next chronologically would be pretty fool proof.

Also worth mentioning that my current solution is using a snapshot field of Total Active Billings. This works will when adding new billing items to a property. It doesn't work when deactivating billing items.

I'm open to any solution that works! Thanks.

Photo of JW

JW

  • 0 Points

Posted 5 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
If you have a practical maximum number of Billing items per property, say 5 or 10 or 20 max I suppose, then there is a way to do this with a series of Summary fields and lookups of those Summary fields.


The rough steps are this and they involve a bunch of summary fields on the main relationship between Properties and Billing items. Let me know if I have skipped too many steps and need to get more detailed.


Summary MIN Record ID# of Billing item 1 (Min of record ID#)

Lookup that to Child (Billing items)

Summary MIN Record ID# of Billing item 2 (Min of record ID# but record ID> Summary MIN Record ID# of Billing item 1.


Lookup that to Child (Billing items)

Summary MIN Record ID# of Billing item 3 (Min of record ID# but record ID> Summary MIN Record ID# of Billing item 2.

Lookup that to Child (Billing items)

Summary MIN Record ID# of Billing item 3 ......


etc for as many loops as you need.

Then have a formula - numeric field on the Billing items record called [Line #]


Case([record ID#],

Summary MIN Record ID# of Billing item 1, 1,

Summary MIN Record ID# of Billing item 2, 2,

Summary MIN Record ID# of Billing item 3, 3, .. etc)






Photo of JW

JW

  • 0 Points
This worked perfectly.  At the moment I only need 10 of these summary fields.  It was easy to build and I never would have figured this out on my own.  Thanks for your help!!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 69,612 Points 50k badge 2x thumb
:)
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
This is simple to do with script and you don't need to clog your table up with faux relationships and summary fields:

CSF - Let Me Count The Ways

https://haversineconsulting.quickbase.com/db/biwrbeb9z?a=q&qid=1

Pastie Database

https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=258


Note I only implemented the counter on the specific query with qid=1. Normally the image onload field is named [-] but in this instance I am using the image onload field as a client side formula so I named it [Counter]. In the past we took every effort to make the image onload field hidden but when used as a client side formula we want it to look like a normal field displaying a result. It just so happens that our result is calculated client-side rather than server-side like normal formula fields.
Photo of Greg

Greg

  • 0 Points
Does anyone know if the image onload technique will work inside of an exact form?  For example if I call the Counter field inside the exact form, will it work properly?  I have not been able to get it to.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 30,184 Points 20k badge 2x thumb
The only purpose of the image onload technique is to run user supplied JavaScript on a QuickBase page load. Exact forms can run JavaScript directly so there would be no need to use IOL. However, I am not a fan of Exact forms so I discourage people from using them (use a templating engine such as Mustache or Underscrore' template() method instead).
Photo of Greg

Greg

  • 0 Points
Thanks Dan!