Discussions

Expand all | Collapse all

Create Numeric Identifier on Child Record

  • 1.  Create Numeric Identifier on Child Record

    Posted 22 days ago
    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
    ------------------------------


  • 2.  RE: Create Numeric Identifier on Child Record

    Posted 22 days 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
    ------------------------------



  • 3.  RE: Create Numeric Identifier on Child Record

    Posted 21 days 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
    ------------------------------



  • 4.  RE: Create Numeric Identifier on Child Record

    Posted 21 days 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
    ------------------------------