What is JotForm?
JotForm is a free online form builder which helps you create online forms without writing a single line of code. No sign-up required.

At JotForm, we want to make sure that you’re getting the online form builder help that you need. Our friendly customer support team is available 24/7.

We believe that if one user has a question, there could be more users who may have the same question. This is why many of our support forum threads are public and available to be searched and viewed. If you’d like help immediately, feel free to search for a similar question, or submit your question or concern.


  • Profile Image

    Is it possible to add values of a database to the calculation?

    Asked by vitaminer_janina on April 18, 2017 at 10:36 AM

    Hi there,

    I've got a question. I want to create a complex calculation form which is based on a database with lots of values. In the screenshot you can see a part of this database. In response what the user types in the form the database should "decimale" itself so there are just the items with similar values left.

    E.g. only the first item of the database should be left when the data in form is "Rund; 0; 508,0; 2,0;0,7"
    The form should be able to recognize that the other items don't fit with that values.
    Plus it should be able to calculate with some of the "Ed" values.

    Do you have any idea how I can set up such a database? I already tried the widget "Data Grid" but I wasn't able to filter the items according to the form inputs and I also wasn't able to calculate with the different values of specific items.

    Maybe there's a nice walkaround to "fake" such a database but keep the database structure and all values of an item.

     

    Thank you and best regards,
    Janina

    Screenshot
  • Profile Image
    JotForm Support

    Answered by Welvin on April 18, 2017 at 12:09 PM

    Maybe, you can use our spreadsheet widget for this calculation. Link to the widget is here: https://widgets.jotform.com/widget/spreadsheet

    You can use Match Functions in the widget, reference here: https://developer.mozilla.org/en-US/docs/Web/JavaScript/Reference/Global_Objects/Math#Methods. Or this: 

    https://www.jotform.com/help/267-Form-Calculation-Math-Function-Reference

    If there's a formula in the shared screenshot, please share it with us so we can check and help you do it in the widget. I'm not sure if it's possible, but we can try it. 

  • Profile Image

    Answered by vitaminer_janina on April 19, 2017 at 09:24 AM

    Thank you for your answer.

    In the database of the screenshot aren't any formulas and it won't be visible in the frontend. The thing is that there will be some formulas in the form but they will be based on the user's inputs and they'll have to refer on the database's numbers. It's difficult to explain but I'll try it:

    In the form (independed of the database) will be two numbers calculated.

    The first value should be compared to the "Ed,R0,max"-column in the screenshot (only the smalles one of all matching results which are bigger).
    The second value should be compared e.g. to the "Ed,R60,max"-column (depends on the user's input)(again: only the smalles one of all matching results which are bigger and independend of the results of the first match-searching).
    Now the database/system/whatever compares both values and gives that row back which contains the bigger of both matched values (or one specific cell e.g. "Größe").

    It it possible to get the data of one specific cell into a text field? And it's possible for the widget to move on or back in the colums to it dynamically knows where it has to pick the datas up from?

  • Profile Image
    JotForm Support

    Answered by Welvin on April 19, 2017 at 10:10 AM

    In general, using an external database to fetch a data and compare in the form are not possible. I am thinking of utilizing a few of our features combine to make this possible. But based on the screenshot, it seems that you have a thousand if not a hundred of rows in the database. That makes it hard. 

    For example, you can create the database using our textbox fields and have the data as the default value. With this, you can compare each field and show a result by conditional logic.

    https://www.jotform.com/help/268-How-to-Insert-Text-or-Mathematical-Caculation-into-a-field-using-a-Condition

    However, if you have a thousand of rows, that means you'll have 14, 000 textbox fields which will slow down the form. Worst case, the form builder will trim the form (character limit). 

    I am sorry, but maybe you can program this up by API. This is a technical one, so maybe you'll hire someone to do it for you. You can hire a freelance developer from Upwork or Freelancer or Peopleperhour. 

    For API reference, check this page: http://api.jotform.com/docs/

     

  • Profile Image

    Answered by vitaminer_janina on April 19, 2017 at 10:42 AM

    I just looked up there are exactly 307 rows...

    Let's say all of it is transfered into the spreadsheet widget or the input table.

    Is it possible to choose single cells of the spreadsheet widget (e.g. E2)? Maybe in combination with the field infos? So it can be transfered into a text field and then in conditions?

    Is it possible to check checkboxes of the input table by conditions? I only saw they can be checked by the user.

  • Profile Image
    JotForm Support

    Answered by Welvin on April 19, 2017 at 12:11 PM

    Unfortunately, you cannot fetch the values from the widget in the conditional logic. You also cannot prefill the input table.

    Checking the checkboxes in the input table by a condition is also not possible. 

    The only way would be to create a row of textbox fields. You can create a column by positioning the fields: https://www.jotform.com/help/90-Form-Field-Positioning. It's a whole lot of fields and manual typing of data if you are going to adopt the textbox method. You may consider trying one row first.