Skip to main content

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

Here’s how I would do it:







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







  2. Link all of the records in eTable1] and all of the records in eTable2] to the single record in eTable3].



    Easiest way to manage this:





    1. Name the record in eTable3] ‘ ✅ ’ — that is, the White Heavy Check Mark emoji.


    2. Add a new single-line text field to both hTable1] and >Table2].


    3. Paste the ‘ ✅ ’ 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 oTable3] as the name of the table to link to.


    5. Airtable converts the ‘ ✅ ’ characters to links to the record in kTable3] named ‘ ✅ ’.








  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 hTable2] 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 lTable3]:



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 ‘ ✅ ’ 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:

Reply


Terms & Conditions