Formula to number each child record as related to parent record

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I want to number each child record for a given parent.  More specifically, I have many Line Items (child) that all below to Proposals (parent).  I want the first Line Item for each Proposal to be numbered "1", the second to be numbered "2", etc.  Is there a way to do this?
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
How are your line items being created?
-One at a time through the "Add Button"
-Via a script?
-Grid edit?
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
Currently through an Excel import weekly.  Soon through a connection with SF.  

Regardless of the method,  Line Items are added to a proposal simultaneously, but it does not matter which line item becomes "Line Item 1", "2" etc. as long as it does not change after it is assigned
I believe you have already defined a "one-many relationship" on the tables Proposals(parent) and Line Items(Child).  If not define that relationship.

Step 2:
On table Proposals Go to -> settings -> Relationships -> click on the relationship defined between the tables.

Step 3:
On Master Table Proposals -> click on "Add Summary Field" -> on the next window leave the default selection to create "# of Proposals" field.

Step 4:
On Line Items table -> click on "Add Lookup Fields" -> on the next window select the "# of Proposals" field from the Proposals table and click on Create (name it as "Total Line Items Count").

Step 5:
On Line Items table -> create a field "Line Item Number" -> type formula numeric and name it as "Line Item Number" or anything you want.

Step 6:
On table Line Items Go to -> settings -> Forms -> Go to the form and "Dynamic Form Rules" tab.
Add a new rule using "Add New Rule" 
When 
The record -> Saved
Action
Change -> "Line Item Number" (field you created int step 5) to lookup field (Total Line Items Count) you created in step 4.

Conclusion:
With this settings Line Item Number will have the numbering you are expecting for.  
Photo of Matthew Neil

Matthew Neil

  • 31,418 Points 20k badge 2x thumb
Yeah, I agree with Mark.  This will not work.  It wont even number them sequentially
This won't work for import true.  
But It will number them sequentially within a parent proposal if you create them using  form and steps are followed as above.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,038 Points 50k badge 2x thumb
That method will not work for importing records - only when adding manually.  Form Rules only apply when manually editing records.

There may be some hope of doing something similar with snapshot fields, but I'm pretty sure that the lookup will not snapshot in until all the records are saved so they would all end up being the same line number.
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
You can however do this with a combination of summarizing the Record ID#'s and the count of line items, then a formula field to make you line item number.

But this is assuming that you import the items sequentially, or all the items are created at the same time (thus the Record ID#'s are sequential)

That's why your method of creating the line items is important.
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
Thanks for everyone's input.  Based on Matt's suggestion, I've added summary fields of # of Line Items and Total Record ID# on the Master Proposals Table and lookup fields of Proposal-# of Line items and Proposal-Total Record ID # on the Details Line Items table.  Still having trouble envisioning which formula could be used to generate the numbering.  Can you give me an idea of the formula?
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Sorry it took me so long to get back to you.  

So you are close, but I should have explained this better.

For this example lets assume you have 5 line items and the minimum record is 21 and the max is 25 

You will want to summarize the "Minimum" record ID#, not the total.
> [Minimum Line Record ID#]

Then pass the newly summarized minimum record ID# as a lookup field back dow to your line items table.
> [Proposal - Minimum Line Record ID#]

This way all 5 of your line items will have the number 21 in that new lookup field.

Make a formula-numeric field, called "Line #" on the line items table.
>
Use the formula:
[Record ID#] - [Proposal - Minimum Line Record ID#] + 1

That will take the current record, say 23, minus 21, and add 1, thus it will be line # 3.
25-21+1=5, etc.

***Keep in mind that this will only stay true if your line items are made or imported sequentially, thus sequential Record ID's ***
If you have issues with this you might have to create a manual Line # override.

Let me know how it goes.
Photo of Jessica

Jessica

  • 414 Points 250 badge 2x thumb
This works!  Thanks so much!  I had figured I might need to use the max or min but the thought of subtracting Min Record ID from the Record ID was eluding me!
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 27,954 Points 20k badge 2x thumb
Here is a demo application that demonstrates a variety of ways you can requence the child records using ArabicRoman or Revision numbering by pressing the appropriate button:

ReSequence Children
https://haversineconsulting.quickbase.com/db/bmnukbw5c?a=td

Pick a record to view and select one of the buttons to resequence the child records.

Pastie Database
https://haversineconsulting.quickbase.com/db/bgcwm2m4g?a=dr&rid=566

Notes

(1) The script uses two helper functions romanize() and toBase26() to define the sequencing scheme used but any function that converts an integer to some sequential symbol could be used. How about using Cuneiform numbers (https://en.wikipedia.org/wiki/Cuneiform_script) or tally marks (https://en.wikipedia.org/wiki/Tally_marks)?

(2) No formulas are used to do the sequential counting so the results will persists even if other fields change values or if child records are deleted. The buttons to sequence the child records should be performed administratively only when the child records needs to be sequenced (this might be a one time affair) and there is no need to maintain a historic record of prior sequencing.

(3) In all cases the child records ion my demo are naturally ordered in alphabetic order by [Name]. Whatever ordering of your child records is (by date, number, alpha etc) you should consistently use your sequencing convention so that the sequencing assigned by script stays consistent.

If you need assistance implementing this solution fee free to contact me off world using the information in my profile:

https://getsatisfaction.com/people/dandiebolt
(Edited)