1 2 Previous Next 19 Replies Latest reply on Apr 7, 2017 11:55 PM by Menno

    Pallet Calculation


      Hello Everyone!


      So, my boss decided to pitch me a curve ball and I've been thinking about his request for quite some time now; and, have been unable to come up with a clear solution. In our current FM solution, we have 3 products (A, B, & C).  When doing inventory and palletizing these product, they have their own pallet count and sequence (e.g., product A will P01, P02, P03, etc., and product B will have P01, P02, P03, and so on).  The request this time around is to have all three product to share the same pallet sequence.  For example, if the first product I scanned is B, then it'll be on Pallet 1 (P01); and,  if the next product I scan is C, then it'll need to be on Pallet 2 (P02).  Product A, of course, will be on Pallet 3 (P03).


      Now, these product are not at all sorted prior to scanning; but, rather, mixed together.  For example, I could be scanning Product B five times in a row, and then the new 2 or 3 products could be Product C and/or Product A.  What needs to happen is that all scanned Product B must be on its own pallet, and likewise for Product A and C.  The caveat is the amount of product on a pallet:  that Product A's max unit on a pallet is 180, Product B's max is 18, and Product C's is 26 on a pallet.


      I just cannot come up with a calculable solution for this.  Hope you all can give me some direction.  Thank you in advance.




        • 1. Re: Pallet Calculation

          Can you explain where this breaks down for you?


          Each pallet would be a record with a pallet ID, a product ID and an item count. A related table of products would store the max count per pallet for that product.


          Scanning a bar code should perform a script that looks for an available pallet record and which creates a new one if a pallet for that product is not available.


          There's not much in the way of calculations in what I just sketched out.

          • 2. Re: Pallet Calculation

            Thank you, philmodjunk for your reply.  I was just having a hard time conceiving and putting what this would look like together.  This is what I'm currently using for pallet calculation for each product (and each product have their own Table):


                 Case (

                        ItemNumber ≤ 180 ; "P01" ;

                        ItemNumber ≥ 181 and ItemNumber ≤ 360 ; "P02" ;

                        ItemNumber ≥ 361 and ItemNumber ≤ 540 ; "P03" ;

                        ItemNumber ≥ 541 and ItemNumber ≤ 720 ; "P04" ;

                        ItemNumber ≥ 721 and ItemNumber ≤ 900; "P05" ;

                        ItemNumber ≥ 901 and ItemNumber ≤ 1080; "P06" ;

                        ItemNumber ≥ 1081 and ItemNumber ≤ 1260 ; "P07" ;

                        ItemNumber ≥ 1261 and ItemNumber ≤ 1440 ; "P08" ;

                        ItemNumber ≥ 1441 and ItemNumber ≤ 1620 ; "P09" ;

                        ItemNumber ≥ 1621 and ItemNumber ≤ 1800 ; "P10" ; ""



            ItemNumber field (text field) has the calculation of:


                 If (  IsEmpty ( ProductA_ItemNumber::s_MaxItemNo ); 1 ; ProductA_ItemNumber::s_MaxItemNo + 1 )


            For this to work, I basically self-joined the Product A table as such:  ProductA::Date = ProductA_ItemNumber::Date.  I know it looks noobish and very unprofessional like.


            So, after looking what I have (above) and what the request was, I was just stuck (for two months) looking for different calculations and what not.  I was hoping to avoid making another table (as you have described above), but if there's no other choice than to create one for this kind of request then I guess it must be done.


            I kind of see where this must go with your explanation above.  I will need to either redo the calculation above, or overhaul it completely.  I appreciate and value your insights and replies.

            • 3. Re: Pallet Calculation

              The calculation that you have posted does not make sense to me. So "P01" identifies what exactly? As written it doesn't really even identify the pallet unless there is no more than 10 pallets ever used in your factory--which seems extremely unlikely. Perhaps it identifies a location where a pallet is placed while it is loaded with product?


              It definitely looks far from ideal and very inflexible--as in you'll be constantly redefining the calc every time something changes.

              • 4. Re: Pallet Calculation

                My apologies, the Case statement above is in my calculation field name c_Pallet; and, P01 identifies as Pallet 1.  I shorten it to P01 rather than having it spelled out on the print out.  What the current calculation does is look at the ItemNumber (field), and auto-enter the appropriate pallet indicator (in this case "P01" and so on).


                I do agree that it is very inflexible if all 3 product tables were to be combined into one table.

                • 5. Re: Pallet Calculation

                  I'm not questioning the actual text used but the fact that your calculation only allows for 10 different values.


                  How can that identify a pallet?


                  You never have more than 10 pallets in your entire factory and warehouse? That seems highly unlikely.

                  • 6. Re: Pallet Calculation

                    Oh, for this particular inventory we don't usually keep them (Products A, B, and C) long in the warehouse (approx. 2 weeks then they get shipped out), that's why we usually don't add up to more than 10 pallets for Product A.  However, Products B and C will have more than 10 pallets.

                    • 7. Re: Pallet Calculation

                      However, Products B and C will have more than 10 pallets.

                      That just illustrates even more why using a calculation like this isn't a workable option.


                      In terms of how you physically work with the pallets in your inventory, how do you tell pallet "P01" from paleet "P02"?


                      Seems like you need to add a label--possibly a bar code to each pallet that uniquely identifies it. Don't see much to gain by always numbering them from 1 to 10 for Product A, let alone the undertermined larger sequence for products B and C. Seems like you just need a unique identifier. If you set up a table of pallets, that identifier can be auto-entered each time you create a new pallet record.

                      • 8. Re: Pallet Calculation

                        To differentiate between pallets the c_Pallet calculation field actually does the job correctly.  If using the calculation above for Product A, any count under 180 will be Pallet 1 (P01), and that is displayed on the screen as "P01" as well as our placard (print out) which is taped to shrink wrapped pallet(s).  As for the Product A units, we scan in Serial Numbers which will be associated with the pallet number.

                        • 9. Re: Pallet Calculation

                          Sorry, but you missed my point.


                          If you are standing in the warehouse looking at a pallet, how do you tell if it is pallet "P01"?


                          It's not that you can't make it work by limiting the values to "P01" thru "P10" for product A. It's that doing so greatly complicates your design and I see no advantage to doing so.

                          • 10. Re: Pallet Calculation

                            Well, without the placard or any other ID on the pallet you can't really tell if it is Pallet 1 or Pallet 2 and so on.  You can only tell the difference by finding the Serial Number(s) that was scanned to that pallet.

                            • 11. Re: Pallet Calculation

                              Well, without the placard or any other ID on the pallet you can't really tell if it is Pallet 1


                              Does that mean that you don't have such a label on your pallet or that you do?


                              My point is, that the pallets don't have to be numbered 1, 2, 3, 4 ...10 with the numbers re-used as the pallets ship out. All that really matters is that each pallet be assigned a unique ID. If you don't have any kind of pallet label to identify the pallet, this is even more the case. If you do have a physical labeling system, then this can be an issue you have to consider.


                              You need that table of pallets with an auto-entered pallet ID. You MIGHT need an additional pallet label ID field if there is a physical label of some kind used as you have to fit your label value to the conventions and limitations of that physical label.


                              Using such a table of pallets, you can simply keep a running count of items loaded onto the pallet with your script comparing that count to the total possible for a single pallet given the product ID. The max count for a product is then a field in your products table.

                              • 12. Re: Pallet Calculation

                                We do have a label just not a barcode of any kind (that could of course change).  Our "label" is basically a print out of the vendor, batch number, pallet number (P01), part numbers and their quantities, pallet location (where it's stored in the warehouse), and the date that the pallet was created.


                                Each pallet does get assigned an ID, but not a pallet ID, i.e., _palletID with an auto-enter serial number.  The pallet number, P01, is generated by c_Pallet field I mentioned above; and, the unique ID is from the Product A table (or B or C table). I'll attach a screen shot of what it looks like when I back at work.


                                Using such a table of pallets, you can simply keep a running count of items loaded onto the pallet with your script comparing that count to the total possible for a single pallet given the product ID. The max count for a product is then a field in your products table.

                                This probably will be something I will need to consider doing when combining all three products together to one table. Going about doing it will be a challenge.

                                • 13. Re: Pallet Calculation

                                  What I do not see any reason whatsoever for the "PO1" value. The serial number alone would be all you need.


                                  And yes, you need a table of products in a single table.

                                  • 14. Re: Pallet Calculation

                                    Each product should have it's own record in a table with a "Pallet_qty" field

                                    The pallet naming calculation can be simplified to :


                                    "P" & Right( "00" & ( Int ( ItemNumber / Pallet_qty) + 1) ; 2 )


                                    Note: this assumes Pallet_qty and ItemNumber are not 0

                                    1 2 Previous Next