Background:

I am creating a zap that will trigger when I receive an e-mail from a specific sender.  Once triggered, ChatGPT will read the body of the e-mail to see which jobs are approved or not approved in the e-mail and export the data as a JSON Array.  Once the Array is created a Zapier Loop will then use the JSON Array to update the status of the jobs in a Notion Database.

I’ve been able to set up the trigger properly.  The Zap is able to find e-mails from the correct sender and separate the text of the e-mail into relevant groups of Unstructured Text.  

The ChatGPT (OpenAI) step is able to identify the body of the text and read through the text properly.  The issue is that the Data Out only shows one job and its status.  However, the body of the e-mail contains several jobs and their status.  The jobs are identified by Job Number and the status is either Approved or Not Approved. 

How do I get ChatGPT to create an array of data that lists each individual job number and its relevant status in a JSON Array? 

I want to use the JSON Array to create a Zapier Loop from List to then run the following Actions in Notion:

4. Action: Find Job in Notion

• App: Notion

• Event: Find Database Item

• Database: Select your jobs database.

• Search Property: Find the "Job Number" column.

• Search Value: Use the job_number variable from the Loop step.

This step will locate the correct row in your Notion database for the current job number.

5. Action: Update Notion Database

• App: Notion

• Event: Update Database Item

• Database Item: Use the database item ID found in the previous step.

• Update Logic: This step will require conditional logic using Zapier's Paths (a premium feature).

  • Path A (If status is "approved"):

    • Condition: status variable from the Loop (Text) Contains (Case-insensitive) "approved".

    • Action: Update the "estimate approved" column to True.

  • Path B (If status is "not approved"):

    • Condition: status variable from the Loop (Text) Contains (Case-insensitive) "not approved".

    • Action: Update the "Job Completed" column to True.

6. Action: Compile Summary & Send WhatsApp Message

• App: WhatsApp Business

• Event: Send Message

• Message: Use a Zapier Formatter step before this to compile the summary message.

  • Start with a title like "Recent Job Updates."

  • Use the output from the Looping step to list each job and its status (e.g., "JOB-12345: Approved," "JOB-67890: Not Approved").

  • This is another place for complex logic. You would need to add a step that checks if the "Find Job in Notion" step was successful or not (if it didn't find an ID, it means the job wasn't in the database). You could then compile a separate list of these "not found" jobs to include in the message.

Data In for ChatGPT:

prompt

Approved Jobs: 29157 29158 29159 Not Approved: 29160 29163 29164 AXXXX PXX *XXXXXXXXXXX* XX W XXth St, New York, NY XXXXX Tel: (XXX) XXX-XXXX IG: @XXXXXXXXX <https://www.instagram.com/XXXXXXXXX/?hl=en>

model

gpt-4.1-nano

temperature

0.0

description

From the following email text, identify all unique job numbers and their approval status. Job numbers are always a three to six digit string of numbers. Create a list of each job number. The status of each job number will be 'Approved' if the text indicates approval (e.g., 'approved', 'OK to proceed', 'signed-off'). The status will be 'Not Approved' if the text indicates a rejection (e.g., 'not approved', 'rejected', 'return'). Return the data as a JSON array of objects. Each object in the array should have two properties: job_number (string) status (string, either 'Approved' or 'Not Approved') The output must be a valid JSON array and nothing else.

arguments

1

Job Number

2

Status

required_Status

true

type_Status

string

description_Status

The value for Status should be "Approved" or "Not Approved"

enum_Status

1

Not Approved

2

Approved

required_"job_number"

true

description_"job_number"

Job Number should be an array of all three to five digit numbers contained in the body of the e-mail.

type_"job_number"

string

details_"job_number"

Approved Jobs: 29157 29158 29159 Not Approved: 29160 29163 29164 AXXXX PXX *XXXXXXXXXXX* XX W XXth St, New York, NY XXXXX Tel: (XXX) XXX-XXXX IG: @XXXXXXXXX <https://www.instagram.com/XXXXXXXXX/?hl=en>

details_Status

Approved Jobs: 29157 29158 29159 Not Approved: 29160 29163 29164 AXXXX PXX *XXXXXXXXXXX* XX W XXth St, New York, NY XXXXX Tel: (XXX) XXX-XXXX IG: @XXXXXXXXX <https://www.instagram.com/XXXXXXXXX/?hl=en>

details_(.) Job Number

Approved Jobs: 29157 29158 29159 Not Approved: 29160 29163 29164 AXXXX PXX *XXXXXXXXXXX* XX W XXth St, New York, NY XXXXX Tel: (XXX) XXX-XXXX IG: @XXXXXXXXX <https://www.instagram.com/XXXXXXXXX/?hl=en>

description_(.) Job Number

These are job numbers extracted from the email body.

required_(.) Job Number

true

type_(.) Job Number

array (strings)

required_Job Number

true

type_Job Number

string

description_Job Number

Job Number is an array of three to six digit numbers.

Empty fields:

What are the possible values for "Job Number"?

empty (optional)