What have you tried with ChatGPT and how didn’t it work?

I feel like it should be able to give you an output in a useable format in a single step, but even if not, it should easily be able to put this into “csv format” so that you can use other formatted steps later on. 

Well, I say that. There’s a very good chance that I needed do something with the code that I don’t know how to, I have relatively no code experience so I merely copied and pasted from ChatGPT.

I found some code on another zapier community post about the problem with Jotform data having the ]|e, so I copied and pasted that into the zap, obviously didn’t work because I didn’t know how to alter the code to be for my input data. So I put it into ChatGPT to see if it could make the changes for me based on my input set. It tried, but when I put it back into the zap it told me input something or other was undefined? And so I started over and just asked ChatGPT to write me the code I needed to format my input set (copied and pasted the output of the Jotform submission in the zap) to be used in a zap code step to format into line item format, with examples. But every time I copied and pasted that code into the zap code step it gave me the same undefined input something or other. of course I already closed it all out in frustration after my 10th attempt at pasting the error message from the zap back into ChatGPT and asking it to make the changes and going around and around. I can go back through it if you would like, but if you have any better ideas I'm happy to try those out!!

Like I said, this very much could all be user error, I'm just now diving into the automation stuff so it’s all pretty new!! I am happy to take any advice you have!! Thank you so much for the quick reply!!

Let’s just start from the beginning. Given your data here, can you, the human, retype it in the format you are ultimately trying to achieve? That way I can see what may be needed to go from start to finish.

The first two items look like, line items in the format of “description”, “cost”, “quantity”, “total”. But then after the first two, I get lost in what those are or should be. (Maybe you already said that, but I was having problems following.)

Sorry, very stream of consciousness haha. Yes. So the data comes from a table on a Jotform that looks like this: 

So the technicians choose a service from the dropdown in column 1, add the fee in column 2, quantity in column 3 and the price in column 4 is automatically calculated. The form is setup to have space for multiple services, as we may perform up to ten services at a job. So when the data comes in from JotForm it will have the rows that are filled out, such as you noticed in the above data set. And then any unused rows come in as the “”,””,””,”0”].

So ideally what I would like is to get the above data set that comes from this table from:  "Basic Inspection Fee","200","1","200"]|,"Sprinkler System","35","1","35"]|m"Water Well","175","1","175"]|,"","","","0"]|""","","","0"]|""","","","0"]|""","","","0"]|""","","","0"]|""","","","0"]|""","","","0"]|""4","0","5","7"]|["0","1","2","3","4","5","6","7","8","9"]

To:

1: Basic Inspection Fee

   Fee: 200

   Qty: 1

   Price: 200

2: Sprinkler System

   Fee: 35

   Qty: 1

   Price: 35

etc.

So that next I can map those fields into the line item fields for the Zoho Invoicing. I believe that I can set the Zoho Invoicing to ignore empty rows, so I’m not too worried about those. I just need a way to I guess get rid of the brackets and quotation marks so that I can map the data into Zoho Invoicing. I have gotten far enough to get the line items separated, but they all still have the brackets and quotes and I'm hoping that I don’t have to do extra formatter steps to remove each of those characters individually, that seems kind of silly. 

I thought that was kind of the idea of the Zapier Utility line item-to-text feature, but it did not remove the brackets or the quotes when I tried to use it with several different inputs.

I hope that’s a little clearer, but please let me know if I can give you any other information to help! Thank you so much for your time!

You can use the “AI by Zapier” step to create a prompt, feed the Jotform data into it, and it will spit out the desired data.

I told it to output them as line items, but I didn’t add a next step to verify if they are, or it’s just formatted text. Here’s the AI step and its output:

Wow, amazing. I definitely tried the AI by Zapier but clearly didn’t do the prompt right because you nailed it. I will copy and paste that in directly and use it. Thank you so much for your help!!

Glad I could point you in the right direction. Like I mentioned, the prompt might have to be tweaked a little bit if the output isn’t actually line items. The more I look at it, the more I think it’s just formatted text.

Okay, I spoke too soon. So this all looks fantastic, but when I got to map the data into the Zoho Invoice, it is all returning as one block of text, instead of each item of each line item being mappable individually.

For context, this is what the Zoho Invoicing zap step is asking for and how the data is being given to me from the AI step:

Yes, that’s the part I’ve mentioned a couple times now that you’ll need to tweak the prompt for AI to get it to output true line items.

