PROBLEM
In our TABLE 1 (PROJECTS):
- We want our concatenated field to read: P1234 (Joe Smith - Smith, Joseph & Jones)
- What we get: P1234 (“Joe Smith - Smith, Joseph & Jones”)
Where: Joe Smith - Smith, Joseph & Jones is a linked field to our TABLE 2 (CUSTOMERS) table.
WHY: Airtable is concatenating a linked field that contains one or more commas, and for whatever reason surrounds the entire string with quote marks. You should see that any concatenated fields containing linked fields without commas do not show the quote marks.
EXAMPLE DATA / FORMULAS (Solution Below):
This example shows Table 1 concatenating Table 2’s primary field (which is also a concatenated formula). Even if your primary field in Table 2 is a write-in field containing one or more commas, the result & solution are the same.
TABLE 1: PROJECTS
- Column 1: Project (formula field:
Concatenate({Project Number}, " (", {Customer}, ")") This is the field that will show the unwanted quote marks because you are concatenating using a linked field containing comma(s)
- Column 2: Project Number (ex, P1234)
- Column 3: Linked field to TABLE 2’s Customer field. This linked field displays as you would expect.
TABLE 2: CUSTOMERS
- Column 1: Customer (formula field:
Concatenate({First Name}, " ", {Last Name}, " - " {Customer Company}) )
- Column 2: First Name
- Column 3: Last Name
- Column 4: Customer Company
TABLE 2 (CUSTOMERS) displays the correct Concatenate format. However, Column 1 is your primary field, which will then be the linked field referenced in TABLE 1. It will produce the unwanted quote marks when seen in TABLE 1’s concatenated field. Again, even if your Column 1 is a write-in field, if it contains commas it will produce the unwanted quote marks in your other table’s concatenated field.
- Column 1: Joe Smith - Smith, Joseph & Jones (<-- Primary field containing commas)
- Column 2: Joe
- Column 3: Smith
- Column 4: Smith, Joseph & Jones
SOLUTION:
Concatenate using a helper column from your linked table rather than the linked field.
- In TABLE 2, create a “helper column” called “Customer String” with the same formula contained in Column 1. In our example the formula was:
Concatenate ({First Name}, " ", {Last Name}, " - " {Customer Company}) If you are using a write-in field for Column 1, your helper column can simply reference your Column 1 field name. The point is you are creating the same text string that is no longer the linked/primary field.
- In TABLE 1, keep the linked field as-is, but add another column with a Lookup Field referencing the new helper column Customer String.
- In TABLE 1, in your Concatenate field, reference your helper column instead of the linked field. Example:
Concatenate({Project Number}, " - ", {Customer String})
- In TABLE 2 you can hide the helper column since it is essentially a duplicate. In TABLE 1 you can hide the lookup column and keep your linked field intact.
