PS - Im really sorry about the formatting of that post.
If I understand the need correctly then this will work for you:
I assume you have a stock table; if not, you should create one. It should also have length and width in it along with specifics on the paper type etc. In the stock table, add a calculation called cDimensions of: length & ¶ & width. Set the result to number (which is the default). Also create a calculation (result is number) of length * width. Call it something like cSquare. Now create a relationship from jobs to stock as:
Jobs::length ≤ Stocks::cDimensions
Jobs::width ≤ Stocks::cDimensions
This then will provide you with the paper sizes which fall within this range (are large enough for your print job without regard to orientation) but if there are more than one stock size which fits, it doesn’t tell you which one is the best fit with least amount of waste. To make that determination, while still in your relational graph, sort the Stock side (see options below) ascending on cSquared.
Now the FIRST related stock record will always be the one that is within size and also the one with the last amount of waste if used. You can simply place the fields from stock onto your jobs layout to view the best stock for the job. Or you can place a portal of Stocks onto your jobs layout to see all which will fit.
Once you determine that you can trust that the first related Stock record is always the best, you can set the Jobs::stock field to insert the StockID automatically by using a Lookup.
THank you so much for the help LaRetta - ill work in it today and let you know how I go.
I wondered why this post had no action, and it was obviously because for some reason my results didnt post.
So, to recap:
I made the changes as you suggested and expected the following results:
For xSquare(d) - simply total area value.
Once the relationship has been made and sorted we are given the l x w values that fit. This is good, however this (based on my experients) doesnt seem to automatically be able to swap the l or w values.
Because I need to display the values graphically (all be it simply on a rectangle with a number of other calculations) i need the calculation/relationship to be able to determine the l or w value and display them appropriately.
I hope that makes sense.
Maybe there is another easy way to do this that I am simply not seeing, if so I apologise.
Ill buy you a coffee to say thanks!
Well I had, of course, tested it before providing this solution. You didn't explain how you determined that it 'doesn't seem to automatically be able to swap the l or w values.' Swap the values where? The purpose of creating the cDimensions multi-line number was to provide a relationship regardless of your Jobs length/width.
Anyway, I've uploaded a demo here. http://www.4shared.com/file/MBAozgrD/Stock.html
Can you check it and see if it gives you the results you expect? It does for me. The portal directly below the jobs length and width shows the stock which will fit, sorted by the relationship of which stock works the best.
"i need the calculation/relationship to be able to determine the l or w value and display them appropriately."
I do not understand what this file doesn't give you. Can you be specific on where it breaks for you?
OK - I can see where our miscommunication lay.
The solution you have provided is great - but what it does is select the best stock based upon the size requirements of the job.
The problem is, that it isnt just the size of the stock that determines its choice for the job. There are a number of other factors.
For this reason, the staff select the stock they wish to use for the job (and hence the L/W values) - and I need to arrange the stock size to work for the job.
So, if I say the stock is 100 x 500 and the job is 500 x 100 my current solution says it wont fit. It displays the stock and job on screen, and you can clearly see that 100L of stock cannot fit 500L of job.
But, if the solution has the smarts to recognise that if the stock or job swaps its L and W values it can fit 1.
I'd like my calculation to have the smarts to notice this and record the values in their 'alternate' positions.
Does that make a bit more sense?
The trick of not only calculating this, but then altering my display is the part Im still a bit iffy with.
Well I'm glad it worked for you. If I understand you correctly, you not only want to select the proper stock but display it in the most logical match to the jobs selection of length and width. If so, then try this calculation (result is text):
stock::length - width ≤ stock::width - length and width ≥ stock::width or length > stock::length or width > stock::width ;
stock::width & " x " & stock::length ;
stock::length & " x " & stock::width
If you instead want to switch your Jobs length and width let me know. But I would assume that the simple display of stock in matching orientation will do it for you.
Hi LoRetta - thanks for that. I am learning a lot about utilising text within FM thanks to you!
I would, however, like to switch the jobs length and width - this means that I can utilise the values much more easily for the further calculations I perform.
Thanks once again.
Since the Jobs length and width is data within a field, it will not work well to have it automatically switch, i.e. during data entry, User may still be typing values or changing their mind. Even with script triggers I would not recommend automatic switch between two fields holding regular data during data entry. I would suggest instead that it display a button if system sees that it should be switched and the User click a SWITCH button which, via script, the values change places. Here is how to handle it:
Create a script called Switch Values as:
If [ stock::length - Jobs::width ≤ stock::width - Jobs::length and Jobs::width ≥ stock::width or Jobs::length > stock::length or Jobs::width > stock::width ]
Set Variable [ $length ; Jobs::length ]
Set Field [ Jobs::Length ; Jobs::Width ]
Set Field [ Jobs::Width ; $length ]
Then type regular text on your Jobs layout (right next to the length and width fields). Set the text to blue and underline and make it say (for example) Switch Text. Attach the script to it. Now also select the text and add conditional formatting of Formula:
not ( stock::length - Jobs::width ≤ stock::width - Jobs::length and Jobs::width ≥ stock::width or Jobs::length > stock::length or Jobs::width > stock::width )
Below, select 'more formatting' and set the font size to 250 point. This will make the 'button' disappear when the length/width is in the correct orientation. One could also change the font color to the background but unfortunately that will not always completely hide the text and it can appear as ghost text in some situations (such as viewing through Remote Access or Citrix). Also you might forget and change the background color. If you change the font to huge, it will aways disappear completely no matter what.
Now when in layout mode, it will show Switch Text and if your first related values from Stock (the best selection for this job) are reversed, Switch Text will show in Browse mode. The User can then click the button and the values in the two Jobs length and width fields will reverse. If they click the blank layout where the button exists nothing will happen except a flash.
OK, I understand what you are saying and thanks - but Ill give you an example to see what your thoughts are.
If you download:http://www.4shared.com/file/B_sashwR/Stock-1.html I have updated your example.
I have included the following:
print sheet length and print sheet width - Stock size is the raw 'paper', print size is the size of the print, job size is the prints layout on the stock
cNumberOfPrintsUp and cNumberOfPrintsAcross - uses the size of the job to determine how many times it can be printed on a single sheet
cPrintUp - how many total prints per sheet
overs + qty - the number of prints required by the customer and the number of security, overs, prints
cRunQty - how many needed in total?
cSheetsStock - how many sheets needed in total?
cStockQty - same for stock
Using these values, I display the NumberOfPrintsUp and cNumberOfPrintsAcross, print sheet length and width, size of job width and a few other values graphically.
If the numbers entered don't fall into these calculations properly, the user is displayed with questions marks or 0 rather than having the ability to swap data or display alerts.
An example of this is http://www.4shared.com/photo/0k0Tbqb3/image.html
Hopefully I havent confused the issue more, but I'm hoping this puts into context exactly what I am hoping to achieve.
I would need to see the file and not just a picture, Dave. :^)
Sorry, we had a tradgedy in our family and I havent been using a computer.
Ill get back to you asap.
Thanks for your help,
Am I able to send you an email with my latest iteration? It is a file I dont want to publicly post.
If that is OK, please email me at inkerman[at]me.com.