How can I use a table to populate other fields like a vlookup in Excel?

I need to create a form that sets several other fields based on the result of one drop down. There are too many possibilities to use individual conditions. Is there an existing method to achieve this?

For example, if Dropdown1 selection is "A", then Textfield1 value is "A1", Textfield2 value is "A2", etc.

Greetings.

You can use Spreadsheet to form widget to load data from Excel into the form fields. To learn more about this, please visit the following link:
https://www.jotform.com/help/442-how-to-use-the-spreadsheet-to-form-widget

If you would like to use data from another form, you can use lookup directly from JotForm Tables, however, you will need to select the lookup value from Table, you can't do it from the form.

To create a lookup, you will first need to add a connection between your tables. To explain this better, let's set up an example. I have created two tables - users and project. Each project can be assigned to multiple users.

Once you have your tables set up, click on Add to add a new column. In this example. I'll add it to the Projects table, so I can connect users to a specific project:

From here, select connections and connect Table or a Form:

Proceed with the next steps and create a column.

Now, you will be able to select the value from the second table and see it in the first one. You can click on the link in the selected value which will open a popup where you will see all information about the selected row from the second table, and all rows in the first one where this value is selected:

Now you can add a lookup by selecting the new row option, and clicking on Lookup under the Formula tab:

You will need to select the previously added connected field and select the field from the second table that you wish to show. Now, if you update the first field, this one will update automatically:

Let us know if you have any additional questions.

I'm not sure where looking up from one table to another is helpful for me. If I'm not talking to and from tables and forms, then I'll just download the data into Excel where I can meet my needs with less of a burden.

I'm very confused about what seems to be very fundamental requirement with this application: The person filling out the form doesn't know the "unique ID" record needed in the uploaded Excel file, so it would make sense to have that be a drop down to select from, then that selection would drive the auto-population.

It feels like a big miss that I can't select a name from a drop down and have an email or phone number populate in another field that is associated with that name. I also have QR codes on our machinery that I can scan, but they are asset records (numeric) so I need to associate a description when they are scanned. I can't seem to figure out how to do this and it feels ridiculous that I can't, so I have to be missing something. What am I missing?

We have a conditional logic feature that will allow you to populate fields based on an input/selection from another field. However, we think you have a database with a long list of selections, so my colleague proposed an ideal way to do it - by the spreadsheet to form widget.

You can make this two-way. Select from a dropdown and prepopulate the widget with that selection by conditional logic - then the user has to click the button of the widget to fill all other fields related to the selection.

Here's a short demo for the above method: https://form.jotform.com/212048656730051.

Now, by design, you can hide the input box of the Spreadsheet to Form button by custom CSS codes to make it look like there's only one field there.

Here's a demo: https://form.jotform.com/212049073437958.

Well, it's still a complicated approach, I would say. However, you only need to do this once, and the end-users would have a greater experience with filling up your form. We can help you with all of these. Just create the form, upload the file in the widget. Let us know the URL so we can have you the custom CSS codes.

As for the QR, I think we can do the same method above: Scan QR using our QR Scanner widget, autofill the spreadsheet to form widget with that scanned QR code, the user has to click the button to autofill the description to a different field (maybe, long text entry element). How's that? I can create a demo for you if you want to try it. Let us know.

I hope that helps.

Looks great! So, I'm noticing that I'm not clicking on the "click me" button but the data in the result fields is updating anyway. Is that intentional? If so, can we hide the button so only the drop down and the result fields are visible?

I made a sample table that I will populate later. I'm assuming I can copy a large set of data from Excel and paste it into a table, correct? Here is the table:

https://www.jotform.com/tables/212033836375050/212057978894069

The unique record is the asset number. This is what will be scanned by the QR Code in the form. Once the scan is complete, I would like the rest of the information to populate in the respective fields.

The form is here:

https://www.jotform.com/build/212033836375050

Thank you for all the help!

When data is passed to the Spreadsheet to Form widget the widget will be automatically triggered. You can hide the Spreadsheet to Form widget with CSS.

Demo: https://form.jotform.com/212061045802947

I inspected your form, but I did not find the Spreadsheet to Form widget. You need to add the Spreadsheet to Form widget to your form and upload your Excel file with the data to the widget.

Related Guide: How to Use the Spreadsheet to Form Widget

I misunderstood and thought it was referencing a Jotform table. I added the spreadsheet to form widget and uploaded the file. Thanks!

HI! Checking back to see if you need anything else from me or if you have a timeline on the completion of the support? Thanks!

From your previous reply, we understood that you solved the issue.

I saw that you added the Spreadsheet to Form widget to your form. Is the widget not working? Can you please provide more details?

If you read up in the string you'll see Welvin offered up some programming and support to help meet the need. I'm not even close to a solution at this point.

Greetings.

I assume you wish to populate form fields based on the value of the QR code.

To do this, you will need to add a new Update/Calculate Field condition:

Here, you need to check if the QR code is filled, and then copy the scanned value to the widget:

However, this won't work if the Spreadsheet to form widget is hidden. You will need to show it by right-clicking on it:

To hide it, you need to add the following CSS:

#id_47 {

 visibility: hidden;

}

You can add the CSS from the Form Designer:

Please note that this will completely hide the question in your form. If you wish to update the field, you will need to remove the CSS.

Let us know if you have any additional questions.

Hi there,

Unfortunately, there's not any way to view or download the spreadsheet once uploaded. You will need to have it in your backup in case you need to edit and upload it again.

FWIW, the CSS code provided hides the field but it leaves the blank space on the form where the widget is. It looks like {display: none;} will remove the blank space from the form as well.

Yes, the code

#id_47 {



 visibility: hidden;



}

will only set the field hidden but it is still displayed and would occupy the field space.

If you add your display:none code then the whole field wouldn't be displayed in the form.

If you need further assistance, let us know.

It was stated above that

"When data is passed to the Spreadsheet to Form widget the widget will be automatically triggered. You can hide the Spreadsheet to Form widget with CSS."

This does not appear to be working and I'm not sure why.

The data is loading into the widget but I have to click the button for the data to populate in the respective fields.

Can someone help me with this?

Hi there,

Please go to the widget settings and make sure that Autofill By Condition is set to Yes.

I applied it in this demo form (https://form.jotform.com/212431788433962), and it is automatically populating the fields. Feel free to test this on your end.

I hope this helps. Please let us know if you need further assistance.

That was my problem. Thank you!

I'm trying to apply the same idea to another form but when I put

#input_657 {

  display : none;

}

in the css code it doesn't do anything. For whatever reason, the sheets to forms is bringing in "#input" instead of "#id" like the previous ones but I'm not sure what the issues is.

Can anyone help me with why this isn't hiding the field?

I answered my question in my own question...

Please disregard.

Thanks.