Nov 01, 2018 12:59 PM
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?
Nov 01, 2018 01:16 PM
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.
Nov 02, 2018 06:55 AM
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!
Nov 02, 2018 08:24 AM
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:
Nov 03, 2018 05:49 AM
Here is a link to a quick-and-dirty base that does what you want — with a caveat or two:
[Calc]
table. (General instructions on how to do this can be found here.)[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: