Filemaker ExecuteSQL in a table view field

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

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

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

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

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

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

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

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

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

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

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

Set location to a photo taken without GPS information using GeoSetter

I have two camera that does not provide GPS location information. If it is just a few pictures then it it will be easy to manually set it using GeoSetter. Just select the location on the map. Choose the picture to set location and  click the button "Assign Position Marker to Selected Image". You can select multiple pictures.

If I take hundreds of pictures then it will take aweful time to set the location. Moreover, I will not be able to remember the exact location of each picture that is taken.  I do record GPS track with my app on handphone when I go for a trip. With this track data, it is easy to update the location of photos by following a few steps.

First you must import the GPX file downloaded from your phone app. At GeoSetter bottom corner you will see a section called "Tracks". Click "Open Track Files" and select the particular recorded tracks. If your trip consists of a few tracks you can download all of them. This example download only one track.

Next, selecting all those photos without GPS location. Then click "Images" then "Synchronize with GPS data file". A popup window will be shown as below

First you must choose "Synchronize with selected track" (assuming you only have one track). On the "Assignment from found location" choose "Interpolate Regarding Shoot Time...."

On the "Time Adjustment" section, Choose "Use Time Zone." The next step depends on your GPS track recording. Some track records using UTC time instead of local time. Look at the bottom right hand corner of GeoSetter you can see a track point time. Also look at the right hand corner of your pictures. It shows a "time taken" information (more info will be available when you mouse over it). If that time is the same for the picture that you know the position and the track point, the track time should be the local time.

If the track time is different, make sure you set the time zone correctly as GeoSetter take the default time zone information from the computer.

If the track time is same as your photo, Click "Ignore Existing Time Zone...". Ensure "Ignore Existing Time Zone..." is checked and "Request Time Zone..." is unchecked.  Choose "(UTC) Europe/ Dublin". Which adds an offset of 0 hours to your photo time.

Now there are times when your track time recording and your camera time differs. You must again choose a photo with a known position and note the time difference between your photo time and the track time at the exact position. Make the "Additional Time Adjustment" by setting the appropriate offset. It can be a negative value. Ensure that "Add offset values to taken date" is unchecked otherwise your photo "time taken" will be adjusted too unless, of course, you do want to sync your photo time with the track time.

If everything is set properly, just click "OK" button and it will show a popup to tell you whether you are successful and the number of successful photo that can be tagged. You can click on the "Report" button to see why the tagging failed. If all are successful, just choose "Yes" when it says "would you like to continue with the result."

Remember to click "Images", "Save Changes". Otherwise your efforts will be futile.

Apostle

While reading up on Madonna's controversial performance, I chance to see the word "Apostle" being attached to a local pastor's name. I was curious and thus tries to look up on the topic of Apostle.

The word "Apostle" (ἀπόστολος) is found in a number of times in the Bible. They are generally referring to the twelve disciples of Jesus. There are exceptions. Paul, Barnabas , Andronicus, Junia, Silas, Timothy, and Apollos are also called Apostles in the Bible. Later Christians like Saint Patrick and Saint Boniface are also called Apostles (from WikiPedia). The Pope is traditionally from the "Apostolic succession".

Apostle in greek actually means "One who is sent away". The disciples were "sent away" to preach the gospel. In modern terms, they are missionaries.

I do not have problem with people conferred with the title "Apostle" as long as they follow the teachings of the scriptures. What I am curious is who conferred the title? It turned out that it is Peter C Wagner who founded the "New Apostolic Reformation" and re-introduced the title in 2001.

Again I do not have any problem with Wagner conferring the title. It is but a title given by an individual/group to an individual. It is only significant to the individual/group.

People on PCN

Recently there have been quite a debate between cyclists and pedestrians going on even ministers are mulling on setting speed limits or reinstate licencing. While I have a lot of arguments for cyclists, I choose to be neutral. The following describes what is the best behavior for both cyclists and pedestrians.

Cyclists

1. Should realize that pedestrians are not well educated on the use of PCN. They treat it like part of a park thus walks without looking out for traffic. So be patient and be alert.

2. Should realize that high cycling speed could cause serious injuries to pedestrians during accidents. So slow down please when there are pedestrians around. Go on the road if you are a speedster in need of speed. PCN is not just for cycling.

3. Should realize that children are ignorant of your presence. Be extra careful when approaching them (especially from behind).

4. Should not be rude to other users as they have the same right as you to use PCN.

5. Should use voice communication as it will unlikely startle pedestrians unlike ringing the bell.

6. Speed if you want if the PCN is deserted. Just be aware that there are blind corners and other speedsters around. PCN is originally designed for pedestrians only.

7. Should realize that even adults may misbehave especially when they are looking at their handphones. They can't hear you if they have earpieces on or having a nice time talking. After all they are enjoying the walk.

8. Should realize that it does not make you a second class user. By being courteous and giving way to others, it makes you a higher class user than them. Yes, you could not cycle smoothly but it ensures that you reach your destination without incident. Think of others rather than concentrate on yourself.

9. If there is a marking for cycling path on PCN, please use it. Nevermind if pedestrians tends to go on cycling path. That problem is a general education issue.

10. Be aware that there are some learners cyclists on PCN. Their balancing are bad. They don't signal. Their control of the bicycle are feeble. Do be understanding as you at one time also behave exactly the same as them.

11. Keep a reasonable gap between you and others. You expect cars to give you 1.5M space. Do it the same to pedestrians.

12. Cycling in a group may be fun but if you cause inconvenience to others. It is bad.

Pedestrians

1. There is no rule on using PCN. NParks does have a basic guide line. KEEP LEFT!

2. Should realize that pedestrians are not the only users. There are cyclists which travel much faster than you.

3. Should look out for fast traffic when making turns or change direction. Others does not have telepathic senses to know what you are doing.

4. When you give way to others please keep left instead of some right and some left making it difficult for cyclists to go pass you.

5. Avoid blocking the complete route when walking in groups. There are faster PCN users who can overtake you easily.

6. Always hold the hand of your children when walking on PCN. They won't know the danger of fast moving vehicles and they won't look out for them.

7. Walk your children/dogs on your left instead of on the right while keeping left. It is safer.

8. Stop playing on PCN. It is for walking. Sudden moves may invite accidents as there will not be ample time for cyclists to react.

9. Stop looking at your handphone or listen to music unless you are just walking straight without crossing any path.

10. Zebra crossing on PCN are supposed to be safe. Don't assume. Look out for errant cyclists.

11. If there are markings on the path separating cycling and walking. Please use the walking path.

If everybody plays their part, there will be minimal arguments or accidents. I won't say there will be no accidents as there is no way you can prevent it. Under unforseen circumstances, accidents do happen.

How accessible is Kranji Marshes

Kranji Marshes was opened officially yesterday. I went there yesterday afternoon. It was practically deserted.

Today I saw "Today" news paper that the marshes is actually not for public access. Once a month NParks and NSS will conduct a free guided tour each that goes into the marshes. Meanwhile the general public will have to settle for a small area at the edge with a glimpse of the marshes.

Contrary to other places like the neighbouring Sungei Buloh Wetland, there is practically no access to naturists like myself who prefer to just walk in.

It is therefore not visitor friendly. A review will show how accessible it is.

Firstly the place is off Neo Tiew Road. There is not even a sign board by the roadside to show where is the road that leads to the marshes. I have to try every side road along the way to find the actual place.

As far as I know, there is no public bus service in the area. There is a private bus service (Kranji Express) but I did not find the destination included in the schedule. That limits access to those who drive or cycle. In view of the government's drive towards 80% utilizing public transport, Less than 3/4 of the population has no access to this place.

Even if you can reach the main building, there is practically no access to the marshes unless you register for the tour.

NParks and NSS guided tour is only once a month each and you must register first. People who have adhoc free time will have no chance. Moreover a large group of people walking around will disturb the birds so chances of seeing one loitering around is much less.

Naturists and bird watchers will want to stay much longer at one spot to catch a view of nature. NSS does have a tour in the morning but that is only for members or at best, registered visitors.

NParks tour is only conducted in the evening. There is practically no chance to get the early birds. They certainly won't wait for you to loiter around.

With this I conclude that it is not a good place to visit unless you are just a tourist wanting to see nature on a guided tour booked in advance. If this is the trend for future parks of similar nature, it spell doom for casual naturalists.