Showing posts with label Filemaker. Show all posts
Showing posts with label Filemaker. Show all posts

Tuesday, February 16, 2021

Filemaker Making Progressive Limits

 It is not unusual for performing progressive limits. Basically it is just to limit the search by selecting various attributes of the item. For example, in a electronic product, you would want to limit to hand phone, Xiaomi, 4 GB Memory, 256 GB storage to find the price of item that match the criteria. Its like setting filters as you browse the products. 

Most on-line product sellers would allow for filters. Here, the idea is to do it in FileMaker which does not have dynamic content and have difficulty in doing paging without resorting to web based techniques.

Doing filters in FileMaker is not that easy. as lists are not set dynamically according to selected fields. Using SQL to filter is also not that easy as user select different options in various fields.

Below is a simple method of doing progressive filtering. The key in doing that is to force user to select various options in a fixed sequence/

Using the model mentioned above, there are three filters. 1. Model, 2. RAM, 3. Storage. Obviously, in real products there are much more options to choose but for simplicity, only 3 options are shown here.

STRUCTURE

One main table where the filters are set.

One product price table where the Model, Ram and Storage are set.

METHOD

Create a field for each filter in the main table.

Create a list based on Model on the product price table and set "Model" field to select from this list.

In the database Relations tab, create a duplicate product price table. Set the "Model" field of main table to the "Model" field of the price table. Create a list from this duplicate and set it as select list to "RAM" field of the main table. Hide the "RAM" field if "Model" is not selected.

In the database Relation tab again create a second duplicate of the price table. This time Set the "Model" and "RAM" field of the main table to the "Model" and "RAM" field of the price table. Create a list from the second duplicate. Hide the "Storage" field if any of the "Model" or "RAM" is not selected.

In the database Relation tab again create a third duplicate of the price table. This time Set the "Model", "RAM" and "Storage" field of the main table to the "Model", "RAM" and storage field of the price table. Create a script to set the "Price" field of the main table to the "Price" field of the third price table.

In this way the fields are chosen progressively according to the order of the available fields. This relation setting allows user to change various options easily. You only need to ensure that the "Price" field of the main table is blank first every time user make changes on any of the fields unless you want to make the field as the "Price" in the third price table without the script.





Sunday, June 23, 2019

GSX Questions

It was something that was started quite sometime ago but wasn't made compulsory. Questions are a set of data that was posted as questions and user is supposed to answer it accordingly. Its as simple as that.

However, the Questions are nested questions and no fixed pattern. Previously, a pointer is used if there are nested questions. It could not work that well. Recently GSX changed the data format to JSON and the Questions were set in nested JSON format. This makes it much easier to follow as we just need to traverse the nesting structure typical of JSON.

Fortunately, Questions uses fixed format in the form of Questions and Answers. The nesting is always at the Answers array or as a array in the Questions level.

This blog talks about how to traverse through the list and nested questions using FileMaker. As it is, GSX returns a JSON. Since FileMaker has JSON functions that can read nodes from JSON stored in fields or variables and the format (path) of reading the nodes is like "XX.YY.ZZ". If it is an array, just add the array number after the node name like "XX[0]".

The method of traversing the nodes is simple. The path is stored in a variable.  We start off with $trees="questionDetails[0].trees[0].questions[0].answers[0]". Using JSONListKeys($json;$trees) we can get a list of keys. If there is no answers, we remove the last node and add 1 to the previous level array then repeats to find the answer keys.

Upon finding the answer, we post the question and gets an answer from user. Depends on answer type, user have to respond accordingly. Once finished answering then we look for nested questions in the answer. If there are nested questions, we add one new level after answers and go to the first question in the nested questions.

In the nesting, if there are more items in the array, we go into the next item in the array and look for questions or answers. If there is no more or no questions/answers. we back up one level and see if there are more items in that level.

In this way, we could go into the nesting systematically and back up to the higher level if there are no more array item.

GSX requires you provide the question ID when you answers. So you must create an answer JSON that corresponds to the Questions. Every time you add a question/answer level you need to add the array first so that when you answer, you can write the answers using the same $trees array.

I won't be diving into showing examples of traversing. Do figure out how to do it yourself.




Thursday, June 20, 2019

Communicating with Apple Web Services (GSX) via Filemaker 2

My previous blog on the same subject is 3 years ago. Since then Apple has changed from using XML to JSON as a means of transferring data. Therefore there is a need to show how to do it with JSON.

JSON (Javascript Object Notion) by its name, is a Javascript feature but it has extended its use to many other applications. It is a format of storing data just like XML except it is more human readable when you format it nicely.

Fortunately, FileMaker has caught up with the latest trend by including a few JSON related functions. It is not that easy to use those functions to compose JSON but it is very much easy to get data from JSON.

To create simple JSON you just need to use $example =JSONSetElement("";"elementkey";"elementvalue";1) and it will create a nice JSON '{"elementkey":"elementvalue"}'. However, if you want to insert nested JSON then it is a bit tedious.

For example, you want to create a key that contains an array with one key and  value. First you must create the key and value as a JSON like last paragraph. Then you have to create an array to contain that JSON like $array= JSONSetElement("[]";0;$example;3).

Finally you then create the root element like $root=JSONSetElement("";"root";$array;4). You have created a JSON '{"root":[{"elementkey":"elementvalue"}]}'. This format is generally used by Apple in GSX in both request and response.

If it is a simple JSON that need to store a small set of data with fixed keys, it is better to create the JSON with unique values for each element that needs to be changed like "~!1", "~!2" etc. After that use substitute function to replace the "~!x" sequence. Two lines instead of 3. In actual fact, one line encompassing both action can be done by just using the JSON as source of substitute directly. In this way you could reuse the same code repeatedly or even store it as custom function.

The parameters used in JSONSetElement can be found at. JSONSetElement.

Getting values from "elementkey" in the above JSON is easy. $value=JSONGetElement($root;"root[0].elementkey")

If you looking at this blog first then I suggest you go to the previous blog on the same subject first to get an understanding.


Wednesday, November 01, 2017

Filemaker add new records into related tables/portals

Filemaker has this nice feature of being able to add new records to a related table without having to go to the table and insert new record.

What you need is simply click on the relation link and click "allow creation of records" in the related table side.

To add a new record just change the value in any of the fields in the relation table.

