Table, spreadsheet, array in form - for calculations in other field

  • Profile Image
    Asked on October 30, 2014 at 06:21 AM



    we have a robust price list with 1500+ different items.
    I'd like to place the whole price list in the form as a hidden field and I'd like to use it's values in different calculations depends on selections of user.
    I wouldn't like to take 1500+ separated number fields and use them in calculations, I think it's an abnormal workaround :-)
    I tried 'Spreadsheet' and 'Data grid' widgets, but these are not useful in my project because the values inside of widgets aren't accessable and calculable outside of widget (ie. in a calculation field).

    A dropdown list with 1500+ items also not a good solution because I can't make references in calculation field to an unselected item (it's impossible to calculate with an item that is not actually selected in the dropdown list).

    Has anybody any idea to help me? :-)
    What would be the most easy "much variables"-implementing method?


    Thank you!



  • Profile Image
    Answered on October 30, 2014 at 08:58 AM

    Hi Noemi,

    Can you create a form with the items that you are selling, so that we can take a look and better assist you with the best option to achieve your desired result.

    Please create a sample form and provide us with the link.

    We await your response. Thank you.

  • Profile Image
    Answered on October 30, 2014 at 01:11 PM
    Hi Shadae,
    it's very hard to build a form as complex as you need the final form...
    We have a price list that has got 1500+ different items of services,
    reductions and extra fees...
    Our small business has a transport service with 3 different lines inside
    of EU and holiday services in South Spain (accomodations, excursions,
    airport transfers etc.).
    We'd like to build a complex (Jot)form to change our actually working
    form made by Spreadsheet Converter.
    The final form would be cca. 10 page and about 150-200 input fields.
    The form would be show immediatelly the actual price in selected
    currency, and of course the total amount by all of selected items in the
    whole form.
    The best idea is to insert our price list as a table, spreadsheet etc.
    to the form as hidden field, and I would make references to its items
    from a calculation field, when it necessary, what is necessary.
    I made a very simple example of it, without calculations, just to
    present what I talk about...
    I don't know it would be enough for you, but I hope... :-)
    The main thing is I'm looking for a solution to insert a robust table
    (array) of different variables and call them step by step as references
    when it needs, from other calculation fields.
    I know, I can make a dropdown list with 1500 options and 1500 values -
    but then I can't calculate with any unselected value of it. Now it looks
    like the one and only solution is 1500+ separated, hidden number fields,
    named as 'Svedish ticket', 'Rental small cage' etc., and call to
    calculation when their service would be selected by user... But it would
    make me frustrated :-)), to copy/paste 1500+ values, add names them and
    place them as individual number field - and of course, make them as
    hidden field. Two month work... :-))
    Please, help me...
    Thank you
  • Profile Image
    Answered on October 30, 2014 at 02:25 PM

    Hi Noemi,

    This may be possible to accomplish when using the calculation field and the update form field condition.

    Please provide me with some clarification by addressing these questions:

    1. Will the currency, country and item determine the final price?

    2. Does the hidden price list, the 1500+ options, account for all the prices for all options (If the currency is Y and the country X and the item is Z, the price is A) or will different options have to be added to each other?

    3. You would like when all the options are selected for a final price to be calculated and the users are sent to a payment tool?

    Look forward to hearing from you to better assist you.

  • Profile Image
    Answered on October 30, 2014 at 03:11 PM
    Hi Shadae,
    thanks for your helpful work!
    My answers are...
    1.) The selected currency causes a 'multiplier' value, depends on
    selection. For example: we have the price list with values in Euro. If
    user selects currency ie. 'Hungarian Forint', then multiplier will be
    310 and all of calculated field that shows any amounts, will be: 'value'
    * 'multiplier' (and this way they will show the exact prices in HUF,
    instead of EUR.
    The selected country means only a selection, what transport line ordered
    by user. We have 3 different lines in EU, with populated timeline:
    spanish (Marbella-Budapest), swedish (Budapest-Stockholm) and "BNX"
    (Marbella-Amsterdam-Stockholm). The prices are differents in each lines,
    ie. a ticket for a passenger is 89 euros in spanish line, 168 euros in
    BNX-line and so on. Any of transporting service (passenger, package,
    pet...) available for different prices in a different line... (Sorry if
    my English would be too poor :-)))
    When user selects a transport service (ie.: "Pet transport"), then has
    to select a line also from a dropdown. If he/she selects a line, then
    has to select a departure/arrival country and town also. We have cca.
    1200 predefined prices for different towns in Europe and sometimes a
    door-to-door service in same town available for different prices in
    different lines! (ie: Berlin is cheap in swedish line, because this bus
    will go across the region, but more expansive in BNX line, because this
    bus has to take a large extra route to touch this city).
    Some items of price lists are not direct prices, maybe "percents". For
    example: if a good in parcel is 'fragile', it causes a 10% extra fee.
    The price list contains: Fragile: 10% (and so on... 'Keep cold: 20%',
    'Do not turn: 10%' etc.) Other items means some reductions (retour price
    ticket within 30 days is offer a 20% discount - the price list contains:
    Retour ticket: -20%).
    The user selects one or more transport services, then selects from/to
    countries, then selects from/to towns, specific service(s) and gives
    details, selects currency and payment method (this causes more extra
    fees), selects some special options, extras, reductions etc. and always
    can see the actual price of item. In the last page of form user can see
    a 'Summary', includes all partial prices (ie.: Pet transport total
    amount, Passenger ticket and extras total amount ...), a summary of
    summaries :-) (total amount), reductions (quantity discount, coupon
    discount, special discounts), extra fees (paying method, special fees
    etc.) and the final amount to pay. Everything on the selected currency.
    2.) The 1500+ item price list contains 1200+ door-to-door prices in
    different towns. Plus the prices of items, extra percents, discount
    percents. These values need to be available in the form to calculate
    continuosly the amounts, to give precise informations to our client. It
    means, some items depends on selection of one dropdown list, other items
    can increase/decrease amount by selection in a Radio button or
    Multiselect group, other items needed only in the background for
    calculations additionally. It's a complex situation, I can't say any
    universal rule when items are necessary...
    3.) When the Summary is ready and user sent the form to us, we check it.
    It is necessary, because - for example - user has option to select a
    town "NOT IN LIST", and then we recalculate manually the price of this
    door-to-door service. When checking is OK, we send a confirmation to
    user, including checked finally amount, and payment details, depends on
    his/her selection as payment method (PayPal, bank transfer, cash etc.)
    I know, I'm not an easy client of you :-)
    But if you can solve this situation and offer a good, acceptable method
    to use a robust variable list inside of whole form, then I will be the
  • Profile Image
    Answered on October 30, 2014 at 04:22 PM

    Please allow us some time to check the possibility on this requirement and get back to you with relevant information.

    Thank you for your patience.


  • Profile Image
    Answered on October 31, 2014 at 05:41 AM
    Hello Kiran,
    of course I'll wait for your response and hope, you'll find a good and
    useful solution!
    Thank you!
  • Profile Image
    Answered on October 31, 2014 at 10:09 AM

    Hello Noemi,

    Both Kiran and Shadae are offline right now.
    But, let me ask them about their progress on your request when they're available and we'll try to find the best solution for you.

    Best Regards.

  • Profile Image
    Answered on October 31, 2014 at 11:41 AM
    Thank you, Raul!
    I got a mail yesterday evening from Kiran and wrote: will work on my
    request and need some time to do it...
    So now I'm waiting for a solution and can't work on this project until
    it will arrive :-))
    Thanks again!
  • Profile Image
    Answered on October 31, 2014 at 01:29 PM

    As your requirement is studied, it seems to have a listing of 1500+ items on the form could be a challenge. Though JotForm supports to have many number of fields on the form, the more fields on a form the more load we have on the browser that might freezes the browser sometimes. Hence, I am checking for alternative solution instead of having individual fields for each item.


    Are there any sub-categories in the price list to have it simplified? Could you share us with the price list or the spreadsheet template that might help us in getting a different idea?

    Thank you.

  • Profile Image
    Answered on October 31, 2014 at 02:01 PM
    Hello Kiran,
    I attached the price list - sorry, it's on hungarian language but maybe
    you can understand what I need by using this list...
    Of course, I can wait more, it's an important work for us and I don't
    want to drop the use of JotForm (that we like already) :-)
    I hope, you can find any alternative solution...
  • Profile Image
    Answered on October 31, 2014 at 03:06 PM

    Could you send the attachment to since we didn't receive the attachment here?

    Thank you.

  • Profile Image
    Answered on October 31, 2014 at 04:11 PM
    I sent it to the given e-mail address!
    The subject is same as here, the body includes your name and
    'jotformforum_451572' reference.
  • Profile Image
    Answered on October 31, 2014 at 05:24 PM

    Yes. We confirm the receipt of your email. Thank you for sharing the file. Let me find what best we can do for you in this regard after checking the fields from the file.

    Thank you.

  • Profile Image
    Answered on November 01, 2014 at 03:41 AM
    Thank you so much, I'll be waiting for your solution!
  • Profile Image
    Answered on November 01, 2014 at 06:22 AM

    Szia Noemi,

    Would there be anything else on the jotform as well above or under it?

    Also, from what I see this is practically a list of options for the user to see/choose from.Since it is never a good design to have your users scroll down for so much on the form I believe that there would be some segmentation made on the form.

    It would be best if you could create these categories and placed them in a desired layout (check page break and form collapse tools in the Survey Tools menu) and we could assist you in adding the other fields and how to add them.

    I know that my colleague Kiran has told you that he will provide you a solution and based on his work it would be a great one, but you will certainly help him if you can create some elements that would serve as guides.

    Also, will the user be able to select any of the options or will they just be shown the them with the prices being changed on some other selections that they make on the jotform?

    Sorry for asking, but it is better to be sure instead of changing everything once finished.

    Best Regards,

  • Profile Image
    Answered on November 10, 2014 at 02:01 AM

    Hello Ben,


    thanks for your message and sorry for my long time silence (I'm just on route now).

    Of course I know it's a good option to use page break or form collapse, I use this method in our previous forms. At this moment my actual working form is a 8-page multisection form that is OK, but sending process frozen often and would like to find another solution (developer can't help us).
    We found another form builder, that is also OK - but can't handle strings answers correctly.
    You can find these forms here to see how I imagine our work, now by JotForm also :-) :
    Actually working form by SSC:
    The other form that is not passed by testing:
    These form are in hungarian language, sorry :-)), but you can see and understand what is my problem with including/inserting our price list...
    I hope, Kiran would develop some useful solution!
    Thank you so much!


  • Profile Image
    Answered on November 10, 2014 at 07:09 AM

    Hi Noemi,

    This page gives me a 404 not found error: Would you mind correcting the URL please? 

    But maybe creating the form and using the form source codes method with complete CSS and JS files and hosting these files to your own server would help you speed up the form. You will be hosting the form files, but the submissions would still go to our servers.

    With regards to the form design and content, that is indeed a very long form. And some of the items has its price based on category. I'm not sure how would I go on creating this form, honestly. Maybe you could upload this PDF file to your website and use it as a reference and process the orders "manually". We'll, that defeats the automation process.

    Will go for Kiran. Let's see what he will come up with.

    Thank you!

  • Profile Image
    Answered on November 13, 2014 at 07:11 AM
    Hello Welvin,
    ouh, I'm sorry...
    The URL is:
    (Really sorry... :-) )
    Hosting CSS and JS files in our server is a good idea, we can do it. Our
    hungarian server provider offers PHP, MySQL accesses also.
    I'm waiting for Kiran's solutions so much :-))
    Have a nice day!
  • Profile Image
    Answered on November 13, 2014 at 07:39 AM

    Hello Noemi,

    Thanks for sharing the links. I believe that your current working form by spreadsheet converter is the same working at Being complicated and having language barrier, I am trying to understand the model how it works and we may then come up with a basic template that can be modified as per the requirement.

    Since you have access to your hosting servers, as Welvin mentioned the source code method might work, but then again we'll need to get the form ready. Please allow us some more time to work on this and let you know the possibilities of the solution.

    Thank you for your patience.

  • Profile Image
    Answered on November 13, 2014 at 09:41 AM
    Hello Kiran,
    thanks for your message!
    Yes, you see right, the form currently used by us is made by the
    'Spreadsheet Converter' and available in URL you copied.
    This is a useful form builder (SSC), but the main trouble is the sending
    process (and have to say, JotForm is much more customizable than SSC).
    The sending process stops too often, without success (nothing happens),
    but if our customer press the SEND button again, an error message
    appears 'The form is already sent'. Our customers are frustrated of this
    situation and we have to change it. Because of support of SSC can't help
    us, we found JotForm and it's a real love at first sight :-)
    We hope, JotForm can offer a solution and we build a good, useful,
    modern design form to our customers being satisfied.
    Of course I can wait for your good work, and I have to say a really big
    thanks to support team of JotForm, you are really helpful, fast and
    friendly team!
    Thank you!