- vitaminer_janinaAsked on April 18, 2017 at 10:36 AM
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,
- JotForm SupportWelvinAnswered 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.
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.
- vitaminer_janinaAnswered 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?
- JotForm SupportWelvinAnswered 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.
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/.
- vitaminer_janinaAnswered 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.
- JotForm SupportWelvinAnswered 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.