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, August 31, 2017

新加坡视障人士协会常年会议回顾 - 中文版


最近我参加了视障人士协会(SAVH)的常年大会。 这只是一个典型的常年大会,一个会长正在朝着一个方向前进,但是成员却有其他的想法。

故事始于去年的常年大会。 去年,会长试提出7修正案。

1。删除以下内容:协会成员由绝大多数视障人员组成。

2。将无视障会员的永久会员费从150元提高至500元,普通会员费从15元提高至100元。

3。增加以下“少于一年会籍的会员无权在任何年度或特别大会上投票。”

4A。 删除“视障者7人”,“无视障者6人”,“给执行委员会简单多数”,“3人视障者”。 添加“最多5视障者”

4B。如果上述4a通过,则修改“co-opt 6名成员”,成“co-opt 10名成员”。

5。加上“由指定的无视障会员共同签名”。

6。添加以下内容:在本宪法中,具有小于6/60的矫正视力最好的人应为被视为“视障者”。

去年周年大会上交流热烈。 一方面,会长试图删除一些被认为阻碍扩大协会的宪法声明。 另一方面,成员认为这些修正案威胁到他们的权利。 最后,这些修正案都不能通过三分之二的投票要求。

今年会长再次提出5项修正案。 有3项特别令人感兴趣的修正案。

1.去年提案的第一项再次提交。

2. 项目3再次完全相同。

3。去年提案的第4a和4b项改为“不低于30%”,代替“视障者最多5名”。 这意味着视障者3.9人。

这次会议不是那么热烈。 上述3项提案均未超过三分之二多数。

从两年的建议来看,不得不认为会长正在努力做到以下几点。

1.删除宪法第5条中的绝大多数视障人士。

2.通过让新成员加入,以阻止他们的投票通过或拒绝,以防止成员中的团体试图推动他们的意见。

3.将视力障碍人数减至“视障者至少3名”-第6.8条规定的最低限度。

会长甚至在大会通知中附上了日期为28/7/2017的个人信息。 他强调,目的是“吸引新鲜,有活力和有才能的个人,无论是否视障者”如果无视障者的数量有配额地位,我们将无法这样做“。 如果他的意图对无视障者和视障者都是中性的,那么他为什么主张对执行委员会会员进行“30%或更多”的配额,并且抱怨宪法第6条和宪法中同一宪法条款中的“简单多数” “宪法第4条 会长说的话显然有冲突。 或者也许他的陈述仅适用于“会员”部分,不包括“执行委员会会员”部分。

我在会上问提出“30%以上”的理由。 我没有得到直接的答复。 我想原因是给予一视障者至少有一个最小的保障,否则这使得修正案听起来非常恐怖。 而且要符合宪法第6.8条的规定。 我想知道根据会长的“不管是否视障”的理论,他为什么不去除”3个视障“条款。 但是,删除该条款后,会使建议的修订更为威胁性。

会长的信息的总体语气是他想要更多地增加无视障者到协会。 那已经有偏见了。 社会已经偏向无视障者。 新加坡的残疾人士为获得“残疾人权利公约”而努力奋斗,会长认为简单多数的条款是一个障碍,同时非常强调让无视障者成为视障人士协会

让双方加入更多的会员加入协会,没有求不到才能的问题? 新加坡大约有3000名视力障碍人士。 我相信2999无视障者足以填补人才库。 如果视障者无法支付会员费用,为什么不免费或仅仅是收1元的象征? 提供每年15000元会消耗大量资源? 巧合的是,2999名无视障者会员缴交了45,000元的会费。 视障会员费用还不够?

即使是经济困难,我相信这些“有天赋”的视障人士没有问题成为成员,同样数量的无视障者也可以加入。 执行委员的数量最多为13个,co-opt最多为6个,您需要拥有大量的“才华”? 它甚至没有20个成员的分量。

在常年大会之前,总裁还举行了一次对话会议,强调与常年大会通知相同的信息。 也许在会长看来,他看到宪法规定的限制给视障的简单多数。 他认为视障者不符合无视障者的标准,所以他试图删除限制无视障者人数的条款,使他能更有目的地在协会任职。

当前,SAVH是视障人士协会。 人们会认为它应该是绝大多数视力障碍的成员。 然而,该协会实际上是在1951年以“新加坡为盲人协会”的名义开始的。以它的名义,它被认为是由无视障成员组成的,目的是帮助视障者。 会员组合会包括视障和无视障者是不足为奇的,在以前和现在都一样。 随着宪法的改变,发生了以下重大变化(一些取自会长的信)。

1. 1983年 - “宪法被修改为限制无视障成员人数占总成员人数的多数”。 “宪法”第4.1条改为“成员由会员组成的大多数视障人员”。

2. 1987年 - 协会名称改为“新加坡视障人士的协会”。 值得注意的是,总统信中没有提到这一点。

3. 1994年 - “变更配额严重限制了我们吸引和招聘合格人才和人才在执行委员服务”。 “宪法”第6.1条规定视障者在执行委员成员数量并宣告“将会给执行委员会一个简单的多数”。

会长在提及上述第1项和第3项时提到“因为没有记录的原因”。 很奇怪,两个日期是在没有发现背后的原因的情况下被发现的。 所有会议记录的所有记录都有保存在档案中,会长究竟如何确定哪一年作出修正。 尽管这些记录只是提出了一些修正案和三分之二成员的批准,但它已经说明了很多。

