How to count the number of times a value in the 1st column appears in another column in the same table

[Emily_Malone]

Hi there,

I have a table with the first column ‘Short Name’ which has the names of some mappings. Another column ‘Nested Mappings Used’ is linked to this one, so that I can show if one mapping is actually comprised of several other ones, nested within it.

I’m trying to work out a way of showing in another column how many times any field in the first column ‘Short Name’ is linked to by any field in the ‘Nested Mappings Used’ column.

An alternative would be to somehow show which mappings in ‘Short Name’ are being linked to in the ‘Nested Mappings Used’ column.

Either / Or, any help much appreciated.

Thanks!

[W_Vann_Hall]

I think this will work; I haven’t tested it, though.

1. Create a new table called, oh, [Join].
2. Link each record in your main table to a single record in [Join]. (Easiest way: Add a single-line text field to [Main]; 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 [Join] as the target. Now every [Main] record is linked to a [Join] record named '.'.)
3. In [Join], 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.
4. In [Main], define a rollup field called {MappingsCount} that follows the link to [Join] 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 [Main], {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…

[Emily_Malone]

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!