1 2 Previous Next 17 Replies Latest reply on Oct 2, 2011 6:17 PM by dave_1

# Ability to swap IF statements or CASE?

### Title

Ability to swap IF statements or CASE?

### Post

Hi all, I am developing a solution for a company that does large screen printing. Basically, they have a pre-defined sheet size, a size of job and a print sheet size. For example, the size of the job they are required to do may be: 841 x 1189 The stock size may be: 1540 x 1040 The print sheet size may be: 1300 x 1040 In my db I have assigned these as length and width values,  This, initially, made sense to me. The problem comes when you try and do calculations based upon the length and width values. Looking at the numbers above based upon maths and field values only, the size of the job (the work that needs to be performed) of 841 x 1189 cannot fit within a 1540 x 1040 area because the width value of the job (1189) is greater than the 1040 stock size. Common sense tells you that if you simply switch the length and width values around so that 841 goes into 1040 and 1189 goes into 1540 so you can at least get 1 print. Their current system that is being replaced by FM has the smarts to switch this value - my question is, how can I achieve this with FM? I have been playing for this for quite a while and cant quite get my mind around it.  Is anybody able to assist? thanks in advance. Dave.

• ###### 1. Re: Ability to swap IF statements or CASE?
PS - Im really sorry about the formatting of that post.
• ###### 2. Re: Ability to swap IF statements or CASE?

Hi David,

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

• ###### 3. Re: Ability to swap IF statements or CASE?

THank you so much for the help LaRetta - ill work in it today and let you know how I go.

Thanks again.

• ###### 4. Re: Ability to swap IF statements or CASE?

Hi Laretta,

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!

Cheers,

Dave.

• ###### 5. Re: Ability to swap IF statements or CASE?

Hi Dave,

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?

• ###### 6. Re: Ability to swap IF statements or CASE?

Hi LaRetta,

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.

• ###### 7. Re: Ability to swap IF statements or CASE?

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

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

• ###### 8. Re: Ability to swap IF statements or CASE?

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.

• ###### 9. Re: Ability to swap IF statements or CASE?

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 ]
Commit Records/Requests
End If

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.

• ###### 10. Re: Ability to swap IF statements or CASE?

OK, I understand what you are saying and thanks - but Ill give you an example to see what your thoughts are.

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.

• ###### 11. Re: Ability to swap IF statements or CASE?

I would need to see the file and not just a picture, Dave.  :^)

• ###### 12. Re: Ability to swap IF statements or CASE?

Hi Laretta,

Sorry, we had a tradgedy in our family and I havent been using a computer.

Ill get back to you asap.

Dave.

• ###### 13. Re: Ability to swap IF statements or CASE?

• ###### 14. Re: Ability to swap IF statements or CASE?

Hi LaRetta,

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.

Thanks.

1 2 Previous Next