Using Value lists in a calculation Field

[lesterd]

I have two Value lists I want to use in a caliculation field. Both fields are text. The first has 3 statements and the second has 3 statements and the combination of any two will give a third statement result.

Let's say the first value list is called "Planning" with values of "ready", "set", "go",

and the second value list is called "Performance" with values of "Unsatifactory", "Developing", and "Proficient" ... and every combination of those will produce a certain comment.

For example the coment fields are, "KOC_U", "KOC_D", "KOC_P". There are 9 Possible combinations.

How would I write the calculation?

[Lee Smith]

Are you wanting 9 different calculations, one for KOC_U, one for KOC_D, etc., that equals the answers in Planning and Performance, or a new field that has a calculation that will return KOC_U, or KOC_D, etc. based on the two fields of Planning and Performace.

Lee

[lesterd]

Are you wanting 9 different calculations, one for KOC_U, one for KOC_D, etc., that equals the answers in Planning and Performance, or a new field that has a calculation that will return KOC_U, or KOC_D, etc. based on the two fields of Planning and Performace.

Lee

I want a new field that the data in KOC_U will be entered.

For example if Value list Planning="Ready" and VL Performance ="Unsatisfactory" It would bring KOC_U into a comment field which would say, "Try to be on time tomorrow" . . . or

Value list Planning="Ready" and Value List Performance ="Developing" It would bring KOC_D into the same comment space field which would say, "Nice to see you on time today"

[McCormick]

I think you need a nine-part case statement series in a calc field. Each case will be one combination. The result in each case could be a text statement ("Try to be on time...") or could use GetField to point to some global text fields. Then KOC_A could be a global, etc. (What's KOC, anyway? "Kind Of Comment"?) One advantage would be making it easy to change the language in those comments without changing the fundamental structure.

Case (

Planning="Ready" AND Performance ="Unsatisfactory", GetField(KOC_U),

Planning="Ready" AND Performance ="Developing", GetField(KOC_D),

"")

etc.

[Lee Smith]

Here is a small example file. You will need to change change the fields for the statements to match the fields in your file. In other words, change Stmt1 to KOC_U etc.

HTH

Lee

Lesterd.fp5.zip

[lesterd]

Do I have to treat the Value list data any differently when i use it in a calculation. Do I have to let the program know it is a value list item?

KOC_U and the other KOC_D, KOC_P are fields I created and all have text in them. For example KOC_U has "Teacher makes content errors of does not correct content errors students make." and KOC_D has "Teacher displays basic content knowledge . . ." etc.

I have about 60 Statement fields to write calculations for. I have created another database to house all the statement fields and am going to make it relational.

Thanks for any help you can give.

[Lee Smith]

lesterd said:

Do I have to treat the Value list data any differently when i use it in a calculation. Do I have to let the program know it is a value list item?

No, as the example shows, you need to "Hard Code" them into the calculation though. Which mean you need to add your other combinations to the list and to the calculation.

If you want, send me a striped down copy of your files (stuffed or Zipped) back channel and I'll help you.

lesterd said:

KOC_U and the other KOC_D, KOC_P are fields I created and all have text in them. For example KOC_U has "Teacher makes content errors of does not correct content errors students make." and KOC_D has "Teacher displays basic content knowledge . . ." etc.

I know that. And since the fields have changed in number, you will need to change the calculation to include the other possiblilities. I would start by copying the Calculation from my sample file, and pasting it into the calculation box for your calculation field. AND then you'll need to slect the Names of the of your fields in your file to replace the ones I named Stmt1, Stmt2, Stmt3, etc. In other words, Stmt1 in the calculation would need to be replaced by KOC_U (or whatever), Stmt2 to KOC_?, Stmt3 to KOC_? (Just match the calculations to your fields and replace it. FileMaker will not allow you to close the box until you have replace my Stmt fields with your own).

lesterd said:

I have about 60 Statement fields to write calculations for. I have created another database to house all the statement fields and am going to make it relational.

I'm assuming that there are more words in both of your value lists, in order to accommodate the other 51 statements?:

Like I said, maybe it would help to send it to me or post it here as an attachment.

HTH

Lee

[-Queue-]

Here's a completely dynamic way to do it, using Get Repetition and a repeating global. As long as you set up your statements as in the example, it should work for any number of combinations. I may set this up as a sample file later on. What do you think?

Example:

Repetition1 = Result of VL1 Item1 and VL2 Item1

Repetition2 = Result of VL1 Item2 and VL2 Item1

Repetition3 = Result of VL1 Item3 and VL2 Item1

Repetition4 = Result of VL1 Item1 and VL2 Item2

Repetition5 = Result of VL1 Item2 and VL2 Item2

Repetition6 = Result of VL1 Item3 and VL2 Item2

etc.

Lesterd1.zip

[lesterd]

Here are the two files I am working with - one is attached to this message and the next file follows

Thanks

Lester

[lesterd]

Here is the second file

[Vaughan]

Why use repeating fields at all? Why not make another file and do it relationally. Much easier to add and remove entries.

Use the concatenated fields as keys on noth sides of the relationship.

[Lee Smith]

Hi Queue,

Man oh man, it took me a couple of looks to see that you had modified the sample file

I really like your concept, I'll play around with it a bit and see how hard it is to implement 60 different statements.

Lee

[-Queue-]

Thanks, Lee. If it helps, here's the formula I derived for it that details it a bit more.

If A = position of Item X in VL1 and B = position of Item Y in VL2, then Result of X and Y is the nth repetition of the global, where n =

A + (count VL1 Items) * B - (count VL1 Items) * (count VL1 Items + 1).

Enjoy! :cD

[Lee Smith]

Hi Lesterd,

After playing a little with the files, I noticed a couple of things that need to be fixed. Your value list "Planning" has two misspelled words in it which will come back to bite you in the butt if not corrected.

The next thing I noticed is, I don't see your "Preparation" value list (the second part of the calculation)? Also, there is a value list called "DOMAIN_One_Components", but I don't see what it's purpose is?

lee

[lesterd]

Thank you for your help and spelling catch.

I really appreciate the time you spent to help me out.

Lester

[Ugo DI LUCA]

I see that some Mathematicians were still playing in August.

I'd keep Vaughan advice though as relationships are more flexible than any repetitions or text fields.

I simply added "Satisfactory" in the Performance Value List and Changed g_comment 3rd repetition to "Smtp3 Go and Satisfactory"...and it stopped working.

I went back and kept the Performane Value List as it was, then selected "Ready" and manually entered "Satisfactory", and this lead to c_comment = "Smtp1 Ready and Unsatisfactory".

Back to change g_comment repetition setting "Stmp1 Ready and Satisfactory" and "Smtp4 Ready and Unsatisfactory".

Then selecting from the lists "Ready" and "Unsatisfactory" lead me with c_comment = "Smtp1 Ready and Satisfactory"

[-Queue-]

I agree with the relationship idea, but the idea is still intriguing. If I have more time I want to try the transpose concept, which would be easier to modify. But you can't just randomly add or change values, silly. : You have to follow the example 'formula' for it to know which line to pull. If you added "Satisfactory" as the fourth option in the second value list, then any results for it would have to be the 10th-13th statements in the repeating global, according to the example. This is simply a case of user error. Hehe.

[Ugo DI LUCA]

-Queue- said:

This is simply a case of user error. Hehe.

Ahhahh !!

Choose the key. Please not Esc

Agreed it was intriguing and that's why I played a bit with it man. As my user would do