Handling CSV text Output from AI Query

overview in this tutorial, we will see how cloudfiles can be used with salesforce flow automations to extract multiple data fields from uploaded documents using a single query each time a file is uploaded into salesforce or external storage, the following actions will occur the file is processed using cloudfiles document ai a natural language query executes to extract information from the document in csv format extracted information is then updated in its respective fields in order to setup this automation, you first need to have some pre requisites after this you can start setting up some query automations and updates to achieve your use case check the sections below for more information pre requisites before starting with this tutorial, you need to ensure that cloudfiles document ai is properly installed & configured in your system additionally, you also need to create some initial automations to process the files before you can query them check the 2 sections below carefully installation & configuration ensure the following before starting cloudfiles document ai installation docid\ rfxciqrbejmpsmbczexam and configuration docid\ amnhmxwixyngnaxerbkwd are complete you have an active subscription or trial of cloudfiles document ai the cloudfiles event mode setting is set to custom object initial automation setup there are 2 flows you will need to setup to process the uploaded files the first flow is triggered when the document is uploaded into salesforce or external storage this flow sends the file to ai for processing this might take a few seconds once the ai is done with processing, the second flow will trigger which will contain the processed document you can then run queries on this processed document flow 1 send document for processing triggered when file is uploaded into salesforce or external storage sends the file for processing which may take a few seconds to a minute flow 2 document processing complete triggered when the file has been processed by ai performs queries on the file to extract the data once your flow setup is complete, your flows should look like the following read the article on ai flows initial setup guide docid\ sxn79rxu7gw7bb6ogpuix carefully to setup both the flows once your initial flows are setup as shown above, you are ready to move to the next step query automation setup now that you have the initial automation setup, we can take the document processed flow and extend it to query the document and update the required fields in this section, we show how to query the processed document, check the results and perform the necessary updates querying the document in order to query the document, we will simply use the query document docid\ dm3eh gzaocyoqd5y0r8d flow action this action taken in a processed document id and a text query as input the processed document id is available as output of the get event details action here are the full inputs used in the image below processed document id ( {!get triggering event details documentprocessed processeddocumentid} ) query = enter a detailed natural language prompt that clearly defines the structure and format of the expected output "return the following details from the driver's license full name (first name + last name), dl number, issue date and expiry date the ouput should be in the format fullname; dl number;issuedate;expirydate for example if the name is john doe, dl number is 123456, issue date is 12 mar 2014, expiry is 12 mar 2019 the output should be john doe,123456,12 03 2014,12 03 2019 if any of the details are missing, put a space as placeholder " example result john doe,12345678,12 03 2014,12 03 2019 this output can now be parsed using formulas to update respective fields like name, dl number, issue date and expiry date one prompt can be designed to simultaneously extract multiple fields from a document parsing ai output with salesforce formulas the query document action returns output in csv format use flow formulas to extract specific values below are examples formula to extract dl holder's name if( find(",", {!retrieve all details}) 1 = 0, "n/a", left({!retrieve all details}, find(",", {!retrieve all details}) 1) ) formula to extract issue date of dl if( mid({!retrieve all details}, find(",", {!retrieve all details}, find(",", {!retrieve all details}) + 1) + 1, 1) = ",", null, date( value(mid({!retrieve all details}, find(",", {!retrieve all details}, find(",", {!retrieve all details}) + 1) + 7, 4)), value(mid({!retrieve all details}, find(",", {!retrieve all details}, find(",", {!retrieve all details}) + 1) + 4, 2)), value(mid({!retrieve all details}, find(",", {!retrieve all details}, find(",", {!retrieve all details}) + 1) + 1, 2)) ) ) formula to extract expiry date of dl if( right({!retrieve all details}, 1) = ",", null, date( value(mid({!retrieve all details}, len({!retrieve all details}) 3, 4)), // year value(mid({!retrieve all details}, len({!retrieve all details}) 6, 2)), // month value(mid({!retrieve all details}, len({!retrieve all details}) 9, 2)) // day ) ) formula to extract dl number if( find(",", {!retrieve all details}, find(",", {!retrieve all details}) + 1) find(",", {!retrieve all details}) 1 = 0, "n/a", mid( {!retrieve all details}, find(",", {!retrieve all details}) + 1, find(",", {!retrieve all details}, find(",", {!retrieve all details}) + 1) find(",", {!retrieve all details}) 1 ) ) updating contact record once the values are parsed, use an update records action to populate them into the correct fields to identify the right contact record, use a get records element where id = {!get triggering event details documentprocessed context} filter and retrieve the ontact, then update its fields using the extracted values see it in action now that everything is set up, you can test your flows when a driver's license is uploaded as salesforce files onto a contact record, the required fields should get populated accordingly note since flow executions and ai processing may take a few moments, the updates might not appear instantly refresh the page to view the latest values flow 2 debug whenever a file is processed using the process document using ai docid 31ujx1ligtkwfkjuanbzt action, a document processed docid\ xrr pnbpocwwcmyg6qgk9 object record is published you can query these event object records to verify successful file processing and debug the flow example soql query to check document processed events sorted by the most recent select id, name, createddate, cldfs data c from cldfs cloudfilesevent c where cldfs type c = 'document processed' order by createddate desc you can check the context and file details in the data field of the record when a flow runs in debug mode and executes query document docid\ dm3eh gzaocyoqd5y0r8d actions, you can check the results and modify the queries if required