1 2 Previous Next 19 Replies Latest reply on Oct 9, 2014 1:35 PM by JavierDura

# Sum ListA to ListB

I have two value lists:

ListA: 4 ¶ 2 ¶ 3

ListB: 5 ¶ 4 ¶ 1

What is the right function (or custom function) to get a new list that sums each value from listA to its corresponding value in listB.

In other words, I want to get this result:

9 ¶ 6 ¶ 4

Thanks!

• ###### 1. Re: Sum ListA to ListB

A custom function or an UNSTORED calculation field can do the job.
The calculation could be made along this line:

Let([

\$i = \$i + 1 ;

a = GetValue ( listA ; \$i ) ;

b = GetValue ( listB ; \$i ) ;

v = Max ( ValueCount ( listA ) ; ValueCount ( listB ) ) ;

result = If ( \$i ≤ v ; List ( a + b ; sumLists ) ) ;

\$i = ""

];

result

)

Note that sumLists is the name of the fied itself: create it before as a text field than change it to a calculation field.
The custom function could use nearly the same calc.

*** the unstored calculation works well with value lists that have not more than 200 150 values.***

1 of 1 people found this helpful
• ###### 2. Re: Sum ListA to ListB

A calculation (stored or unstored is fine) that does not use recursion could be the following.

Using Substitute() it builds an expression for summing the values in each line of the list and then uses Evaluate().

Testing using FileMaker Pro 11,

I found that it worked on lists up to 2500 test values composed of a simple sequence 1¶2¶3¶4...¶2500 for both ListA and ListB.

It gave an empty result when I tried 3000 values.

I expect that the length of the string generated for Expression() is the limiting factor.

At 2500 values the argument for Evaluate() was over 130,000 characters long and still worked.

3000 values the argument for Evaluate() was over 158,000 characters and didn't work.

Let([

fieldnameA = GetValue(Substitute(GetFieldName(ListA);"::";"¶");2);

fieldnameB = GetValue(Substitute(GetFieldName(ListB);"::";"¶");2);

\$i=1;

lineBR = Filter(GetField(fieldnameA); "¶");

expr =

Case(  // Accommodate empty first values in both lists

// Return 0 instead of empty quotes if you want adding two empty values to be zero

IsEmpty(Trim(GetValue(ListA;1))) and IsEmpty(Trim(GetValue(ListB;1))); "\"\"";

GetValue(ListA;1) + GetValue(ListB;1)

) &

Substitute(lineBR; "¶";

"&\¶&Let(\$i=\$i+1;GetValue(" & fieldnameA & ";\$i)+GetValue(" & fieldnameB & ";\$i))")

];

Case(

ValueCount(ListA) = 0; "";

Evaluate(expr)

)

)

-Tom

• ###### 3. Re: Sum ListA to ListB

Why is it necessary to do this?

Why isn't the data stored in normal FileMaker fields?

• ###### 4. Re: Sum ListA to ListB

Where did you empty the \$i ?

If a list ( listA ) is empty, the sum of the two value lists correspond to listB, I think.

A recursive custom function can evaluate 10000 values.

• ###### 5. Re: Sum ListA to ListB

Daniele Raybaudi wrote:

Where did you empty the \$i ?

I'm not sure I understand the question.  \$i is initialized to 1 in Let().  Its value is inherited in when \$i is evaluated inside the Let() inside the Evaluate().

Since \$i is a script variable, the calculation engine will empty it after the outermost Let() is evaluated.

If you invoke this from another calling context, script or calculation, the value of \$i will persist for that context.

If you anticipate that this will present a name conflict problem, I recommend renaming \$i to something that is not used by the calling function or script.

Daniele Raybaudi wrote:

If a list ( listA ) is empty, the sum of the two value lists correspond to listB, I think.

I think that is a valid interpretation of the function's requirements.  I think that interpretation would imply also that if ListB were empty, the function should return ListA.   Thus

ValueCount(ListA) = 0; "";

could be rewritten as

ValueCount(ListA) = 0; ListB;

ValueCount(ListB) = 0; ListA;

or perhaps

IsEmpty(ListA); ListB;

IsEmpty(ListB); ListA;

-Tom

• ###### 6. Re: Sum ListA to ListB

"Since \$i is a script variable, the calculation engine will empty it after the outermost Let() is evaluated"

\$i isn't a script variable, it is a Let variable; its value persists at the end of the calculation.

You could see it with the data viewer.

• ###### 7. Re: Sum ListA to ListB

Daniele Raybaudi wrote:

A recursive custom function can evaluate 10000 values.

True.

Recursion works pretty well in that context.

