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.   

[Dimitris_Goudis]

Hey @mayuran_manicks,

The Rank() function is not available on Airtable... Even the "Sort" action in automation doesn't generate the rank number...

How many users do you have in your system? 

Thanks,
Dimitris Goudis

[mayuran_manicks]

Yeah, I was surprised by how limited the equation features are. I skimmed through the full list on the help page and I'm not too optimistic about the options. I guess making scripts a premium feature makes sense for their business.

For now just 2 admin accounts, at least until we decide on a platform and set up our product trees. Ideally after that the whole development team would have access.

[Dimitris_Goudis]

Hey @mayuran_manicks, 

this makes sense. How many users are you gonna have finally on the platform no matter what the platform will be?