STOCK FOLLOW-UP

TomorrowlandSup · 04/06/22

Hi everyone,

To improve our warehouse we are making a stock tracking.

It works like this:

When someone takes something from the stock they fill in a form and tick 'use something'. If they bring something back they also fill in the form and tick the 'bring back' box.

In the column 'Quantity left' the amount of stock is calculated. If the checkbox 'use something' is ticked the quantity is deducted from the stock, if the checkbox 'bring back' is ticked it is added to the stock.

We would like to somehow see that if too little is brought back from the same person that the row turns red or that there is just a notification.

Question:

Is there a way in Smartsheet to see that the same person has brought back too little? Is there a formula or...? Any suggestions are welcome!

Formula in 'Quantity left' column : =SUMIFS([Amount taken]:[Amount taken]; Product:Product; Product@row; [BRING BACK]:[BRING BACK]; 1) - SUMIFS([Amount taken]:[Amount taken]; Product:Product; Product@row; [USE SOMETHING]:[USE SOMETHING]; 1)

See also the screenshot below of the sheet. Under 'INPUT' the info of the form is shown.

Thanks in advance!

Kind Regards,

Warehouse Tomorrowland

Katy H · 04/11/22

I believe the answer is yes but I have a few clarifying questions:

Is there an expected amount that the person should be bringing back after that item is checked out? What would qualify as "too little"?
Is only one item checked out at a time? And is there a chance that the person checking that item out would be checking out several other items in the same day?

To build a formula that calculates based on a person, you would need to have the form capture contact information from the user, which means you should turn on "require Smartsheet login to access this form" on the form settings.

Screen Shot 2022-04-11 at 4.00.15 PM.png

Once you have that turned on you will be able to incorporate the person's personal inventory tracking into a formula.

TomorrowlandSup · 04/12/22

Hi Katy H,

The person who rents something from us should actually return everything. When that doesn't happen we want to see that.

So if a person rents 5 things and he brings back 4 then we should be able to see that with a notification.

We use forms to collect the data so a new row is created when one would add a second item. One can rent more quantities of 1 item in the same row (column "Amount")

So it is possible to rent multiple items on 1 day.

Thanks for your help!

Greetings,

Warehouse TML

TomorrowlandSup · 04/13/22

https://community.smartsheet.com/discussion/comment/325784#Comment_325784

Hi Katy H,

The person who rents something from us should return everything. When that doesn't happen we want to see that.

So if a person rents 5 things and he brings back 4 then we should be able to see that with a notification.

We use forms to collect the data so a new row is created when one would add a second item. One can rent more quantities of 1 item in the same row (column "Amount")

So it is possible to rent multiple items in 1 day.

Thanks for your help!

Greetings,

Warehouse TML

Katy H · 04/13/22

Thank you!

So once you have the column that captures the person checking the item out, I am assuming that column might be "Ordered by" in your existing sheet, you will want to add another column that counts inventory by person. I will call that column "Personal inventory" for the formula I am writing below.

=SUMIFS([Amount taken]:[Amount taken]; Product:Product; Product@row; [Ordered by]:[Ordered by]; [Ordered by]@row; [BRING BACK]:[BRING BACK]; 1) - SUMIFS([Amount taken]:[Amount taken]; Product:Product; Product@row; [Ordered by]:[Ordered by]; [Ordered by]@row; [USE SOMETHING]:[USE SOMETHING]; 1)

Then you can leverage conditional formatting or notifications to let you know if their inventory is off.

STOCK FOLLOW-UP

Answers

Help Article Resources

Categories