Excel or Database Reference Field to prefill the form

  • d1vp
    Asked on December 4, 2020 at 2:21 PM

    Hello,

    I am working on a form that I want to reference a large data table. The large data table has every address, parcel number and parcel size in it. Someone doing data entry selects an address in the database, and the parcel number and size prefill the form for them.

    I have tried the Google Sheet integration, but not having much success getting the data validation to work.

    My form is: https://www.jotform.com/build/203250132878048

    Thank you

  • John Support Team Lead
    Replied on December 4, 2020 at 4:37 PM

    If you are using an Excel file, you may want to try using the Spreadsheet to form widget. It allows you to upload your spreadsheet to your form and use the values you have on the spreadsheet to pre-populate your form.

    By using a unique code that is present on every row of your spreadsheet, you can access that specific row that matches the unique code.

    The values are automatically populated on your form fields, as long as the spreadsheet column headings match your form field labels.

    Here's a guide on how-to-use-the-spreadsheet-to-form-widget. While here's a demo form: https://form.jotformpro.com/71500914392957

  • d1vp
    Replied on December 4, 2020 at 5:22 PM

    I did get that to import, however, what I would like is the self-complete line to start displaying what is in the database as the numbers are entered...

    Example - 12345 Main Street, Anywhere, MS

    As soon as 12 is entered, a list appears with the partial matches displaying...

    123 - Johnson Road

    1261 - Denver Highway

    1299 - Rockpile Drive

    12345 - Main Street

    The user could click on the match in the list, and have the fields then populate.

    Maybe I'm using the wrong database for that to work.

    Thank you

  • John Support Team Lead
    Replied on December 4, 2020 at 6:57 PM

    To clarify, there is no option to pre-populate the form fields with data from an external database using the features available in the form builder.

    So I may say the only way to achieve that is to either use the Spreadsheet To Form widget which doesn't have the capability to show an auto-completed list as you mentioned in your previous reply;

    Or use the AutoComplete widget to achieve the partial matches display you prefer:

    1607126140 5fcacc7ccb8a6 asaf Screenshot 10

    However, this is not applicable for large databases since you would need to create conditions for each record to pre-populate certain fields like this one:

    1607126216 5fcaccc80843f  Screenshot 21

    So if you have 100 options there, that means you would need to create 100 conditions as well.

  • John Support Team Lead
    Replied on December 4, 2020 at 7:06 PM

    I forgot to add the link to my demo form. Here it is: https://form.jotform.com/203387098520054

    Also, another option that I would recommend is to create your own custom webpage where you can embed the form's source code. Then add custom scripts there to allow each form field to extract data from your external database.

    This would require an average-advance knowledge in using PHP/Javascript. So I would recommend hiring someone to do this for you.