And if not, you can always use a formatter step to split text to line items, using the “newline” character as the separator.

Yes I see, sorry I missed that. Do you have a recommendation of how to do that? I tried a couple things with the prompt generator and then adding steps to the original one that said make them into individual mappable line items and it didn’t work (once again, super newbie, don’t know much!). If I do the formatter step to separate the text first, do I then have to run the AI step for each line item independently? That would end up being like, 18 different steps so I'd think it would be better to do it after. I tried the text to line item zapier utility and it did not return separate mappable items. 

Thank you so so much for your help!

I’ve updated the prompt to say this:

Your task is to parse this output and create formatted line items for a Zoho invoice. Follow these steps:

1. Split the input string by the pipe character '|' to separate individual entries.

2. For each entry, parse the comma-separated values within the square brackets.

3. Ignore any entries where the first value is empty or a number (these are empty rows).

4. For valid entries, extract the individual fields for each item to be used in populating a Zoho invoice with the following structure:

- Line number (incrementing for each valid entry)

- Item name (first value in the array)

- Fee (second value in the array)

- Quantity (third value in the array)

- Price (fourth value in the array)

Which then gives a JSON line item payload. You can use that with a looping step to fill your Zoho invoice.

When I use what you entered above, I'm still only getting one output that is mappable into the Zoho step. I tried making it give me each line item category instead of each line item itself, and eventually with a formatter step got the test step to look like each item was individually mappable, but when I got to the Zoho step it still only gave me the option of mapping the one ouput chunk of text.

Is there a simpler way I could/should be doing this? I thought that using a Jotform would be the easiest way to get the information into the format I need it for this, but obviously I was wrong. What I really wanted to do was be able to make Square invoice directly, but that’s not an option, so I'm trying to use Zoho Invoicing and that doesn’t seem to be working out either. Im just at a loss now.

You’re lucky I’m so obsessive. 

Here’s the A.I. prompt to take the Jotform garbage and transform it into a valid JSON payload:

You are a master of data parsing and JSON formatting, and you are asked to process Jotform output data into a structured format for Zoho invoices.

Given the following raw input text from a Jotform:

```

<<THIS IS WHERE YOU INSERT THE DYNAMIC DATA FROM THE JOTFORM TRIGGER>>

```

Your task is to parse this input and create JSON array of line items containing the key:value pairs for a Zoho invoice. Follow these guidelines:

1. The input consists of datasets enclosed in square brackets, separated by vertical bars (|).

2. Each dataset represents a row from the form and contains four values: item name, price, quantity, and total.

3. Ignore any datasets where the first value is empty or a number, as these represent empty rows.

4. For valid datasets, extract the information and create a line item object.

5. The line item object should consist of multiple key:value pairs, which should all be accessible to add to individual fields of a Zoho invoice.

Please remove any JSON markers. i.e. '''json at the beginning and ''' at the end. I literally just need the raw payload itself.

Then you’ll add Code step with Javascript:
 

const lineItems = JSON.parse(inputData.raw_json); // Parse the input JSON

// Process each line item to create an array of key:value pairs
const processedItems = lineItems.map(item => {
  return {
    item_name: item.item_name,
    price: item.price,
    quantity: item.quantity,
    total: item.total
  };
});

// Return the structured data
return { processedItems };

Here’s the output if all is working correctly:
 

If Zoho invoices has line item support, then your last step is Zoho Invoices. You just map the fields accordingly. For testing, I used Google Sheets, but this is what the setup looks like:

You’ll see there’s just a single row for each field and you may be worried that all 3 names show up on a single line item in Zoho, but if Zoho has line-item support, it will automatically add each row with the corresponding data.

BUT…

If Zoho does NOT have line-item support, then you’ll need to create a Looping step after the code block. It will be “Create Loop from Line Items”.

And inside the looping step, you’ll have the Zoho invoice, where each field is mapped to the loop value for those fields.

And that’s that.

Here’s the whole Zap if Zoho invoice supports line items:
 

And this is what it would be if Zoho does not support line items:

Wow, thank you sooo so much!!! I really appreciate all your work on this!!! Thank you thank you!!!

Hi ​@Jordan Butler,

Wow! Thank you for confirming that ​@DavidLGS ’s resolution got the Zap running. This will significantly help our Community members to have as a reference for the same issue.