3 Replies Latest reply on Oct 12, 2009 2:27 PM by philmodjunk

# Calculate Where an Item is Stored

### Title

Calculate Where an Item is Stored

### Post

Goal: When I do a find, for example, item #1709 the location shows Bin #1.  I don't want to manually key the bin number for each item.

Data:

- I have a table that contains the item numbers that I have as well as other information about the item

- Sample item numbers: 1501, 1709, 2365, 3948, 5346, 23301, etc.

- Each bin holds 32 items

- Bin 1 would contain the first 32 items in ascending order - 1501, 1709, 1710, 1855, etc.  In this case I don't have item #1502 through #1708 or #1711 through #1854.

- Bin 2 would contain the next 32 items in ascending order

- etc.

What I think needs to happen:

- Sort the data base into item number order - no problem doing that

- Find the first 32 items and assign them bin #1

- Find the next 32 items and assign them bin #2

- etc.

Problem:

-  I don't know how to find the first 32 items of the table and assign them bin #1.  Example #1501 - #01, #1709 - #01, #1710 - #01, etc.

-  After the first 32 items, the next 32 would be assigned bin #2, and so on.

-  Also, if I obtain item #1505, it would go into bin #1 based upon the sample information above.  But, since the first 32 items are already assigned to bin #1, the last item in bin #1 would move to bin #2.  If bin #2 already had 32 items, the last item in bin #2 move to bin #3 etc.

-  And if I remove item #1710, bin 1 would be filled back to 32 items by moving an item from bin 2.  Then bin 2 would be filled back to 32 items by moved an item from bin 3, etc.

I know this is probably a very strange request

• ###### 1. Re: Calculate Where an Item is Stored

First to clarify some table design.

Only one table involved, I'll call it "Table", with a field of type number: ItemNumber and a field for bin assignment: BinNumber.

Script it with two loops, one inside the other:

Show All Records

Sort [Restore, No Dialog]

Go To Record [First]

Set Variable [\$Bin; Value: 1]

Loop

Set Variable [\$ItemCount ; Value: 1 ]

Loop

Set Field [Table::BinNumber ; \$Bin]

Set Variable [\$ItemCount; Value: \$ItemCount + 1 ]

Go To Record [Exit after Last; Next]

Exit Loop If [ \$ItemCount > 32 ]

End Loop

Exit Loop If [\$ItemCount < 32]

end Loop

Each time you add or remove items, you'd run this script again.

• ###### 2. Re: Calculate Where an Item is Stored

Works Perfect with 1 addition:

Show All Records

Sort [Restore, No Dialog]

Go To Record [First]

Set Variable [\$Bin; Value: 1]

Loop

Set Variable [\$ItemCount ; Value: 1 ]

Loop

Set Field [Table::BinNumber ; \$Bin]

Set Variable [\$ItemCount; Value: \$ItemCount + 1 ]

Go To Record [Exit after Last; Next]

Exit Loop If [ \$ItemCount > 32 ]

End Loop

Set Variable [\$Bin; Value: \$Bin + 1]

Exit Loop If [\$ItemCount < 32]

end Loop

Thank you so much for your help!  I'd have never figured it out.

• ###### 3. Re: Calculate Where an Item is Stored
The great part about catching my mistake is that it shows you understood the script well enough to know how to fix the mistake! :smileywink: