Showing posts with label Tech stuff. Show all posts
Showing posts with label Tech stuff. Show all posts

Friday, July 16, 2021

Setting up a Broadlink SP4L-UK Smart Plug

 SP4L-UK is a smart switch that can be controlled by hand phone via WIFI. It is compatible to Google Home. This model is suitable for Singapore home because it uses the same 3 pin plug configuration commonly used in Singapore. 

Now before you could do anything, make sure that your home wifi is set at 2.4G because this device is not a 5G compatible device. Most home network have both 2.4G and 5G wifi connection. You handphone will auto switch between two wifi connection automatically anyway. Only problem is that you need to specifically connect to your 5G network manually. While setting up, you must turn on bluetooth and use a 2.4G network on your phone.

Download BroadLink-Universal TV Remote app. Android users need to make sure no screen overlay app is running when setting up the app. You cannot turn on/off screen overlay if one is already running.

After setting up user login, follow the following steps.

  1. Plug the smart switch into the power socket. You will notice the LED on the switch blinks continuously.
  2. Click add device. 
  3. Choose "Wi-Fi Devices" 
  4. Select "Other Wi-Fi Devices". 
  5. Choose "Smart Setup" and click "Next". 
  6. Click "Done" ignoring all the information on the screen. 
  7. It will prompt you to enter your wifi login information. The SSID will be automatically chosen. Just enter password accordingly.
  8. After a while your device will be set up properly. The LED on the switch should go off.
  9. Set up the Name and room information for the device and you will be done.
In the even of error the LED blinking rate will go much slower. You need to reset it by pressing and hold the button near the IR sensor at the end furthest from the three pin socket. Wait till it blinks at a faster rate. It will be ready for you to try again. 

You may wonder why I choose a smart plug that is using only 2.4G wifi. Actually, there is not much choise. Furthermore, the range of the wifi is wider than a 5G wifi. Most manufacturer choose to use 2.4G wifi over 5G wifi. 

You can use your 5G connection to control the devices as they are all on the same router connection.







Monday, December 07, 2020

Internet Download Speed is Crawling

 Quite some time ago my win10 browser loading suddenly become sluggish. I check with Ookla Speed Test shows that the download speed is just 6 mbps whereas I subscribed to 1 Gbps. The upload speed is very much faster but still well below the 1 G bit as subscribed.

Tried various methods of finding out why the download is slow but to no avail. Checking the speed on my handphone proved that the router is OK thus not service provider problem. It is only the laptop that has the problem. It really puzzles me as it is not due to browser issue since Ookla has a app that could perform the test without using browser.

After a month or so of intermittent attempts to find the fault, I finally found a clue. Netizens mentioned that SmartByte Network Services could slow down network.

I uninstalled SmartByte and the speed went up to almost 1 Gbps. SmartByte come pre-installed with Dell laptops. There are instructions in the internet that teaches you how to uninstall it. One such example is https://windowsreport.com/smartbyte-services/.




Tuesday, October 27, 2020

digiKam Used for Managing Flora and Fauna Photos

 I need to use an application that could manage those photos of flora and fauna so that I could easily refer to them or search for specific specie. After the demise of Picasa, I found an application called digiKam that could perform most of the functionalities like Picasa. 

digiKam has lots of functionalities that are available. Here I will only talk about those I would use to manage flora and fauna photos.

Showing photos that are stored in directories grouped by directories is the basic requirement. It won't work if I have to go to individual directory to show the photos inside. digiKam did a good job by showing an entire directory with subdirectory as a selectable list.

In addition to directory aka Album, digiKam is able to show lists by Maps, Tags and Dates. It also have a search by title. Other list types are available but is of little use to me. Tags list is mentioned together with Tag editing below.

Maps listing is an interesting feature. You can show a list of photos that is defined by a square selection or select a group of photos that is grouped by digiKam. I find it extra useful to identify flora pictures. It all goes by memory. If you have seen the flora before and remembers a rough location, you could list the photos in the area and try to find the flora. For fauna, it is a bit difficult as they don't stay in the same place unless they are territorial.

I also contributes to iNaturalist. The location can match with digiKam map to show a list of photos in the same area. It helps if I have already identify the flora and have a picture of it. Inversely, I could use my photo's location and find in iNaturalist to see if there are any observation of the same kind. iNaturalist have the same map area list like digiKam.

