AggregateList(data;aggregation_type) function for foundset field / variable

Idea created by Vincent_L on Aug 20, 2017
    Active
    Score1

    Hi,

     

    This has been inspired by :

     

    hehazelhorst's https://community.filemaker.com/ideas/1546#comment-26880

    Beverly's Use Variables in Aggregate Functions (not just fields)

     

    For ages we needed a way to list the found set content of a particular field quickly, unfortunately FMI decided to implement this as the ListOf aggregated kind field (yet another field to clutter the database structure), which is much less convenient and not super fast either (it's faster than many things but for instance snapshot links for recordIDs are faster).

     

    So obviously we would need as hehazelhorst said a List(fieldname) function that would target the foundset field.

    But of course why stop at the list, that's also needed for every aggregate functions.

    And as Beverly said, why not use a value list contained in a table

     

    So rather than modify the behavior of all aggregate function, which could be tricky, let's create a new universal aggregate function :

    AggregateList(data;aggregation_type)

     

    where data can be either :

     

    - a variable containing values (return delimited)

    - a string of values

    - a fully qualified name :

         if the TO involved is the context's TO, then list all contents of the given field for the foundset

         if the TO is a related TO of the current context, then list all contents of the related field

     

    And aggregation_type could be :

     

    the usual :

     

    Average, Count, List, Max, Min, StDev, StDevP, Sum, Variance, VarianceP

     

    AND the new unique one (which will only list unique values)

     

    The new unique one already exist with UniqueValue, but frankly that's just logical to also have it here, and it will save a call, and hence it will be faster

     

    Of course, make this super fast. This is something we've all waited for years in a convenient (extendable, as FMI could introduce new aggregation types if needed) package.

     

    This could be even exposed to the end user via this Built-in clipboard copy of all found set values / aggregate of a particular field