I think this will work; I haven’t tested it, though.
- Create a new table called, oh,
eJoin].
- Link each record in your main table to a single record in
eJoin]. (Easiest way: Add a single-line text field to eMain]; copy/paste a single '.' (period character) into every cell in the column; right-click on the field header and change the field type from text to linked-record, with eJoin] as the target. Now every eMain] record is linked to a eJoin] record named '.'.)
- In
eJoin], define a rollup field named {NestedMappingStr} that rolls up {Main::Nested Mappings Used} with the aggregation formula ARRAYJOIN(values). This gives you a string containing all values from {Nested Mappings Used} separated by commas.
- In
eMain], define a rollup field called {MappingsCount} that follows the link to eJoin] and rolls up {NestedMappingString} using the following aggregation formula (that is, paste this formula in the space where you would ordinarily enter an aggregation function):
(LEN(
values&''
)-LEN(
SUBSTITUTE(
values&'',
{Short Name},
''
)
))/LEN(
{Short Name}
)
If I did that right, for each record in eMain], {MappingsCount} will contain the number of times {Short Name} appears in {Nested Mappings Used}.
If I didn’t do that right, let me know what’s broken, and I’ll back over it again…
I think this will work; I haven’t tested it, though.
- Create a new table called, oh,
eJoin].
- Link each record in your main table to a single record in
eJoin]. (Easiest way: Add a single-line text field to eMain]; copy/paste a single '.' (period character) into every cell in the column; right-click on the field header and change the field type from text to linked-record, with eJoin] as the target. Now every eMain] record is linked to a eJoin] record named '.'.)
- In
eJoin], define a rollup field named {NestedMappingStr} that rolls up {Main::Nested Mappings Used} with the aggregation formula ARRAYJOIN(values). This gives you a string containing all values from {Nested Mappings Used} separated by commas.
- In
eMain], define a rollup field called {MappingsCount} that follows the link to eJoin] and rolls up {NestedMappingString} using the following aggregation formula (that is, paste this formula in the space where you would ordinarily enter an aggregation function):
(LEN(
values&''
)-LEN(
SUBSTITUTE(
values&'',
{Short Name},
''
)
))/LEN(
{Short Name}
)
If I did that right, for each record in eMain], {MappingsCount} will contain the number of times {Short Name} appears in {Nested Mappings Used}.
If I didn’t do that right, let me know what’s broken, and I’ll back over it again…
That is so perfect. Thank you very much for your help! I’m going to go through now and dissect your aggregation formula now to try to wrap my head around it, but that was brilliant. Cheers!