从修正案本身来看,这一变化明确的说明要在非常有偏见的世界中为障碍者会员和执行委员在协会里提供了多数的成员条款。 这些修正案没有寻求绝大多数条款。 只是给视障者带来轻微的优势。 毕竟,一名成员在周年大会上提到的,这是视障协会。 会长试图恢复项目1和3。

让我们来看看协会的一些目标

3.1 作为视障人士的协会。

3.3促进和鼓励视障人士更多地参与管理其事务和活动以及协会的管理。

3.4努力消除建筑,态度,社会,文化,教育,就业以及阻碍社区视力残疾人全面融入和平等对待的其他障碍。

以前的修正是符合上述目标的,而会长的建议似乎违背了上述目标。 会长正在试图从无视障中获得“才能”,而不是,在会议期间一名成员提出的,“为残障人士提供机会发展”。

从现有人才库中挖掘出来,而不是让视障人士参与,确实更有成效,但这违背了宪法的目标,从而打败了协会的整体宗旨。 为什么要在执行委员让绝对多数的无视障者参与,同时在目标是“促进和鼓励视力障碍者更多地参与协会管理”的条例下,将视障者减少到最低限度。

会长通过取消会员限制以获得更多有才华的无视障成员参加协会的想法也脱离了协会目标的范围。 协会根据其目标3.1为“视障者的协会”。 通过消除视障人士的绝大多数,他正试图迎接更多的无视障成员参与协会事务。 它如何为3.1和3.3的目标迈进? 如果他们想要在执行委员做出贡献,他们才需要成为会员。 正如另一位成员所说,“你不需要成为协会的成员”。

会长担心无视障者的人才将“转向其他志愿福利组织”。 一位成员指出,自1983年以来,现行宪法已经到位,无志愿者人数不足。 事实上,有些人转移了,但还有其他人自愿参加。

一位会员提到在会长任期内,“没有一个视障人被提名为增选(co-opt)“。 在这里,您可以清楚地看到会长在执行委员增选的选择。 总统明确表示,他想消除无视障人增选局限。

视障者可以自己操作而不用无视障者吗? 在过去的岁月里,这可能是真实的,但在现代,一个只视障者组成的组织并不罕见。 香港盲人联盟就是这样一个例子。

SAVH为寻求平衡而包括无视障者为成员。 无视障者不应该试图为自己的优势而努力。 他们应该保持协会的目标,并努力让更多的视障者参与。

“删除多数成员”和“不少于30%的执行成员”修正案的投票结果显示很多成员的意见。 20名成员同意“删除多数成员”修正案。 这还不到会员名单数目的五分之一。 31名成员投票同意“不少于30%的执行成员”。 这大约是三分之一的成员人数。 前者显示,成员绝大多数反对删除视障人士多数成员条款。 后者至少有30%是赞成的,因为该条款仍然受到成员选举的限制。 我有关于后者一个事实的故事,但我不会说明。

在选举新视障的执行委员时,值得注意的是,一名研究院博士级会员输给了一名按摩按摩师。 看来成员们不在乎执行委员的成员选择是否是人才。这与会长的方向有所不同。 看看会长或执行委员会成员是否会选择这样的“人才”是很有趣的。

一名议员试图提出议案,以永远防止失败的动议再次被提升。 在这样的想法是有点极端。 执行委员会议员否决这项议案,因为根据宪法没有提前提交。 值得注意的是,法律顾问说“没有任何妨碍让成员不能提出修正案”。 连续两年,会长已经提出了两个相同的议案,我想他会一次又一次的表决直到通过。 在法律上,他可以通过任何无道德考虑来做到这一点。 看看他是否会在明年再次这样做是有趣的,因此继续保持同样的故事,把协会的目标和成员的意见放在一边。

最终,像常年大会上的一个成员提到,所有成员都应该致力于视障人士的利益,而不是争取谁应该拥有多数。

点击英文版部落格


Sunday, August 27, 2017

Reflection on AGM of SAVH

Recently I attended an AGM of the Association of the Visually Handicapped (SAVH). It is just a typical AGM where a president of a society is trying to drive in a direction but the members have other thoughts.

The story started with the AGM of last year. Last year the president tried to table 7 amendments.

1. To remove the following "membership of the Association shall consist of a majority of Visually Handicapped members."

2. To raise the membership fee of life members from $150 to $500 and Sighted members from $15 to $100.

3. To add the following "any member with less than one year's membership shall not be entitled to vote at any Annual or Extraordinary general meeting."

4a. To remove "7 visually handicapped and 6 non visually handicapped", "as will give them a simple majority on the Executive committee" and "of whom 3 has to be visually handicapped." To add "up to 5 visually handicapped"

4b. If 4a above is passed then amend "co-opt 6 members" to "co-opt 10 members".

5. To add "to be signed jointly by designated sighted member".

 6. To add the following "In this Constitution, a person having best corrected visual acuity of less than 6/60 in the better eye shall be a person who is defined as being “visually handicapped.”

There were heated exchanges during last year's AGM. On the one end, the president tried to remove some constitutional statements that is deemed to be obstructive to the expansion of the association. On the other end, the members think that the amendments threatens their very rights. In the end, the amendments all could not pass the two third majority vote requirements.

