Forum Discussion

ReneeTumacder1's avatar
ReneeTumacder1
Qrew Member
2 years ago

Need help creating a child record identifier that is based on Parent Record +1

I have a parent table (records within are Roadmaps, representing large-scale initiatives) and a child table (called Projects).  My goal is name create a numeric identifier for the child record based on the Roadmap #, adding '-1' to the first child, -2 to the second child and so on.  For example:
BIG INITIATIVE 600 (Roadmap record ID)
   600-1 Project Name A
   600-2 Project Name B
   600-3 Project Name C

I also want to automatically add a child record every time a Parent Record is added (and plan to build a Pipeline to achieve this).

Has anyone successfully done this?  If so, can you share your steps?  (eg is this two discrete actions or is there a way to include the 'RID=Parent RID, increment by 1' within the pipeline configuration?)

Thanks!

Renee​​

​

------------------------------
Renee Tumacder
------------------------------
  • BrianSeymour's avatar
    BrianSeymour
    Qrew Cadet
    2 years ago
    Hhmmm … please be careful if you rely on the "-x suffix" for identification (relationships) since your counts could potentially change and have unexpected consequences if a child record were deleted.

    Depending on your use case, it's possible to achieve this with the Formula Queries by counting child records having the same Parent where the child ID is less than or equal to the current child ID. For example, on the Child record you could create a "Formula - Numeric" field. Then, basically count siblings, like so:

    // 7 is the Field ID of the Child::Related Parent
// 3 is the Field ID of the Child::Record ID#

var Number siblings = Size(GetRecords(
    "{7.EX.'" & [Related Parent] & "'} AND {3.LTE.'" & [Record ID#] & "'}"
));

$siblings​


    Then, you'd create a "Formula - Text" field to concatenate the Parent and Child values together.

    // Parent One 1-1 Child One

[Parent - Name] & " " & [Related Parent] & "-" & [Siblings] & " " & " " & [Child Name]


    You can also head over to the Child's Advanced Table Settings and set the Record Title to the Formula - Text field above.

    When you create the Child via Pipeline you mentioned you could set a static counter on the Child. First, you'd need to create a Summary field on the Parent counting its children. Then, in your Pipelines use Jinja to add one to the Summary count.

    Does that help?

    • ReneeTumacder1's avatar
      ReneeTumacder1
      Qrew Member
      2 years ago

      Thank you Brian!  I understand at a high level (not as deeply as I want!) but will give this a try and report back.

      My experience with Pipelines is pretty minimal (& jinja.. um, no not yet) so it makes sense to decouple: Pipeline to auto-create the child, and use your reco to come up with a new child identifier.

      About your caution with the "-x suffix" if a child is deleted.. I'm ok to see -1, -2, -4 (assuming 3 is deleted).  Or do you mean -4 will become -3 if the original -3 is deleted?



      ------------------------------
      Renee Tumacder
      ------------------------------
      • BrianSeymour's avatar
        BrianSeymour
        Qrew Cadet
        2 years ago
        Yes, it raise a flag for me that that -4 could become -3 if the original -3 is deleted.

        A better approach may be a hybrid of the two previous suggestions.

        • Create a Child field to store a static sibling number
        • (Just plain numeric field. But in the field properties, check the box to Treat blank values as "0" in calculations.)
        • Create a summary field on the Parent that finds the max sibling number (of field the above)
        • (This is different from a Child count. Use max instead.)
        • Create a Pipeline that is triggered when a Parent is created
        • The Pipeline will set the Related Parent on the child
        • The Pipeline will set the static sibling count based on the Max summary and then add one via Jinja.
        • e.g. {{a.sibling_count + 1}}

        You should be fine displaying the Child record info that way, but I would suggest leaving the Child's default "auto-incrementing table key" alone. In other words, be careful of any relationships based on the "-x" suffix.

        The main point being use max vs. count on the Parent summary! Then, that could be set permanently on the new Children. And be careful when a Child is deleted, as you could still run into the -x shifting when the most current child is deleted. e.g. If you have four children and -4 is removed, but then a new Child is added it would become -4.

        Ahh, writing the above, I realized yet another alternative. Store the sibling count on the parent. And just increment it whenever a Child record is created. That way you shouldn't end up with using the same sibling count twice! For example, in the Parent's button to Add Child you could increment the parent count, then add the Child (so the same button would edit and add record via nested redirects). Or perhaps a second Pipeline could be create that increments the Parent whenever a Child is created. I suppose it depends on how you intend to create Children!

        Anyway, there are multiple ways to approach this. Hopefully that gives you some ideas without too much confusion!