FileMaker to LiveCode with SQL Yoga
Moderators: FourthWorld, heatherlaine, Klaus, robinmiller, trevordevore
-
- Posts: 5
- Joined: Mon Jul 23, 2018 7:17 pm
FileMaker to LiveCode with SQL Yoga
Hi All,
We're moving a FileMaker project to LiveCode/MySQL using Dave Simpson's great tool FMPro Migrator Platinum which is doing much of the heavy lifting. It also uses SQL Yoga for the database manipulation.
In FileMaker, we make extensive use of:
Unstored Calculations
Global Fields
Summary Fields
I'm wondering if the forum has any ideas on the best way of reconstructing these field types on the SQL Yoga/MySQL.
Thanks for any advice,
Eric
We're moving a FileMaker project to LiveCode/MySQL using Dave Simpson's great tool FMPro Migrator Platinum which is doing much of the heavy lifting. It also uses SQL Yoga for the database manipulation.
In FileMaker, we make extensive use of:
Unstored Calculations
Global Fields
Summary Fields
I'm wondering if the forum has any ideas on the best way of reconstructing these field types on the SQL Yoga/MySQL.
Thanks for any advice,
Eric
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: FileMaker to LiveCode with SQL Yoga
Hi Eric,
I'm not very familiar with Filemaker so I don't know the specifics of what Unstored Calculations, Global Fields, and Summary Fields are. If you can describe what they are and how you use them then perhaps I can offer some suggestions.
I'm not very familiar with Filemaker so I don't know the specifics of what Unstored Calculations, Global Fields, and Summary Fields are. If you can describe what they are and how you use them then perhaps I can offer some suggestions.
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- Posts: 5
- Joined: Mon Jul 23, 2018 7:17 pm
Re: FileMaker to LiveCode with SQL Yoga
Thanks for jumping in, Trevor. Nice to have the creator of SQL Yoga provide input. Basically, we’re just seeking to narrow our research a bit. Once we get going, we’ll recap our choices for others doing the same thing. I think the issue is more the number of choices rather than the limits of choices moving forward.
Here’s an attempt at definitions of the data types in question. Field=Column Record=Row. We will be using LiveCode executable against a MySQL database (about 95% Mac, 4% iPad, 1% Windows). Database sizes up to 500GB:
In FileMaker, as you know, data and GUI are tightly bound vs. loosely bound in LiveCode/MySQL,(which, of course is made easier by SQL Yoga).
In FileMaker, a global field has a single value for all records in the table. The global field is owned by the user for each session. Global fields can be any data type including a calculation. An example would be rpt_StartDate, a date field that can be set by each user for that one session of the app. So everyone can run reports using their own date range. In FileMaker these global fields are attached to a table so that’s where they’ll be coming from. Maybe a LiveCode file loaded from the database?
Calculation fields perform calculations on the following types of data: text, number, date, time, or container (blob). The data in a calculation field is the result of a formula. The migration tool brings over stored calculations.
It is unstored calculations that we are interested in finding a corollary.
Unstored calculation fields are only updated when the record is refreshed, the field is clicked or tabbed into or out of, or the field is refreshed (for example, by a script). The main use of unstored calculations are calculations that reference data in another table.
For example, in a client record, the number of active pets is an unstored calculation based on the count of pet records in the pet table.
Summary fields hold values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May and will be used in the monthly and grand total lines of a report.
Summary fields are associated with groups of records (found sets in FileMaker parlance). The value in a summary field can change depending on where you place the field on a layout, how many records are in the found set, and how the records are sorted. If you change a value in one of the fields on which the summary is based, or if you change the found set, FileMaker recalculates the result in a summary field.
This is pretty standard stuff—summing all the data in a particular field (row) and delivering a total. In FileMaker you declare special fields that do this; so we have to get that somehow translated into the way SQL does it.
Thanks in advance for any thoughts.
Eric
Here’s an attempt at definitions of the data types in question. Field=Column Record=Row. We will be using LiveCode executable against a MySQL database (about 95% Mac, 4% iPad, 1% Windows). Database sizes up to 500GB:
In FileMaker, as you know, data and GUI are tightly bound vs. loosely bound in LiveCode/MySQL,(which, of course is made easier by SQL Yoga).
In FileMaker, a global field has a single value for all records in the table. The global field is owned by the user for each session. Global fields can be any data type including a calculation. An example would be rpt_StartDate, a date field that can be set by each user for that one session of the app. So everyone can run reports using their own date range. In FileMaker these global fields are attached to a table so that’s where they’ll be coming from. Maybe a LiveCode file loaded from the database?
Calculation fields perform calculations on the following types of data: text, number, date, time, or container (blob). The data in a calculation field is the result of a formula. The migration tool brings over stored calculations.
It is unstored calculations that we are interested in finding a corollary.
Unstored calculation fields are only updated when the record is refreshed, the field is clicked or tabbed into or out of, or the field is refreshed (for example, by a script). The main use of unstored calculations are calculations that reference data in another table.
For example, in a client record, the number of active pets is an unstored calculation based on the count of pet records in the pet table.
Summary fields hold values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May and will be used in the monthly and grand total lines of a report.
Summary fields are associated with groups of records (found sets in FileMaker parlance). The value in a summary field can change depending on where you place the field on a layout, how many records are in the found set, and how the records are sorted. If you change a value in one of the fields on which the summary is based, or if you change the found set, FileMaker recalculates the result in a summary field.
This is pretty standard stuff—summing all the data in a particular field (row) and delivering a total. In FileMaker you declare special fields that do this; so we have to get that somehow translated into the way SQL does it.
Thanks in advance for any thoughts.
Eric
-
- VIP Livecode Opensource Backer
- Posts: 1005
- Joined: Sat Apr 08, 2006 3:06 pm
- Location: Overland Park, Kansas
- Contact:
Re: FileMaker to LiveCode with SQL Yoga
Since you are dealing with a session only value two options come to mind. Note that I may mention some LiveCode concepts in here that require additional research. I won't be going into detail about each LiveCode feature in this initial post. Feel free to ask for additional details on anything.In FileMaker, a global field has a single value for all records in the table. The global field is owned by the user for each session. Global fields can be any data type including a calculation. An example would be rpt_StartDate, a date field that can be set by each user for that one session of the app. So everyone can run reports using their own date range. In FileMaker these global fields are attached to a table so that’s where they’ll be coming from. Maybe a LiveCode file loaded from the database?
- A library used to store session data
- Adding a custom property to a table object in SQL Yoga (requires that you use SQL Yoga Record objects)
In LiveCode you can use any stack script as a library. I often create a library for storing session data. This is simply a matter of adding getters/setters to the library and storing values in a script local variable. Here is a script only stack example:
Code: Select all
script "My App Session Library"
local sDateRange # script local variable for storing session value
command dataRangeSet pDateRange
put pDateRange into sDateRange
end dateRangeSet
function dateRangeGet
return sDateRange
end dateRangeGet
Adding a custom property to a table
SQL Yoga allows you to add custom properties to table objects in SQL Yoga. Here is an article which discusses how to do it:
http://revolution.screenstepslive.com/s ... -to-tables
You could create a custom property for your table like this:
Code: Select all
local sDateRange
function tableobj.get.MYTABLE.daterange pRecordA
return sDateRange
end tableobj.get.MYTABLE.daterange
command tableobj.set.MYTABLE.daterange pRecordA, pValue
put pValue into sDateRange
end tableobj.set.MYTABLE.daterange
http://revolution.screenstepslive.com/s ... oga/c/2059
SQL Yoga doesn't have a library that binds to the UI like this.I think you could use table object custom property for this as well. You would define a getter that performs any necessary lookups (e.g. count the number of pets in the pet table). You would need to manually call this getter when any UI changes might affect the value.Unstored calculation fields are only updated when the record is refreshed, the field is clicked or tabbed into or out of, or the field is refreshed (for example, by a script). The main use of unstored calculations are calculations that reference data in another table.
I see two approaches to this:Summary fields hold values such as subtotals, averages, and grand totals across multiple records. For example, a summary field can display the grand total of all sales in the month of May and will be used in the monthly and grand total lines of a report.
Summary fields are associated with groups of records (found sets in FileMaker parlance). The value in a summary field can change depending on where you place the field on a layout, how many records are in the found set, and how the records are sorted. If you change a value in one of the fields on which the summary is based, or if you change the found set, FileMaker recalculates the result in a summary field.
- Write a function that loops through the result set array that is returned by SQL Yoga handlers, add up the target field values, and then return the value
- Write a function that use SQL to generate the summary
Code: Select all
function getSummary
put sqlquery_createObject("MY_TABLE") into tQueryA
# set "conditions" property as needed
sqlquery_set tQueryA, "conditions", ...
sqlquery_set tQueryA, "select clause", "SUM(MY_FIELD) as summary"
sqlquery_set tQueryA, "find", "first"
sqlquery_retrieveAsData tQueryA, tResult
return tResult
end getSummary
https://www.bluemangolearning.com/revol ... ry_set.htm
Hopefully this gives you some ideas on how you might approach this. Others will probably have additional ideas or automated ways of approaching these problems. The types of apps I write in LiveCode don't do a lot of reporting or data entry that requires summaries, etc. so it isn't a problem I have pondered considerably with regards to more streamlined solutions in LiveCode.
Trevor DeVore
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
ScreenSteps - https://www.screensteps.com
LiveCode Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode
LiveCode Builder Repos - https://github.com/search?q=user%3Atrevordevore+topic:livecode-builder
-
- Posts: 5
- Joined: Mon Jul 23, 2018 7:17 pm
Re: FileMaker to LiveCode with SQL Yoga
Hi Trevor,
Greatly appreciated and thanks for taking the time.
We're excited to get going on this.
Eric
Greatly appreciated and thanks for taking the time.
We're excited to get going on this.
Eric
Re: FileMaker to LiveCode with SQL Yoga
Hello from Barcelona,
Me and a colleague want to move a FileMaker solution to LiveCode. We know all the differences between database, but .... what about the scripting ? How it becomes to script livecode ?? is it automatic ??
Thanks.
Me and a colleague want to move a FileMaker solution to LiveCode. We know all the differences between database, but .... what about the scripting ? How it becomes to script livecode ?? is it automatic ??
Thanks.
-
- VIP Livecode Opensource Backer
- Posts: 7258
- Joined: Sat Apr 08, 2006 8:31 pm
- Location: Minneapolis MN
- Contact:
Re: FileMaker to LiveCode with SQL Yoga
LiveCode is a programming environment so you need to script it yourself. The User Guide will give you information about how to do that. You can open the User Guide from LiveCode's Help menu.
Jacqueline Landman Gay | jacque at hyperactivesw dot com
HyperActive Software | http://www.hyperactivesw.com
HyperActive Software | http://www.hyperactivesw.com
Re: FileMaker to LiveCode with SQL Yoga
I pass the link to fmpromigrator: http://www.fmpromigrator.com/services/d ... rvice.html
I see that you already know the differences between the databases, it's one of the things that has given me the biggest headache. Especially the theme of accents with the MariaDB bug
Regards
Carles
As you will see, it is passed directly to SQLYoga and FileMaker Pro scripts are converted to executable LiveCode code. I guess Eric can confirm itFileMaker Pro scripts are converted into executable LiveCode code.
Utilize SQL Yoga and LiveCode Data Grid technology for sophisticated database functionality.
I see that you already know the differences between the databases, it's one of the things that has given me the biggest headache. Especially the theme of accents with the MariaDB bug
Regards
Carles