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

    Calculate Where an Item is Stored

    jdhelle

      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
          philmodjunk
            

          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
            jdhelle
              

            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
              philmodjunk
                 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: