Hello! Need two queries.
hi zcsf,
Rate please.
"ZCSF" wrote:
Query #1 will work. This will clear the BinLocation field.
Query #2 will only work at the store database level, not at HQ. There is no way to assign store-specific bin locations. What bin location do you use for items that exist at multiple stores? There is no StoreID with which to populate the BinLocation field in the HQ DB.
Setting a bin location of 1 at store 1 is pointless. Store 1 has it on hand, therefore it's at store 1.
At HQ - Item Properties | Store Quantity tab
At Store Operations - Item Lookup | Check Stores button.
Tom
One or both of us is/are confused.
PROBLEM: You want to set bin locations at the HQ level that are store-specific.
WHY YOU CAN'T DO THIS: As you have already noticed, the BinLocation field is part of the Item table (global information) and not the ItemDynamic (store specific) table. If Item GADGET-A has a bin location of 1 and GADGET-B has a bin location of 2, this information will be identical AT ALL STORES.
Changing the BinLocation field in this manner at the store level is pointless. If you have the item on hand at the store level, then you have it on hand. Using bin location = this store doesn't help anyone. WS51 could accomplish this, but to what end?
EVEN WORSE: If you issue a WS250, you will be changing the bin location value for all of the items on said worksheet at all of the stores that process the worksheet.
Are you trying to accomplish something other than what the HQ Item Properties Store Quantity tab and Store Operations 'Check Stores' features already provide?
There may be a way to set the BinLocation field like this:
4 stores (1,2,3,4) GADGET-A Bin Location = 134 (none at store 2) GADGET-B Bin Location = 3 (only available at store 3) GADGET-C Bin Location = 1234 (in stock at all stores)My SQL-fu is not strong, maybe somebody else can help with this part...
For each ItemID you would want to SELECT from ItemDynamic the StoreIDs where Quantity > 0 (or perhaps Quantity > QuantityCommitted), concatenate the selected StoreID(s) and UPDATE Item.BinLocation for each Item.ID with the concatenated string.
You would then issue a WS250 for all items. This would be a 'snapshot' kind of thing, and you would want to perform this task on some sort of schedule.
Tom
Uh oh. This is really going to mess up some plans I had. I was just getting ready to start using Bin locations in my enterprise.
Are you telling me that I can't set Bin locations at the store level and that if a 250 is done it will wipe out the bin locations I set at the store? That makes the Bin field completely useless in an HQ environment.
At Store X I keep apples in Bin 1 and at Store Y apples are kept in Bin 4. Are you telling me that I need to keep apples in the same bin number at all locations for RMS to work for me? Talk about letting your POS system run your business. This needs to change!
I never really thought about it much--bin locations aren't critical when you're dealing with recliners and refrigerators--but I guess you are correct. Bin locations are useless with HQ unless all stores are identical.
"You are coming to a sad realization. Cancel or allow?"
Tom
In reality I just want to use Bin locations at one of the stores (which functions as a warehouse) so I could make it work as long as I update the bin via HQ, but then I have to remember to do a 250 to send it down (also time consuming). The real problem is that I can't administer the bin location at the store without it potentially being overwritten with a 250.
Yuck!
I can't imagine any situation where the bin field would be useful for it's intended purpose unless your enterprise had a bunch of 100% homogeneous locations. Seems like a significant miss during HQ's development... This could be fixed without much issue by changing a 250 to not update the Bin field. Or... create a "249" as an option...
Come to think of it, wouldn't it be great to be able to make a custom worksheet that updates selected item fields only?
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.