There are times when your relation is one to many and you need to create the records in similar ways as above. Well, it is possible but you have to do a few setups.

First you must have the relation setup as mentioned before. Next you need to create a portal in the form view of the original.

If you just want to insert relation rows manually, just go to the portal and click on the last blank row and type in the new data.

To insert the new records in scripts is a bit more complicated. You need to do further setup. The first thing is you must set the portal to allow "vertical scroll". Also, you must name the portal. In your script, you need to do two things.

Go to Object; []
Go to Portal Row:[last]

After that you could just use "Set Field" to insert any value.

The setup looks simple but if you missed the "Go to Object" script, it will never work.



Sunday, October 29, 2017

Filemaker Finding a Range of Dates

Finding a range of dates in Filemaker is not new. It is available in Filemaker Helps but since it is quite unusual, it is worth repeating.

To find a date range, you need to enter Find mode. At the date field enter the date range like .. then press enter. Note that the date entered is the system date format not the displayed date format as the display date format may not be the same as system date format.

To do a date range find in Script is slightly more complicated. You need to create two date type global fields.  These global fields is for user to enter the search date range.

First enter a script step to start find without definding the find requests. Set the pause off. Then set variable to the field with the two global fields as start and end date like below

Enter Find Mode []
Insert Calculated Result [Select; mytable::mydatefield; mytable::GlobalStartDate &"..." & mytable::GlobalEndDate]
Perform Find

There are instances where your date range is not taken from user's input. In that case you have to modify the script a bit.  Get rid of the 2 global fields and create two variables that contains date values. Here I use static date values. You can use variables to define the date values.

Set Variable [$startDate; Date( 8; 1; 2017)]
Set Variable [$endDate; Date( 8; 31; 2017)]
Enter Find Mode []
Insert Calculated Result [Select; mytable::mydatefield; $startDate &"..." & $endDate]
Perform Find

Normally the date range is by month. It is easy to modify the above script to search by month. 

Set Variable [$startDate; Date( 8; 1; 2017)]
Set Variable [$endDate; Date( 8+1; 0; 2017)]
Enter Find Mode []
Insert Calculated Result [Select; mytable::mydatefield; $startDate &"..." & $endDate]
Perform Find






Friday, October 27, 2017

FileMaker check for date range overlapping

Normally when we want to check if a datetime falls between a range of dates, we will use a SQL to count where queryDate >= startDateField and queryDate <= stopDateField.

The tricky part is to find if a range of date overlapps other range date. Common sense will say that just modify the query to

(queryStartDate >= startDateField and queryStartDate <= stopDateField) or
(queryStopDate >= startDateField and queryStopDate <= stopDateField) .

The above method works if the queryStartDate and/or queryStopDate fall within the startDateField and stopDateField. What if queryStartDate and queryStopDate is beyond the date range covered by startDateField and stopDateField? The modified query will not work as both date are outside of the date range.

To cover such condition, there is a need to check whether startDateField and/or stopDateField is in the range of queryStartDate and queryStopDate. So in addition of the modified query, we need to add extra condition like below.

(queryStartDate >= startDateField and queryStartDate <= stopDateField) or
(queryStopDate >= startDateField and queryStopDate <= stopDateField) or
(startDateField => queryStartDate and startDateField <= queryStopDate)  or
(stopDateField => queryStartDate and stopDateField <= queryStopDate)

In plain English, the query check whether the query date range overlap/within the field date range or the field date range overlap/within the query date range. Took a while for me to sort out the confusion.



Sunday, October 01, 2017

Scheduling of Employees using Filemaker

There was a request to write an app using FileMaker to do employee scheduling as part of a overall project for HR to check employee attendance. The request is quite peculiar.

1. The business opens 7 days a week.
2. Each employee is entitled to have 2 days OFF each week.
3. Supervisor and back up must not be off on the same day.
4. Special task and back up must not be off on the same day.
5. Other team members must be evenly distributed across the week.
6. The whole team must be randomly and evenly distributed across the week.
7. The order of schedule by person must also be random.
8. Employees can request for OFF on specific day on per request basis (to coincide with their Leave).
9. The schedule must be displayed by month with days of the month on columns and employees on rows.

Although you can define up to 250 million fields, it is impossible to use date for fields/columns otherwise you must keep adding fields with specific date. FileMaker does not have scripts that could create fields and add to table layout. It is easier to use employee for columns as the number of employees are limited. Moreover, the business is small thus easier to set a maximum number of fields/columns and increase them when necessary.

The first table is structured with first field/column as date followed by a fixed number of employees with field names designated as Exx. xx denotes a two digit leading 0 number. Its a small business thus need not set a large number. The first row is designated as Employee name. The date field is blank and each employee column is set with an employee name. It is easy then to just add rows with dates by month. This is highly unconventional. However, since the requirement is to show by month, it is still viable.

The search function should have 2 criteria

1. blank date
2. dates of the specific year/month.

Sort the records by date and you get a nice table with employee on row one and the rest of the dates below it.

Item 9 is using dates as columns. Since it is displayed by Month, It is easy to create a temporary table by transposing the rows into columns and add weekdays as row 2. The table have 32 fields. First field is employee name. Next field is day 1 of the month and so forth.

It is easy to create one row with days of the month as data. Row 2 will then be the dayofweek translated into text using case function. I am sure you know how to translate number to text.

The method is to use ExecuteSQL to retrieve one employee at a time for the whole month. Since we set a maximum number of employees. We can retrieve the whole list of employee like below.

ExecuteSQL("select E01,E02 from firsttable where scheduleDate is null fetch first 1 row only";¶;"")

We will also retrieve the whole months of data like below

ExecuteSQL("select E01,E02 from firsttable where sMonth=? and sYear=?";¶;"";firsttable::gMonth;firsttable::gYear)

gMonth and gYear are global fields for user to indicate which month and year to get data from. sMonth and sYear is the month and year of the date (calculated field)

Do a loop within loop. The outer loop adds a new row and uses the result of the employee list to retrieve them one by one. The inner loop picks up one particular value from the list as defined by the first loop.

For example, outer loop choose item 1, inner loop goes through the entire list but picks item 1 from list only. This will populate the table row by employee E01 and those E01 values by date.

The result is a nice transformation of row into columns.

