Restart number sequence with each group?

[Kevin_Duffy]

I have my table separated into groups by chapter, and then by section. Each row represents a “policy”. I want to create a column that generates the policy’s chapter-section-sequence number. So if it is the third policy in Chapter 1 Section 1, it would generate 1-1-3. The problem I’m running into is generating the “3”. Any suggestions?

[Alex_Wolfe]

One idea is you could manually input the ‘3’ or equivalent sequence number in a new field and pull that field into your combined numbering system. I’m not sure how Airtable could number them by grouped records off the top of my head without first seeing the data or the architecture of the base - but a manual entry type of field (workaround) may be needed.

[Kevin_Duffy]

Yeah, that’s what I’m thinking. I was hoping to autogenerate based on what the previous record has, but I guess airtable can’t reference other records in formulas.

Thanks for your reply!

[Alex_Wolfe]

You may find this thread useful (if you aren’t already aware of it) as it relates to calculating fields based on other records in the same table. I’m not 100% sure if it has what you need but may provide some useful information nonetheless:

Multi-record calculations (c.f. 'running balance,' 'row number,' and the like) Show and tell

tl;dr version: A frequent query and/or complaint concerns users who wish to access data from a record for use in a calculation performed in a different record from the same table. Often this takes the form of a user asking ‘how to retrieve a value based on row number’; other times the user wishes to perform a calculation that assumes knowledge of the previous record — for instance, maintaining a running balance. A recently published base contains four approaches to this problem, three involving …

[W_Vann_Hall]

@Kevin_Duffy

Here is a link to a quick-and-dirty base that does what you want — with a caveat or two:

1. Caveat #1 is that every record in the main table must be linked to a single record in a second, [Calc] table. (General instructions on how to do this can be found here.)
2. More importantly, the link to [Calc] can only be made after all policies have been created and the base is sorted in chapter-section-policy order. If, once you’ve generated your C-S-P list, you add or reorder policies, you have to clear all links to [Calc] and create them. This isn’t all that onerous; in the case of the demo, to recreate such links, all I did was first delete and then paste a period character ('.') into the {link2calc} field. Doing so guarantees the linked records are added in the same order as the records in [Main]; failing to do so will mean your policies will at least be out of order within a section and may bear out-of-range numbers.

In brief, the base causes an entry for each [Main] record called {C-S} and defined as [Chapter#]-[Section#]-[Autonumber] to be rolled up into a concatenated string in [Calc]. From there, an aggregation formula within a rollup field in [Main] finds the difference between the index of the location of [Chapter#]-[Section#]-[Autonumber] within {str} and the first instance of [Chapter#]-[Section#]-; divides the difference by the length of {C-S}; and adds 1 to the result to arrive at the corresponding policy number.

It’s actually a lot simpler than this poorly written description would lead you to believe. Duplicate the table into your own workspace, examine the formulas, and get back to me if it still doesn’t make sense. :winking_face: