8 Replies Latest reply on Nov 12, 2012 8:32 AM by StephenSkoutas

    calculation for load optimizations



      calculation for load optimizations


           Does anyone know if there is a way to calculate optimized loads within filemaker?  Given a master cartons dimensions and the maximum loadable dimensions on the target container (be it a pallet, a 20' ocean container, or a 53' trailer), I need to be able to calculate the maximum number of cartons that container can hold.  My product is light, so I don't need to concern myself with maximum weights.

           Also because my product is light, I don't have to worry about crushing, which means that my cartons can be loaded with any dimension vertical (there is no "this side up.")  

           I wrote a solution a while ago in Excel, but that solution was dependent on the Solver add-on.  I'd love to have an in-FileMaker pro solution if there is one.  Thanks in advance.

        • 1. Re: calculation for load optimizations

               There is an API for warehouse packing on this site, Filemaker can talk to a number of APIs, depending on the API.


          • 2. Re: calculation for load optimizations

                 Tonight I was able to rewrite what is probably a better Excel solution than my original.  Tonight's solution does not rely on Solver.  It is all live-calculated, no macros or solver solutions, and the results are instant.  Problem is, it is in excel and I don't have the foggiest idea how to recreate this in FileMaker Pro.

                 That said, the excel solution tonight needs 5 pieces of data to work - carton L, W, H, container, and if the "container" is a pallet, what the pallet's maximum height is.  It the provides the answer via three pieces of data - maximum number of cartons, total cubic footage of those cartons, and the percentage efficiency.

                 Is there a way to use this spreadsheet as a "live" solution for FileMaker Pro so that when an item gets called up in FM (which also calls up the dims) it can send those dims back to excel without user intervention and then display excels results?  I could easily tweak my excel solution so that it provides an answer for each possible container type (20' ocean, 40' ocean, 40 high ocean, 45 high ocean, 48' trailer, 53' trailer, and pallet for anyone who cares).  That way the user doesn't have to select a container within FM.  They could call up an item number (record) and get all load optimizations displayed in their layout.

                 Any ideas are greatly appreciated.  Thank you.

            • 3. Re: calculation for load optimizations

                   If you post the Excel solution it may well be perfectly possible to re-create it in Filemaker.  if you still want to use the excel calculation you can:

                   - capture (or generate) the data in Filemaker
                   - export that data to a text file
                   - have Filemaker open your Excel sheet
                   - have a macro run 'on open' in the Excel sheet that imports the text file to the appropriate cells and therefore generates the results
                   - have Filemaker import from the spreadsheet's results into the 'answer' fields in the Filemaker file

                   All of that can be scripted.

              • 4. Re: calculation for load optimizations

                     Thanks, Sorbsbuster.

                     If I use the scripted example, I don't believe I can store the results per record, can I?  The ultimate objective is to have this function run for thousands of items (records) as they are entered into the database, thereby adding load information for each item.

                     I believe with the scripted info, you can only have that result for the one "live" focused record, at least the way I'm envisioning it.  

                     That said, I will gladly post the excel file.  Do I have to host it somewhere like dropbox to make it accessable to the forum?  I only see a way to upload image files.  

                     Assuming I can get the excel file to the community, there are 3 tabs.  Tab one is user input and the results.  Tab two is the result of each scenario.  Tab three is doing all of the heavy lifting.

                • 5. Re: calculation for load optimizations

                       You could load 1000 records into Filemaker, export them to a 1000-line text file, get Excel to import the 1000 rows, format a sheet in the excel file to have the 1000 rows of results, and then import the 1000 spreadsheet rows back into the matching 1000 records that generated the text file. All in the one script.

                       You can only post images - yes; use dropbox, sugarsync, 4shared, whatever.

                  • 6. Re: calculation for load optimizations

                         Here's the file. https://dl.dropbox.com/u/416537/Steve%27s%20Load%20Optimizer.xlsx

                         Let me know what you think after you've had a chance to play around with it.  I started it yesterday and finished it after 2AM, so there's bound to be an error or two and room for improvement.

                    • 7. Re: calculation for load optimizations

                           I don't see any reason why the data exchange with Excel wouldn't work.  You could position the inputs and outputs across rather than down and it would be much easier to import and export, but that's it.

                           The only formulas that I see you use are Max, Min, Sum, If, and Rounddown.  All those are available in Filemaker, with Floor for Rounddown, and an additional Case Statement which is much easier to use than If.

                      • 8. Re: calculation for load optimizations

                             I'm looking deeper into my Excel solution.  I think it is a very good estimate of an optimum load, but I now think it is a little overly-simplified.  I'm going back to the drawing board on this one for a bit.