If we combine the techniques with batching, I suspect we could handle lists with 100,000 values.

Recursion inside an unstored calculation field other the other hand can apparently handle far less.

In FileMaker 7, your calc's recursion appears to stop evaluating at 169 values.

In FileMaker 12, the recursion looks to go deeper allowing 185 values.

-Tom

• ###### 8. Re: Sum ListA to ListB

Daniele Raybaudi wrote:

"Since \$i is a script variable, the calculation engine will empty it after the outermost Let() is evaluated"

\$i isn't a script variable, it is a Let variable; its value persists at the end of the calculation.

You could see it with the data viewer.

That's a new one to me.  I had mistakenly assumed that \$value assigned in a variable and one assigned in a script would have a similarly limited scope.

I interpreted the persistence of \$i to be at the same level or inherited into sub-calcs.  I didn't think it would persist past the current calculation context.  The data viewer and viewing using other calculated fields reveals its persistence.

Thank you for making me aware of that.

This begs the question of what difference is there, if any, between using \$\$variableName and \$variableName in a Let() assignment in a FileMaker calculation outside of a Script?

-Tom

• ###### 9. Re: Sum ListA to ListB

TomHays wrote:

This begs the question of what difference is there, if any, between using \$\$variableName and \$variableName in a Let() assignment in a FileMaker calculation outside of a Script?

FileMaker Help has enlightened me.

"Local variables defined in a calculation are scoped to the file but are only available when scripts are not running."

Thanks again Daniele for pointing this out.

-Tom

• ###### 10. Re: Sum ListA to ListB

"This begs the question of what difference is there, if any, between using \$\$variableName and \$variableName in a Let() assignment in a FileMaker calculation outside of a Script?"

In a CF or in a calculation using the \$ variable assigned by a Let ( ) function there is no need to use a \$\$ global variable, they work the same and both must be emptied.

• ###### 11. Re: Sum ListA to ListB

Here is a later version where the index variable is cleared to prevent it persisting beyond the scope of the Let() following Daniele's advice.

I also cleaned up the code so that the only references to the field names is at the beginning (as originally intended).

This code has the same limits on performance as before.

Let([

fieldnameA = GetValue(Substitute(GetFieldName(ListA);"::";"¶");2);

fieldnameB = GetValue(Substitute(GetFieldName(ListB);"::";"¶");2);

\$j=1;

lineBR = Filter(GetField(fieldnameA); "¶");

expr =

Case(  // Accommodate empty first values in both lists

// Return 0 instead of empty quotes if you want adding two empty values to be zero

IsEmpty(Trim(GetValue(GetField(fieldnameA);1))) and IsEmpty(Trim(GetValue(GetField(fieldnameB);1))); "\"\"";

GetValue(GetField(fieldnameA);1) + GetValue(GetField(fieldnameB);1)

) &

Substitute(lineBR; "¶";

"&\¶&Let(\$j=\$j+1;GetValue(" & fieldnameA & ";\$j)+GetValue(" & fieldnameB & ";\$j))");

result = Case(

IsEmpty(ListA); ListB;

IsEmpty(ListB); ListA;

Evaluate(expr)

);

\$j=""

];

result

)

-Tom

• ###### 12. Re: Sum ListA to ListB

This is a custom function:

/*

SumLists ( listA ; listB )

*/

Let([

\$i = \$i + 1 ;

a= GetValue ( listA ; \$i ) ;

b= GetValue ( listB ; \$i ) ;

v= Max ( ValueCount ( listA ) ; ValueCount ( listB ) ) ;

result = If ( \$i ≤ v ; List ( a + b ; SumLists ( listA ; listB ) ) ) ;

\$i = ""

];

result

)

• ###### 13. Re: Sum ListA to ListB

I'm not a big fan of using local variables in custom functions. Especially a generic one, like \$i. You may want to consider using \$sumlists_counter as your variable, or passing a parameter to use as a counter.

If a developer attempts to use the \$i as a counter in a script along with this function, it could really screw things up.

• ###### 14. Re: Sum ListA to ListB

Here's one without a local \$variable.

/* SumListsAB (listA; listB)

*/

Let(

[

maxCount=Max ( ValueCount ( listA ) ; ValueCount ( listB ) ) ;

firstSum = GetValue(listA; 1) + GetValue(listB; 1)

];

Case(

IsEmpty(listA); "";

IsEmpty(listB); "";

maxCount = 1; firstSum;

firstSum & "¶" & SumListsAB(RightValues(listA; maxCount -1); RightValues(listB; maxCount -1))

)

)

-Tom

1 2 Previous Next