Forum Discussion

ReneeTumacder1's avatar
ReneeTumacder1
Qrew Member
3 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 ...
BrianSeymour's avatar
BrianSeymour
Qrew Assistant Captain
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
3 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 Assistant Captain
    3 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!
    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      3 years ago
      Another approach to get line numbering is to use a formula query.

      var text QUERY = "{20.EX.'" & [Related Job] & "'} AND {3.LTE.'" & [Record ID#] & "'}";

      Size(
      GetFieldValues(
      GetRecords($QUERY),3))



      In the example above field ID 20 is the field for related parent so basically we are counting the number of children which are related to the same parent when the rack where the record ID is less than or equal to the record ID of the record that I'm sitting on. 

      You will have to block any user, even admin, from deleting lines as that would cause re-numbering of historical records.  So you would probably have to do something like have a flag to say the line was deleted but filter it off reports even though it still exists. 

      If you can get a simple formula query to work then it's a nice piece of code you can reuse over and over and more simple to set up than a pipeline.

      ------------------------------
      Mark Shnier (Your Quickbase Coach)
      mark.shnier@gmail.com
      ------------------------------