The next thing is to fill in the data for the first table. Use a drop down list tor each field in the table. It ensure standardized data definition.  This will allow leave applications and OFFs to be added into the table. With this data added, we need to do the actual scheduling.

Item 8 above allows for pre-request OFF days. Therefore, the scheduling must take into consideration the pre-defined off days. It is much harder to find those pre-defined off days and leaves and do a schedule on the first table directly.

Therefore, a third temporary table is required. The table structure is similar to the second table except the columns are only 7 which consists of the days of week starting from Sunday. We retrieve the weeks data from the first table sorted by date like the translation method for second method except we don't have to add the first 2 rows. Also, you should skip those columns without employee name defined to shorten the computing time.

In addition to retrieve employee name and the 7 days. we need to define additional fields to facilitate the scheduling. This is for reasons which we will explain later. The additions are as follows

1. We need to find out which Employee is assigned to which field/column in the first table. This is to allow for locating the particular employee after we do random sorting.

2. We need a column to fill in the random number for sorting purposes.

3. We need 7 column as summary fields (count) for each day of week to check the off and leave distribution.

4. We need a global field to store the first day's date so that we could update the table later.

Now, we retrieve the data from the first table and transpose it in to this third table. However, there is a twist. Instead of retrieving the literal text, we have to translate it to 1 or 2. Leaves are designated as 1 and off and pre-defined OFFs are designated as 2.

Immediately you will see that the 7 summary fields will show the count of all leaves and offs for the day.

Item 3 requires Supervisor and backup to be scheduled not on the same day. Obviously, there must be at least 4 days available for scheduling. First schedule one person by randomly choose between the two. Just get the list of days which are blanks. You can just go to the row and read each day to see if it is blank then add the field name to list. Randomize the list and set the first two day as OFF. you must first count the number of 2s. Set the number of OFFs accordingly.

The schedule for the second person is a bit tricky. You must use a count function in the SQL and limit to the two person to retrieve the blank dates. This will effectively ensure that only those days that both are not schedule can be selected. Randomize the list again and set the first two days as OFF. Before setting any OFFs, you must first count the number of 2s. Set the number of OFFs accordingly.

Repeat this for Item 4. Suggest to put Supervisor and back up as row E01 and E02. Special task and backup as E03 and E04. This will make it easy to identify which one to schedule.

Now, before we could do anything for the rest of employee, we need to randomize the employee list. This is easily done by filling in a random number. Random numbers in FileMaker are from 0 to 1. There are custom functions that could provide integer random numbers within a range. Suggest to use a range at least 3 times the maximum number of employee list in table 1.

Sort the table by the random number field. This will effectively randomize the employee list. Note that it is the entire employee list. This include supervisors and special tasks. This action satisfy Item 5 and 6.

This is when the 7 summary fields are coming into play. We will choose the first summary field that has the smallest count. Since is it is summary field, it appears in every row. First set a variable with a large number. Second set a variable that is blank. Go through each summary field. if the count is smaller than the first variable, replace the first variable with the count and set the second field as the corresponding field name of the day of week. Just set the fiend name as OFF ("2").

Go through the entire employee list using a loop of 2. You need to redo the random number and sort again to scramble the employee list before the second loop. To schedule exactly two OFFs, you need to first count the number of 2s for the person. If it is already 2 then skip to the next employee. This will effectively allows for pre-defined OFFs to be treated like off. You can even repeat the same week 's schedule and it will not alter the schedule if there are already 2 OFFs assigned to the person. This will satisfy Item 7.

Once the schedule is complete, you need to translate the table back to the first table. First limit the first table to just the week and sort it by date. Remember we stored the first day of the week as date? Just create 7 variables to store a date by adding 0 to 6 days to the first date. Use the 7 variables to create 7 search condition and put them in a "find" function.  Use a SQL to read the third table by the weekday and the employee field name.

Again we need to do a loop on the rows in the first table. Get the date and translate it to dayofweek text. We do a inner loop to go to the whole list of the third table to find the employee field name and the particular day of week. Update the first table with "Off Days" if the first table is blank and the third table data is "2".

The result is a nicely updated OFF schedule.

Note that you may need to get the data of the field by using GetField("fieldname"). This is to allow for getting the content of the field by calculated field name.

You also need to do update to fields by using "Set field by name("fieldname"). However, the field name must be in this format "tablename::fieldname". I don't know why two different ways of defining field names but that is FileMaker. I end up using Get(LayoutTableName) & "::" & "fieldname".






Thursday, June 08, 2017

Doing a Filemaker PO

Doing a PO is quite simple thing. It gets a bit tricky when you want to create PO number and do sums that is stored in the main table while the PO items is in the related table and fill in the items one by one. Each record is related to the main table by the PO number.

First the main table must have a self increment field. This will act as the PO number. You can add prefix or post fix with it. And to add in the fun you can do "Right('00000' & table::idfield;6)" to get a nicely formatted 0 pre-pended text and store it in another calculated field.

The first thing to do is create the relation. Drag the previously mentioned calculated field to join to the sub-table field. Now click on the link itself and it will display a pop up like this


The trick is to check "Allow creation of records in this table via this relationship". When you check this you are actually telling FM to create the record with the specific PONo::POID value inserted into PO::Po Number automatically if you start to fill in PO fields. The relation will be automatically created this way. PO table will also add new record for you automatically. Lets go to the layout to do a practical.

In the layout You add PO Date and Supplier from PONo. You will then create a portal and choose PO as the relation table. It will prompt you for what fields in PO to add to the portal. Just choose any fields other than PO Number. Add the appropriate field name title. Exit editing after that.

Now you can add records. First click "Add Record" from the menu. It will add a new Record in PONo table. Fill in PO Date and Supplier. Click the top left end of the portal and you can enter the PO table fields without having to create the record. It will self create and automatically add the relation field info to establish the relationship. You can continue to click on the area below the last record to create more records.

The next trick is to do calculations. The PO table have a Unit price and a quantity field. There is a field that do the sub price by multiplying Unit price with Quantity. There is also a summary field that sums the sub price (not incremental). This is the field that we are suppose to update back to PONo table. The PONo also have a GST calculation that add a specific percentage of tax. Finally, there is a total sum field to show the total sum.

Now getting the summary field back to PONo is no issue. Just do a calculated field at PONo to get the PO summary field value. The GST and the Total value is then calculated from there.

Problem appears once you try to do this. The calculated fields in PONo will just go blank once you add some Unit Price and Qty. I don't know whether it is Filemaker bug or not but it gets irritating to not be able to see the calculated value.

Luckily there is a way out. The method used is "script triggers". On the Unit Price and Qty field, add a trigger "on Object Exit" for both. The trigger script will do a object refresh by name. You should name Unit Price and QTY plus the overall total field. Refresh all three fields in sequence and you solved the issue. You may want to do "commit" first before doing refresh.

On the report layout just use a list form and put PO fields in the body with the PONo fields in header or footer accordingly. You just got yourself a nicely done PO report.


Thursday, March 30, 2017

Filemaker hiding field object in Table View

There are times when we don't want to see some fields based on the values of another. In form view you can easily set it to "hide when" and set the condition to hide it.

However, in table view, the field is not hidden (up to V15). What are other means to hide the field?

I used a simple "conditional formatting" setting and set the text color to the background color. It is still shown at least not distinguishable to user.

It is a work around for Filemaker Table view which is unable to hide the object despite the "hide when" setting is set.




Thursday, March 16, 2017

Filemaker Custom Function

According to Steve Lane CTO of Soliant, Todd Geist said "he prefers to avoid custom functions whenever possible, preferring to use scripts instead". Steve himself said "everything you can do in a CF can be done in a script." Now  that is an anti-climax when the title above is about Custom Functions. My purpose here is to make sure you get the right perspective about CF (custom function) before even jumping into it.

Unlike most common understanding of a function, FMP custom function is severely limited in its feature. It is like a formula function. The only thing it could do is return a result of a calculation. It works exactly the same as FM functions. It is even less functional than what a Plugin can do.

Let starts with how to create a CF. First of all, you need to purchase Filemaker Pro Advanced. This means that not all users can do maintenance on CF regardless of their rights.

Open FM as normal. Go to File Menu,  Manage, Custom Functions. You will see the following popping up. If you have any CF in other FM, you can import the CF into the FM that you are working on.


Click on "New" the next popup will show.


Change the "New Function" to your function name.

Add any function parameter names as you wish. The line below it will show the structure of the function. You can swap the parameter orders. One thing to note is that the parameters' value cannot be skipped.

You can call the function with the function name and semicolon delimited parameters if you have more than one parameter.

Since CF are like formulae, the available list of FM functions for your CF are all available, Plugin functions are available if you have one or more. CF function is also available if you created more than one in current file.

What functions can be done? Well practically anything as long as it results in a value. You would probably want to do some formula that you used very often in the current or other FM files. Ideally, it should not refer to any fields and tables directly.

There is one very important thing you must remember. There is no loop or recursion. Loop is actually a step not a function. Well, it is not entirely that FM leaves you without some recursive means.  You can actually call the function itself and provide all the parameters that allows it to function. The important thing to remember is that you must have some means of breaking out of the loop otherwise it will loop 10000 times and return a "?". The maximum number of recursion is 10000 times. The following is an example of recursion.

Let([
nvalue = nvalue+15;
ncount = ncount + 1
];
case(ncount > 100; callme(nvalue, ncount); nvalue)
)

This example, coincidentally, is using a method called "tail recursion". If you need to call the function in the middle of calculation (like callme(nvalue, ncount)+somevalue) then you are limited to the FM stack size limit of 10000. The example has all the calculations contained in the parameters itself so FM don't have to remember the result of the function. It therefore allows you to do up to 50000 recursions. Not much, but at least you have 5 times more.

Notice that the example actually uses "LET" and "CASE".  "Let" allows you to performs a number of calculations and still return one value. "Case" can be used to return different values depending on the test result. It is therefore, a valid function with the use of the two. "IF" can also be used if your result is one or the other depending on the test condition. Just remember that "Case" and "If" must be assigned to a variable unless it is a formula by itself

In actual fact, "Let" has to be used in almost all CF except when your calculation is actually a formula itself like param1 + param2. Naturally, your need to create CF is not just that right?

One thing you must remember in using "Let". The very last item in the multiple assignment must not end with a ";" while other previous assignments must have ";". See the example above. If you forget you will get a mind boggling error message something like "cannot be assign to a list".

One more thing to remember is that if your formula ends with error, it will return a "?" as result. Only when it is a syntax error then it has a better response. The debugger in FMPA only steps through script steps.

There are plenty of example CF in the internet. Search for them and see what they do. You will get to know how to code a CF in no time.




Friday, February 24, 2017

Barcodes in Filemaker

When you need to print Barcodes in Filemaker, there are various ways to do it. One way is to use the "repetition" in the field. Each repetition represents a bar or blank. Set the width to 1.  Change the background to black if it is a bar. Obviously, you need to systematically convert the text to the specific type of barcodes first in the form of 1 and 0 then transpose it to the repetition field. It takes two extra fields. One to do the conversion, the other one with the repetition is used to display the barcode. Unfortunately you can't have GS1 HRI (Human Readable Integration).

Another method is to generate the barcode from a script. There are implementations available using plugins (because they want to sell the code). I can't find one that is free.

One other methods uses web views to display the barcode by passing a barcode generator site URL with parameters. However, this requires online access.

Yet another method uses data URL to achieve it. The web page that could create the Barcode using Javascript is stored in a hidden layout object. The data URL then reads the content and replaces a specific string in the web page with the text to be displayed in barcode. In the same way the parameters for the Barcode can also be changed. One good example is FMEasyBarcode. Unfortunately the author no longer maintains the code.

The following is an effort to try to do the same thing as David Shim.

First you need to have a webview object that contains the following script

Let([$html=Substitute(GetLayoutObjectAttribute("QRCode";"content"); ["[[VALUE]]"; mytable::userid)];["[[barcodetype]]";"qrcode"]];"data:text/html," & $html)

In layman's term, get the content of the object "QRCode" then substitute "[[VALUE]]" with mytable::userid (field value). Also substitute "[[barcodetype]]" with the barcode type called "qrcode".

In the hidden object, a complete single page HTML with Javascript is pasted into the content.  This HTML page must not have links that refers to local file (e.g script source). It can of course refer to external URL script source. What David Shim did is to copy the uglyfy js (all extra spaces,tabs,CRLF are removed) and paste into a javascript element. This is done to make sure the HTML have the smallest size.

Since the calculation is using Javascript, the replacement script in the webview will effectively change the data to be processed and run upon loading to produce the barcode. Obviously, that web page should work in normal browser.

I have tried the same method with a code39 script JsBarCode from Johan Lindell. It works fine.

One thing to note is that the size of the display is not by the webview.  I tried to add CSS to automatically resize the element but it just don't work. Some barcode does have a parameter to change the size. You will still gets some white space unless you specifically set the CSS to absolute position and define the top and left coordinate of the element.

One irritating thing is that the hidden object size tends to automatically expand if you try to edit it. But it is a small price to pay unless you want to store the HTML is a global field.

There is no script to show except the one line script mentioned above. The web page content is from other authors. I have included their project page for you to see how it works. David Shim's excellent qrcode project is at QRCodeJS.




Thursday, February 16, 2017

Filemaker Xpath

While doing communications with Apple GSX, I have to constantly receive XML from GSX. Filemaker does not have functions that handle XML in variables.  Normally, I would use BaseElements plugin function BE_XPath to do the node extraction. But due to its functionality, BE_Xpah treats nodes differently. If there is only one node with a specific node name, it can easily be extracted by using "//mynode". If there is multiple nodes with the same node name then it is a issue. You must use a array style like "//mynode[x]" to get the correct value.

If the XML format is always constant, there is no problem. However, if the XML returned can be one or more nodes of the same name, it is an issue. It ended up that I have to check whether there is multiple node of the same name by trying to parse "//mynode[x]" first to see it has a value then go to the appropriate routine to extract the single or multiple nodes. It is a hassle. Moreover, the script needs the BaseElements plugin. Filemaker Go does not have the capability to use Plugins.

I tried to lookup for scripts that is available in the internet. There are some but its codes are a bit too complicated to comprehend. It may be due to efficiency, they try to use as little script steps as possible to achieve results. Many resort to use "Evaluate" function. I tried as far as possible to avoid using "Evaluate" thus do not want to use it.

