3 Replies Latest reply on Jul 8, 2011 10:12 AM by philmodjunk

# Calculation field and value list

### Title

Calculation field and value list

### Post

I have value list with elements for example 50(%), 50(%), 50(%), ...

I have field "x" = 100

How to create calculation field that calculate:

y = x+(x*first element of value list*0,01)

y = y+(y*next element of value list*0,01)

y = y+(y*next element of value list*0,01)

example:

value list: 50, 10

x = 100

result: y = 165

value list: 50, 50, 50

x = 100

result: y = 337,5

• ###### 1. Re: Calculation field and value list

Why use a value list for this? (There are a number of easier ways to put your values into a list of return separated values without defining a value list to do so.)

In a script, you can calculate it like this:

Set Variable [\$Values ; value: ValueListItems ( Get ( FileName ) ; "ValueListNameInQuotesHere" ) ]
Set Variable [\$Y ; YourTable::Xfield]
Loop
Set Variable [\$I ; Value: \$I + 1]
Exit Loop If [\$I > valueCount ( \$Values ) ]
Set Variable [\$Y ; Value: \$Y + \$Y * GetValue ( \$Values ; \$I )/100]
End Loop

• ###### 2. Re: Calculation field and value list

"Why use a value list for this?" - value list is based on the values from antoher table.

"In a script, you can calculate it like this:" - ok

Is there any possibility to do this in the calculation field ?

• ###### 3. Re: Calculation field and value list

To get a list of values from another table, you can use the List function rather than defining an actual value list in Manage | Value Lists:

List ( RelatedTable::ValueField ) will return the list of values from the related table.

This is a much better approach than using ValueListItems as this function would fail to work should you ever change the name of the value list in the future.

The problem with doing this in a calculation field is getting it to loop through the values in your list. This can be done with recursion--either in a custom function if you have FileMaker Advanced or in a recursive calculation field in regular filemaker (Can be tricky to set up and has limitations).

To use your script, keep in mind that you can use a script trigger to perform this calculation and this can produce results nearly identical to setting up a number field with an auto-entered calculation.

If you'd like to see how this would work as a custom function (Custom functions must be defined and added to a file using FileMaker Advanced, but once added to a file, will work as designed with Regular FileMaker), let me know and I'll recast the algorhithm as a custom function.