How can I create a material inventory and order form in the same sheet

maccokm

Hello,

I am very new to Smartsheets and am trying to streamline inventory and how our partners order materials from us. Currently, partners email members of our team and someone fulfils and tracks the order manually in an excel sheet.

I want to create a sheet that tracks inventory with an order form that subtracts from the total. I also saw that alerts can be sent when inventory reaches a certain threshold. I would truly appreciate any advice and insight.

Thank you in advance!

MichaelTCA

Hello @maccokm

An option would be to set up 2 sheets. One for the source data and a second for analytics.

What do you mean by subtract from the total? I assume you're talking about allocated materials for customer orders?

The analytics sheet will be where all the formulas happen and the source sheet will be where the form is developed/new orders will be submitted.

There's no subtraction function, but you could direct the function to a total value and subtract the sum of the remaining values.

=Total1 - SUM(Product:Product)

I recommend putting the total into another column or into another sheet because you don't want to subtract the total from the total (obviously).

Depending on the complexity, it's possible you may need a 3rd sheet to reference a list of all of your products and the remaining value per product and use a lookup function to pull the correct values. You can have a second form to add inventory as well on the same sheet.

You can also you an automation that will copy rows to another sheet to subtract the current value from the remaining in inventory (from our 3rd sheet). In this case you would use the MIN/COLLECT function to be able to pull the most recent information of what is remaining within inventory, which means you will also need to record dates or use submission dates.

maccokm

Thank you for your response @MichaelTCA.

The MIN/COLLECT function seems to be the closest to what I am looking for in regards to what I said about subtraction from the total.

The idea for a second form to add inventory is helpful but how do I differentiate between the two forms in the sheet once responses are captured?

MichaelTCA

@maccokm

You can set up a column with a locked default value, specific to the form. The value will be added to that column for every submission. You can also hide this column from the submitter within the form. Another way is to have the option to select whether it's allocated to an order or purchased from a vendor, by the submitter within a dropdown column. Then you can also set up the logic in the form depending on what data you need for each situation.

Then you will also have a set of data for purchases and allocated products that you can use for further analytics and monitor the progress of how often each product is being bought or sold to customers over a period of time.

maccokm

@MichaelTCA These ideas sound very helpful. Do you know if there are specific videos/support to run through these aspects? I am unsure how to execute what you have suggested.

Or, if you are willing and able, would you be able to send me screenshots or a recording of how to get this set up in this thread?

MichaelTCA

@maccokm

This video should get you started. You will need to create a column, like a dropdown column, and you can enter Form 1 or Form 2 as values in the list.

When setting up the form, you will add this column to the form and there are options on the bottom right to set up the default value and hide it.

https://help.smartsheet.com/learning-track/smartsheet-intermediate/forms

The logic side of it depends on your selections. You set a condition and then select which columns to show within the form after selections are made.

Based on the values submitted, you can use functions to gather the data required from the source file.

1 sheet with the form for submissions and reference data.

1 sheet with the source data.

maccokm

@MichaelTCA Thank you for the pictures and video references. Could you tell me what the buffer is?

I have created the order form link for our partners and to track what we order/when

Partner order form: https://app.smartsheet.com/b/form/ca7a53f4bafc4e83abcbc68d04af4ecb

Ordering Stock: https://app.smartsheet.com/b/form/eb630a6cf8f343cba1125f82ed05f4df

Would I still be able to use parts of these or do I need to start over?

MichaelTCA

@maccokm That was a quick example from a sheet used for project management. I use a "buffer" to estimate time taken outside of the project, such as meetings.

I'll use the ordering stock form as an example.

Depending on the first value, you can use what's already there and simplify the rest of the form by only showing the columns that pertain to the same language (unless they're all required). If language is a big factor, than you can use that as the first selection and build the logic off of that.

If all of the columns are required than it's good to go. If not, without logic set up behind the form, it may be difficult for submitters to use due to clarity.

Because the forms are to submit data and is not an interaction with the sheet, functions are not able to be used within the form such as a lookup. You will need to have the expected results prepared.

Lastly, if you have a detailed source sheet that contains the information you need, you can minimize the selections in the form and use those selections to pull the rest of the data from the source. Such as if I was the submitter, you could use my name to pull information to the sheet instead of having the submitter re-enter the value every time.

maccokm

@MichaelTCA All of the elements are required because they are options of materials that our partners can ask for.

By having them fill out these forms, we will be able to get the orders in a more organized fashion (to date all orders have been sent via email and need many follow-up questions to fulfill).

Earlier you had mentioned something about another sheet with automations to copy rows and have MIN/COLLECT formulas. How do I go about this? Do I save my sheet as a new one and then set up the automation?