Custom Function than handles field reference OR List of values?

Questions
4

Further testing shows this doesn't work as I wanted.

  • If you pass a reference to a related field as a parameter to a Custom Function: List(fieldOrList) gets the list as expected. However, List(fieldOrList;"") only gets the first item of the related list
  • If you pass a List() to a Custom Function, then List(fieldOrList) returns ? but List(fieldOrList;"") gives you the list.

Fortunately, that ? result is very useful.

So in order to make a Custom Function which can handle either case, you have to do something like this:

/*
SumIf() version 1 

Returns the sum of matching values.

For each (key,value) item in the lists,
when key = keyMatch, adds the value to the Sum.

Examples:
 SumIf("Quiz"; Child::Kind; Child::Score) = 30
 SumIf("A"; "A¶A¶B"; "1¶2¶5" ) = 3


Notes:

- You can pass either a field reference (Table::Field) or a List() for keyField and valueField.

- The List() function skips blank values, so if you have blanks in either Key or Value fields, the function will return
an error if the keys and values don't have the same number of items.  If there are missing values, but both lists
accidentally have the same number of non-blank items, the results will be incorrect!

*/

While (

[
  // Special trick: 
  //   List(x) will return "?" if x is already a list, 
  //   List(List();"") will return the original list,
  // Thus we can handle each parameter either as a field reference (e.g. Table::Field) or a list (e.g. "a¶b¶c")

  keyList = If( List (keyField)  = "?"; List(keyField;""); List(keyField));
  valueList = If ( List(valueField) = "?"; List(valueField;""); List(valueField)); 

  i = 0 ;
  n = ValueCount( keyList ) ;
  m = ValueCount( valueList ) ;
  sum = 0
] ;


i <= n ;  // continue loop until i > n

[
  i = i + 1; 
  k = GetValue( keyList; i);
  v = GetValue( valueList; i);
  sum = sum + If ( k = keyMatch ; v ; 0 )

] ;

Case( n = m;  sum; 
  "Error: SumIf() requires same number of keys and values, and does not handle missing keys or values."
  )
)
2 Likes
Similar to Sumif