GPS, Tags and Title are the most important information that is stored in a picture. digiKam allows the editing of these tags as individuals and group editing. 

Tags are usually done as a list. digiKam stored hierarchical tags in its database. It did not store the data in the pictures because other applications only look at tags as a list. This feature is best suited for me as I normally set the taxon family and Gender as separate tags. digiKam is able to use tags to show the list of photos that has the same tag. Now I could use the hierarchical listing to see the gender in a family instead of seeing the whole list of photos in the same family. 

Tag editing also benefits from the hierarchical arrangement. When adding tags I had to find the family and gender separately before adding them. Now I just need to find the gender and the family will also show up if it already exists. This save me from having to find the family name for the gender.  A time saving feature.

Zooming is a very useful tool. digiKam allow list photo to be zoomed to a size comfortable. I zoomed to a level that shows 5 photos in a row. That is most comfortable to see enough details of the photo. 

By double clicking a photo you could see a zoomable view of the photo. It even have a full screen zoomable view of the photo. You could set it to full screen when you double click. This is very useful to see clear details of the photo so that I could find the specific character of the flora and fauna.

Rotation. Sometimes the camera does not automatically store the rotation thus digiKam showed the photo in the wrong position. digiKam has a rotation setting so that you could view it properly. However, I am not sure if the information is stored in the picture after that. 

Photo editing. Not the usual kind of editing feature is available. It is just a set of enhancement that allows photos to be adjusted. I usually use auto color to adjust the photo. Other adjustment features may be useful but is of less interest to me.

Grouping in a list. It is a unique feature but it does help if you go for a tour and need to group the photos by the day. You could group the photos in anyway. I find it useful to avoid seeing those photos that are not related to flora and fauna.

Panorama is not part of the feature required for flora fauna management. But it is useful for people who does not have a panorama camera to stitch few photos together to make a panorama view/ However panorama is not a digiKam application. It has to be installed from outside. Luckily when you select panorama in digiKam, it allows you to download hugin (pto2mk) and GnuWin (make) from internet. Download and install, the panorama feature will be activated.

In conclusion, it is useful to me to manage my photos in the flora and fauna sense. This application is available for Windows, Mac and Linux.






Saturday, October 03, 2020

Blind Navigation System using QR Code

 My previous blog talks about using RFID for navigation. The only problem is that the reader is not cheap and is hard to come by. This blog talks about an alternative for the RFID.

QR code is good because it can tolerate high margin of errors.  QR code can be scanned using a hand phone. Thus the data and the reader is available cheaply. QR Code need not be read from a close proximity. Just enlarge it and it can be read from a distance. 

I tried a two inch square QR code (1234-56) and it can be read from a meter away. Proportionally, a 5 inch (12.7 CM) should ideally be read from 5 meters away. In actual fact, it can be read at 3 meters distance only . However, that will be good enough otherwise it cannot fit the camera view at close distance.

The use of it is similar to RFID. There is a mapping of all the QR code within the area. They are placed 3 meters apart. 3 meter is used to ensure that the next QR code is within scanning distance. Using the same triangulation method of the RFID, the same navigation method can be done.

Obviously, the QR code should be hanging high enough so that human traffic will not obscure it. Since it is passive, Visual Handicapped may totally lost trace of it and wonders off course. Illumination is also a issue unless the place is perpetually lit.

See my post on RFID here.




Monday, December 30, 2019

PHP Use of Openssl Encryption

There are a number of encryption methods available in PHP. While they are used in various applications, the main objective here is to talk about a two way encryption/decryption using Openssl.

Openssl is a general cryptography library. PHP has a number of functions that handles openssl. To use openssl you have to create or buy a SSL certificate. Buying from a cert authority means that your certificate are certified authentic. Creating your own private cert has its own use. For the purpose of encryption and decryption, there is no advantage in not using a authentic cert sine your purpose is only between two parties. You can generate ssl private cert using command line Openssl library. I will skip this part as it does not involve using PHP.

A certificate has two keys (a set of strings that can be used for encryption/decryption). One is Private key the other is Public key. By its name, you can see that the former is only used by one party. The Public key can be distributed to many others. Its uses is discussed below.

The user that has a private key uses it to encrypt a string. Any one with the corresponding Public key can decrypt it. Any one with Public key can use it to encrypt a string but only the one with the corresponding Private key can decrypt it. The former is for sending common data to many people that has the Public key. Its actual use is for a specific purpose detailed later. The latter is for many people to send data to a central person. This means people can sent personal data to a site but only the site can read it.

Lets talk about using Public Key to encrypt and decrypt data. First we do public key encryption.

$aok = openssl_public_encrypt($data. $encrypted, $pubvlicpemkey);

$aok is just for checking whether the encryption is successful. $data is the raw data to be encrypted. $encrypted is simply the variable to store the encrypted data. $publicpemkey is the Public Key in PEM form.

To prevent data transmission error, we need to convert the encrypted data into a special text format using "base64_enconde($encrypted)".

On the receiving end, we need to use bse64_decrypt($encrypted) to return back the encrypted string before using

$aok = openssl_private_decrypt($encrypted, $data, $privatepemkey);

As usual $data is the decrypted data.This method is basically used by sites to let customer send personal data or banking transaction. Since only the site is able to decrypt, the transaction is secure.

Next we use private key to encrypt. Its use is to ensure that the sender is authentic (if it uses recognized cert authority certs or has a understanding between sender and receiver.) Its main use is just to sign the data that was send in plain or encrypted text. It usually involve two certs each exchange their public cert with the other.

The method use is

openssl_private_encrypt($data, $encrypted, $privatepemkey);

The other party then uses the following to decrypt the data.

openssl_pubhlic_decrypt($encrypted, $data, $publicpemkey);

Since only the sender that has the private key can encrypt the data to be decrypted by the receiver with the public key, the receiver basically can be sure that the data is from the sender with the private key.

Another use of the Openssl is to send a signature. A signature generally follows a text and is used to prove that the text is authentic from a specific person. It is similar to sending a private key encrypted message but with a layer of security added called hashing (a method of mapping a data to a specific size).

$hashed = hash($method, $data, false);

There are many methods of hashing. Usually we uses 'sha256'. The last parameter specify using raw output to ensure that nothing is changed.

The next method encrypts the data using a different functtion

openssl_sign($hashed, $signature, $privatepemkey);

Obviously, by sending the above does not quite let the receiver know how to identify the signature. The signature is basically used to accompany a plain text or an encrypted text. If it is encrypted then the receiver has to decrypt it first using methods previously discussed above.

On the receiving end, the following decrypts the signature. Before doing anything, the receiver has to hash the text (plain or encrypted) that comes with the signature. If the text is encrypted then the receiver has to decrypt it first. The following function then is used to verify that the hash matches the signature.

$hashed = hash($method, $data, false);
openssl_verify($hashed, $signature, $publicpemkey, OPENSSL_ALGO_SHA256);

The last parameter specify 'sha256' hash method.

Note that if the text is encrypted, it usually use a public key from the receiver otherwise the encrypted text is already proving that the sender is authentic.





Sunday, June 23, 2019

Navigation system for Blind using Passive RFID

It is always troublesome to find our way in a big shopping center. This applies to Visual Handicapped as well as sighted. Currently, the best way is to use beacons system to provide navigation and information around the beacon.

To maintain the beacon system is not that cheap. Beacons are life beacons that needs power source. In a big building, the number of beacons used is staggering. There are ways and means of using other technologies to provide navigational needs for the Visual Handicapped. Below is one which I think is viable.

RFID has been in the market for decades already. Passive RFID is just a piece of paper attached with a tiny chip and an antenna. It is very cheap and can be bough in bulk. Each RFID is unique. Therefore, if you are near enough with a reader, you could know which RFID is near you.

Firstly, you need to lay breadcrumb (RFID) trails on every path in the building. A mapping of each RFID and nearby POI (point of interest) is necessary for navigation. The breadcrumbs should be spaced at a fixed distance apart. Ideally, at every junction, a breadcrumb should be right in the center of it.

User using RFID reader could then able to read the breadcrumbs as he/she walks along the path. By limiting the range to just able to read at least 2 breadcrumbs, The system will be able to know which direction the user is moving since the last one will drop off and new ones will appear. With the mapping, you can then tell the direction of movement.

At junctions, user should be informed that there are branches and alert user on the possible direction and POI. User can then make a decision to move to the specific direction.

The app can be in a form of a map of the building with blinking dots to show current user position and with voice assistance for Visual Handicapped to use. It will have a list of POI to assist users. It will be good to have a search function that finds the nearest point matching the search string like "exit". A highlighted path to the search string, like map navigation, will be helpful.

Since passive RFID is cheap and need no power, it is just a install and forget type. Any missing RFID can be replaced with the same ID. It is, therefore, simple and cheap to maintain such a system.

Now the expensive part is the RFID reader. It needs to be Wi-Fi enabled so that it can be integrated with hand phone app. The app has to be custom designed to use it as navigation system. All these costs eats into user's budget. However, it reduces the cost of building owners so that they will be more willing to invest in the infrastructure. With many building owners willing to install the system, the cost of the application can then be reduced thus lessens the financial burden of users.

Since this navigation can be used for sighted, it becomes universal navigational system thus further reduces the costs. It is a win-win situation.

This system need not to be indoors only. Outdoor place like parks can install such system using weather proof containers for the RFID. It can even be used for temporary venues like a carnival. The uses are multiple.





Thursday, June 20, 2019

Communicating with Apple Web Services (GSX) via Filemaker 2

My previous blog on the same subject is 3 years ago. Since then Apple has changed from using XML to JSON as a means of transferring data. Therefore there is a need to show how to do it with JSON.

JSON (Javascript Object Notion) by its name, is a Javascript feature but it has extended its use to many other applications. It is a format of storing data just like XML except it is more human readable when you format it nicely.

Fortunately, FileMaker has caught up with the latest trend by including a few JSON related functions. It is not that easy to use those functions to compose JSON but it is very much easy to get data from JSON.

To create simple JSON you just need to use $example =JSONSetElement("";"elementkey";"elementvalue";1) and it will create a nice JSON '{"elementkey":"elementvalue"}'. However, if you want to insert nested JSON then it is a bit tedious.

For example, you want to create a key that contains an array with one key and  value. First you must create the key and value as a JSON like last paragraph. Then you have to create an array to contain that JSON like $array= JSONSetElement("[]";0;$example;3).

Finally you then create the root element like $root=JSONSetElement("";"root";$array;4). You have created a JSON '{"root":[{"elementkey":"elementvalue"}]}'. This format is generally used by Apple in GSX in both request and response.

If it is a simple JSON that need to store a small set of data with fixed keys, it is better to create the JSON with unique values for each element that needs to be changed like "~!1", "~!2" etc. After that use substitute function to replace the "~!x" sequence. Two lines instead of 3. In actual fact, one line encompassing both action can be done by just using the JSON as source of substitute directly. In this way you could reuse the same code repeatedly or even store it as custom function.

The parameters used in JSONSetElement can be found at. JSONSetElement.

Getting values from "elementkey" in the above JSON is easy. $value=JSONGetElement($root;"root[0].elementkey")

If you looking at this blog first then I suggest you go to the previous blog on the same subject first to get an understanding.


Friday, May 24, 2019

Windows 10 grinds to a halt

First it was an extra application called Chromium get installed. Unable to install thus use IObit uninstaller to remove the program.

After that the computer suddenly goes unresponsive. It takes over a minute to respond to clicks.  Task manager indicated that CPU usage is low and Disk activity is low. It just puzzles me.

Nothing I do could solve the problem. Manage to get Win 10 to boot to safe mode by forcing it to close then start again 3 times.

Even at safe mode, it also responses not that well. On searching from internet, it was suggested to check page faults. Starts Performance Monitor and adds page fault. The percentage is 100. Netizens suggest using Sysinternals, Process Explorer. Downloaded it and run. There is an item that has more than one million page fault. It is Mcafee.

Uninstalled it and the system works as normal. One problem still persists. Windows Defender antivirus shows that "the virus protection is managed by other manager". No facility to set it back. Well, that is not part of this issue.



Thursday, November 01, 2018

Setting up Google Play on Mi5X

I always have problem with Google Play using Mi5X. It is not available when shipped. Installed Google Play Apk from websites and it can launch Google Play.

