Sequence numbers grouped by linked field

[mayuran_manicks]

I am trying to build a more comprehensive product-tree type base where I can accurately keep track of Projects, Assemblies, Parts, attachments, and revisions. I want to have a parent-child relationship in that same order as well, but I don't strictly need to assign this attribute. The goal is to have some sequence number for an assembly or part, where the first few strings are based on their parent fields. I

've found a sufficient workaround for the hierarchy by just linking tables and limiting to one field per record. Now I'm stuck because I can't limit my count or sum formulas to only be above the current record like I would in excel. For example, I have AssemblyA, AssemblyB, and AssemblyC.

If assemblies A and B already have 5 parts each, when I create the first part for AssemblyC it should be in the format [Proj#]-[AssyC]-001. With autonumber the sequence would have jumped directly to 11 by now, even though this is the first part for AssyC. Has anyone found a way to implement this?

[Dimitris_Goudis]

Hey @mayuran_manicks,

I had a similar request for a client. The best solution I found was to trigger an automation with a script action that generates the sequential number.

The script has to check the linked fields and recognize the next must-added number. 

You also need a numerical field where the script will add the calculated values. 

Do you need any support on the script? 

Thanks,
Dimitris Goudis 

[mayuran_manicks]

Thanks for the suggestion @Dimitris_Goudis ! 

I figured this would be the logical next step, but for the time being we are sticking to the free plan so I cannot implement the scripts yet. 
The other suggestion I got from a colleague was to do kind of a RANK() function for the records creation date filtered by the parent field and use that for unique numbering. I don't think there is a native RANK() feature available quite yet.