Fortunately, two of the scripts uses the same functionality provided by Andy Knasinsky (http://www.briandunning.com/cf/1). It triggers my mind that I could use the same method to extract nodes. I don't have Filemaker Pro Advanced so cannot copy the custom function. Therefore, I start doing my own script.

To start off, I use the same convention of defining the Xpath. The convention is like this "//mynode[x]". It is an array like node name that starts with "//". I will then have to detect whether there is a "[x]" defined. Filemaker has a function called "position". The syntax is as belows

Position[text;searchstring;start; occurrence]

Now if there is no "[x]" defined then I just set "occurrence" to one else get the value of "x" as "occurrence" value. Since "Position" always requires a "start" and "occurrence", The x value is useful to define which instance the node name occurs. Both the start node and end node position can be extracted this way.

With the use of "Middle" function, I am then able to define the exact position of the node value and extract it.

The following is the code. I can't copy and paste as FMP does not have the capability of copy and past the script to text. I do screen capture instead. The script name is "GetNode".

Well, life is not as simple. There will be instances where the same node name appears inside other node. Therefore, it is necessary to define the specific path to the node hence the term "Xpath".

It turned out that this is made easy by the above script. Since the above script is just text manipulation, it can actually retrieve the other child nodes. The syntax of Xpath is like "//node1/node2". It can be more than two and with array like syntax.

I just break up the nodes then loop it in sequence. For example, first I get "node1" then retrieve the node value (including child nodes). Then, I get "node2" based on node1 result. If there is a third child node, then I get the node2 result and look for the node value. In this way, I can get any value from the XML. Below is the script. I name it as "GetPath"



You can just do "Execute Script" and call the "GetPath" script. The script parameter will be
your xml and the xpath separated by a Filemaker carriage return symbol. You will then get the ScriptResult to get the node value.

There is a script in the above script named "Remove Subnode". This script is a result of having an XML that have subnodes that have nodenames same as the root node. If the subnode is above the root node of the same name, the subnode value gets extracted instead. It is, therefore, important to remove the subnodes as the path does not point to the subnode.


After doing the script, I felt that it is still very troublesome if I were to pull a list of values from the XML. A list script is written just to do this. The parameter supplied is the XML, the specific path, and the list of nodes that I want to get. An interesting point is that there is a need to get one or more subnodes in the list. Therefore, I set the node name with an extra "[]" to indicate that it is a subnode. It will then pull the subnode XML as value.
Ignore the Base64 comment as I though the list might be interrupted by the delimiter. It turned up that Base64 adds more trouble. Then I realize that I must compact the XML by removing all CR, LF, TAB, and extra spaces. This means that the CR delimiter is not found in the data itself thus abandoned the idea.



I make no effort to combine the script steps so that it will be easy to read by any novice. Any how, the script should not be used for XML that is tens of thousands of lines long. Try to "compress" the scripts if you need to extract large number of data from XML. It is not within my scope to show you how to use one script line to do complex calculations.

By the way, the XML has to be well formed and error free.


Tuesday, April 19, 2016

Filemaker Chart vs Web Javascript chart

Recently have issues with Filemaker chart thus have to switch to web based charts. There are quite a number of similarities and oddities. The following is the comparison.

FilemakerWeb
Uses tablesUses variables
Forms data from table with data pre fetched from SQL resultForms data direct from SQL result
Charts PreformedCharts can be defined at run time
Number of legends fixedNumber of legends determined from runtime
Max number of legend fixedNo Max number of legend 
Max number of data rows not fixedMax number of rows not fixed
Titles, X,Y labels prefixedDetermined during runtime although can be prefixed
Chart type prefixedChart type can be changed runtime
Interactive chartInteractive chart
Data values can be displayedData values can be displayed
Number of chart types limitedNumber of chart types depends on the module writer. Some can have 90 plus chart types
No time lapse display of chart dataHave time lapse display of data
Charts module fixedCan practically create the chart from scratch.
Not resizable during run timeCan auto resize or changed during runtime
Color prefixedColor changeable during runtime if needed
Options setting prefixedOptions totally changeable runtime.
Events trigger not availableEvents trigger available depends on module writer.
Chart data can be added/removed runtimeChart data can be added/removed runtime
Must use Filemaker to view chartCharts can be rendered in any web programming language that can work with javascript and have a graphics display capability.
Expensive.Can be totally free.
Charts formed by fix app settingPlain javascript text setting.
Easy to do even with novicesA big learning curve especially when you create chart from scratch
StandardizedWay too flexible until it is scary

You can see that Filemaker charts are for easy creation but is fixed in most features. It is not flexible at all compared to Web Javascript charts. Both caters to specific group of people and interest.


Monday, April 04, 2016

How to use a script to create data for a Filemaker Chart

In my previous post on Filemaker Charts, I touched on how to create a table and use it to create charts. In daily operation, the data are actually daily transactions. it is never meant for charts. How then could we transform the data into charts. It is easily done in Excel where user can create summaries to be used in charts. There is a summary in Filemaker too but it cannot be used for charts.

Here I propose using scripts to populate a summary table from a details table. The example code will use Filemaker Charts as an example.

The first thing to fill is "Months" field. Create a calculated text field in the main table and call it "YearMonth". It is necessary to add year as the data may span a number of years. Use the formula below as calculation assuming the field that shows the date of record creation is called CreateDate.

Year(CreateDate) & Case(Month(CreateDate) < 10;"0" & Month(CreateDate); Month(CreateDate)

The above will generate a text like "201604" for current month. It is necessary to insert "0" for single digit month so that the sequence of the data created will be correctly done.

Create the summary table and chart according to examples shown in Filemaker Charts. Create a script called "Summary".

Usually a summary table will pull the data for the entire last year. So the script will start with getting the year.

Set Variable($year;Year(Get(CurrentDate)-1

With the year, append the month value in a loop like below

Set Variable($mth;1)
Loop
   If($mth < 10)
      Set Variable($ym; $year & "0" & $mth)
   else
      Set Variable($ym; $year & $mth)
   End If
New Record/Request
...
Set Variable($mth;$mth+1)
Exit Loop If($mth>12)
End Loop

The above script only set the loop to get the correct YearMonth value in 12 loops. We need to fill the "Month" field in the summary table. The following script assume you are already in the summary table layout using summary table as database. The subsequent scripts are to be inserted in the line before "Set Variable($mth;$mth+1)" represented by "..."

Set Field(Summary::Month;$ym)

The next task is to fill SiteA, SiteB, and SiteC with the correct summary data from the sales data field "TotalSales". Well, you guess it right, we will use ExecuteSQL function.

Set Field(Summary::SiteA; ExecuteSQL("SELECT Sum(TotalSales) FROM MainTable WHERE SiteLocation = 'SiteA' AND YearMonth=?";"";"";$ym)

Repeat it for SiteB and SiteC.

Run the script and you will get 12 records with the completed sales figure for last year. Your chart will then be able to show the yearly sales figure for the three sites.

A reminder: You need to delete all records of previous data in summary table. Filemaker Charts does not have the facility to define which data to use in the table.  Its useless to set a filter as it only applies while the table is in view.

A suggestion is to copy the data into a history record of the yearly performance table. Extract the necessary data back into summary table to show the yearly figure for the year you want to see the chart.

One last thing, The SQL is a very simple one. You could extract the whole year data for all three sites in just one ExecuteSQL

Set Variable($data; ExecuteSQL("SELECT YearMonth,SiteLocation,Sum(TotalSales) FROM MainTable WHERE YearMonth LIKE '" & $year & "*' GROUP BY YearMonth, SiteLocation" ;"";",";)

Use a loop to extract the various rows using GetValue($data;$loop) then extract each item by replacing "," with "¶" then use GetValue to extract the items.

The complex SQL method only useful if you extract just the sales figure. In many situations you need to get more result than that. Usually they use different criteria like TotalSales > 1000000, Total Sales >100000000. One SQL will not be able to extract such criteria and it will be difficult to update the table if you use more than one SQL. Since the first SQL will fill all the 12 records already. You will then have to use filter to choose the right record then add the subsequent SQL result into the correct record.

It is impossible to use ExecuteSQL to update the records. ExecuteSQL does not allow Insert or Update (3rd party SQL plugin does have this capability).  It is easier to use simple SQL and then create a record to fill the appropriate fields with the correct SQL result with the resultant multiple SQL that loads the system runtime. The choice is up to you.

   


Tuesday, March 08, 2016

Filemaker Charts

Its the first time I tried to make charts in Filemaker. It is quite confusing at first but slowly I figured out how to make the best of the Charts (at least just the Column charts).

Firstly, you need a table to store the data. The fields should have one "heading" and one or more "content". Headings are used to display the X axis. Example of it is "Months". Contents are groups of columns in the Legends and the content are numbers that will be displayed as column heights.

Don't worry, it is pretty confusing at the onset. Lets start with the table. Assuming you want to show the sales figure of three outlets named Asite, Bsite and Csite. You want to show the sales figures by month from January to December. Your table fields should be.

Months, Asite, Bsite, Csite.

The data will then be

January, 200,300.400
February, 250, 350, 450 etc.

Create a blank Layout in Filemaker and click on Charts tool. Click on the layout and drag across the screen to get the right size of the chart.

A Popup will appear. Enter the Title like "Monthly Sales figure". Leave the "Type" as "Column".

Enter the X-axis Title as "Months"

Enter the Data as Table::Months. If you want to select the table then make sure you associate the table with the layout (default table) first before clicking Charts tool. You can still do it without the preset table preference. Just click on the "three dots" button to the left of the Data input field and choose the table and the field.

On the Y-axis, Enter "Month" on the title and click on "Add a Y-axis" button. It will expand to a selectable list on the left and a "Series Name and Data on the right. Choose the first item in the left selectable list. The right hand side Series name will change to "Series 1". Change it to "Asite" and data field select Asite field in your table.

Choose the second item in the selectable list and repeat the above step but choose Bsite instead.

Click on the small "+" button below the selectable list and a "series 3" will appear. Change it to Csite.

It will be good to show Legend. Check it. The legend will show as Asite, Bsite, Csite on top of the chart.

If you want the sales figure to be shown on top of the column then choose "Show data points on chart".

If you want to change the style of the chart, click on "Style" at the bottom corner. You can set various styles of the chart. Unfortunately, the color scheme is preset. You can't choose the color of the column individually yourself.

There is a "Data Source" below "Style". There is a option to show summarized group of records or individual record. The former is used when your table records the sales figure by the day. It has a date field that shows the daily figure. The table must still contain "Month" field which is the month of the date field. Sort the table by "Month" and you can get a summery of the sales figure by month.

If you are satisfied then just click "Done" and then "Exit layout". Anytime you want to change the setting, just click "edit Layout' then double click on the chart itself to edit the chart.

There is no picture available as this editing is done on another computer without Filemaker.








Sunday, February 21, 2016

Filemaker ExecuteSQL in a table view field

There is a need to verify the quantity of part number in the main table against the total quantity in another table. It is impossible to use summary type field as it requires break field but summary type can only provide running total.

In the table view on a layout the subtotal can be computed using the sub-summary feature but it cannot be use as a link to a related table.

The only other option is to use ExecuteSQL. The fist thought is to use the function directly as a calculation. The syntax of the function is as follows

ExecuteSQL("SELECT SUM(Quantity) FROM FirstTable Where PartNumber =?";"";"";PartNo)

The result is "?". According to documentation, it is the result returned when the ExecuteSQL encounters SQL error. However, there is absolutely no error in the SQL statement. It turned out that another function "Let" must be used in conjunction with ExecuteSQL. The modified calculation is as below

Let($query=ExecuteSQL("SELECT SUM(Quantity) FROM FirstTable Where PartNumber =?";"";"";PartNo);$query)

Unfortunately, it failed again. In the field options, there is a setting to not store the result in the table. It must be set so that the calculation must be done every time the field is shown. Only then the result of the calculation can be shown.

The next step is to link this result with another table together with the part number. It again failed. The reason is due to the previous paragraph setting.

There is no way the calculated field can be used as a link to a related table. The only way is to run a script to add the result into a text field then use that text field to link to the related table. It cannot be used for dynamic update but it worked.

One worry is the fact that ExecuteSQL runs a query for every field that is shown. This means that there will be considerable delay since it cannot be stored. If there are millions of records to be updated this  way, it will take forever.

The conclusion is that it can be done but not real time and it causes considerable loading to the process. How I wish FM add a summary function to show total with breaking field instead of running total.



Sunday, September 13, 2015

Passing parameters to Filemaker Script

Filemaker only accept one parameter when you "perform script". There are many suggestions to pass more than one parameters. Some use custom functions. One of the most noted function is #Parameters. It stores name and value set into a dictionary. Others suggest using "List".

For those who do not use Filemaker advanced, there is no way to create custom functions. For list, if you just pass it a list of values separated by "carriage return". The only problem is that if you passed a blank value, the next value will be used instead. This caused considerable inconveniences.

Some others passes a XML or JSON as parameter and it requires a function to do the extraction. I guess this is the best way to do it. The following is an example of getting the variable value by creating a XML, stored as global value and call the script with node name as parameter.

# Construct a xml for all the variable and values. Store as global variable $$xml
# The passed xml must not contain carriage return just join all the nodes end to end
# The xml constructed must also be escaped to make it valid.
Set Variable [ $pkey ; Value: Get(ScriptParameter) ] 
Set Variable [ $$xml ; Value: Substitute ( $$xml ; "><" ; ">"  & ¦ & "<" ) ] 
Set Variable [ $ccount ; Value: 1 ] 
Set Variable [ $pcount ; Value: PatternCount ( $$xml ; ¦ ) ] 
Set Variable [ $pvalue ; Value: "" ] 
Loop
 Exit Loop If [ $ccount > $pcount ] 
 Set Variable [ $xsearch ; Value: GetValue($$xml;$ccount) ] 
 Show Custom Dialog [ "test" ; $xsearch ] 
 If [ PatternCount ( $xsearch ; "<" & $pkey & ">" ) =1 ] 
  Set Variable [ $pvalue ; Value: Let ( [ $p = Substitute ( $xsearch ; "<" & $pkey & ">" ; "" ) ; $p = Substitute ( $p ; "</" & $pkey &">" ; "" )] ; $p ) ] 
  Set Variable [ $ccount ; Value: $pcount ] 
 End If
 Set Variable [ $ccount ; Value: $ccount+1 ] 
End Loop
Exit Script [ Result: $pvalue ] 

Note that the | symbol is actually the Filemaker "carriage return" sign.

Recent development see another way of passing parameters. This is done by using JSON. Filemaker has added functionalities to read/write the elements of JSON. It makes passing parameters much easier with much less hassles.





Friday, September 11, 2015

Filemaker example of Xpath

Previously I talked about using Filemaker to extracting xml node text without using plugins. I have created a working script that does just that. You just need to call the first script that does a "Tidy". The script will remove unnecessary carriage return, tabs and extra spaces between nodes. It also add carriage return after every node. You can get the node text value by adding the xpath path with a carriage return in front of the xml as one parameter to the second script.

The reason for putting them in two script is that you only need to "Tidy" the xml once but you need execute  the second script a number of times to extract multiple node values.

The first script is called "xtidy". The script is as follows

Set Variable [ $xml ; Value: Get(ScriptParameter) ] 
Set Variable [ $xlen ; Value: Length ( $xml ) ] 
Set Variable [ $ccount ; Value: 1 ] 
Set Variable [ $xout ; Value: "" ] 
Set Variable [ $xfound ; Value: 0 ] 
Set Variable [ $xtext ; Value: "" ] 
Loop
 Exit Loop If [ $ccount > $xlen ] 
 Set Variable [ $xsearch ; Value: Middle ( $xml ; $ccount ; 1) ] 
 If [ $xsearch = ">" ] 
  Set Variable [ $xfound ; Value: 1 ] 
  Set Variable [ $xout ; Value: $xout & $xsearch ] 
 Else If [ $xsearch = "<" ] 
  Set Variable [ $xtext ; Value: Substitute ( $xtext ; Char ( 13 ) ; "" ) ] 
  Set Variable [ $xtext ; Value: Substitute ( $xtext ; Char ( 11 ) ; "" ) ] 
  Set Variable [ $xtext ; Value: Trim ( $xtext ) ] 
  Set Variable [ $xout ; Value: $xout & $xtext & "<" ] 
  Set Variable [ $xtext ; Value: "" ] 
  Set Variable [ $xfound ; Value: 0 ] 
 Else If [ $xfound = 1 ] 
  Set Variable [ $xtext ; Value: $xtext & $xsearch ] 
 Else
  Set Variable [ $xout ; Value: $xout & $xsearch ] 
 End If
 Set Variable [ $ccount ; Value: $ccount+1 ] 
End Loop
Set Variable [ $xout ; Value: Substitute ( $xout ; "><" ; ">" & ¦ & "<" ) ] 
Exit Script [ Result: $xout ] 
The second script is call "Xpath". The script is as follows



# Just add the path with a carriage return to the xml like $xpath & $xml as parameter
Set Variable [ $xml ; Value: Get(ScriptParameter) ] 
Set Variable [ $xmlcount ; Value: PatternCount ( $xml ; ¦ ) ] 
Set Variable [ $xpath ; Value: GetValue($xml;1) ] 
Set Variable [ $xpath ; Value: Substitute ( $xpath ; "//" ; "" ) ] 
Set Variable [ $xcount ; Value: PatternCount ( $xpath ; "/" )+1 ] 
Set Variable [ $xpath ; Value: Substitute ( $xpath ; "/" ; ¦ ) ] 
Set Variable [ $pathLevelCount ; Value: 0 ] 
Set Variable [ $pathRepeatCount ; Value: 0 ] 
Set Variable [ $lastXsearch ; Value: "" ] 
Set Variable [ $lastXsearchCount ; Value: 0 ] 
Set Variable [ $xmlresult ; Value: "" ] 
Set Variable [ $xcurPath ; Value: "/" ] 
Set Variable [ $xlevel ; Value: 0 ] 
Set Variable [ $xlevelCurrent ; Value: 0 ] 
Set Variable [ $ccount ; Value: 1 ] 
Set Variable [ $xml ; Value: Middle ( $xml ; Position ( Upper ($xml) ; "" ; 1 ; 1 )+15 ; Position ( Upper($xml) ; "" ; 1 ; 1 )-Position ( Upper($xml) ; "" ; 1 ; 1 )-15 ) ] 
Set Variable [ $xmlcount ; Value: PatternCount ( $xml ; ¦ ) ] 
# Script to find lowest level of the first search path
Loop
 Exit Loop If [ $ccount > $xmlcount ] 
 Set Variable [ $xsearch ; Value: GetValue($xml;$ccount) ] 
 If [ Left ( $xsearch ; 2 ) = "</" ] 
  Set Variable [ $xlevelCurrent ; Value: $xlevelCurrent-1 ] 
 Else If [ PatternCount ( $xsearch ; "<" ) = 1 ] 
  Set Variable [ $xlevelCurrent ; Value: $xlevelCurrent+1 ] 
 End If
 Set Variable [ $xnode ; Value: Middle ( $xsearch ; 2 ; Position ( $xsearch ; ">" ; 1 ; 1 )-2) ] 
 If [ PatternCount ($xnode;"/")  ­ 0 ] 
  Set Variable [ $xnode ; Value: ¦ & ¦ & ¦ & ¦ ] 
 End If
 If [ Position ( GetValue($xpath;1) ; $xnode ; 1 ; 1 )  > 0 ] 
  If [ $xlevel = 0 ] 
   Set Variable [ $xlevel ; Value: $xlevelCurrent ] 
  Else If [ $xlevel > $xlevelCurrent ] 
   Set Variable [ $xlevel ; Value: $xlevelCurrent ] 
  End If
 End If
 Set Variable [ $ccount ; Value: $ccount + 1 ] 
End Loop
Set Variable [ $xlevelCurrent ; Value: 0 ] 
Set Variable [ $ccount ; Value: 1 ] 
Loop
 Exit Loop If [ $ccount > $xmlcount ] 
 Set Variable [ $xsearch ; Value: GetValue($xml;$ccount) ] 
 If [ Left ( $xsearch ; 2 ) = "</" ] 
  Set Variable [ $xlevelCurrent ; Value: $xlevelCurrent-1 ] 
 Else If [ PatternCount ( $xsearch ; "<" ) = 1 ] 
  Set Variable [ $xlevelCurrent ; Value: $xlevelCurrent+1 ] 
 End If
 # Look for path start
 Set Variable [ $xpathtxt ; Value: GetValue($xpath;$pathLevelCount+1) ] 
 If [ Position ( $xpathtxt ; "[" ; 1 ; 1 )  ­ 0 ] 
  Set Variable [ $xpathary ; Value: Substitute ( $xpathtxt ; "[" ; ¦ ) ] 
  Set Variable [ $xtxt ; Value: GetValue($xpathary;1) ] 
  Set Variable [ $ycount ; Value: GetAsNumber ( GetValue ( $xpathary ; 2 ) ) ] 
 Else
  Set Variable [ $xtxt ; Value: $xpathtxt ] 
  Set Variable [ $ycount ; Value: 1 ] 
 End If
 # Look for path end
 Exit Loop If [ Position ( $xsearch ; $lastXsearch ; 1 ; 1 ) = 1 ] 
 If [ Position ( $xsearch ; "<" & $xtxt & ">" ; 1 ; 1 ) = 1 and $xlevelCurrent = $xlevel+$pathLevelCoiunt ] 
  Set Variable [ $pathRepeatCount ; Value: $pathRepeatCount+1 ] 
  If [ $pathRepeatCount=$ycount and $pathLevelCount  < $xcount ] 
   Set Variable [ $pathLevelCount ; Value: $pathLevelCount+1 ] 
   Set Variable [ $lastXsearch ; Value: "</" & $xsearch & ">" ] 
  End If
  If [ $pathLevelCount = $xcount and $pathRepeatCount = $ycount ] 
   Set Variable [ $xmlresult ; Value: Middle ( $xsearch ; Position ( $xsearch ; ">" ; 1 ; 1 )+1 ; Position ( $xsearch ; "<" ; 1 ; 2 )-Position ( $xsearch ; ">" ; 1 ; 1 )-1 ) ] 
   Set Variable [ $ccount ; Value: 50 ] 
  End If
  If [ $pathRepeatCount=$ycount and $pathLevelCount < $xcount ] 
   Set Variable [ $pathRepeatCount ; Value: 0 ] 
   Set Variable [ $lastXsearch ; Value: "</" & $xtxt & ">" ] 
  End If
 End If
 Set Variable [ $ccount ; Value: $ccount + 1 ] 
End Loop
Exit Script [ Result: $xmlresult ]