« Document Picker Funct… | Home | UserNotifications fra… »

FileMaker SQL with Sum, Min, Max and Avg on results

As you may know, you can do SQL queries in FileMaker with MBS Plugin and than get result stored in memory to call other functions on that.

We got a few new functions for this: FM.SQL.Max, FM.SQL.Min, FM.SQL.Avg and FM.SQL.Sum

So you can do a query once and than let the plugin sum up the values or calculate an average on a subset of the result. You can specify column and first/last row to use.

# let FileMaker query data once

Set Variable [ $Records ; Value: MBS( "FM.SQL.Execute"; Get(FileName); "SELECT Price FROM Produkte") ] 

# Now get sum, min, max and avg

Set Variable [ $sum ; Value: MBS( "FM.SQL.Sum"; $records; 0) ] 

Set Variable [ $min ; Value: MBS( "FM.SQL.Min"; $records; 0) ] 

Set Variable [ $max ; Value: MBS( "FM.SQL.Max"; $records; 0) ] 

Set Variable [ $avg ; Value: MBS( "FM.SQL.Avg"; $records; 0) ] 

Set Variable [ $text ; Value: MBS( "FM.SQL.Text"; $records; "" /* firstRow */; "" /* lastRow */; "" /* firstCol */; "" /* lastCol */; ", " /* rowSep */) ] 

Set Variable [ $r ; Value: MBS( "FM.SQL.Release"; $records ) ] 

Show Custom Dialog [ "Sum: " & $sum & ¶ & "Min: " & $min & ¶ & "Max: " & $max & ¶ & "Avg: " & $avg & ¶ & "text: " & $text ] 

 
Coming soon for MBS Plugin 9.1. 

Claris FileMaker Plugin
30 01 19 - 10:37