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修正案。




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

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



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

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


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

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

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





会长甚至在大会通知中附上了日期为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.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视频。 论点是“元代人为中轴线偏差”。后来又看了郭晓强的文章(觉得后者有理由。






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.