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.

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.