Custom Function than handles field reference OR List of values?

Questions
1

Re: Similar to Sumif got me thinking about how Custom Functions handle field references.

My goal is to have a single Custom Function which can handle either a field reference to a related table, OR a List of values.

Is it possible?

What if we always call List() inside the custom function?

Unfortunately, while List(F) works fine when F is a field reference:

image
image334×440 9.12 KB

It does not work if F is already a List:

image
image390×334 15.4 KB

However, the List() function is weird - it seems to work fine when you give it a second empty parameter:

image
image336×412 14.9 KB

The documentation for the List() function: link
does mention this alternate behavior:

Use this function to return a list of values for:

  • a single field (table::field), which returns a single result over all repetitions (if any) for this field and over all matching related records, whether or not these records appear in a portal.
  • several fields and/or literal values (table::field1,constant,table::field2...), which returns a separate result for each repetition of the calculation across each corresponding repetition of the fields. If any fields are related, only the first related record is used.

Perhaps the answer is that it's OK to call

List(fieldOrList;"")

inside a custom function, but it's not safe to call

List(fieldOrList)

unless you know for sure the reference is a field, and not a list?

2

List simply returns your values, concatenated with return separators, as a single text value. The expression below gets the list of all related records as variable. Most importantly, it's not seen as a list - it's just text - and the list function requires either a reference to a repeating field, or a related field, or two or more values. That's why you need to append an empty item in the List() function.

Let( 
    F = List ( child::score )  ;
    List ( F ; "" ) 
)

It's worth noting that list does not return empty values. If you have 10 related records and 2 of the fields that you are accessing are empty you will only get 8 items in the list.

4 Likes
3

This is a great tip!

1 Like
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
5

Hi @xochi

I like the test that you came up with for determining if the function has been passed a field reference or not. I would not have thought of testing for "?" output from List, though it makes perfect sense.

My usual MO for this sort of thing is to use GetFieldName wrapped with the EvaluationError function to determine if the input was a valid field reference.

e.g.

~is_field_reference = EvaluationError( GetFieldName( ~input )) = 0 ;
4 Likes