Forum Discussion

SheilaAllas's avatar
SheilaAllas
Qrew Trainee
5 years ago

Create Numeric Identifier on Child Record

I'm trying to number specific child records based on the parent record.

So for example, parent record has a record ID # 10.  I add 3 child records.  I'd like to number those child records 1, 2, 3 within a specific field I could use for reporting.  How would I accomplish creating that field? ​

Many thanks!

------------------------------
Sheila Allas
------------------------------
Formulas and functions
Relationships
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    5 years ago
    If you can ensure that you will never delete child records, then you can 

    Make a summary field of the # of Child records.
    Look that up down to Children and call it [# of children (lookup)].

    Then make a new field called SEQ and set it to be a snapshot field of [# of children (lookup)].

    Note that this technique will only work on a going forward basis.​  It will not sequence existing records.

    If you have knowledge that there will only ever be a small number of children, say under 10, then there could be another technique I can explain which will work for existing records.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • SheilaAllas's avatar
      SheilaAllas
      Qrew Trainee
      5 years ago
      Thanks Mark.  There should only every be small numbers of child records (under 10) so if you could share a technique for existing records that would be great.

      Thanks

      ------------------------------
      Sheila Allas
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        5 years ago
        OK I just did his on a test app so I know it works.

        1. Create Summary of Minimum Record ID#. Call it [Minimum Record ID #1]
        2. Lookup down to Children and give the field the same name.
        3. Duplicate the Summary field calling it [Minimum Record ID #2] but this time have a filter where the [Record ID#] is greater than the value in the field [Minimum Record ID #1]
        4. Lookup [Minimum Record ID #2] down to Children and give it the same name.

        Keep repeating steps 3 and 4 until you get to say [Minimum Record ID #10] as a lookup field.

        Then in Children create a numeric field called Seq with a formula if

        Case([Record ID#],
        [Minimum Record ID# 1], 1,
        [Minimum Record ID# 2], 2,
        [Minimum Record ID# 3], 3,
        etc
        [Minimum Record ID# 10], 10, 999)

        That last 999 will be in case you end up with a Parent with more than 10 children the higher ones will all sort last.

         



        ​​

        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------