With this setup, you can analyze the data in your table using the ChatGPT model, a prompt, and an interactive chatbox:
This will use the 'Chat GPT with Airtable Data' automation and an additional table.
Start by creating a new formula field in the table you want to analyze. The formula will use the '
CONCATENATE()' function to gather all of the record's information in one field.
Note: If you are working with large tables or fields containing a high volume of data, try to limit the fields that will be concatenated to only those necessary. This will help you avoid field size limitations and Airtable performance issues and also saves OpenAI tokens. Learn more about tokens here.
Below, there's an example with three fields describing animals in a fictional City Zoo: Name, TextBox, and Individuals.
Place everything inside the function's parenthesis.
Each field or fragment of text you want to concatenate must be separated by a comma and be between double quotes for text or curly brackets for field names.
You can use double quotes to add the field names, e.g.,
"fieldname: ", and"\n"to add a line break so the result is organized.
CONCATENATE("Name: ",{Name},"\n","Fact: ",{TextBox},"\n","Zoo Population: ",{Individuals},"\n")
This is an example of the result of the previous function with a record. Note the spaces after the colon and the line breaks:
Name: Penguin
Fact: Penguins don't need to fly; they get their food from the sea exclusively
Zoo Population: 1
Create a new table in your workspace. This table will only contain a single record. In the new table:
Create a linked record field to the table you want to analyze (in this example, it will be the fictional City Zoo) and link all of its records in the field. In this example, the new table is linked to all of the three existing records in the table that we want to analyze.
An easy way to link all of your records is to link the first record of your analyzed table with the record from your new table and then just copy the linked field to all of your records:
Create a rollup field in the new table. The 'rollup source' must be the table you want to analyze, and the 'field you want to roll up' must be the field of your original table with the formula that concatenates the rows.
You can modify the 'Aggregation formula' depending on your use case:
ARRAYUNIQUE(values)only passes unique values. If any field is different between two otherwise identical records, they'll be passed as two unique records.
Use the functions below to keep and analyze duplicate records:
ARRAYJOIN(values, ",")will pass all of the values to the rollup field, including duplicates. You can change the separator character.ARRAYSLICE(values, 1, -1)will pass all of the values to the rollup field, including duplicates. You can modify the numbers to create a slice if required.ARRAYCOMPACT(values)removes empty strings and null values but keeps duplicates.
After clicking save, you will see the result: your whole table data will be contained within this rollup field, separated and identified by the names between double quotes, and organized with line breaks if you added them.
In the same table, add a formula field that concatenates your prompt and the rollup field containing all the data. In the image below, you can find a prompt example. Feel free to modify this for your exact use case:
As the last step, configure the ChatGPT with Airtable data automation to use this new table, selecting the formula field with the prompt as your 'system message' field. The automation will give you a URL that you must use to create a formula field in the new table. Open the resulting link to use ChatGPT as an analyzer for your Airtable base, like in the example below:
Note: The limit of characters for the Airtable linked, formula, and rollup fields is not defined in their documentation, but more than 20k records will impact Airtable performance significantly. You may also run into individual field length limitations if you're dealing with a lot of fields or records each containing a lot of data.