FileMaker to SQL - Sending Unstored Calculation Field values?

[TheLagosChap]

Hi Forum,

Can anybody point me to the right direction on getting FileMaker unstored calculation field values synced into (any sort of) SQL database (one way sync)?

I understand that unstored calculation fields do not contain any stored data and their value can be dependent on a Filemaker context (including relationships, global variables etc)...

Rather than scripting some export from FileMaker itself, could MirrorSync do the job somehow?

Thank you

Serge.

[joshua-rivers-digital]

Hello Serge,

So MirrorSync does not sync unstored calculation fields, because like you said there is not any stored data and their value is dependent on FileMaker context.  The only way I can see in getting that data to sync is by creating a new field and specify the unstored calculation field as it's data.  Uncheck the Do not replace existing value of field (if any) box and then place that new field on the syncing layout.  You can also put the unstored calculation field on the syncing layout.  Even though that field is ignored by MirrorSync, as long as you have it setup correctly, that calc field should update the new text field during the sync.  

 

Joshua

360Works

[TheLagosChap]

Thank you Joshua.

I understand and it's not what I hoped for but what I expected

We're actually trying to expose FM data to Microsoft Power BI and of course the unstored calculated field values don't get there by themselves.

So we were thinking of putting a database in between (another FileMaker or some SQL - with stored data fields only) and use MirrorSync to transfer from solution's database to the intermediate source for PowerBI (transferring unstored calculations into stored data fields).

Since we'd rather not duplicate all fields if possible (it's going to be a big database already), maybe we should use instead some native FileMaker scripts that will have access to the unstored calculated fields values to transfer between the 2 db...

Thanks for your help!

[joshua-rivers-digital]

Hello Serge,

 

So I would like to correct my original statement.  While the work-around I suggested would work, it is actually unnecessary.  I was asked to test your scenario by the creator of MirrorSync, Jesse, and it will work.  Calculation fields are labeled by MirrorSync as not modifiable, so in a bi-directional sync calculation fields are ignored.  However, in a one-way sync from FM Server to MySQL the calculation field will be readable and therefore the data in it will be synced to MySQL.  There are a couple things that you will need to keep in mind.

1. You need to select Table and field names are different and already have the MySQL database setup.  That way you will get the mapping configuration dialog that will enable you to map the Tables and fields to each other.  The SQL generation script will not work here because calculation fields are not included in the generation.
2. You will need to make sure that the modification timestamp field will update in order for the sync to correctly pick up those records that have been modified.  MirrorSync looks for modifications since the last successful sync, if your modification timestamp fields aren't properly updated then those records will never be included in the sync because they fall before the last successful sync time.

I apologize for the initial confusion but am glad to report that your scenario will work if setup correctly.  If this is a proof of concept and something you would like to test, please send an email to [email protected] to request a trial license for MirrorSync with a FM Server to MySQL configuration.  Thank you!

 

Joshua

360Works

[TheLagosChap]

Thank you Joshua (and Jesse). That re-opens the option for us.

We still need to think about the (obvious) need to have the modification timestamp field updated for the record to be considered in the sync. But if our calculated field value changes due to a field value change in another table, the modification timestamp will have not been updated and the record will not sync.

So it appears that to be sure any changed calculated field value is synced, we would have to forcefully modify the modification timestamp for ALL records in the table. That would defeat the principle of "sync" since it would be equivalent to transferring the entire table every time.

So, some more brainstorming to do on our end it seems...

But thank you for the offer of a trial license! I will surely get back to you on this in case we want to explore this option further.