How can I use a Calculated Variable (Number) to look up an Data Element from a Table

  • Profile Image
    ButchJotForm
    Asked on April 29, 2019 at 11:58 AM

    I'd like to create a Table (or Spreadsheet) that could be accessed from within a Form that would have (for example) two columns:

    DAYS     PRICE

       1          5.00

       2         10.00

       3         15.00

       4         18.00

       5         22.00

       6         25.00


    Then I have some pre-entries and calculations that give me a result (for example) of "4" (Days)... which I would like to provide a result of Cost = $22.00.

    Note: the Prices are not linear with the number of Days.

    What is the best way to accomplish this?

    I started to do it with the "Spreadsheet to Form" Widget... but couldn't get the "Days" number into the Widget's lookup field.l

  • Profile Image
    ButchJotForm
    Answered on April 29, 2019 at 12:09 PM

    Oh... if the Spreadsheet-to-Form Widget only accommodates Strings, it seems that would be OK because I could convert the Answer Result (a string edition of a number) into a Numeric.

  • Profile Image
    Nik_C
    Answered on April 29, 2019 at 02:18 PM

    If you need to lookup data from some external source (like excel) you have to use Spreadsheet to form widget, we don't have other lookup data feature.

    This guide could help: https://www.jotform.com/help/442-How-to-Use-the-Spreadsheet-to-Form-Widget

    Please try using it and let us know you need any assistance along.

    Thank you!

  • Profile Image
    ButchJotForm
    Answered on April 29, 2019 at 02:28 PM

    Nik, I am sorry... but I indicated in my write-up of the issue that I HAD tried the Spreadsheet-to-Form Widget... and that the only problem with that is I need a method to "feed" the initiating variable into the Widget (rather than hand-entering it).

  • Profile Image
    Nik_C
    Answered on April 29, 2019 at 03:56 PM

    I'm sorry for the misunderstanding. 

    But I'm afraid that we don't have other option for lookup of the data.

    If you don't need the lookup data, that could be achieved with conditional logic, like to show certain data based on certain entered data.

    Also, I'm afraid that we don't have a method to insert value in the widget since the widget is separate iframe and data cannot be inserted in it except manual enter.