Jotform Tables: Extract the order total from the products payment field

  • Profile Image
    Tren_Long
    Asked 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

  • Profile Image
    Rose
    Answered on June 23, 2022 at 03: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.

  • Profile Image
    Rose
    Answered on June 23, 2022 at 03: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.

    1656013692_62b4c37c8ae92_

    Please give it a try and let us know if you need any help.

  • Profile Image
    Tren_Long
    Answered on June 23, 2022 at 04: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

  • Profile Image
    Rose
    Answered on June 23, 2022 at 05: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.

    1646355837_6221657d7bd3d_

    Select the form version, and click revert.

    1646355961_622165f94f847_

    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.

  • Profile Image
    Tren_Long
    Answered on June 23, 2022 at 06:35 PM

    Got that reverted!

    However back to the original problem:

    I am getting more info on my download than you had on yours:

    1656023723_62b4eaab491e8_


    Please advise

    Thanks

    Tren

  • Profile Image
    Rose
    Answered on June 23, 2022 at 08: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.

    1656030131_62b503b3749fb_

    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:

    1656030271_62b5043f30c40_

    Please give it a try and let us know if you need any help.