Web form populates parent/child relationship through category

dana y

Hello,

was hoping to get some help on an issue i am having .. we have an inventory control sheet .. we would like to use a web form(s) to not only take out something of the inventory but to also add something to the inventory.  I added a Category column and that is reflected in a dropdown list that matches all of the parents.

Question is i want to know how to by selecting a category on the form will it populate under the parent category automatically on the sheet?

 

thanks much

dana

Andrée Starå

Hi Dana,

Unfortunately, it won't, but it's quite easy to move it to the right section.

I hope this helps you!

Best,

Andrée Starå - Workflow Consultant @ Get Done

dana y

understand .. so it has to be a manual process?

Andrée Starå

Yes, but it might work with a service like Zapier, Azuqua or similar. Depending on how much it would benefit your use case it could make sense to look at those options or the API.

Best,

Andrée

 

dana y

ok thanks for the input  

 

I was attempting to use the inventory template .. and it indicated the form to add items to the sheet; simply adds them to the bottom... so i assumed things have to be manually sorted.

 

Do you have any suggestions on any different methods for inventory control?

Andrée Starå

Happy to help!

Regarding other suggestions, I would need more information about your specific needs and more to be able to asses if something else would work better.

Best,

Andrée

Paul Newcome

You could use 2 sheets. One master sheet with a list of all inventory components in the order you want them in to include hierarchies. The other sheet would be the reference sheet where your forms are populating whether plus or minus inventory. The order in this one doesn't matter.

 

In the master sheet, you would enter a formula like this:

 

=SUMIFS({Reference Sheet Range 1}, {Reference Sheet Range 2}, [Name of column on master sheet used to list components]@row)

 

Range 1 is on the reference sheet. You will select the column where the amounts are logged from the form. + and - can both be used in the same column and actually makes this particular process easier.

 

Range 2 is also on the reference sheet. You will select the column where the actual components are listed.

 

[Name of column on master sheet used to list components] is exactly what it says.

 

What this does is sums up all numbers in the reference sheet where the component is the same as the one in that row of the master sheet.

 

Once you drag-fill that down, it will give you a live, running total of each list component in stock. See picture below. On the left is my master sheet. the right is the sheet where the forms are populating. It will provide a live total as new rows are added whether it be because you have added or removed inventory.