-
Tren_LongAsked on June 23, 2022 at 12:44 PM
Hello,
I am trying to get the cost total from the form to show up on the spreadsheet (the total cost for an order). The "my products" column has all the info from the order, but I just need the order total so I can import it into other software.
Thanks
Tren
-
Rose JotForm SupportReplied on June 23, 2022 at 3:09 PM
Hi Tren_Long,
Thanks for reaching out to us for help. I am currently working on your concern and need more time. I will get back to you shortly. Thank you.
-
Rose JotForm SupportReplied on June 23, 2022 at 3:45 PM
Hi Tren_Long,
To extract the order total only from the 'My Products' column on your Jotform Tables, you'll have to first download the submission data as an Excel file. Then, on the downloaded Excel file create a new column and add this formula: = RIGHT(L2, 6)
The Excel formula will extract the last 6 characters of the column which includes the $ plus the order total. On the formula, you can change the column letter to where the 'My Products' field will fall once the Excel is downloaded. Then, drag the formula to the last row to add the formula to all entries.
Please give it a try and let us know if you need any help.
-
Tren_LongReplied on June 23, 2022 at 4:02 PM
Hi
Somehow all the data in the "my products" column has disappeared? It was there before but now all is missing. Is there a way to recover it?
Thanks
Tren
-
Rose JotForm SupportReplied on June 23, 2022 at 5:27 PM
Hi Tren_Long,
Did you happen to remove your 'My Products' field from the form and just added it back? Please note that if a field is deleted from the form, all the submission data associated with it will also be removed from the Tables.
If you indeed deleted the field, you can revert your form to its previous version by clicking on the round arrow beside the "last edited". Please see the screenshot.
Select the form version, and click revert.
If the deleted field is added back to the form, all the submission data should be added back as well to the Tables.
Please give it a try and let us know if you need any help.
-
Tren_LongReplied on June 23, 2022 at 6:35 PM
Got that reverted!
However back to the original problem:
I am getting more info on my download than you had on yours:
Please advise
Thanks
Tren
-
Rose JotForm SupportReplied on June 23, 2022 at 8:25 PM
Hi Tren,
Since your downloaded Excel file has different data in the cells of the generated Excel file, please insert a column beside the 'My Products' field. It should create column W on the Excel file. Then, on cell W2, paste this formula: =MID(V2,FIND("$",V2),FIND(" ",V2,FIND("$",V2))-FIND("$",V2))
Then, drag the formula all the way to the last row so the formula will automatically be added to all the submission rows. The data should populate the total and the word 'Transaction' beside it since there's no space after the amount so formula carried the word 'Transaction' too.
To extract the amount only, insert another column to create the column X and add this formula to it:
= LEFT(W2, 6)
Again, drag the formula all the way to the last row so the formula will automatically be added to all the submission rows. Under the column X the total amount is now extracted:
Please give it a try and let us know if you need any help.