This year the president again tabled 5 amendments. There were 3 amendments that is of particular interest.

1. Item 1 of last year's proposal is tabled again.

2. Item 3 is tabled again exactly the same.

3.. Item 4a and 4b of last year's proposal is modified to "no less than 30%" in place of "up to 5 visually handicapped". This translates to 3.9 visually handicapped.

This time the meeting is not that heated. All the 3 proposals above did not pass the two thirds majority mark.

From the two years' proposal, one cannot but to think that the president is trying to do the following.

1. To remove the simple majority of Visually Handicapped in clause 5 of the constitution.

2. To prevent groups in the members trying to push their views by getting new members to join just to get their votes passed or rejected based on their liking.

3. To reduce the visually handicapped presence to the minimum required by the constitution clause 6.8 of "at least 3 visually handicapped."

The president even attached a personal message dated 28/7/2017 in the notice of AGM. In which he emphasized that the purpose is to "attract fresh, motivated and talented individuals, regardless of whether they are sighted or not...We will not be able to do so if there is a quota place on the number of sighted members". If his intention is neutral to both sighted and visually handicapped why then he advocate a quota of "30% or more" on Exco membership and complains about the "simple majority" in the same constitution clause in article 6 of the constitution and on "membership" of article 4 of the constitution? There is obviously a conflict in what was said by the president. Or maybe his statement is only applicable to the "membership" section and need not apply to the "Exco membership" section.

I have queried on the reason for proposing "30% or more" in the meeting. I did not get a direct answer. I guess the reason is to give some assurance that visually handicapped has at least a minimal representation otherwise it makes the amendment sound very intimidating. It is also to satisfy clause 6.8 of the constitution. I was wondering why that 3 visually handicapped clause is not removed also according to the "regardless whether they are sighted or not" statement of the president. But by removing that clause, it will make the proposed amendments looks even more threatening.

The overall tone of his message is that he wanted to get more sighted into the association. That is already biased. The society is already biased against visually handicapped. Disabled peoples of Singapore are sill struggling to get "Convention of the Rights of Persons with Disabilities" to be fully rectified and he thinks that a simple majority clause is a hindrance while putting so much emphasis to getting sighted members into an association meant for visually handicapped.

By getting more members from both sides to join the association, there is no issue of not being able to get talented individuals right? There are roughly 3000 visually handicapped in Singapore. I am sure 2999 sighted member is enough to fill the talent pool. If visually handicapped are not able to pay for membership fees, why not make it free or just a token of $1? Does it drains a lot of resources by providing a yearly $15000 for it? Coincidentally 2999 sighted members pay close to a whooping $45000 membership fees. It is not enough to cover for visually handicapped membership fees?

Even if it is a financial strain, I am sure those "talented" visually handicapped can be a member without problem and equal number of sighted "talents" can be urged to join too. Do you need to have a large pool of 'talents' when the number of exco is 13 at max and co-ops are 6 at max. Its not even 20 members.

Prior to the AGM, the president also hold a dialog session that stress the same message as the letter attached to the Notice of AGM. It is perhaps in the view of the president that he see the limitations set by the constitution to give visually handicapped the simple majority. It is in his view that visually handicapped cannot perform to the standard of sighted members thus he tried to remove the clause that limits the number of sighted members so that he could get in more sighted to serve in the association.