One of it works just fine but the other one will prompt "Server Busy" on and off. Following netizen advice to totally remove Google Services and Google Play store. The result is that I cannot restore Google Play at all despite installing Google Services and Google Play apk from sites that provide the apk. Even my account information does not list the Mi5X as a device.

While looking for ways to restore back Google Play by searching for "Google" in Mi Store, I notice Mi Store has a prompt saying something like it is not able to list Google products due to limitation. Thus whatever advice on the web does not apply.

Searching for "MiUi Google Play" turn out something like a installer for Google Play. Tried to go to Mi Store again and look for "谷歌安装器”。 It still turn out nothing.

This calls for drastic measures. I notice there is a "去豌豆荚搜索“ in Mi Store when searching for "Google". Tried that and it  actually shows one app called "谷歌安装器”. It is not by Google but look rather like a "Google Play Installer".

Followed instruction to allow for "unknown sources" in Settings.Additional Settings,Privacy. The apk was installed successfully. Running it and it prompts for installation of a number of Google Apps. Upon completion, Google Play can start without issue.

The next thing to do is just wait and see if "server busy" prompt will show again.



Monday, October 29, 2018

Printing Receipt Using PHP again

Last blog on this subject is at Printing Receipt. It talks about a single use printing where there is only one printer for one purpose. There are requirements to use same type of printer for different receipts. Like for example, one company wants to print queue numbers, receipts and barcode travellers using the same type of printer.

It would be easy to just create three PHP page to do the work. Here the attempt is to use a single PHP page like a driver that can print to different printer for different purpose. It becomes like a class file. Also the print codes stays with the app that does the printing so that you do not need to look for specific PHP page for the printing code.

The PHP code is like the previous blog with a slight change. The printer is a Brother QL720NW which does not work with fwrite_stream. A simple fputs will work fine.

All that is required is to send the printer IP and the print code to the page as parameter using POST. Now the twist is that POST only sends AlphaNumeric not the full ASCII. Escape codes usually includes ASCII less than 32. The trick is to convert the print code into Base64 first before sending. At the PHP, just use base64_decode to convert it back before printing. The code is as below

