Form from one sheet populates another sheet

Paul Grim

I'll need to read and digest your post in detail when it's not the end of a long day of staring at spreadsheets! 🤪 Thanks for the detailed suggestions; once I read through it I'll let you know if I have any questions!

Paul Newcome

Haha. I definitely know exactly what you mean there. No worries. I am close to being a the end of my rope day too.

Sean Murphy

@Paul Grim @Paul Newcome Hey guys, so I'm seeing the conditional formatting as an option with forms. I'd like to do a similar inventory sheet for construction manufacturing materials. Have you all done a test run with what's been talked about in the thread? I've never really wrapped my head around how the forms options could work for me

Paul Grim

Awesome! I hadn't noticed that they rolled out the new functionality. I will have to test this today and re-post with my results.

Andrée Starå

Woho! It's live now! 

😀



Paul Grim

I just took a look at the new functionality, and I'm sorry to report that it does not do what I'm looking for. Major bummer! All it does is allow you to display one or more additional fields based on a selection in another field. It does NOT automatically filter the other fields in relation to the user's selection in another field.

For instance, let's say you're sending out an invitation form. You might have a field that captures whether the recipient is "Attending" or "Not Attending". The new functionality now allows you to specify that if the user selects "Attending" then you will display an additional field called "Guest" to capture whether or not they are bringing a guest. If they select "Yes", then you can further display an additional field asking for "Guest's Name". And so on.

What I'm looking for is the ability to have the other fields in the form filter themselves based on the previous selections.

For instance, let's say we have a list of inventory items, along with the vendor that carries each item. We have dozens of vendors in all, but we only stock 9V batteries from 2 different vendors. When the user select the item "9V battery" I want the vendor field to only display the 2 vendor names that carry 9V batteries. Otherwise, the user will be presented with a list of ALL vendors, which will not only be confusing but they could also easily select an invalid vendor (one who does not carry 9V batteries).

So unfortunately the new functionality is not going to help in that regard. Argh! 😫

Paul Newcome

@Paul Grim Let me start by saying the below could prove to be quite a bit of effort to scale, but on a smaller scale, it shouldn't be too terrible to set up...

In the example you provided where there are multiple vendors and only certain ones provide certain stock...

You could create a checkbox type column for each vendor and then use the conditional logic to only display the appropriate checkbox fields (display specific vendor options) based on the product selected.

I know this could take some time to set up if you have a lot of vendors and it isn't exactly ideal, but it could possibly work.

And even though it is a new feature, Submitting a Product Enhancement Request certainly wouldn't hurt. I would think that it may stand out a little more than your older features because it is such a new feature and they are probably going to be tracking feedback closely for a little while after the launch.

@Sean Murphy Can you be more specific about what you are attempting?

Paul Grim

@Paul Newcome Thanks for the thought -- it's a good one, but we have WAY too many items and vendors to make that a viable proposition. I will definitely submit an enhancement request though!

Strange that Smartsheet didn't include this functionality with forms from the get-go. If you've ever filled out an online form, you know how common the feature is (e.g. If you select "Toyota" from the "Make" field in an online form, then you only get to see Toyota models listed in the "Model" form field, etc.)

Paul Newcome

@Paul Grim Yeah. I knew it wouldn't scale well, but I do agree it would be a great addition.

In the meantime, have you looked into Google Forms? It has been a while since I have used them, but I know they have a lot of neat features built in. Plus Google Forms integrates very easily with Smartsheet.

Paul Grim

@Paul Newcome I have not looked into Google Forms, but I will definitely do that!

Sean Murphy

@Paul Newcome My goal is to tackle a monster task of wrapping my hands around inventory management of our warehouse. I might be getting ahead of myself, but I'll attempt to explain it.

A master sheet with all inventory- with as many columns needed to make it work. This will have the reorder points, status, etc.

A Form "Delivery Receipt" that an employee can use to input a new delivery from the vendor, with drop downs of items and a way to specify the number of items. From what I can tell so far, it looks like the form would have to be submitted multiple times for each specific SKU to line up item and quantity- I could be wrong. If I am right, then this wouldn't work, since it would take some time to input a delivery with 20 different SKU's

A Form "Inventory Transfer" that employees can submit when inventory from the warehouse is transferred to a job site. This would be similar as above, multiple items, quantiles (negative value), Job number, date, names, etc. This could fill into the same sheet as the Delivery Receipt.

I imagine the Form sheets to be feeding into a "inventory reconciliation sheet" where I would manually address each line, by adding to the master sheet or moving the transferred inventory cost to the specific job.

The last thing with this I'm hoping to accomplish is creating some simple sheet where we can do a physical inventory and input directly into the iPad on a monthly/weekly/daily basis. This sheet I need to figure out the best way for it to talk to the main sheet or if I need to manually reconcile those counts as well?

There is roughly 60 different SKU's, but only really only 20% of it has a high run rate. Down the road, I want to build this into my project management sheets which you have significantly helped me build on the production/labor side, and this is the last piece- material costs. I appreciate any advice/feedback!

Paul Newcome

@Sean Murphy

I actually have a few ideas on how we can make this work. The best way OVERALL would be to submit a new form for each SKU, but depending on the form setup and data that needs to be captured, we may be able to streamline this pretty well.

My vision for form submission would actually be a form for each SKU where the SKU is already populated in a hidden field as the default value.

If you take the Form URL for each of these and put them on a dashboard, you can have each of the forms set up to take you to that dashboard after one is submitted.

Basically you go to the dashboard and pick the first form. Once you submit, it automatically takes you back to the dashboard where you can use a single click to open the form for the next SKU. Depending on the data you need to capture, you should be able to pretty quickly capture everything you need. I also have a few different ideas on how to make the forms a little more efficient depending on what data you need captured.

From there we may even be able to use cross sheet references in formulas for the reconciliation bits so that it is fully automated with the exception of form entries and physical inventory counts to verify your SS data is correct.

Feel free to begin a new thread where we can focus on your particular solution and post the URL here. I'll be happy to jump on that one as well to try to help with your solution.

Sean Murphy

@Paul Newcome 10-4 Thanks I'll get some things started and pick your brain again, this is a good start

Warren Labuschagne

https://community.smartsheet.com/discussion/comment/248392#Comment_248392

Hi @Paul Newcome and @Paul Grim , I've being following your (interesting) thread and have the same requirements as @Paul Grim. I'm trying to create an order form, where I have a Main category, Sub Category and Description. For each item in the main category, there are several sub categories and for each sub category, there are relevant Descriptions for each. I've previously successfully used Appsheet for this, however it becomes expense for each user and seeing that we have Smartsheet, I would like to move over the Order form. The only way I thought of doing it was creating a separate dropdown column for each item within the main category, then one for each sub category and same for Description. That's a lot of work...

How can I follow up or add my feature request to the one that @Paul Grim created?

Paul Newcome

@Warren Labuschagne The best way I can recommend would be to include a link to this thread and any other applicable threads in your Product Enhancement Request.