Create a table searching answers to multiple questions?

Hello, I have been trying to figure this out by myself but I am not succeeding. I am trying to create a table that summarizes how much of our pest control chemicals we have used this year. This information is submitted through a form our technicians fill out following a service. On the form, there are 8 chemical sections, and in each section they select the chemical from a drop down, then fill in the quantity used.

I'm looking to create a table that has columns labeled for each chemical we carry, and rows for each job we do. And when a submission comes in, it will map how much of each chemical was used into the appropriate chemical's column. I guess where I'm lost is that the spots on the form that the guys fill out are drop downs, not set sections for each chemical since we use different chemicals for different kinds of jobs. I need to figure out how to get it to search which chemical has been entered and then map it to the correct column, and I'm not sure how to do that. Any assistance would be greatly appreciated! Thanks!

Hi Russell,

Thanks for reaching out to Jotform Support. I'm an AI assistant. I'll do my best to help you until someone from the support team is available.

To create a table that summarizes chemical usage from your form submissions, you can use Jotform Tables. Here’s how to approach it:

1. Collect submissions in Jotform Tables: Your form submissions automatically populate a table where each submission is a row.
2. Create columns for each chemical: Add columns named after each chemical you carry.
3. Map chemical usage: Since your technicians select chemicals from dropdowns and enter quantities, you can use formulas or filters in Jotform Tables to search which chemical was selected in each submission and place the quantity in the corresponding chemical’s column.
4. Use filters or grouping: You can filter or group submissions by job or chemical to organize the data better.
5. Consider creating custom views or tabs: This helps keep filtered or grouped data accessible for analysis.

This setup allows you to dynamically map quantities to the correct chemical columns even if different chemicals are used per job.

Give it a try and let us know how it goes.

Hi Russell,

Can you further explain your requirements so we can check for a workaround that will suit them best? Are you looking to have the search table within the form while answering it, or have it in the Jotform Table after the form has been submitted? Also, when you say you'd like to map it, are you referring to mapping a selected option to fields or tables on a PDF? Lastly, do you have a PDF version that you'd like the answers to be mapped into? If so, you can consider using the Smart PDF Form Builder.

Meanwhile, if you can take a screenshot for a sample of the desired output, that'll help us to understand better the request is. Let me show you how to post a screenshot to our Help Center:

1. On the Help Center page, scroll down to the Your Answer section and click on the Image icon.

2. Drag and drop your image into the Upload box, or click on it and select your file.

3. Then, click on the Add button in the bottom-right corner of the window.

4. To resize it, click on your screenshot and then click on one of the boxes in the corners, and drag it inward.

5. Once you're finished, click on the Post Answer button at the bottom right of the Your Answer section.

That's it. Once we hear back from you, we'll be able to help you with this.

I just need the table after the submissions come in to total the correct data in the correct column, I do not need the table in the form. And no, I'm not looking to do the PDF yet, just the table.

I tried to create the columns for each chemical, but I'm not really sure what type of column to use. Because the way the form is set up, the techs may put the same chemical in different places for different jobs. Ex: for one job, they may use Demon Max and Termidor. So they enter the information for Demon max under Chemical 1 and Termidor under Chemical 2. And then on another job where they use the same two chemicals, they may put Termidor under Chemical 1 and Demon Max under Chemical 2. So I need the table to be able to find when each chemical is entered into any of the 8 Chemical options on the form, and then put the amount used in the correct column on my new table for that chemical. I hope that makes sense.

I don't really have a screenshot to send since I don't have the columns built because I don't know which type to use. I thought it would be a rollup, so I could use some sort of if/then formula for the columns, but every time I try to do a roll up it sends me to make a connection column, and I'm not really sure which column to use for that either, since the same chemicals aren't in the same field on the form every time. I guess I could technically redesign the form, but I didn't really want to use space for every chemical we have because some of them we use very rarely. So the dropdown was the best option, but now I need to be able to easily total up the data.

Hi Russell,

I cloned your form to better understand the requirements, and I think I got it. I see the Chemical Usage tab in the form, and the eight section collapse to represent each of the chemical used. Check out the screenshot below for reference:

