Excel document or list containing 1000 unique numerical codes. Can Jotform validate a form entry against them? And return result?

  • Profile Image
    rushhourlocal
    Asked on February 13, 2018 at 11:54 PM

    For example, my form will have one field like this:

    "Please enter your code: _____________

    SUBMIT" 

    Something like this:

     1518584148validation example.png

    I would like to upload a list of these codes as part of creating my form... or have Jotform pull them from Google Sheets, or somewhere else.

     

    Then I just need Jotform to do 1 of 3 things:

    #1 - if the submitted entry matches a number on the Excel sheet, Jotform returns something like "Thank you, your code has been validated at {current time}!" and saves this information (see #2 below)

    #2 - if the submitted entry does match a number on the Excel sheet, but has been previously validated, Jotform returns something like "Thank you, this code is valid but has already been validated at {time previously validated}"

    #3 - if the submitted entry does not match a number on the Excel sheet, Jotform returns something like "Sorry, this is not a valid code."

    I am pretty sure this is possible, but I do not know how to search for something like this in other support tickets that are posted.

    Can you help me configure this, or point me in the right direction? Thank you!!!

    Adam

     PS. I started to build this but am getting stuck on the way to integrate validation. Form I started is attached.

  • Profile Image
    BJoanna
    Answered on February 14, 2018 at 04:32 AM

    It is not possible to pull the data from the Google Spreadsheet and check if the code was previously used or not. 

    You can use a conditional logic to check if the code is valid or not and show a custom message to the user. You can use conditional state contains and list your codes there. Separate them with the comma and space. 

    1518600089code.JPG

    To prevent duplicate entries with the same code, you can use Unique Question feature and set the custom message that the code was already used. However, it is not possible to show the time when it was used. 

    How to set Form Limits Based on a Unique Question

    How to Change Form Warnings

    1518600427valid.JPG

    Here is my demo form: 

    The valid codes are: 111-111-1111, 222-222-2222 and 333-333-3333.

    Feel free to test it and clone it

    Hope this meet what you are looking for. 

  • Profile Image
    rushhourlocal
    Answered on February 15, 2018 at 03:18 AM

    Hi - thank you, that gets me much much closer to the result I am looking for.

    However, I cannot clone the form because you did not include a link to it!

    Do you mind to share it here? Definitely will help me and possibly others.

    Thank you!

  • Profile Image
    BJoanna
    Answered on February 15, 2018 at 03:56 AM

    Sorry about that. It seems that I forgot to add the form link. Here is my demo form - https://form.jotform.com/80441559863969