Fill in a template from predefined fields in another table


#1

Hello I hope someone can help me find a way of replacing letters in a template with predefined names from another table.
In table 2 I have ABC with fields indicating a name which I want to be able to assign
In the Rota table I have a template using A, B, C - I want it to fetch the names I have assigned in Table2 and place them in the rota field replacing the letters.
How can I do this?
Table%202

Any ideas would be welcome.
Thanks
Tudor


#2

Here’s how I would do it:

  1. Create a third table, one with a single record.

  2. Link all of the records in [Table1] and all of the records in [Table2] to the single record in [Table3].

    Easiest way to manage this:

    1. Name the record in [Table3]:white_check_mark:’ — that is, the White Heavy Check Mark emoji.
    2. Add a new single-line text field to both [Table1] and [Table2].
    3. Paste the ‘:white_check_mark:’ character into every cell of these newly created text fields in both tables.
    4. In each of the tables, right-click on the new text field’s name. Choose ‘Customize field type’ and change the field from single-line text to ‘Link to Another Record.’ Specify [Table3] as the name of the table to link to.
    5. Airtable converts the ‘:white_check_mark:’ characters to links to the record in [Table3] named ‘:white_check_mark:’.
  3. In [Table2], create a formula field with this formula:

    {Letter}&':'&{ReplacementName}
    

    Call it, oh, {ReplacementKey}.

  4. In [Table3], roll up {Table2::ReplacementKey} using the aggregation formula

    '|'&ARRAYJOIN(values,'|')&'|'
    

    (Yes, you can use Airtable formulas — not just simple aggregation functions — in the configuration of rollup fields.)

To recap, what you now have is every record in your two original tables linked through a single record defined in a third table. You also have an additional field in [Table2] that contains such values as 'A:John', 'B:Nancy', 'C:Theodore', and so on. Finally, you have these index-and-name values rolled up into a new field in [Table3]:

A:John|B:Nancy|C:Theodore

This brings us to the final step — but first, an equivocation: In Airtable, you could not replace the letters with the names. Instead, you would have to maintain paired letter and name fields; of course, you would expose the letter fields only during data entry, hiding them the bulk of the time. That said…

  1. Create one or more new rollup fields in [Table1]. They will use the {Link2Table3} field — the one with all the ‘:white_check_mark:’ characters — to perform a rollup,. not a lookup, against the {Table3::CodesAndNames} field. Finally, the aggregation function required is this:
    IF(
        FIND(
            '|'&{Letter}&':',
            ARRAYJOIN(values)
            ),
        MID(
            ARRAYJOIN(values),
            FIND(
                '|'&{Letter}&':',
                ARRAYJOIN(values)
                )+3,
            FIND(
                '|',
                ARRAYJOIN(values),
                FIND(
                    '|'&{Letter}&':',
                    ARRAYJOIN(values)
                    )+1
                )-(FIND(
                    '|'&{Letter}&':',
                    ARRAYJOIN(values)
                    )+3
                    )
            ),
        BLANK()
        )

Essentially, it looks for the string '|Χ:', where 'Χ' is an uppercase letter. If it finds it, Airtable proceeds to extract the portion of the string following the colon (':') and preceding the next vertical bar ('|') character encountered.

What can I say? It works; it seamlessly accommodates new letter/name pairs without requiring modification to formulas; and it can support up to 52 such pairs simultaneously.

Even better, after you do your first 8 or 10, they become second nafure, :wink: