FileMaker to LiveCode with SQL Yoga

This is the place to post technical queries about SQL Yoga

Moderators: heatherlaine, Klaus, FourthWorld, robinmiller, trevordevore

Post Reply
ejungemann
Posts: 3
Joined: Mon Jul 23, 2018 7:17 pm

FileMaker to LiveCode with SQL Yoga

Post by ejungemann » Mon Jul 23, 2018 7:22 pm

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

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 888
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: FileMaker to LiveCode with SQL Yoga

Post by trevordevore » Tue Jul 24, 2018 5:58 am

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.
Trevor DeVore
ScreenSteps - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

ejungemann
Posts: 3
Joined: Mon Jul 23, 2018 7:17 pm

Re: FileMaker to LiveCode with SQL Yoga

Post by ejungemann » Wed Jul 25, 2018 2:01 am

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

trevordevore
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 888
Joined: Sat Apr 08, 2006 3:06 pm
Location: Overland Park, Kansas
Contact:

Re: FileMaker to LiveCode with SQL Yoga

Post by trevordevore » Thu Jul 26, 2018 3:45 pm

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?
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.
  • 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)
Session Library

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
The API for the library can be customized based on your needs. The sDateRange script local variable is reset every time a new app session starts.

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 
If you go with this approach then you need to use the sqlrecord* APIs in order to get a reference to a record object that you can pass to sqlrecord_get.

http://revolution.screenstepslive.com/s ... oga/c/2059
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.
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.
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.
I see two approaches to this:
  • 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
If you are writing a function that uses SQL to generate the summary you might use the SUM, AVG, or COUNT functions in SQL (https://www.w3schools.com/sql/sql_count_avg_sum.asp). Your code might look something like this (untested):

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
Refer to theses docs for explanation of properties being set:

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 - http://www.screensteps.com

Levure Application Framework: https://github.com/trevordevore/levure
LiveCode Resources for Developers: http://livecode.bluemangolearning.com

ejungemann
Posts: 3
Joined: Mon Jul 23, 2018 7:17 pm

Re: FileMaker to LiveCode with SQL Yoga

Post by ejungemann » Fri Jul 27, 2018 3:21 pm

Hi Trevor,
Greatly appreciated and thanks for taking the time.
We're excited to get going on this.
Eric

Post Reply

Return to “SQL Yoga”