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

• 0
• Question
• Updated 5 years ago

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.

• 0 Points

Posted 5 years ago

• 0

QuickBaseCoach App Dev./Training, Champion

• 69,612 Points
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)

• 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!!

QuickBaseCoach App Dev./Training, Champion

• 69,612 Points
:)

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,184 Points
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.
• 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.

Ⲇanom the ultimate (Dan Diebolt), Champion

• 30,184 Points
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).
• 0 Points
Thanks Dan!