A list of things to remember about the way FileMaker Pro behaves (in no particular order).
The shutdown script (triggered by onLastWindowClose and configured in the File Options dialog) only runs is the file is closed. If the FileMaker Pro application is quit, the script is NOT run.
Cross Platform Issue: When running in Mac OS, the window cannot be closed when a script is paused (a feature useful for creating "modal" dialogs). However in Windows OS the window CAN be closed.
People should not be required to enter anything that the database can work out using information it already has. For example, dates are entered for a staff availability. This data can be used to display whether the staff is "active" or "inactive" automatically.
When trapping for errors, turn Error capture off as soon as practical after the trapped step. You WANT to know when errors happen, because they'll be unexpected.
In every scripted creation of records, trap for errors on the New Record step to prevent the over-writing of existing data in subsequent Set Field steps. Then trap the Commit Record/Request step for errors, which are likely to occur if the table has field-level validation for uniqueness (for instance). (Delete the record if the commit fails.)
FileMaker can display multiple windows in preview mode (each window's mode is completely independent) however there is only one print setup (page size and orientation) for all windows.
Thursday, June 30, 2011
Wednesday, June 29, 2011
No substitute for uniqueness
A client has a web-based enquiries service. The back-end of this is MySQL and the data gets to the client through a web interface that generates a csv file. This csv file gets imported into the main FileMaker solution.
I recently created an importer that converts the flat-file web data to a format suitable for a modern relational database. The process is to import the web data into an empty table, clean up the blank and header rows, then compare it to an archive of all previously imported records to ensure it only gets processed once. After being imported into the main table the new load is imported into the archive table. The table did not have a real unique serial number primary key field, but it had what I thought was the next best thing, a timestamp field that resolved to seconds. I figured that this would serve the purpose nicely. This timestamp field was validated to be unique to ensure that the web data could only be imported into the archive table once.
It broke down in less than a month.
After a just couple of weeks of use the importer threw a 729 import error and stopped. Investigation revealed that the error was being thrown in the first import, into the empty table. How could that be?Error 729 is "Errors occurred during import; records could not be imported" and it usually occurs when field-level validation prevents records from being created, usually because of duplicates. So how could the unique timestamp field be preventing the errors from being created when the table is initially empty?
An examination of the raw csv file revealed that the timestamp field in that particular batch was NOT unique: somebody managed to hit the submit button twice in the same second!
Moral: if you need a unique field, use a field that is KNOWN to be unique and not one that that is only "probably" unique, even if that probability appears to be high.
The solution: well, there were really two problems and only one has a solution. The problem of the importer "not working" was solved by keeping the unique validation but accepting the error 729 and allowing the process to continue (after checking that the resulting found set was not empty). This will result in the occasional "duplicate" record in the web data not being imported into the database, but it's not a really problem because it is almost certain the duplicate record is the result of a double-submit and will be duplicate data anyway.
Or will it? Have I solved the problem or just replaced it with a sightly-less-likely one?
As it is, the timestamp is used as the unique identifier. Consider if (when) two different web users hit submit in the same second: the timestamp will be duplicated but the rest of the record's data will be different. The import validation will prevent the second timestamped record from being imported and consequently real data will be lost. To mitigate this risk, the unique identifier has been changed to a concatenation of the timestamp and the enquirer's name. This will reduce the likelihood of data loss to only those cases where where two people with exactly the same name submit data at exactly the same second.
Or will it? Have I solved the problem or just replaced it with a sightly-less-likely one?
As it is, the timestamp is used as the unique identifier. Consider if (when) two different web users hit submit in the same second: the timestamp will be duplicated but the rest of the record's data will be different. The import validation will prevent the second timestamped record from being imported and consequently real data will be lost. To mitigate this risk, the unique identifier has been changed to a concatenation of the timestamp and the enquirer's name. This will reduce the likelihood of data loss to only those cases where where two people with exactly the same name submit data at exactly the same second.
Which leads to the second problem, that of uniquely identifying the web records to prevent multiple imports. Alas, without a real unique primary key in the web database there is no bullet-proof solution. The concatenated timestamp field is an improvement but it's not infallible.
Wednesday, June 22, 2011
Primary Keys and ID Codes
Clients often ask for "codes" so that they can easily identify accounts, invoices and the such. This post is about creating such codes.
The first property that codes need is that they need to be unique. They should also be unambiguous, so avoid any code that includes both zero and the letter O, the letter I and the number 1.
The story so far: unique, unambiguous.
I have one client who had "codes" for courses that were over 10 characters long which made them about as easy to work with as a software product activation key. Partly this was because they wanted the codes to contain embedded information about the course and venue. This is not necessary for the code to function: their purpose was to be typed into a web enrolment for by end users, and the usability was suffering badly because of their length. Also, they tended to be lots of 1s and 0s so reading them was difficult. So another property is that the codes should be as short as practical.
Update: unique, unambiguous, short.
So why not use a simple serial number for the code? Because if sequential serial numbers are used, a typing entry error might mean that the resulting code matches another code: the incorrectly entered code might match a different code, and create an undetected error. Thus there is an additional property for codes: the code needs to be errorresilient detecting. (I'm not sure the word "resilient" is correct but I cannot think of another: what I mean is that the simple mis-typing of one code should not match another code. It might be "error detection".)
Unique, unambiguous, short, error detecting.
Fortunately, such technology already exists and is easy to implement: check digits. The last digit of your 16 character credit card number is actually a check digit for the previous 15. So changing the last number of a credit card won't create a new valid credit card number, and similarly a typo in any of the other characters has a low probability of creating another valid number.
The codes I'm creating don't need the security of a check digit, but adding an extra digit to an already unique serial number makes another uniquer code that is no longer as susceptible to typing errors because the resulting incorrectly entered code las a low probability of matching a real code.
In one client's system I've just created such codes for accounts, courses and students. These codes are the primary key (a simple serial number) plus a Luhn check digit added to the end. This creates a unique number (they were unique anyway) that is now non-sequential so simple typos don't easily match other keys. These have proven very effective as the codes that end users entered into a web enrolment form, and replaced the previously 10+ digit codes used last year. Simply typing the next number won't result in somebody else's code.
Examples:
primary key = 8098, Luhn check digit = 6, code = 80986
primary key = 8099, Luhn check digit = 4, code = 80994
It looks as though this method will break after serial number 9999, but it doesn't. Remember that every serial number gets a check digit added, so there will never be any clashes and the method won't suddenly run out of numbers. The serial number 10000 will be come 10000X (where X is the Luhn check digit); the serial number 10000X becomes 10000XX. All will be unique.
Note that this method is used for generating a "code" field. These are fields that are displayed for the benefit of the end users and are only used on-screen or printed. This code is NEVER used for internal relationships: this is what the serial number is used for.
For variety it's possible to use the check digit to create an alpha character instead of a digit, and the new character need not be put at the end. The simplest method to convert a digit to an alpha character is to use a Choose function. The FileMaker code snippet below will do this:
Choose( LuhnCheckDigit( primarykey ) ; "K" ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "J" ; "L" )
Note that in this code I have chosen not to return the letter "i" since it will be confused with "1". Also note that the letters don't have to be sequential or in any particular order. Finally, the "L" should never be returned because the Luhn check digit function should only return values between 0 and 9 inclusive. If "L"s start appearing in codes there is some real trouble afoot.
For variety this alpha code can prefix or suffix the primary key. Prefixing the key value makes the code change significantly while scrolling through the records because the first character bounces around the alphabet.
The LuhnCheckDigit() custom function is available here:
http://www.briandunning.com/cf/707
The first property that codes need is that they need to be unique. They should also be unambiguous, so avoid any code that includes both zero and the letter O, the letter I and the number 1.
The story so far: unique, unambiguous.
I have one client who had "codes" for courses that were over 10 characters long which made them about as easy to work with as a software product activation key. Partly this was because they wanted the codes to contain embedded information about the course and venue. This is not necessary for the code to function: their purpose was to be typed into a web enrolment for by end users, and the usability was suffering badly because of their length. Also, they tended to be lots of 1s and 0s so reading them was difficult. So another property is that the codes should be as short as practical.
Update: unique, unambiguous, short.
So why not use a simple serial number for the code? Because if sequential serial numbers are used, a typing entry error might mean that the resulting code matches another code: the incorrectly entered code might match a different code, and create an undetected error. Thus there is an additional property for codes: the code needs to be error
Unique, unambiguous, short, error detecting.
Fortunately, such technology already exists and is easy to implement: check digits. The last digit of your 16 character credit card number is actually a check digit for the previous 15. So changing the last number of a credit card won't create a new valid credit card number, and similarly a typo in any of the other characters has a low probability of creating another valid number.
The codes I'm creating don't need the security of a check digit, but adding an extra digit to an already unique serial number makes another uniquer code that is no longer as susceptible to typing errors because the resulting incorrectly entered code las a low probability of matching a real code.
In one client's system I've just created such codes for accounts, courses and students. These codes are the primary key (a simple serial number) plus a Luhn check digit added to the end. This creates a unique number (they were unique anyway) that is now non-sequential so simple typos don't easily match other keys. These have proven very effective as the codes that end users entered into a web enrolment form, and replaced the previously 10+ digit codes used last year. Simply typing the next number won't result in somebody else's code.
Examples:
primary key = 8098, Luhn check digit = 6, code = 80986
primary key = 8099, Luhn check digit = 4, code = 80994
As an added bonus I can look at the code and easily work out what the actual primary key value is by ignoring the last digit.
Note that this method is used for generating a "code" field. These are fields that are displayed for the benefit of the end users and are only used on-screen or printed. This code is NEVER used for internal relationships: this is what the serial number is used for.
For variety it's possible to use the check digit to create an alpha character instead of a digit, and the new character need not be put at the end. The simplest method to convert a digit to an alpha character is to use a Choose function. The FileMaker code snippet below will do this:
Choose( LuhnCheckDigit( primarykey ) ; "K" ; "A" ; "B" ; "C" ; "D" ; "E" ; "F" ; "G" ; "H" ; "J" ; "L" )
Note that in this code I have chosen not to return the letter "i" since it will be confused with "1". Also note that the letters don't have to be sequential or in any particular order. Finally, the "L" should never be returned because the Luhn check digit function should only return values between 0 and 9 inclusive. If "L"s start appearing in codes there is some real trouble afoot.
For variety this alpha code can prefix or suffix the primary key. Prefixing the key value makes the code change significantly while scrolling through the records because the first character bounces around the alphabet.
The LuhnCheckDigit() custom function is available here:
http://www.briandunning.com/cf/707
Tuesday, June 21, 2011
Great quote
"Once you're crazy and know nothing about numbers, the chances of finding something psychotic and hateful in a scrabble factory explosion are hovering just around 100%." - Penne Jillette, from the seminal article about the Wingdings font.
Wednesday, June 15, 2011
Cascade delete
I has an issue a couple weeks ago where I needed to import older records into an existing database. I had already planned ahead and left "room" in the primary key serial numbers for the older records, my only challenge was to get the older set of records BEFORE the newer ones.
Importing the older records will put them after the new ones. I therefore needed to (1) export the newer records, (2) delete them, then (3) import the older records, finally (4) import the newer ones again. Easy.
Except for the cascading delete.
Relationships in FMP can be set up to automatically delete child records when the parents are deleted. I like this feature because it enhances data integrity. However this is one instance where it needs to be worked around.
(I know other developers who insist that cascade delete is a Really Bad Thing because it's deleting data, but the data that's being deleted will be a record with a key value that points to a deleted master record: IOW it's dead data anyway. If the deletion of the master record was unintended the it needs to be recovered from a backup, and the deleted related records and be restored at the same time too.)
The step 2 above is where the cascading delete will automatically delete all the child records. In the case of this database this was several table's worth of data. I could have gone through and unchecked the cascade delete option from all the relationships. done the import, then added it again... but that was too stupid.
The solution was to NOT delete the records, but instead over-write them. Doing this is as easy as selecting the option in the Import dialog to "update existing records" and to add remaining records as new records.
Naturally, I tried the process first on a backup copy of the database, and it worked as advertised. It was a simple matter to export, delete, import the old then import the new. Much faster than changing the cascade delete option and much less error prone.
Importing the older records will put them after the new ones. I therefore needed to (1) export the newer records, (2) delete them, then (3) import the older records, finally (4) import the newer ones again. Easy.
Except for the cascading delete.
Relationships in FMP can be set up to automatically delete child records when the parents are deleted. I like this feature because it enhances data integrity. However this is one instance where it needs to be worked around.
(I know other developers who insist that cascade delete is a Really Bad Thing because it's deleting data, but the data that's being deleted will be a record with a key value that points to a deleted master record: IOW it's dead data anyway. If the deletion of the master record was unintended the it needs to be recovered from a backup, and the deleted related records and be restored at the same time too.)
The step 2 above is where the cascading delete will automatically delete all the child records. In the case of this database this was several table's worth of data. I could have gone through and unchecked the cascade delete option from all the relationships. done the import, then added it again... but that was too stupid.
The solution was to NOT delete the records, but instead over-write them. Doing this is as easy as selecting the option in the Import dialog to "update existing records" and to add remaining records as new records.
Naturally, I tried the process first on a backup copy of the database, and it worked as advertised. It was a simple matter to export, delete, import the old then import the new. Much faster than changing the cascade delete option and much less error prone.
Tuesday, June 7, 2011
40,000 since 16 December 2010
40,000... that's the number of e-mails since 16 December 2010 that one client has sent from a database I built for them last year.
The database automates the generation of personalised messages and optionally attaches a pdf containing information that is tailored to the recipient's enquiry. The pdf generation and e-mail sending is all performed by FMP. This automation has replaced a largely manual process that took days to complete.
Because the e-mail sending and pdf generation can take several seconds to complete, the database generates the message text and pdf then "queues" the message, rather than sending it immediately, thereby saving about 7 seconds each message. A server-side script on FM Server runs every 30 minutes and performs the task of actually sending the messages via SMTP to the mail server. This frees-up the user's computer much faster and thereby improves productivity. A pleasant side effect is that it's possible to review the queued messages and delete them before they get sent. It's also possible to select a queued message and send it immediately, and to re-send a sent message with a single click.
The limitation? FileMaker's Send Mail script step only supports plain text messages and a single attachment. This is fine for the business since all their fancy HTML mail campaigns are done through a third party.
The database automates the generation of personalised messages and optionally attaches a pdf containing information that is tailored to the recipient's enquiry. The pdf generation and e-mail sending is all performed by FMP. This automation has replaced a largely manual process that took days to complete.
Because the e-mail sending and pdf generation can take several seconds to complete, the database generates the message text and pdf then "queues" the message, rather than sending it immediately, thereby saving about 7 seconds each message. A server-side script on FM Server runs every 30 minutes and performs the task of actually sending the messages via SMTP to the mail server. This frees-up the user's computer much faster and thereby improves productivity. A pleasant side effect is that it's possible to review the queued messages and delete them before they get sent. It's also possible to select a queued message and send it immediately, and to re-send a sent message with a single click.
The limitation? FileMaker's Send Mail script step only supports plain text messages and a single attachment. This is fine for the business since all their fancy HTML mail campaigns are done through a third party.
Wednesday, June 1, 2011
Defaults and Exceptions
I'm currently working on a system for a performing arts company, and they wanted to set up next years 260+ courses in a spreadsheet and import them. The client has a huge issue with course setup because each year they spend several days playing around in a spreadsheet then another couple of days battling to import the data into the database. Any half-decent database is relational and importing data from spreadsheets is not a trivial process.
If the interface and data structure are well designed, entering the information directly into the database is faster and easier than entering it into a spreadsheet. This will then save the time wasted with data imports.
Working directly in the database also offers other opportunities for efficiencies that spreadsheets cannot. For instance, I discovered that next years courses will, to within experimental error, be the same as this years courses. The solution was to set up a process whereby the client finds the current courses, omits those they want to exclude, then clicks a button to duplicate them and assign them next year's start dates. The exceptions are handled by either adding or deleting a couple of courses manually. Using this new solution the 264 courses were created in less than 1 minute. The scripting took less than an hour.
The general principle is to create a set of defaults that meets the majority of needs, then manually handle the exceptions. In this case the defaults were the current courses.
If the interface and data structure are well designed, entering the information directly into the database is faster and easier than entering it into a spreadsheet. This will then save the time wasted with data imports.
Working directly in the database also offers other opportunities for efficiencies that spreadsheets cannot. For instance, I discovered that next years courses will, to within experimental error, be the same as this years courses. The solution was to set up a process whereby the client finds the current courses, omits those they want to exclude, then clicks a button to duplicate them and assign them next year's start dates. The exceptions are handled by either adding or deleting a couple of courses manually. Using this new solution the 264 courses were created in less than 1 minute. The scripting took less than an hour.
The general principle is to create a set of defaults that meets the majority of needs, then manually handle the exceptions. In this case the defaults were the current courses.
Friday, April 22, 2011
Junk Data
I am working on a new database for a client, the original was developed several years ago. A lot of effort has been put into the new version to make it easy to use and efficient. Part of this process has been identifying the business process that the database is supporting, and determining what data is required. Not surprisingly the old system has been collecting a large amount of data that is completely unnecessary.
However old habits die hard and one of the managers is upset that there is no place in the new system to enter all the data that they have been typing in for years.
The fact that data is still being collected (from paper forms and an existing web site) doesn't mean it should go into the database. It's not the issue of storage, it's the time wasted on data entry and support.
However old habits die hard and one of the managers is upset that there is no place in the new system to enter all the data that they have been typing in for years.
The fact that data is still being collected (from paper forms and an existing web site) doesn't mean it should go into the database. It's not the issue of storage, it's the time wasted on data entry and support.
Wednesday, April 20, 2011
Happy developers
I've been re-reading some classic texts, again.
Fred Brooks in No Silver Bullet observed way back in the 1960s that developers don't need specifications, they are happy to invent as they go along.
Fred Brooks in No Silver Bullet observed way back in the 1960s that developers don't need specifications, they are happy to invent as they go along.
Business Re-think Part 2.5
Following on from the previous post, here are what I see are the main challenges and solutions to implementing our new business model.
* The client has to know what problem they want to solve. (Business Process)
* We need agreement that the solution we propose will solve the problem. (Project Plan)
* We need to agree on the scope of the solution so that we know what to build, how much it will cost, and determine when the project is finished. (Scope and Specifications)
I'm becoming more and more convinced that some decent time needs to be spent defining the *problem* in detail. This includes the business process, but also includes exploring the "solution space" with quick proof of concept databases and rapid prototypes. This is required because people don't know what they want, but they know what the DON'T want when they see it. Rapid prototypes and story boards and diagrams are ideal for discovery.
FileMaker is an excellent tool for rapid prototypes.
* The client has to know what problem they want to solve. (Business Process)
* We need agreement that the solution we propose will solve the problem. (Project Plan)
* We need to agree on the scope of the solution so that we know what to build, how much it will cost, and determine when the project is finished. (Scope and Specifications)
I'm becoming more and more convinced that some decent time needs to be spent defining the *problem* in detail. This includes the business process, but also includes exploring the "solution space" with quick proof of concept databases and rapid prototypes. This is required because people don't know what they want, but they know what the DON'T want when they see it. Rapid prototypes and story boards and diagrams are ideal for discovery.
FileMaker is an excellent tool for rapid prototypes.
Tuesday, April 19, 2011
Wrong seat
I flew to Melbourne on Monday morning, from Sydney. An unremarkable 65 minute flight. Except that when I got up to get my luggage after landing I realised that I had been sitting in the wrong seat the whole time.
Thursday, April 14, 2011
Prototyping
One of the HUGE advantages of working with FileMaker Pro is the speed at which proofs of concept and prototypes can be churned out. There is no better way to decide an issue than to try it out, both for the developer and the client.
I'm doing a database that consists of organisations and the services they provide. The "users" of this database are telephone hotline staff. In a single day I was able to put together two very fast prototypes to test ideas for interface and get feedback from the users.
I'm doing a database that consists of organisations and the services they provide. The "users" of this database are telephone hotline staff. In a single day I was able to put together two very fast prototypes to test ideas for interface and get feedback from the users.
Monday, April 11, 2011
Interface Restrictions ≠ Data Security
Today I worked on a client's system where the developer had disabled export from the menus. They needed the data out. I got called in to fix the problem but it turned out that nobody had the full access password, and the developer was overseas on holiday.
After spending some time working through password possibilities, it occurred to me that the export restrictions were caused by the disabling of the command in the menus. So I tried the backdoor approach, and it worked. This highlights the difference between securing the interface, and security at the record level.
My "back door" method took only a couple of minutes to complete.
1) Create a new database file.
2) Delete the table that is created by default.
3) Add an external data reference to the main database using the user-level account.
4) Add a TO of the external table.
5) Create a layout to display the external table TO.
And voila, the entire data set is available via an interface with full menus. That's because the security was provided by the original interface, yet we've simply by-passed it. It can be fixed by disabling the ability to export in the user account privileges in the original file.
FMP 11 adds additional security by optionally requiring a full access password to allow the file to be added as an external data source.
After spending some time working through password possibilities, it occurred to me that the export restrictions were caused by the disabling of the command in the menus. So I tried the backdoor approach, and it worked. This highlights the difference between securing the interface, and security at the record level.
My "back door" method took only a couple of minutes to complete.
1) Create a new database file.
2) Delete the table that is created by default.
3) Add an external data reference to the main database using the user-level account.
4) Add a TO of the external table.
5) Create a layout to display the external table TO.
And voila, the entire data set is available via an interface with full menus. That's because the security was provided by the original interface, yet we've simply by-passed it. It can be fixed by disabling the ability to export in the user account privileges in the original file.
FMP 11 adds additional security by optionally requiring a full access password to allow the file to be added as an external data source.
Subscribe to:
Posts (Atom)