Now, SAVH is an association of the visually handicapped. One would think that it should comprise of overwhelmingly visually handicapped members. However, the association was actually started with the name "Singapore Association FOR the Blind" in 1951. By its name, it is assumed that it was formed by sighted members to help visually handicapped. It is not surprising that membership consists of both visually handicapped and sighted then and now. The following significant changes took place with the change of constitution (some taken from the president's letter).

1. 1983 - "the constitution was revised to restrict the number of sighted members to be majority of the overall membership".  Constitution 4.1 was changed to include the clause "Membership of the Association shall consist of a majority of visually handicapped members".

2. 1987 - The name of the association changed to "Singapore Association of the Visually Handicapped". It is noted that this was not mentioned in the president's letter.

3. 1994 - "change severely limits our ability to attract and recruit qualified professionals and talents to serve in the Exco due to the quota". Constitution 6.1 specified the number of visually handicapped against the number of sighted in Exco members and states "as will give them a simple majority on the Executive Committee."

The president mentioned that "for reasons that is not recorded" when referring to items 1 and 3 above. It is quite strange that the two date was discovered without discovering the reason behind it. I though all minutes of AGM are kept in archive otherwise how would the president find out exactly which year the amendments were made. Even though the records are just about some one proposed the amendments and two third member approves, it speaks a lot already.

From the amendments itself it is not ambiguous that the change is meant to provide visually handicapped the simple majority in both membership and Exco in a world that is very biased against visually handicapped.  The amendments did not seek a vast majority clause. It is just to give a slight edge towards visually handicapped. It is, after all, an association meant for the visually handicapped as mentioned by one member in the AGM. The president attempts to revert items 1 and 3.

Lets look at some of  the Objectives of the Association

3.1 To serve as an Association for the visually handicapped.

3.3 To facilitate and encourage greater participation of the visually handicapped in the administration of their affairs and activities and in the management of the Association.

3.4 To work towards the abolition of architectural, attitudinal, social, cultural, educational, employment and any other barriers that prevent the total integration and equal treatment of the visually handicapped in the community.

Now the previous amendments are inline with the objectives stated above whereas the president's proposal seems to go contrary to the objectives above. He is trying to get "talents" from sighted instead of "providing means for the visually handicapped to excel" as mentioned by a member during the AGM.

It is indeed more productive to tap from existing talent pools of the sighted but without grooming the visually handicapped to participate, it is counter to the objective of the constitution thereby defeat the whole purpose of the Association. Why do you want to have a overwhelmingly majority of sighted in Exco while diminishing the visually handicapped to a bare minimum when your objective is to "facilitate and encourage greater participation of visually handicapped... in the management of the Association"?

The president's idea of getting more talented sighted members to join the Association by removing the membership limitation is also off the scope of the objective. The association is "for the visually handicapped" as per its objective 3.1. By removing the simple majority of visually handicapped, he is trying to usher in more sighted to participate in the affairs of the association. How does it serve the objectives of 3.1 and 3.3? Does sighted need to be members in order to contribute. They are only required to be members if they want to be in Exco. As mentioned by yet another member about sighted that "you don't need to be a member to contribute to the association."

The president is worried that sighted talents will "move on to other VWOs". One member noted that the existing constitution has been in place since 1983 and there has not been a lack of sighted volunteers. Indeed some sighted moved on but there are others that volunteered.

One member mentioned that "there is not a single visually handicapped being co-opted into exco" during the president's tenure. Here you can see clearly the choice of getting people to help out in the exco. And the president stated clearly that he wanted to remove the limitations of having visually handicapped as co-ops.

One question to ask is "can visually handicapped operate on its own without sighted." In older days, it might be true but in modern days, it is not uncommon to have an association that comprises of visually handicapped only. Hong Kong Blind Union is one such example.

SAVH seeks to have a balance by including sighted. Sighted should not be trying to work it towards their advantage. They should keep the objectives of the association in view and works towards having more visually handicapped participation instead.

The results of the voting on the "removal of majority members" and "not less than 30% exco members" amendment speaks a lot about the sentiments of the members. 20 members are for "removal of majority members" amendment. It is less than one fifth of the number of members present in the AGM. 31 members voted for the "not less than 30% exco members". It is about one third of the number of members present. The former shows members are overwhelmingly against removing the visually handicapped majority member clause. Whereas at least 30% are in favour because the clause is still subjected to members' vote to elect individuals. There were stories about this latter fact but I shall not dwell on it.

On the election of new visually handicapped exco members, it is interesting to note that a PhD research level member lost to a masseur on revote. It looks like members does not care whether exco member are 'talents' contrary to what the president is driving. It will be interesting to see whether the president or exco members will co-opt such 'talent'.

One member tried to move a motion to "prevent defeated motions from being raised ever again". It is a bit to the extreme in such idea. Exco member denied such motion since it is not tabled in advance according to constitution. It is interesting to note that the legal adviser said "there is nothing to hinder members from raising an amendment". The president already raised two same issues in consecutive years, I guess he will table it again and again till it is passed. Legally he can do so over any ethical and moral considerations. It will be interesting to see if he will do it again next year thereby continuing the same saga and putting the interest of the association and the sentiments of the members aside.

Ultimately, like one member mentioned in the AGM,  all members should work towards the good of the visually handicapped instead of bickering on who should have the majority.

The Chinese version of the BLOG.





Friday, July 21, 2017

Caldecott to King Albert Park

It was quite some time since I last blog about walking trips. This trip is from Caldecott MRT to King Albert park MRT.

The trip starts at Caldecott. Walking along Thomson Road to reach MacRitchie Reservoir. Bought a drink at Mushroom Cafe and immediately noticed this Cymbidium finlaysonianum orchid.



After walking along the lake side for a while, a Malay Viscount start to flash around so I am obliged to take a picture of it.


Further down, there is a maimed Multiline Sun Skink.


When I turned inland, a black bird perched on a barren tree trunk. But I cannot identify it before it flew away. Guess it is a Drongos.


A bit further down, an Archduke settled in front of me. Again I am obliged to take a photo of it.


Walked across the golf course with nothing much to see but meet quite a number of hikers coming from opposite end. Further down, I saw Purple Heron. This one is probably the same one I saw earlier but flew away before I can take a photo.


An Oriental Macpie Robin can't resist to show off in front of me again.



Before I move away from the reservoir, a bright colored Common Posy flashing its way around. I had to stop and wait for it to settle down. I seems to be attracting all the living things in the reserve, it settled right within my camera shot distance.


Further in land, there is a young plant that is full of  Branded Imperial. They are so engrossed that I can even walk very near them.



A few steps away, two species of damselfly came by. These are large species. I can't tell its specie name but got a hint from a fellow observer who took a picture nearby on the next day. It is probably a Vestalis amethystina. It should have very nice purple wings but this two are either dull brown or clear wings.



The next animal I come across is at the dirt track which is still labelled "Riffle Range Road". It is probably a Robber Fly. Interestingly there are a number of observation of this specie in Singapore but no one can positively identify to specie name. Just look at the muscular legs.


Nearby a Mycalesis appears. I have problem distinguishing between species so just stay at gender level.


Near the Army camp, saw a family of Treehugger. A male and a female resting side by side.



Just before reaching the real Riffle Range Road, a Yellow Vein Lancer makes its debut.


Along Riffle Range Road till King Albert Park, there is no other sighting. The trip ends at King Albert Park MRT.

Total distance covered is 11.48KM.

Almost all the photos are already shown so there is no album link.
Since this is a repeat trip, it is not uploaded to Wikiloc.

Thursday, June 29, 2017

北京故宫中轴线偏差


上图是一个YouTube视频。 论点是“元代人为中轴线偏差”。后来又看了郭晓强的文章(http://idea.cas.cn/viewdoc.action?docid=42614)觉得后者有理由。

郭晓强说夔中羽是用北京时间不是北京当地时间。这话没错。元朝时都还没格林威治标准时间那理有北京统一时间?

每四年都有多一天。因为是用阳历。元朝用阴历。阳历是辛亥革命后才用的。夔中羽用2004年的天文日历。会不会有时差呢?

夔中羽自己做了日晷。用2004年的天文日历为准。视频没说怎样调准。会不会是太不科学的量法吧?正确的做法应该用日晷确定何时是零地点正午后才来与故宫中轴线比较。元朝人大概也是如此量法。

当然与700年前的正午标准时间比现在正午时间那可要一切都不变。事实是如此吗?我们连元朝人哪月量的都不知道。地壳一直在移动。地球又不是正园形绕太阳转动。轴心也不是与太阳成正角。而且宇宙都一直在膨胀。用如此简陋的量法真的很难说服人。

当然以一个外行人来批评学者是不对的。不过外行人都觉得不大对,其他学者又没说那又是谁对呢?


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.


Tuesday, May 23, 2017

Filemaker Conditional List

There is often a need to limit the lists that is available for selection in an input field. Normally we would use a list that is based on a related table. User either selects a value from the first table or key in a value in the first field. That input value is linked to a related table. On a second field it uses drop down list and the list is taken from the related table. The list will only show the related data based on the relation.

Now there is this condition that requires multiple selection with conditional lists. Lets say a world wide company wants to know a particular group of sales person's performance from a particular state in a particular country in a particular region. Normally it would means that the user chooses a religion like Asia first. Then from the list of Asian countries, choose the country. Then the user narrow down further by choosing a state from the country before he can get a list of sales person's performance.

The first try is to create the fields to do the different selection. Therefore, there will be three input fields 
  1. Region
  2. Country
  3. State
Using the method described in the first paragraph above, It will be easy to choose Region from a unrelated table (sales table). The country field list will then be obtained by joining the Sales table to the input table linked by Region. The question will then be how to get States list based on the Region and Country chosen. Well, you guess it right. It is by joining the sales table with another instance of the sales table linking by Country. Normally this setup will be perfect because Country will be unique. There will not be two Countries that has the same name. However, in real life, there will be instances of duplication.

For example, we want to categorize hardware based on its configuration and make. So we have Brand, Processor, Screen Size, HDD, Ram. Now Brand is unique but Processor might not be. Neither did the rest of fields. Using the Sales method mentioned above, it will be impossible to choose Screen size based on Processor selection. It looks logical to self join the tables like Product::Brand->Product 2::Processor->Product 3::ScreenSize... In actual fact, by the time you choose ScreenSize list, The list is only based on Processor and not by Brand and Processor. It could be a Filemaker bug but that is what I found out.

In order to avoid such confusion, there is a need to revert back to the first paragraph method by just a simple join between two tables. First we have a list from Product::Brand without relation. The Processor input field will then have a relation using "Brand" and the list is taken based on the input table::Brand relation.

The ScreenSize input field then requires a separate product table instance that is joined to the input table by Brand and Processor. The HDD input field list will then have yet another instance of product table that is joined by Brand, Processor and ScreenSize. Finally the Ram input list will be based on a third instance of product table joined by Brand, Processor, ScreenSize, HDD.

By doing this method of joins, user can be assured that the result is exactly as what was defined in the various input fields.



Thursday, April 13, 2017

To Condemn City Harvest Leaders or Not

Recent reduced sentence for City Harvest caused quite a stir. NewNation published an article on 9th April 2017 titled "No Highly Religious S'porean Condemn 6 City Harvest Leaders for Going to Jail". A commentor said "The leaders of our conservative religious leaders do not know what to say...".

On a FB share on the above report, I commented that "Other religious (faith) leaders will refrain from condemning to preserve religious harmony. Christians do not condemn (John 8:11). Indeed there is no one who condemn."

The topic is actually whether to condemn the church leaders. John 8:11 is a typical verse on the topics of condemnation. However, the story is about Scribes and Pharisees trying to trap Jesus. Jesus said "He who is without sin among you, let him be the first to throw a stone at her". After all have left, he then confronts the real issue person to person - the sin committed by the woman.

John 8:11 is the very verse that is applicable to the NewNation article for Christians. Why must we condemn people in public? Gal 6:1 says "You who live by the Spirit should restore that person gently".  Matt 18:15-17 starts with "if your brother or sister sins, go and point out their fault, just between the two of you". 1 Tim 5:20 fits the description of Mat 18:17 but not before other previous measures (vs 15-16) is done. James t:19 says the same thing as Gal 6:1.

Forgiving is better than condemning. 2 Cor 2:7. "so you should rather turn to forgive and comfort him". Luke 17:3-4 puts forgiving even further.

Ultimately, if a person refuses to listen, Matt 18:17 should be used rather than condemnation. 2 Thess 3:14 says the same thing. However, that is not the end of the story. Eph 4:32 say to forgive. 2 Cor 2:6-7 also says "forgive".



Monday, April 03, 2017

Boycott the Earth Hour Stunt?

Read the article "If you really care about climate change, you should boycott the ridiculous Earth Hour stunt" on "Independent" by Adam McGibbon.

He thinks that "Earth Hour" is a stunt. It is a stunt. It is a drive that bring the awareness of climatic changes to the public. The aim is not to actually make an effort to reduce the climatic change effect during the hour. It is just as a "symbol" (according to Wikipedia). Without the mass drive, many don't even get to be aware of such efforts without WWF drive.

I have not heard of the author or his activities before. He might be doing a great job to bring awareness on his part. However, his effort is limited to what he can do. Whatever he is doing has not reach to my end of the world yet. At least WWF is driving towards general awareness worldwide. Both are doing something towards bringing awareness of climatic change to the people. Why try to discredit what WWF is doing?

In is article he said "just 90 of the world's largest companies are responsible for 2/3 of all carbon emissions." What can he do to stop these companies? Isn't that the companies produced based on demand? Without bringing awareness to the general public, small bands of environmentalists could not even touch them.  

Let's just work on climatic change reduction in each's own way without trying to discredit other's effort.







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.




Sunday, February 26, 2017

Drawing buildings/Roads on OpenStreetMap

I used to update OpenStreeMap. One of the most headache thing is that my drawing skill is horrible. Thus far, I can only draw simple roads and square buildings. While trying to add a complex building, it strikes my mind that if I could have a overlay on the map and can trace over it just like what we do on paper.

In actual fact, a lot of people also does that. What they wanted to do is actually to put a logo on the window yet allow user to interact on the windows below it. Helow is an application that I used. The name is Custom Desktop Logo 2.0.

It is a simple app you can set the position of the photo to a standard point on the screen or you can specifically place the photo at exactly the right spot. There is a facility to set transparency. The photo must be in a directory alone. If more than one, the app will try to rotate the display of all the photos at a specific interval. Obviously there is a feature to size the photo to a limited dimension (within a range depending on the size of your photo). You can hide the logo if you want. Since it is on top all windows, it can be used on any windows applications.

With the available feature, I can then place the photo (like route maps from NParks). Set the transparency to just distinguishable. Set OpenStreetMap to the appropriate size, adjust the size of the photo to exactly the same size of the map with existing elements (using a slider). The only other thing to do is to trace the lines.

It is impossible to dynamically resize the photo to get more details in sync with the map but it sure helps in the drawing of map features.

I drew the Chestnut Nature Park North using this feature on Google Maps. It wasn't exact copy but at least it resembles the actual drawing and the positioning of the routes are quite close to real.



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.




Monday, February 20, 2017

Life Long Learning

What can a O level second grade passer do? There is no chance to proceed with U or POLY. There is no financial resources to study ITC. But is that all that I could do? Not really.

I am interested in Electronics thus joined the army as a technician. It is the only formal technical training I ever get. While attached to civilians as a Quality Assurance tech, I have chance to contact with automatic testing technology. My superior encourages me to write programs of our own to do automatic testing instead of relying on contractor's program. With some guidance from the contractor, I managed to write the program which performs equally well as the contractor's without even going for any programming training. At that time the programming language is BASIC which is easily understood. If it is C language  then probably I am done for.

After my army service term completes, I have no certificate that is recognized in the civilian world so I worked for a call management company doing outsource work. The procedure is complex so they have documents to assist the call agents. However, the documents are never synchronized. Each person may have a subset of what is available and updating is a headache. By then I have been promoted supervisor (because nobody wants the post) working in Hewlett Packard.

I began to look for standardization. At that time we are using UNIX system as a computer console. I have virtually no access to the system except the filing directory. After reading books on computers, I noticed that there are help files. Help files can be created using an application. I began to learn how to use the application at my own time and managed to create a standard help file with all the information in one place thus achieved standardization.

As time goes along, the UNIX system has been replaced by Microsoft Windows. The help file can still be used but again my customer's superior urge me to do a web based help system. I have virtually no knowledge of web programming but I took up the challenge by studying HTML and Javascript. The result is a simple web based help system.

After that my customer superior become aggressive and wanted to have a interactive web program by using ColdFusion and web server. Again I took up the challenge and studied ColdFusion while actually writing apps. Luckily it is quite easy to learn Coldfusion. In the process I also learned SQL. The result is a CRM system. All these without the company spending a single cent for training.

As we proceed along, there are some redundant servers available for scrap and we took them in. However, since we only have one licence for ColdFusion, it is deemed too expensive to buy more copies of it. Moreover, the web server is a internally hosted by IT department and it costs to maintain it with them. Therefore, we switch to PHP and Apache web server. They are all free.

With the PHP, Apache web server and unlimited supply of MS SQL server (the customer company have signed an unlimited supply of MS SQL server licence), I wrote two separate PHP CRM system in the course of work. It even include a Standalone Queue display system. How much it costs the company? 0 cents except my salary which is not even a skilled technician pay.

In between all the web programming work, there is a need to transfer the CRM data to the customer's legacy system. All the while we are doing manual copy and paste work. I studied the legacy system and noticed that there is a macro programming which can be controlled externally. The result is that I wrote a DLL program that can be called by Javascript using ActiveX that can write into the fields of the legacy system and able to click the buttons available. It saved us extra manpower to manually enter the data. It saved the customer's money but it is actually counter productive for my boss (we charge by the manpower used for the outsourcing). I ended up being the bad guy for my own company.

To amend for that, I began to write utilities to make our people's life easier without having to cut down on manpower. The result is a series of automatic checks to ensure that the data entered is according to specification. It automatically correct the wrong data according to specification. It even includes an automatic quotation program. We invested a bit and added an automatic SMS and email facility. It was the peak of my programming era with two additional manpower under my charge.

The work also requires reporting system. So, I wrote a series of web reporting system and produce summaries including charts. I even created 6 sigma charts at the customer's request. There is a excel based summery report which is so complex that I have to write macros to do thousands of distinct summary calculations till I myself am amazed to how I actually achieved it.

After my boss have to quit the outsource contract due to difficult customer demands, I become jobless. The customer manager wanted me to sign on with the other outsource company who took over. I declined as I am still loyal to my ex company. Also, it is due to the same customer manager that my boss quits the contract.

Again without formal training, my skills are useless. After two years my ex boss started a new business as a authorized repair agent for Apple handphone and Mac. He contacted me after one year of opening business and request my help to automate the system. I agreed without actually knowing the system used. It turned out that they are all using Mac and iPad. Moreover, the programming tool used is FileMaker. The actual task is to automate between their system and Apple web services.

Being completely new to all three system, I could not promise my boss but he is willing to spend 6 months of time (1/2 day part time) to try achieve it. In the end, I learned how to use a MacBook to write programs with Filemaker and is able to communicate with Apple web services using CURL on Filemaker. The result is a total success without even going for formal training.

I am currently writing Filemaker programs on ad hoc freelance basis for my boss and there is no other new programming challenges but I am still eager to learn new things.

In my free time, I started to cycle and walk around Singapore as an exercise. While exercising I saw flowers and plants that  I cannot even name it. I started to take pictures of plants and try to find their names. In the process I joined iNaturalist and Project Noah and a number of Facebook groups which allow others to help identify nature creatures. I began to take animals and insect photos too. it is a total new "hobby" and I again learned tremendously on this area about plant and animal kingdom. The result is that I walked all over Singapore to places which I have never been and accumulated around 17,000 photos with 2600 plus distinct species (including overseas).

I have created two Blogger blogs one each on plants (Plants in Singapore) and animals (Fauna Singapore) and duplicates them in WordPress webs. After all, what I learned could be useful for others too. I also shared some of my technical skills in this blog too (Everything Jonathan).

While trying to exercise, I encountered problems finding PCN paths. NParks did have a map but it is just a plain map. It did not tell me whether the path goes above or below roads and I have a problem whenever it switch to the other side of road and did not mark it properly (in early days). I have created a map on Google Map to show all these information and other helpful info necessary to have a seamless connectivity between PCNs (New PCN on Bikes). As of to date it has 360,000 plus views.

In the process of creating maps, I learned the skills about GeoTagging. I learned to use different tools to create custom layouts. I learned to tag photo with Geolocation in sync with GPX routes data. It all started just for fun but I learned.

What I have achieved in learning is plenty although it is not recognized by other companies. I am proud of what I did and believes that learning is never redundant in life. There is a Chinese saying "活到老学到老". Learning never ends.







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, February 07, 2017

Abortion

A lot of people said that the Bible does not say anything about abortion. Indeed there is no specific mention of abortion as the population before 1st century is sparse. In today's world, the population is 7.4 billion as at August 2016 (Wikipedia). Heavy pollution occurs in hugely populated cities. More and more forests have been cleared for human habitation and food cultivation. More species have gone extinct. What then is the Christian point of view in abortion.

Roman Catholic prohibits abortion. They probably based on  Exodus 21:23, Deut 5:17, Gen 25:21-22, Psalm 22:9-10, Psalm 139:13-16, Psalm 22:10-11. It is valid during the 1st century.

Gen 1:28. God created man to subdue and dominate over God's other creation. Does God create plants and animals so that human can destroy them?

Deut 22:6 talks about caring for the birds. You take the eggs but you don't kill the mother.

Leviticus 25:1-55 talks in length about Sabbatical year. Why is it so? Why the land need to rest?

Exodus 23:10-11 talks about letting other animals to eat.

Leviticus 19:9-10 talks about leaving some for the poor and desolate. Why not leave some for other plants and animals?

The above quotes has no relationship to abortion. What is portrayed is the need to maintain balance with the world we live in. We look at human as more important than environment. We multiply so much so that nature have to give way to support human. This is not what "subdue and dominate" means.

In our effort to support humans, we destroy vast stretches of forest. We produced vast amounts of rubbish. We pollute many rivers and seas. We over fished. We don't allow earth to replenish itself. Is this what the Bible taught us to do?

The only way to live in balance with environment is to control the human population.

Abortion is not the best way to control human population.  The best way is contraception. Abortion comes about due to bad human behavior and ignorance. There is no need for abortion if the proper preventive actions were taken. But due to unforeseen circumstances, there is a need for abortion. It must not be a norm. It should be under exceptions.

There are a lot of arguments about at which stage of pregnancy should abortions be prohibited. I think it should be very early stage. It is cruel to abort when the baby is well formed.




Friday, January 13, 2017

Cycling Two Abreast on Roads

Recently the Active Mobility Bill was passed on the second reading. Soon enough there are cyclist who question the reason for the limitation "cycling two abreast will be allowed on all roads with at least two lanes in that direction, except those with bus lanes during the bus lane operational hours".

Now, before we talk about the new bill on this topic, lets just see the existing laws regarding cycling on roads.

The title of Article 7 is "Travelling abreast prohibited". Article 7.1 specifically indicate bicycle cannot ride "on the right of another vehicle". There is no explicit definition of vehicle in the article. According to Oxford Dictionary (and others) it generally means "a thing used for transporting people or goods". It is quite clear cut that bicycle cannot be excluded.

Article 7.2 then restricts Article 7.1 by prohibiting "cycling on the right of any two other bicycles proceeding abreast".  There is absolutely no ambiguity about cycling abreast in this two article.

Unfortunately, those who have bias always ignore Article 7.1 by treating "vehicle" as equivalent to cars, lorries and buses. Furthermore, they read the word "two other bicycle proceeding abreast" as that the law allows two cycling abreast not withstanding that Article 7.1 already prohibits and that it is taken out of context.

It is quite confusing why the specific article mentions two bicycle. It is assumed that overtaking 2 bicycle is still allowed due to the fact that the space taken by two bicycles is small enough. Obviously, those  who are biased towards cycling abreast take it as cycling two abreast is allowed.

Article 7.2 actually refers to the prohibiting of the third third bicycle with respect to the first two (while the second bicycle is overtaking). "Proceeding abreast" was interpreted as cycling abreast. However, "proceeding" could also means "begin a course of action" in addition to "move forward". Anyhow, Article 7.2 is not about allowing, it is about prohibiting and the subject is on third cyclist. Naturally, when you are biased you tends to read the words that conform to you bias instead of open interpretation.

With the biased understanding of "two cycling abreast" being allowed in the old law, they objects to the idea of "putting restrains" in the Active Mobility Bill". They event question why it is not stated in the bill. However, LTA already specifically indicated that it is "key regulation for on-road cycling in the Road Traffic Act".

LTA never specifically explain why cycling abreast is not allowed on certain roads. I have done a bit of measurement and come out with an explanation.

According to an article in asiaone (dated 2012-08-28), Mr. Francis Chu measured the width of roads around Toa Payoh, Ubi and Buona Vista. He concluded that "standard car lane width to be 2.8 Meters" but it can be as wide as "4 Meters".

A bicycle generally measures 0.5 to 0.75 Meter wide. Lets take the shortest width as standard. In order to be safe from swings and water grills on the road, it is generally advisable to cycle just to the right of the double yellow line. That is about 1 meter from the curb side. Now there is a campaign to promote 1.5 Meters safety gap between cyclists and other fast moving vehicles. Assuming, the safety measures are all in place, the total distance that other vehicles must move will be 1+0.5+1.5 = 3 Meters. Even with the widest of road lanes, other vehicles will have to end up partially on the second lane or opposite lane.

If cycling two abreast, then you have to add another 2 Meters (bicycle width plus safety distiance). The total gap (5 Meters) will be wider than any road lane width. Therefore, defining cycling two abreast on "at least two lanes in that direction" is appropriate safety measure rather than restriction.

On single road lanes and bus lanes (multi lanes are not excluded), the safety distance for one cyclist is already 3 Meters. It will be dangerous for other vehicles to overtake. They generally have to go on to the other lane to pass safely. Many cyclist feels the pressure when other vehicles overtook them too close. They do not realize that they have the rights to be on the road so is other vehicles. I do know that it is flesh against steel. Motorcycles also face the same issue. but because their speed is equal to cars, they feel less intimidated because less vehicles overtake them and they are not blocking others.

Cyclists cannot expect other vehicles to follow them because even the slowest speed limit (40 KPH in HDB areas) is also faster than the average 30 KPH that a cyclist can do (except some very good ones). While overtaking bicycles, the other vehicles are also restrained by other vehicles in the same direction or opposite direction. It is inevitable that they sometimes misjudge the safety gap and go uncomfortably close to cyclists. It is not bias on the driver part because they do it to other drivers too.

There are many drivers who question the reason for allowing cyclists on roads. They even say "cyclist do not pay road tax". We can understand their frustration because cyclists used to obstruct their ways. However, cycling on roads are explicitly allowed by law. Cyclists have the right to use roads. It is a matter of accommodating each other.

With the above reasoning, cyclist should be glad that it is now explicitly allowed two abreast on certain roads and not whine about cycling abreast on all roads.

One last thing. Roads are designed for commuting, it is not specifically designed for convoys. Many cyclist like to use the road for recreation/sports in a group. There is no law against travelling in convoys/groups. Remember that cyclists are not travelling faster than other road vehicles which could easily out run them. Out of courtesy, you should consider that they need to overtake you thus try to give way to them whenever possible as a cooperation between road users. There is no law against cycling in groups that does not means you sacrifice other's right to move faster.

I myself am cyclist. I advocate mutual acceptance rather than biased toward cyclists. I do full heartedly support the campaign of having cycling paths on roads. That will alleviate a lot of issues.