$ipAddress = $_POST["IP"];
$printCode = $_POST['PrintCode"];
$printCode = Base64_decode($printCode);
$fp = fsockopen($ipAddress, 9100);
fputs($fp, $printCode, strlen($printCode));
fclose($fp)

With this, you can send to different printer with a different print code using the same PHP page. The advantage is that any app that can do a POST will be able to do printing.

Most programming codes will have base64_encode (or similar). If not then there are probably programmers who wrote a code library in the same programming language. If it does not have then you may have to create the code yourself. The trick is to convert the byte into Binary then take every 6 bit and convert it back to number to match against a table see https://en.wikipedia.org/wiki/Base64. to get the ASCII character. It is not that difficult.




Thursday, August 02, 2018

HTML Table Scrollable Tbody

HTML tables can display rows and columns of data in a nicely excel like layout. With CSS, you can make beautiful tables. With huge rows of data, the header tends to be scrolled out of the screen making it difficult to know what the column represents.

Using CSS, you can actually fix the header and able to scroll the data. The trick is to use THEAD and TBODY with CSS.

For THEAD and TBODB, you need to use Display: Block. For TBODY only you need to use overflow-y: auto so that it could scroll up and down.

The problem with this arrangement is that the column width is uncontrollable especially when a column data is all empty. THEAD columns cannot sync with TBODY. Its really painful to have it working yet not able to align the column widths. I wonder why it is still not fixed.

One way to align the tables is to use Javascript. However, if user set to not allow JavaScript then the display is messed up. Therefore, a CSS way is more proper. The catch is to use fixed width columns. Many designer frown on such idea. There is no choice if you want to scroll.

However, Width:50px, for example, does not work well, the width still self adjusts according to the text length especially when the table size is limited. Putting too big a width also make the table look ugly. I used the following to at least make the width more consistent. It is not a cure but at least it looks more presentable.

The HTML/CSS used is as follows :-

  1. Assign the table with a class name.
  2. Set the table width to 100%, Table_Layout: fixed
  3. use table.classname thead to define display:block
  4. use table.classname tbody to define display:block and overflow-y:auto
  5. use table.classname th:nth-child(x), table.classname td:nth-child(x) to define the min-width and other styles.
Item 5 above actually forces both header and body column as defined by x to the same size. Assign a minimum size (min-width) to the column so that it will accommodate the longest single word text in the column (text with spaces will wrap by default). It will take a while to adjust it but at least the columns will align if the tbody cells are empty. Each column must have a corresponding width setting.

Until such a time when W3C solved the scrollable table issue, this is probably the make-do way. Others might have a better solution but this one is an accumulation of various designer's idea.





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






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, 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.


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.




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.




Thursday, April 21, 2016

Web Based JS Charts again.

Constricting web based charts is quite a simple job. Using SQL statement to pull a summary of the data, structure it according to the requirements of the JS Chart and there is the charts. On the previous post, I have mentioned the use of Rgraph (http://jnhiew.blogspot.sg/2016/04/javascript-charts.html) to achieve the purpose. This post is to show how it could be done.

First lets start with constructing the data. The format of the data is [0,1,2,3,4,5,6].

The SQL to get the data will be

SELECT SUM([yourvaluecolumn]) AS mysum,[yourfield] FROM mytable WHERE [yourcondition] GROUP BY [yourfield] ORDER BY [yourfield]

The PHP code to extract sqlite data is as follows

$db= new SQLITEDatabase('sqlitedb');
$query = $db->query('[your query statement]');

The result will be an array something like below

123, 'abc'
456, 'def'
789, 'ghi'
1012, 'klm'

To extract the array, use the following
$csv = "[";
$csvkey="[";
while ($data = $query->fetchArray(SQLITE_NUM){
   $csv.=$data[0].",";
   $csvkey .= "'". $data[1]."',";
}
$csv = rtrim($csv,",")."]";
$csvkey = rtrim($csvkey,",")."]";

The RTRIM command is to remove the last comma.

In the javascript replace  the "data:" and the "labels;" line with

data:<?php echo $csv; >,
labels::<?php echo $csvkey; >,

The above is only for simple charts with one set of data. If the data is required for multiple bar or multiple line chart like showing the set of data in groups by month, then the format is different for "data". The "labels" will remain the same. The format of the data is like

[[1,2,3],[4,5,6],[7,8,9]]

Before you do anything, you must extract the labels from the database. The reason is that there will not be a data result if the field value does not exist for a particular month causing the number of columns become variable. Furthermore, it is impossible to predict the labels without first extracting it before hand. Therefore, the first thing to do is to extract a label list and an 0 initiated associative array that includes all the labels.

$csvkey="[";
$csvary=array();
$qlabel = $db->query('SELECT DISTINCT [yourfield] from mytable order by [yourfield]');
while ($data = $qlabel->fetchArray(SQLITE_NUM){
   $csvkey.="'".$qlabel[0]."',";
   $csvary[$data[0]]=>0;
}
$csvkey = rtrim($csvkey,",")."]";

The SQL will be

SELECT SUM([yourvaluecolumn]) AS mysum,[yourmonth],[yourfield] FROM mytable WHERE [yourcondition] GROUP BY [yourmonth],[yourfield] ORDER BY [yourmonth],[yourfield]

The format of the result will be

123,  'March', 'abc'

PHP code will be as follow

$dta=array();
while ($data = $query->fetch(SQLITE_NUM){
   if (!isset($dta[$data[$data[1]])){
      $dta[$data[1]=$csvary;
   }
   $dta[$data[1]][$data[2]]=$data[0];
}
$data2="[";
$key2="";
foreach ($dta as $key=>$value){
   $data2.="[";
   $key2.="'".$key."',";
   foreach($value as $k2=>$v2){
       $data2.=$v2.",";
   }
   $data2=rtrim($data2,",");
   $data2.="],";
}
$data2=rtrim($data2,",")."]";
$key2=rtrim($key2.",");

Replace the two JS as before.

data::<?php echo $data2; >,
labels::<?php echo $key2; >,

Add an extra option to the option in JS. It will be shown as legends.

key::<?php echo $csvkey; >,

Obviously, there are times when there is a need to do multiple stacked bar group by month. I will leave it to you to figure out. Hint:- Third level array.


Wednesday, April 20, 2016

Javascript Charts

After playing with Filemaker charts and found it limiting in its use, I decided to change to Web based charts. The first concern is what language to use. PHP has been used to do a lot of things for me thus it is the only choice.

The idea is not to create graphics charts from PHP. It will take considerable processing and data transfer size. Therefore, client side charting is the choice. Javascript is the only scripting language on a browser that does not require plugin so it will do.

The last thing to consider is what type of graphics to use to draw the chart. I have used SVG before to create charts. It is nice but I would like to make use of HTML5 canvas element to do it instead. So I finally settled with canvas graphics.

PHP can talk to javascript via AJAX. I too have done it before but the chart I am doing is not real time dynamic data. Thus, PHP is used to create javascript code. There isn't much javascript code to generate to create a chart if you use a javascript chart module that is easily available.

There are plenty of Javascript charts available. Some offer 90 plus type of charts. Since the requirement is probably just Line, Bar, and Pie, The consideration is to get the smallest size module to do the simple work.

Most popular Javascript charts is actually using HTML although the name is Javascript Chart. They even use ".js" as file name. I find it troubling as they actually requires access to the remote site to get the JS source. It also uses "crossorigin" links to JS source. The worst of all is that even the JS file cannot be downloaded locally. Three of the most popular JS chart module I tried cannot even be run on local directory.

I did a considerable search around internet and finally settled with Rgraph. It is a true JS library module and can be downloaded to run locally. The graphic is simple and not as elegant but it works.

The user JS to generate a charts is very simple. You can see it as below


    var pie = new RGraph.Pie({
        id: 'cvs3',
        data: [4,8,6,3,5,2],
        options: {
            shadow: true,
            shadowOffsety: 7,
            shadowBlur: 25,
            strokestyle: 'rgba(0,0,0,0)',
            labels: ['Henning','Louis','John','Pete','Lucy','Fred'],
            clearto: 'white',
            variant: 'donut3d',
            labelsSticks: true,
            labelsSticksLength:25,
            radius: 100
        }
    }).draw();

All you need to do is change the "data" and the "labels" option. Getting PHP to generate the two parameter is very easy.

Construct the data in the appropriate format in php and use the following line to replace the data line in JS.

data:<?php echo $data; ?>,

The other change is the Labels line

labels:<?php echo $label; ?>,

That's all. The chart will come out beautifully.

If you want to get the chart to be much more interactive and vibrant, the Rgraph documentation will show you very much more options to set the graphs. Below is a picture of a donut pie chart from the Rgraph examples above.




Tuesday, April 19, 2016

Filemaker Chart vs Web Javascript chart

Recently have issues with Filemaker chart thus have to switch to web based charts. There are quite a number of similarities and oddities. The following is the comparison.

FilemakerWeb
Uses tablesUses variables
Forms data from table with data pre fetched from SQL resultForms data direct from SQL result
Charts PreformedCharts can be defined at run time
Number of legends fixedNumber of legends determined from runtime
Max number of legend fixedNo Max number of legend 
Max number of data rows not fixedMax number of rows not fixed
Titles, X,Y labels prefixedDetermined during runtime although can be prefixed
Chart type prefixedChart type can be changed runtime
Interactive chartInteractive chart
Data values can be displayedData values can be displayed
Number of chart types limitedNumber of chart types depends on the module writer. Some can have 90 plus chart types
No time lapse display of chart dataHave time lapse display of data
Charts module fixedCan practically create the chart from scratch.
Not resizable during run timeCan auto resize or changed during runtime
Color prefixedColor changeable during runtime if needed
Options setting prefixedOptions totally changeable runtime.
Events trigger not availableEvents trigger available depends on module writer.
Chart data can be added/removed runtimeChart data can be added/removed runtime
Must use Filemaker to view chartCharts can be rendered in any web programming language that can work with javascript and have a graphics display capability.
Expensive.Can be totally free.
Charts formed by fix app settingPlain javascript text setting.
Easy to do even with novicesA big learning curve especially when you create chart from scratch
StandardizedWay too flexible until it is scary

You can see that Filemaker charts are for easy creation but is fixed in most features. It is not flexible at all compared to Web Javascript charts. Both caters to specific group of people and interest.