Skip to main content

I have what I think is a fairly straightforward task for Airtable AI but I can’t figure out how to execute. Here’s my use case:

 

Users create a new SURVEY record via form, attaching a PDF that contains the topline results of a public opinion survey -- ie, it’s a PDF with text like “Q12: What is your opinion of itopic]?” and then shows the results broken down by subgroup.) I want the AI to

  1.  extract all of the questions from the PDF and then...
  2. create records in a related table QUESTIONS, one record per question. 

What I’ve done: 

  • I can get an AI Field Agent to create a list of the questions in a text field. But I can’t figure out how to make one create new records in the linked Questions field.
  • I can get Omni to do this via a manual prompt (“using the attachment in grecord name], extact the questions from the file and create….”)

But I can’t figure out how to make this automatic. Ie, once a new SURVEY record is created, the AI goes and makes the child QUESTION records by itself. 

Am I missing something?

Thanks!

@dsolimini I created this exact workflow yesterday. I have an Agent field called {Champ Analysis} analyze a very specific prompt that took me a while to refine. I would run the test, see results, refine, repeat. I then added a button field the runs a script (which took less time to refine because the data was quite clean by then), on click, and that script adds new records to an output table. First, I write down a very detailed workflow of what needs to be accomplished, and then I test and iterate. I hope this helps. I’ve included my agent prompt below:

 

Please extract billing data from the PDF located in the {Champ Analysis}  field in Airtable.

For each unique account found in the PDF, return only the top summary line (ignore breakdown lines labeled “Parcels,” “MAIN,” “Priority Mail,” or “USPS Ground Advantage”).

Use the following field structure. For each account, return data in this format using pipe separators (|):

Account Code | Account Name | Pieces | Billing Amount

Account Code: A 3-digit number (e.g., 001, not 1)

Account Name: The full name following the account code

Pieces: A whole number (e.g., 13)

Billing Amount: The third dollar amount on the line (e.g., $91.633)

⚠️ Do not include:

Column labels

Blank lines

Colons, dashes, or commas between field names and values

Return one line per account, in this format:

001 | First West Insurance | 13 | $91.633  

002 | Stockman Bank Insurance | 14 | $114.006  

No extra text or formatting. Only return the raw data.


I think the easiest way to accomplish this would be to:

  1. Ensure the AI extraction is formatted uniformly:
    {Q1 - answer}
    {Q2 - answer}
    {Q3 - answer}
  2. Use a series of formula fields to extract each question:
    if(
    {AI-field},
    REGEX_EXTRACT({AI-field}, “\\{Q1 - (.*?)\\}”),
    blank()
    )
    if(
    {AI-field},
    REGEX_EXTRACT({AI-field}, “\\{Q2 - (.*?)\\}”),
    blank()
    )
    if(
    {AI-field},
    REGEX_EXTRACT({AI-field}, “\\{Q3 - (.*?)\\}”),
    blank()
    )

     

  3. Set up a view that only contains the records where all of the formula fields are not empty
  4. Create an automation that is triggered when a record enters that view, which would create a new record for each of the question fields in that record.

If you can get the AI to put the output into a comma separated list in a single line text field or maybe long text too, you can then use an automation to place that text into a linked record field, which will automatically create new linked records. the primary field on the target table needs to be single line text I believe. 


@jasonalliant - That’s an elegant solution if you can get the AI to format it consistently. However, I’ve had issues with the Field Agent responses sometimes being entirely encapsulated within quotes, which would make a single linked record for the entire response (if it’s within the character limits).


Hi ​@dsolimini

The best way to handle this is to have your AI field agent return a JSON of the questions instead of a list of questions. You can tell your prompt to only return a JSON without any other commentary. You can also provide it with the JSON format you want. You can use something like this:

{
 "questions": s
  {
   "name": "What is your opinion of itopic 1]?"
  },
  {
   "name": "What is your opinion of ytopic 2]?",
  },
  {
   "name": "What is your opinion of htopic 3]?",
  }
 ]
}

Then you can have an automation script parse the questions and create records of each question in your table, you can use something like this:

let inputConfig = input.config();
let table = base.getTable("your table name or table Id where you will create your question records");

let data;
try {
data = JSON.parse(inputConfig.ai_agent_response); //ai_agent_response is the variable you would bring in from your automation with the AI agent field response in JSON.
} catch (err) {
console.error("Invalid JSON!", err);
}


for(let i=0; i<data.questions.length; i++){
console.log(data.questionsci].name);
let recordId = await table.createRecordAsync({
"Name of your questions field": data.questionsoi].name
});

}

If you run into any inconsistency issues like the AI agent returning the JSON with additional quotations, you can add a clean up function in your script before attempting to parse the JSON. In my experience if your prompt is detailed enough, you shouldn’t have many issues here, but I’ve seen random hidden characters before that will crash the parse function, but fixed with an updated prompt!

 

Hope that helps!


Agree that json would be better if you can write a script to handle it. I’ve had mixed results getting a correct json, and a correct text list for that matter. takes some time to tweak the prompt.


Hey ​@dsolimini!

This is pretty much what I do on Min 4:30 of the video below. Hope this helps!
 

 
Mike, Consultant @ Automatic Nation


@Mike_AutomaticN Thank you, this would be almost exactly what I need! However I can’t find y our script for processing the json file on the youtube or on the blog page...


@airvues Thank you for this. I think I’m going to take this approach, but via an automation rather than an agent. Now I just have to figure out some scripting/reawaken that part of my brain ;-)