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.
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.
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.
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.
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.
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.
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.