Workflow for Tracking Inventory Counts and Bin Locations
Hi,
I am trying to setup a sheet to keep track of when we count bin locations in our warehouse and how often. I would like every bin to be counted at least once ever 12 weeks. In our bin locations column, I have a multi-checkbox setup with all 403 of our bin locations in it. We typically count 5-12 bins a day. Can I setup an automation to tell me when a bin needs to be counted if it is outside that 12 week window?
I plan on making a metadata sheet as well as a dashboard for future metrics as well.
Best Answer
-
Okay, so, I ended up figuring out what I needed to do. I have a Form our warehouse associate will fill out with the Date, Bin Locations, Counts, and variance. The form will put the fields into the Cycle Count sheet.
然后,我有一个metadata sheet with all of the bins in our warehouse with 2 other columns for Number of counts preformed for each bin, and one for the most recent count date to track the last time a bin was counted.
Last Count Date Formula: =MAX(COLLECT({Inventory Cycle Counts Range 2}, {Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row)))
Number of Counts Total Formula: =COUNTIF({Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row))
With these 2 sheets I have created a Dashboard that has 2 widgets on it so far. One widget is tracking our IRA % and the other Widget will list all bins that are out of the 90 day audit window.
Answers
-
Jeff Reisman ✭✭✭✭✭✭Sure, you can do this with automation. The question is, do you want the notification to be at precisely 12 weeks (84 days,) or will 90 days do? If it's 84 days you want, we'll need a helper column. If 90 days is okay, we can just do that with automation.
Let's do just automation first:
If you want the alert at exactly 12 weeks, add a helper date type column. Enter a column formula:
=[Cycle Count Date]@row + 84
Then create a Workflow just like the one above, just set it to run when the date in the helper column is reached.
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接:Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Okay, so, I ended up figuring out what I needed to do. I have a Form our warehouse associate will fill out with the Date, Bin Locations, Counts, and variance. The form will put the fields into the Cycle Count sheet.
然后,我有一个metadata sheet with all of the bins in our warehouse with 2 other columns for Number of counts preformed for each bin, and one for the most recent count date to track the last time a bin was counted.
Last Count Date Formula: =MAX(COLLECT({Inventory Cycle Counts Range 2}, {Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row)))
Number of Counts Total Formula: =COUNTIF({Inventory Cycle Counts Range 1}, HAS(@cell, [Bin Location]@row))
With these 2 sheets I have created a Dashboard that has 2 widgets on it so far. One widget is tracking our IRA % and the other Widget will list all bins that are out of the 90 day audit window.
-
Jeff Reisman ✭✭✭✭✭✭
@MTGoodmanThat's some nice work!
I built some similar things for my company's warehouse as well, but for tracking Bills of Lading.
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接:Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
@Jeff ReismanWould you care to share your idea on how you implemented that? That was one of our next goals. I'm curious to see how we could use BOL tracking.
-
Jeff Reisman ✭✭✭✭✭✭
It's a really large workspace, and has just been retired since we moved to real-time tracking in SAP, but it did help the warehouse staff over the course of two years or so to get used to organized workflow and tracking. Let's see if I can nutshell it for you.
Each warehouse started with a BOL tracking sheet populated by admin staff via a form. They had USB bar code scanners to scan the BOL numbers off the paper copies' bar code, scan the assigned picker's name and a carrier name off sheets of bar codes on the wall, and enter the BOL $ value. (I actually had them scanning these into a form on an entry sheet, with a move row workflow that actually moved the rows through a secondary entry sheet, and then moved them into the main BOL tracking sheet. This was because as the tracking sheet got larger, it took too long to save each submission. By leapfrogging, the entry sheet never had more than 1 or two rows in it at a time, so saving was instantaneous.)
When the picker collected their assigned BOLs, the floor supervisor would update the status from Assigned to In Progress. When the order was picked and on the truck, the supervisor would verify and then give the paper BOL back to the admin to update status to Closed. (Before this, nobody was tracking who was working which BOL, so if one got lost - a handful went missing each day - nobody knew who was responsible for it.)
I created a handful of metrics sheets for each warehouse. For each pickers, counting number of BOLs assigned to them on the current day, number closed by them for the day, $ value closed by each one on the current day, as well as counts for total assigned, total in progress, total closed for the current day. I used the data to create a dashboard with donut charts - # assigned to each picker, % assigned to each, # Closed by each, metrics for total number and values, a chart for total $ value closed by each picker for the day. This dashboard went up on two big 75" TVs in the warehouse. Seeing their current metrics actually got some of them to work faster, which was good because our sales picked up a lot over the last two years!
I also listed the oldest BOL in the tracking sheet that was not yet closed, and the date/time it was entered, so at a glance they could find out what was getting left behind. I wrote about how I did that wrong at first and how I got it right, here:https://community.smartsheet.com/discussion/69081/find-the-row-with-the-earliest-created-entered-date-that-matches-certain-criteria
Here's what the dashboard looked like, there's no data, but just imagine three donut graphs, a bar graph, and a chart!
And part of a metrics sheet:
Good luck!
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接:Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Jeff,
Thank you so much for going into detail on this. It really gives me a good idea on the direction I want to go with our warehouse.
Great work all around.
-
Jeff Reisman ✭✭✭✭✭✭
Glad I could help!
Regards,
Jeff Reisman,IT Business Analyst & Project Coordinator,Mitsubishi Electric Trane US
链接:Smartsheet Functions Help Pages链接:Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
Help Article Resources
Categories
I figured it out! Updated formula to get the 2 decimal places as well.<\/p>
=\"We are at \" + IFERROR(ROUND([% closed rate]@row * 100, 2), \"//www.santa-greenland.com/community/discussion/comment/\") + \"% closed rate on ticket status for the month of \" + Month@row<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":108861,"type":"question","name":"IF\/AND Formula","excerpt":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I have not received an invoice and the invoice due date is within 30 days. I know I'm close since I've gone from \"unparsable\" to \"incorrect…","snippet":"Formulas are the bane of my existence and I really need to take a class! Today I'm trying to set up a formula to throw a flag when 2 conditions are met. I want a red ball when I…","categoryID":322,"dateInserted":"2023-08-11T16:27:44+00:00","dateUpdated":null,"dateLastComment":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"updateUserID":null,"lastUserID":120231,"lastUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":4,"countViews":47,"score":null,"hot":3383549849,"url":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/108861\/if-and-formula","format":"Rich","lastPost":{"discussionID":108861,"commentID":390268,"name":"Re: IF\/AND Formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/390268#Comment_390268","dateInserted":"2023-08-11T17:49:45+00:00","insertUserID":120231,"insertUser":{"userID":120231,"name":"Pamela Wagner","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Pamela%20Wagner","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-11T17:47:38+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-11T17:48:48+00:00","dateAnswered":"2023-08-11T17:12:43+00:00","acceptedAnswers":[{"commentID":390261,"body":"
You were, indeed, very close.<\/p>
=IF([Invoice Received?]@row = 0, IF(AND([Renewal Date]@row >= TODAY(), [Renewal Date]@row <= TODAY(+30)), \"Red\"))<\/p>"},{"commentID":390264,"body":"
