Replicating Filemaker Calculation and Summary fields functionality

For those of you who have migrated from Filemaker to Xojo, I’m wondering how you have replicated Filemaker’s Calculation and Summary Fields with Xojo/SQLite applications? I can only think of doing things like this:

Create a field in your SQLite file table that is to receive such Calc or Summary Field data. Have the UI update that field in that field’s Shown event. And then also code the TextChange event of each field that may be updated in that Page/Window/Container to update the Calc/Summary field when the former field’s contents changes.

There must be something easier than this. Is there?

[quote=289808:@Ralph Alvy]For those of you who have migrated from Filemaker to Xojo, I’m wondering how you have replicated Filemaker’s Calculation and Summary Fields with Xojo/SQLite applications? I can only think of doing things like this:

Create a field in your SQLite file table that is to receive such Calc or Summary Field data. Have the UI update that field in that field’s Shown event. And then also code the TextChange event of each field that may be updated in that Page/Window/Container to update the Calc/Summary field when the former field’s contents changes.

There must be something easier than this. Is there?[/quote]

I not from the FIlemaker world but can you give me an example of a calc/summary field (what it does) so I can maybe be able to give you some advice in the Xojo world?

I use a trigger in the database for that purpose. it’s done by the database, no need to make it from the UI
so it’s not possible to bypass it.

Scott,

A Filemaker Calc field computes a value based on one or more other fields. E.g., a Total field might be a Calc field, where it computes its value from the Price, Quantity and Sales Tax fields. A Filemaker Calc field can be stored in the database, or unstored (for the UI only, computed when displayed or otherwise accessed).

A Filemaker Summary field dynamically computes (when displayed or otherwise accessed, but never stored in the database) the total of, or the average of, or the count of, etc., a found set of records. The field is defined in such a way that whenever it’s accessed or displayed, it runs its logic and spits out its returned value.

Jean-Yves,

Triggers sound promising. I’m reading up on them now.

The equivalent sql functionality is a View. It runs your logic when you query it. I don’t recall if sqlite supports views atm.

@Ralph Alvy

For Summary fields, you can mimic those in the UI. or you can create SQL functions or views that can do the calculations for you. I am not sure if SQLite itself supports those.

For the calc fields that store the data I would use triggers. in a trigger when one of the relevant fields (cost, number of, tax in your example) is updated/changed/modified it would calculate and update the “total” field.

I think the short answer there is not a 1 for 1 mapping of Calc/Summary field to field in SQLite/PostgreSQL/MariaDB/MySQL/MS SQL Server/Oracle/etc. But there is a mapping to something based on the particular use case. Sorry I dont have a better more direct answer for you.

SQLite supports Views, but I don’t see how statements of this form will help me:

CREATE [TEMP | TEMPORARY] VIEW view_name AS SELECT column1, column2..... FROM table_name WHERE [condition];

All this does is create a virtual table with an assortment of columns found in one or more real tables. It would be different if I could create a previously nonexisting field in the virtual table, like Total, and have the logic of the View update it.

Unless, of course, as usual, I’m missing something very obvious here.

On the other hand, I can definitely see how Triggers can handle this.

[quote=289869:@Ralph Alvy]CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1, column2…
FROM table_name
WHERE [condition];[/quote]

Do you want to add column1 and column2?

[quote]CREATE [TEMP | TEMPORARY] VIEW view_name AS
SELECT column1 + column2…
FROM table_name
WHERE [condition];[/quote]

it should be better compared to something like : ( for a summary field):

select sum(column1) from mytable where [condition]

Ah. I see. I had no idea I could sum 2 columns to essentially create a new column in the View. Does that “new” column get accessed with a name like ‘column1 + column2’ ?

Name it whatever you want.

select column1 + column2 as total

FileMaker combines the business logic with the schema. There is no reason to store calculated values. (Always exceptions). You can perform calculations in sql. Select quantity, amount, quantity * amount as total from line items where invoice_id = ?

[quote=289876:@Scott Griffitts]Name it whatever you want.

select column1 + column2 as total

Well, that was easy. Thanks.

But to echo Peter, the only time I encounter views is with databases I don’t control. With my own databases, I just let the sql do the calculations.

So you and Peter suggest just using SQL logic in Triggers that update existing fields (instead of Views)? Or, alternatively, just have the UI update existing fields when needed, and forget about using a Trigger?

I guess it depends on your needs. Do you always need a total field to exist in the db or can you just add column1 to column2 whenever you need the total?

Just as you posted that last reply, I was about to say I could just use SQL logic to refresh a layout field without actually updating a field in the database, which is all the Filemaker unstored calcs do anyway.

Views can come in handy as an abstraction layer so that you can promise the users of your db that the columns in the view will never change though the columns in the tables behind the view can change. But that scenario assumes multiple users writing sql against your db and I doubt you’re attempting that with sqlite.

That’s correct. I don’t have anyone writing sql statements against the db other than myself.