Wednesday, February 07, 2018

Business and Religion

In a mixed religion country, there are inevitably points of conflict while doing business between individuals of different beliefs. In some country, the difference is heavily suppressed while in other more open country, it becomes a viral news.

The following is just a few conflicts sited.

In America, the country's law legalize gay marriage.  However, people who solemnize the marriage have religion that is against such matrimony. So when a gay couple ask a religious person to perform the solemnization, there will be issues. Both sides claim that they have the rights to do or not to do.

In recent American news, a baker refused to make a cake specifically for gay couple. Again both sides claim that they have the right.

Yet another American news is on a company applying to set aside a certain law on religious grounds. The contention is about providing contraceptive pills.

In our neighborhood country a self service laundry company decided to serve only people of the same religion on religious grounds due to "contamination" of forbidden food stuff.

How would the law provide for such different stands. Both side claim that they have the right by law or by religious belief freedoms. It is deemed that one's freedom encroaches upon another.

In the gay marriage case, it is ruled that solemnizer can refuse the service and the council must provide others who can perform the service.

In the Bakery case, it is also ruled that the baker can refuse the service since there are other similar services available.

The company that applied to set aside the law on religious ground has won the case.

For the last case, it is not brought up in court ruling, the country's ruler (head of religious matter in the state) decrees that the company refrain from such practice.

One's freedom should not be imposed on another's freedom. If there are alternatives that is available, it should be taken to avoid conflict. Obviously, there will be impasse like the case of Temple mount in Jerusalem. It is claimed by Muslims, Jews and Christians. None will give way to another.

Monday, December 25, 2017

Christmas Day Reflection

Well, it is the time of the year where Christians celebrate the birth of Christ. I am the type that likes to find out the facts. The following are my findings from internet.

1. Most scholars don't think that Christ was born on 25th December. The most biblical argument is the fact that shepherds are tending their sheep in the field. It is unlikely to be so if it is in winter.

2. Christmas day is set by Roman Catholic church for a variety of reasons to make it more appealing to pagans in Roman Time.

  • Winter Solstice (Dongzhi in Chinese)- a pagan celebration call Yule, Iranian "Yalda night" and "Sol invictus" a Syrian God's birthday are all around the same day.
  • Satunaria - the Roman god Saturn.

3. Christmas trees - a Yule tradition that has nothing to do with Christ.

4. Christmas wreath - another Yule tradition that has nothing to do with Christ.

5. Yule Log - yet another Yule tradition that has nothing to do with Christ.

6. Mistletoe - Anglo Saxon term which could literally means "poo on a stick". A Norse mythology. Anything to do with Christ?

7. Santa Claus, North Pole, Reindeer - A tradition of mixing St Nicholas and Odin (German god Wodan) practices.

8.Twelve days of Christmas - It was said that it is a "Coded primer on Christianity" due to early persecution of Roman Catholics in England. It is not. It is just a mix and match reference to Christianity. The coded theory is only dated to 1990s.

Do I disapprove Christmas celebration? Not at all. Christmas is a traditional day set to celebrate birth of Christ. The focus must be on Christ. It doesn't matter whether the day is really the birthday of Christ. The rest of the practices are just unnecessary distractions.

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




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%是赞成的,因为该条款仍然受到成员选举的限制。 我有关于后者一个事实的故事,但我不会说明。

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

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