Could you confirm first that each of the section collapse represent the columns you want to add in the table? If that's the idea, then, there should be at least eight columns that will be added in the submission table. Each column represent each chemical selected/used, and have the details summarized.

I think this is achievable using the filter feature of the submission table. You can check for more details from the guide about How to Filter Entries in Jotform Tables. But we need your confirmation first if this is actually what you're looking for.

Once we hear back from you, we'll be able to help you with this.

Yes that's mostly correct. Except I'd like to have the columns be for each of our chemicals we have in stock. So there will be a column for each of the options in the "Chemical #" dropdowns. And then there will be a row for each job, and it will fill in the amounts used into the correct column for the correct chemical. So each row will have several empty cells because we don't use every chemical for every job. But the goal is to be able to see how much of each chemical we have used during the year, for our licensing reporting.

Hi Russell,

Things are much clearer now. So base on Chemical # dropdown list, there should be 15 columns, representing each chemical. But could tell us which question/field is the job? Was it the Treatment Method?

Actually, before this, I've tested the filter feature using my test form. You can check out the form table here. What I did was, I've added a new table tab representing each chemical, and I filtered the tab so that only the specific chemical data will be in the table tab. There will be 15 tabs, to represent each chemical from the Chemical # dropdown list. See the screencast below for reference:

The Qty can be summarized straightforwardly, as there is an existing option for that. But I just need to understand the job data first, to include it in my demo table.

Once we hear back from you, we'll be able to proceed to the next steps.

Amazing! Thank you so so much! This is absolutely perfect!!

The "job" will be referenced by the invoice number, so I would use a connected column for the invoice number, then lookup columns for the service address and the service date. I can do that much at least!! I'm not sure if that answers your question or not.

The tabs for each chemical works great, because then I could do a summary with all of the totals on another table but also see all the jobs amounts separately on the tabs!

Hi Russell,

I'll need a bit of time to look into this. I'll get back to you as soon as I can.

Thanks for your patience, we appreciate it.

Hi Russell,

Using the invoice number as the job reference along with lookup columns for the service address and date can be a great approach. That setup will give you a clear and organized view of each job, and tying that into the chemical usage tracking will make your summary and reporting much easier. If you need any more help, let us know and explain a bit so we can help you better.

Reach out again if you have any other questions.

Okay, I'm realizing now this is missing one thing. When you are filtering by the chemical name in the example above, it is only searching the "Chemical 1" answers, and not including any instances of that chemical in answers to "Chemical 2" through "Chemical 8".

So if two submissions were as follows:

Job 1 - Chemical 1: Demon Max; Chemical 2: Termidor; Chemical 3: Alpine Dust

Job 2 - Chemical 1: Alpine Dust; Chemical 2: Demon Max; Chemical 3: Termidor

The way you have it above, if you filtered for Chemical 1: Demon Max, only Job 1 would be included. It would not pull in the Demon Max info for job 2 because it is listed in the "Chemical 2" field. Does that make sense? I need it to be able to find Demon Max in any of the 8 chemical fields. Is that possible?

Hi Russell,

When using the filter in Jotform Tables, it will only search within the specific field you applied the filter to. For example, filtering “Chemical 1” for “Demon Max” won’t include entries where “Demon Max” was selected in “Chemical 2” through “Chemical 8.” One option would be to integrate your form with Google Sheets. In Google Sheets, you can set up formulas that check all eight fields per row and sum up the quantities whenever the selected chemical matches. This gives you much more flexibility to track total usage per chemical across all jobs.

Let us know if you have any other questions. 

But you're saying it's not possible to do that within Jotform tables in any way? Without another app?

Hi Russell,

Yes, that's right, as mentioned by our teammates, currently, Jotform Tables filters work field by field, so they don’t search multiple fields at once automatically. And one option is to connect your form to Google Sheets, where you can combine all the Chemical fields and run a single search or summary across them. Many users find this super handy for cross-field tracking.

Let us know if there's anything else we can help you with.