How to link response to a question to a value for calculation

  • LDOE
    Asked on July 21, 2021 at 7:13 PM

    Hi, I am trying to link a response to a question (a zip code) to corresponding value based on the zip code. I tried to link using jotform tables, but I might be overlooking a feature.

    We are basically trying to estimate a dollar amount based on a few different factors,

    Base grant 10,000

    An example of the formula:

    1626908607 60f8a7bf6fb1e  Screenshot 10

    The SVI is based on zip code entry. We have a table with corresponding SVI to zip codes. How can we match the table to the response from the form to allow us to calculate this? How much CPU/Server power would this take if possible?

    It is a one to one match, zip code = SVI.

    The table is named ZIP_SVI_Master

    The form is Arpa Stabilization Grant Application


  • Welvin Support Team Lead
    Replied on July 22, 2021 at 5:28 AM

    I tried looking for the fields in the form, but I'm not sure which part here. I see you have Zip which is our Spreadsheet to Form widget. This is the best tool to use when getting a specific result from a long list of codes, in this case, the Zip.

    You may want to get the result conditionally.

    https://www.jotform.com/help/268-how-to-insert-text-or-calculation-into-a-field-using-conditional-logic

    Before I'll proceed with providing you a demo, I want to know if the questions are select fields or input types. Can you label them, please? I presumed the following:

    #ccap = user input

    Center Type = user input

    Non-Trad to ESL = list of options to select.

    Is this correct?

    And if you don't mind me asking, how both #ccap and Center Type are calculated? Is there a percentage?

    Thanks

  • LDOE
    Replied on July 22, 2021 at 10:22 AM

    capacity - # - base of all calculations

    capacity * $ base amount = estimated capacity base amount (all below calculations are dependent on this number)

    #ccap/capacity - percentage of ccap served. Field Name CCAP Percentage

    Based on that percentage - % * total capacity Base Amount

    1626962199 60f97917b53e6  Screenshot 10

    Do you serve infants - Yes/No, Yes = 15%*capacity Base Amount

    Do you serve toddlers - Yes/No, Yes = 15%*capacity Base Amount

    Do you offer non-traditional hours? (overnight, after 6 pm, before 6 am) - Yes/No, Yes = 5%*Capacity Base Amount

    Do you offer Transportation (excluding field trips) - Yes/No, Yes = 5%*Capacity Base Amount

    Do you offer ESL services for non-English speaking children/families? Yes/No, Yes = 5%* Capacity Base Amount

    Zip

    RPL Theme - Dependent on table, Calculation conditioned by below criteria (percentage * Capacity Base Amount

    1626962669 60f97aedbd2c3  Screenshot 21

    We would like to present them an estimate of their grant payment based on their answers to these questions.

    I created a field Capacity Base Amount with calculation.


  • LDOE
    Replied on July 22, 2021 at 12:42 PM

    I think i am figuring it out, i am using greater than and less than formulas in calculations to get the desired additive value. :)

  • Welvin Support Team Lead
    Replied on July 22, 2021 at 6:29 PM

    Thank you for the details, and it's great to know that you have figured it out.

    Please get in touch with us again if we can help in any way.