Combine several Field results in a single Calc. ?

[corey]

This post does relate to the other post i have, but it deals with another issue i have with it.

If i have 6 Cal. Container fields and i want to set up a field (say item number).

If the item number entered is 1 then all the 6 Calc. Fields will remain separate.

But if i input 2 into the Item Field, then i want the 1&2 Calc. Containers both to give the same result.

Therefore in effect making the 6 Calc. Container fields, into 3.

See Attached file

What sort of Calc. would i need to do this?

multiply.zip

[transpower]

Base the calculation on an If statement dependent on what is put in the Item field.

[Lee Smith]

You only need one Container field for this. For this purpose, you can select one of the one you already have, or create a new one.

c_background (Calculation, Container Result) =

Case(

Number of Items =1 ,Shade a,

Number of Items =2 ,Shade b,

Number of Items =3 ,Shade c,

Number of Items =4 ,Shade d,

Number of Items =5 ,Shade e,

Number of Items =6 ,Shade f

)

HTH

Lee

[-Queue-]

Also

Choose( Number of items - 1, Shade a, Shade b, Shade c, ... )

[corey]

I got what i was after, probably the long and tedious way by this:

Case(Amount of Items = "3",Section 1 ,

Amount of Items="2",Section 1,

Amount of Items="1",Section 1,

Section 2 Colour="Green",Shade Green,

Section 2 Colour="Yellow",Shade Yellow,

Section 2 Colour="Red",Shade Red)

Each of the Shades have different Item amounts and refer to various other Sections.

Problem is, i created this with 16 separate sections, but i get a NOT RESPONDING error when i try to open an existing Record or Create a new one.

Could a Calc. do that to Filemaker ?

Any ideas whay i can not open the record/layout now?

corey....

[-Queue-]

Case( Amount of Items > 3, 

Choose( (Position( "Green  Yellow Red", Section 2 Colour, 0, 1 ) + 6) / 7 - 1, Shade Green, Shade Yellow, Shade Red ), 

Section 1 )

Note: there are 2 spaces between Green and Yellow.

Calcs can cause FileMaker to loop indefinitely. I wouldn't think FM 4 would have self-referencing issues. There may be too many levels of calcs, however, that are unstored.

[corey]

With the last post then how can i convert that from this :

Case(Total Amount of Items="2", Section 9,

Total Amount of Items = "3" , Section 11,

Total Amount of Items = "4" , Section 13,

Total Amount of Items="5", Section 13,

Total Amount of Items="13", Section 14,

Total Amount of Items="14", Section 14,

Section 15 Comments="Good" , Shade Green,

Section 15 Comments="Fair" , Shade Yellow,

Section 15 Comments="Bad" , Shade Red)

As you can see that depending on the entry into the Item Total Field, is related to which Section to paste/copy.

The Calc. i need is dependant on the [Total Amount if Items] field.

And the Section ? it copies is related to the number placed in the Total Amount field.

Is there a simple Case(Total Amount if Items>="2-7") formula that looks for an amount between 2 amounts.

The Top Calc. did work but then froze Filemaker up.

Any Ideas

corey....

P.S To put you in the picture.

I have a oval shape that represents a conveyor belt.

It has 16 Containers around it to represent a section of the belt.

It is easy if there are 16 sections, because all the sections will be separate (16).

If there is only 2 items (or joins) in the belt the number 2 would be placed into the Total Number of Items field.

BUT, that means there is ONLY 2 x Sections of the belt that need shading/section.

The calc. that i had broke up the 16 sections into smaller portions.

If say there was 8 items, then every second section would be automatically selected the same as the first section related to the item position.

[-Queue-]

Your calc is nearly as streamlined as you're probably going to get it, since FM4 doesn't have the GetField ability. However, you don't need quotes around number field tests and a couple tests can be combined.

Case(

Total Amount of Items = 2, Section 9,

Total Amount of Items = 3, Section 11,

Total Amount of Items = 4 or Total Amount of Items = 5, Section 13,

Total Amount of Items = 13 or Total Amount of Items = 14, Section 14,

Choose( (Position( "GoodFairBad", Section 15 Comments, 0, 1 ) + 3) / 4 - 1, Shade Green, Shade Yellow, Shade Red ) )

[corey]

Choose( (Position( "GoodFairBad", Section 15 Comments, 0, 1 ) + 3) / 4 - 1, Shade Green, Shade Yellow, Shade Red ) )

I dont understand whet this section of the Calc. does ?

And what each numeric value represents.

For each of the different sections would it be always the same or change?

corey....

[-Queue-]

If Section 15 Comments is 'good', the Position function returns 1 (the position of 'g').

If it is 'fair', the Position function returns 5 (the position of 'f').

If it is 'bad', the Position function returns 9 (the position of 'b').

Adding three to each of these numbers gives 4, 8, and 12, respectively.

Dividing these three results by 4 gives 1, 2, and 3, respectively.

The Choose function starts at zero, so we subtract 1 to give 0, 1, and 2, respectively. Zero corresponds to the Shade Green result, 1, corresponds to Shade Yellow, and 2 corresponds to Shade Red.

This is a nifty trick that can be used whenever you have a series of tests using text. It is even more powerful when combined with the GetField function in 5.5, but is useful in lower versions also.

A 'formula' for this is

(Position( "TEXT", testfield, 0, 1 ) + N - 1) / N

where N is the number of characters in the longest word contained in TEXT and TEXT contains each testfield word in consecutive order, with N - A spaces appended, where A is the number of characters in a particular word. The result will always be 1, 2, 3, 4, 5, etc. (Note that the last word in TEXT does not require extra spaces.)

Another example is

Middle( "RAINBOW", (Position( "Red   OrangeYellowGreen Blue  IndigoViolet", colorfield, 0, 1 ) + 5) / 6, 1 )

which returns R when colorfield is 'red', A when colorfield is 'orange', etc.

The longest words in the text block are orange, yellow, indigo, and violet, with 6 letters each. So 3 spaces are added to 'red' and 2 to 'blue' so that each word starts at a position that is one more than a multiple of 6. The positions are 1, 7, 13, 19, 25, 31, 37. Adding 5 to each gives 6, 12, 18, 24, 30, 36, 42. Then a simple division by 6 gives 1, 2, 3, 4, 5, 6, 7.

To reduce each result by 1, to use in a Choose test, you can either subtract 1 after the division or subtract 1 instead of adding N - 1 to the Position result. So your calculation could also be

Choose( (Position( "GoodFairBad", Section 15 Comments, 0, 1 ) - 1) / 4, Shade Green, Shade Yellow, Shade Red )