Fill in a template from predefined fields in another table

Tudor_Oman · ‎Mar 17, 2018

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?

Any ideas would be welcome.
Thanks
Tudor

W_Vann_Hall · ‎Mar 17, 2018

Here’s how I would do it:

Create a third table, one with a single record.
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: ’.
In [Table2], create a formula field with this formula:
```
{Letter}&':'&{ReplacementName}
```
Call it, oh, {ReplacementKey}.
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…

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, :winking_face: