Meet the Modern FileMaker Platform

FileMaker 18 was released on 23 May 2019. This is in line with the annual release strategy adopted by FileMaker Inc., since version 14 in 2015. So we can probably expect version 19 will be released in May 2020.

What’s New for FileMaker 18?

The official list of new features for each of the FileMaker products is in the online help:

  • FileMaker Pro 18 here
  • FileMaker Server 18 here

However, these lists are not usually complete – there are often lots of other minor changes in features and behaviours. The other thing that I have always grappled with on each new release is looking at a feature and asking “what can I do with that?” and “how would that work in my FileMaker solution?”.

Spoiler Alert: a new seminar may be of interest to you

Less Presents, More Often

With an annual release cycle, the FileMaker engineering team made major changes to the way in which it develops and releases new features. At any time, there are two or more release cycles in motion. At a certain time, features that will make the next release are moved onto the current release cycle; others are moved off onto the next cycle. This means that there can be a more responsive release methodology.

However, 12 months (or less in reality) is not long to develop, test and release new features. This means there are typically fewer groundbreaking new features in each release. This, in turn, has resulted in comments in the FileMaker Community like “disappointing release, nothing for me, not upgrading”.

There is a change in how we should see each new release. Instead of having major feature upgrades every 15-18 months, now we have ‘Christmas in May’ every year. Sure, this means less presents each time, but there is a steady stream of new stuff that comes through consistently. To continue the Christmas analogy, we may not get a new bike, but we get a couple of wheels to play with one year and then a frame to connect them the next. Eventually we can construct the whole bike, but the components are the latest technologies as they are released.

OK, so the analogy may not be great, but my point is this – with every new release, new features will build on existing features to make something that is even better. This contrasts with the old release cycles where we had to wait until something was almost fully baked before it was released. I am pleased to see FileMaker releasing new features that are not fully baked but still somewhat functional. I can start using them and then expand their use in a future release. Make sense?

FileMaker 18 Development and Experience

So what does the development world look like in FileMaker 18? As suggested above, it is the sum of many recent releases. The reality of regular releases is that you may often skip a version or two before upgrading again. So right now, you may still be using FileMaker 15 or 16. What have you missed in 17? And what might be useful in 18?

Wouldn’t it be useful to get an overview of the current state of FileMaker development? And wouldn’t it be useful to see some practical examples of use of this features that have crept into the FileMaker platform over the last few years?

Well we have great news – we have a full day seminar that you can attend!

Seminar – Meet the Modern FileMaker Platform

Meet the Modern FileMaker Platform is a presentation of the range of new features introduced in the last few versions. With yearly releases, some features are easily overlooked, while others become more useful with each version. We present the modern FileMaker platform and the new opportunities for development, user interface and data management. See the full seminar outline below.

This seminar is suitable for anyone who is familiar with the FileMaker Platform – users, administrators and developers of FileMaker database solutions. You may be using recently released FileMaker versions (16-18), or you might still be running an older version (15 and earlier).

This seminar is a presentation/demonstration format. Although you are welcome to bring a laptop, this is not a hands-on course and limited power outlets will be available. You should bring all your questions about the FileMaker Platform for discussion. You will receive comprehensive seminar notes, along with high quality presentations, demonstrations and discussions. The seminar day is fully catered with lunch included.

Seminar Outline

FileMaker Versions
• recent releases
• managing upgrades – requirements, compatibility, using new features
FileMaker Platform Products
• current, deprecated and past
New Features and Updates (v16, v17, v18)
• FileMaker Pro, FileMaker Server (incl. WebDirect), FileMaker Go
The Modern FileMaker Platform – demonstrations
• Developing in FileMaker Pro 18
• User Experience in FileMaker 18 clients
• FileMaker 18 hosting and connectivity
FileMaker Future – published roadmap

Seminar Schedule

At this time, we have scheduled the Meet the Modern FileMaker Platform seminar in three locations:

  • Sydney – Monday, 8 July 2019
  • Melbourne – Wednesday, 10 July 2019
  • Brisbane – Tuesday, 23 July 2019

Places are limited in each location. If we sell out in any location, we may schedule another seminar.

To book your place now, go to our FileMaker Classes page. The seminar cost is $450 (ex GST) for the full day with breaks and lunch. And there is a special early bird rate – save $100 if you book on or before 20 June.

We are considering more locations and possibly offering it online if there is enough interest. If you would like to attend the seminar but cannot get to one of the locations below, please email us with your request now.

Hope to see you at a seminar.

Isolating a single record

There are many reasons you might need to isolate a single record in a table. This post looks at methods to do this in FileMaker databases, along with pros and cons for each.

1. Find the current record

This method may be the most obvious – perform a find that will return only the current record. To do this, you need to search for something that is unique about the record. While there may be unique data in the record such as a product code or member number, it is always safe to search on the one thing that should always exist and must always be unique – the primary key.

The problem is that the primary key is usually not exposed to the user. So how would they perform a search on that field? The answer is a scripting method that does not require a field to be on the layout. Here is a sample script:

Set Variable [ $key; Value:event::ID ] 
Enter Find Mode [ ]
Set Field [ event::ID; $key ]
Perform Find [ ]

What we are doing here is first storing the primary key value in a variable. Then we get into Find mode and construct the request – setting the primary key field to search for the primary key value we put into the variable. Then perform the find to return one and only one record.

This same script can be written in just two steps:

Set Variable [ $key; Value:event::ID ]
Perform Find [ Restore ]

The Perform Find step has a stored find the same as the one constructed in the first example. Many developers see the first example as self-documenting code. In the second example, you would need to open the second script step options to see what was being restored.

Either way, you will always return just one record using this method. The downside is that this process must be scripted – there should be no user access to the primary key field.

2. Do the Omit dance

Over the years, this has been a popular way to isolate the current record. It uses the scripted sequence:

Show All Records
Omit Record
Show Omitted Only

The advantages of this method are the simplicity and that it can be performed manually with two shortcuts and a menu command. The downside is that there is an edge case where it can break in an active multi-user environment (returning more than one record). If you are scripting this, it may be useful to test that you have a found set of exactly one record before proceeding.

3. Go to Related Record via self-relationship

You could set up a special self-relationship from your main TO (table occurrence) to another TO of the same table. This relationship would use the match:

event::ID = event__same::ID

This is a match for all records with the same primary key (ID) value like the find we did in #1. You can use this relationship to isolate the current record using the script step:

Go to Related Record [ From table: “event__same”; Using layout: <Current Layout> ]

As for #1, this method will always correctly isolate the current record. The downsides are that it is requires a scripted process, and that you need to add a TO to your relationship graph.

Do No Harm

In all of the above, the aim of scripting will be to do no harm (or as little as possible) to the current working environment of the user. For example, if a user has a specific found set of records sorted in a particular order and is currently on the fourth record, that is where you would ideally return the user after they run a script.

In most cases, this is most effectively done by creating a new window and performing all following operations in there. At the end of the process, you can simply close the window and that returns the user to their starting environment.

Other Methods?

Do you use any other methods to isolate the current record? Post a comment below.

API Primer for FileMaker

What is an API and why should you care? APIs are the means by which your FileMaker solution can access all kinds of services and interface with other data stores. When you get a little understanding of this, the world of systems integration really opens up for you.

API stands for Application Program Interface. It is the interface by which an application accesses a service. It essentially provides an interpreter service translating requests into language the service can understand and returns the results requested. It means that the service can be treated as a black box – requests go in, unseen magic happens, and results come out.

The Restaurant

An analogy may be useful. You are a customer (application) seated at a restaurant. You are hungry and would like to eat, so you peruse the menu and decide on a steak.

Waiter taking an order (API receiving request)

You can’t go into the kitchen and cook it yourself or direct the chef to cook it for you. Instead, a waiter (API) takes your order. He or she interprets your specific requirements such as wanting it cooked medium-rare and serving the sauce on the side. The waiter then communicates the order to the kitchen (service) in a manner understood by them. The kitchen staff prepare the meal and ring a bell when it is ready.

Neither you nor the waiter know how it was done. Your meal is delivered to your table as ordered.

The reverse can happen when it comes to pay for your meal – the restaurant becomes the application requesting a result (payment) from you.

API Services

In our case, we are interested in accessing external services from our FileMaker solution. This may be to simply push or pull data, or to manipulate data for us.

Examples of services we may use:

  • send a text message by SMS
  • submit invoice data to an accounting application
  • retrieve the English version of some foreign language text
  • submit an ISBN to get all the data about a book
  • verify a credit card for processing

A lot of the services fit into the category of ‘not reinventing the wheel’ – it is far easier and more robust to access a service that is designed expressly for purpose, then to try to build and maintain it yourself.

Making an API Request

The basics of any API transaction are similar:

  1. establish identity and authority to make a request
  2. make the request providing data in the required format
  3. receive a result

Sometimes the result is as simple as ‘OK’. Sometimes it is a stream of data.

Example – Sending an SMS

In this example, we will send an SMS text message from a FileMaker database to a given mobile (cell) number using a service called ClickSend. ClickSend have a number of APIs – the one we will be using is their REST API – by their description it is the “Latest most powerful API with JSON request and response.”

1. Authorisation

To use the API, you need to create a free account. This sets you up with an API username and API key. These are used to authenticate yourself to use the service. It also authorises the service to use credits in your account to send SMS messages.

My username is davidhead and my API Key is of the form 5AF333BF-0D51-617D-4586-A1B1188006XX (not my real key).

The Authorization header is constructed by combining username and password as a string – username:password. This is encoded using Base64 encoding and the authorisation method and a space is put before the encoded string. Using the above username and key, you will get:

Authorization: Basic ZGF2aWRoZWFkOjVBRjMzM0JGLTBENTEtNjE3RC00NTg2LUExQjExODgwMDZYWA==

Side Note: if you use the FileMaker function Base64Decode on the encoded text, you will see the original username and key separated by a colon.

2. Make the Request

The ClickSend API documents for “Send SMS” detail the structure of the request including what data is required and in what format.

The HTTP request is a POST – data is being sent to the service. Another common method is GET – to request data from a resource.

The content is supplied in JSON format. A single message has a number of possible properties:

  • source – method of sending e.g. php
  • from – the mobile number that will show as the sender
  • body – the text message to be sent
  • to – the mobile number of the recipient
  • schedule – an optional time code to schedule sending of the message
  • custom string – your reference for the message

In FileMaker Pro, the request is made with a script step Insert From URL. In that script step, we can specify the URL to call, any options to be sent with the call, and the target for the result (a field or a variable).

The URL we call is known as an endpoint. The API documents tell us the base URL and the endpoint for each service. For the current ClickSend API, the base URL is https://rest.clicksend.com/v3/ and the endpoint for the Send SMS service is sms/send. So the URL we will send to is https://rest.clicksend.com/v3/sms/send

The options sent with the request are cURL options. These are the request type (POST), an authorisation header (encoded in Base64 as above), a content header (saying the content is in JSON format), and the message properties structured in JSON.

3. Receive a Result

The result is received back into the target defined in the Insert from URL script step. This may be a field or a variable. For the ClickSend API, the result is returned in JSON format. It contains a copy of all the data supplied as well as sending information such as message_price (0.077), status (SUCCESS), http_code (200), and more.

The result can be parsed into a FileMaker record to save the result of the message send operation.

Conclusion

This has been a brief primer on the use of an API in your FileMaker solutions. In this article, we have only considered one example of an API request from FileMaker. FileMaker Server also provides a Data API. This can be accessed by external services to push data into and pull data from FileMaker solution hosted by FileMaker Server.

Can we help?

Do you need assistance finding an API or integrating it into your FileMaker solution? Or do you just want to discuss the range of possible options for accessing the myriad of API services out there? We are here and ready to help you. Contact us today.


When spreadsheets don’t work

For many people, a spreadsheet is the “go to” tool for quickly storing data for a process or part of the business. It is so simple to open up a new Excel workbook, name your columns and start entering data. Before long, you have a whole swag of records entered and can safely save and file it away for later reference.

But spreadsheets are not really meant to be used as a database. Sure, they have functions like sorting and filtering but the basic record structure and integrity is just not there.

Many people think that spreadsheets are good for ‘crunching’ numbers. And they are. But a database can do that too.

There are clear cases where spreadsheets fail, and a database is the more appropriate tool. While there are many more, here are five cases where a spreadsheet will fail to perform as needed for a business.

Security

An Excel workbook can be secured with a password to open and a password to modify. However, there is no robust security model. This would allow for business rules to be applied to data to control creation, editing and deletion.

Multiple Users

An Excel workbook allows a single user access at any time. Even when that is reasonable, often users will have their own copy of the workbook and be making modifications independently. So there is no ‘master’ version that has all edits. Document management systems may be used to control the check out and check in of documents.

Business Rules

As suggested above, the lack of robust security means that business rules about access to data cannot be applied. Business rules also apply in basic data entry – validation of data and ‘reasonable’ values should be checked. Something as simple as ensuring that a piece of data is entered is not possible.

Validation of data is available in worksheets but it must be rigorously applied to individual (or groups of) cells. And the validation is fairly limited in scope to a range of acceptable values.

More complex business rules are often applied in the entry and maintenance of data. There may be a specific sequence of data required during stages of a process. This cannot be easily controlled in a spreadsheet.

Efficient Storage

One of the hallmarks of databases is the efficient storage of data. This means that data is stored in one location and leveraged in many. For example, the details of a customer can be used again and again for different projects. If the customer details are updated, they are effectively updated for every project. While this can be done across multiple worksheets with Lookup formulae, it is often the case that a user simple re-enters the data for convenience. Such redundant data is problematic for updates.

Reporting

Data in a spreadsheet is presented as a list. While this list can be filtered and sorted, it is more difficult to filter (hide) columns from a report, or to present the data in other ways such as mailing labels or summary reports.

Is a database a better solution?

Not always. But the very name “database” suggests that they are the tool that is actually designed for working with and presenting data. A database allows a flexible and efficient data structure that will allow you to produce the reports you need in the required format and layout. It will easily allow multi-user access and be available on a host accessible from many different devices in any location with Internet access. And access to the data will be secure with user accounts and encrypted data over the wire.

And that is just the beginning!

Do you want to know more?

Contact uLearnIT today and we can talk about a rapid process to build a working trial prototype based on your spreadsheet data. Through this process, you will quickly get to understand the enormous benefits and returns of moving to a database for managing your business data. Let’s talk!

Time to Innovate

FileMaker Inc., has positioned FileMaker as a Workplace Innovation Platform

What is a Platform?

FileMaker has long been viewed by developers as a platform rather than a product. In terms of databases, it is a client-server platform that revolves around the FileMaker Server. 

FileMaker Server provides fast, secure and reliable hosting for files, scheduled backups and processes, and web services. It is the host that can be accessed by various clients – FileMaker Pro on macOS and Windows, FileMaker Go on iOS devices, modern web browsers, and through an API interface. 

As well as being a client for accessing hosted files, FileMaker Pro also provides a development interface. Development is full stack – consisting of data structure, business rules, and user interface. The database engine is proprietary rather than being any flavour of SQL. 

Why the Workplace?

FileMaker started life with non-programmers developing solutions for personal use and in very small workgroups. Now FileMaker Inc., sees its main market in the workplace. It is still possible to buy single FileMaker Pro licenses for a premium price but most licensing sold is now annual subscriptions starting at five (5) users. 

Specifically positioning FileMaker in the workplace is a strategic move. This is in contrast to most Apple products that are marketed at both individuals first and businesses second. 

That said, FileMaker has a strong position in the workplace to be a very flexible and agile product used to solve all sorts of business problems. 

What is Innovation?

This is probably the biggest part of the equation. There is a lot of hype about workplaces being more innovative. But what does that mean? Can you just be more innovative? What does an innovative person look like? What do they do differently? How does their innovation help in the workplace?

Innovation can be seen in designing new ways of doing things. In business, this can save time and money, and create an advantage over competitors. So innovation can be creating more effective processes, products and services.

Being innovative can mean adapting to changes in the work environment to do things better. It should be part of a business strategy, with a culture of innovation encouraging innovative thinking and problem solving.

Measure

To know if innovation is making a difference, something needs to be measured before, during and after. Time and money can easily be measured. You could measure the time to complete tasks or the number of tasks completed in a given time. You could measure the cost to provide services.

But some measures are more subjective. Satisfaction is one example. By innovating, you could increase the satisfaction of both employees and customers. This could lead to higher staff retention (reducing recruitment and training costs), or increased customer referrals (for more customers). So the subjective satisfaction can then be measured with objective numbers. 

Can uLearnIT help you?

If you are looking to innovate and improve the effectiveness of your workplace, let’s have a discussion about where you are now and where you can improve your processes.

Any solution we develop needs to be able to be measured in its effectiveness and the ability to provide real value. Call now on +61 481 252 214 or email us at info@ulearnit.com.au. We look forward to helping you innovate.

FileMaker Training – Singapore 2019

We have just scheduled FileMaker training courses in Singapore in March 2019. Our FileMaker courses are hands-on with small class sizes. They put you on the fast track in FileMaker development. If you, or someone you work with or know, would like to become a better FileMaker developer, have a look at the course schedule now.

All courses listed below are currently open and are subject to minimum enrolments before they can be confirmed.

When you enrol, you will pay a holding deposit. This is non-refundable except if the course does not run. In that case, you will be notified and the holding deposit will be refunded in full.  The decision to run the courses will be made by 12 February. If the course is confirmed, you will be asked to enrol using a coupon code which will provide an additional $100 discount.

Get Started with FileMaker Pro

This introductory course is scheduled for 11-12 March 2019 (Tuesday-Wednesday).

Get Started with FileMaker Pro is an introduction to FileMaker Pro and creating custom database solutions. It covers everything from creating a new solution, setting up data structure, customising the appearance, securing and deploying your file and more.

Check the full course outline and book here.

Building Effective FileMaker Solutions

This intermediate course is scheduled for 13-14 March 2019 (Thursday-Friday).

Building Effective FileMaker Solutions is an intermediate course in FileMaker development. We briefly review FileMaker components and then dive into how they really work. The course covers development methods and habits, and a deep look at systems analysis and data structure for better outcomes. Over the two days, we cover many examples of calculations, scripting, security and user interface design. 

Check the full course outline and book here.

FileMaker Scripting in Depth

We are also offering an advanced course in FileMaker scripting, scheduled for 18-19 March 2019 (Monday-Tuesday).

FileMaker Scripting in Depth is an advanced course in FileMaker development. We will cover all aspects of script editing, management and use. The course covers scripting methods using various classes of script steps. Over the two days, we cover many examples of scripting that will be useful in your FileMaker development. 

The full course outline is available here and you can book here.

Training Courses – Brisbane, Sydney, Melbourne

We currently have confirmed courses running in Brisbane, Sydney and Melbourne. This means that you can plan your time with confidence knowing that the course will run.

Our training courses give you the fast track in FileMaker development. If you, or someone you work with or know, would like to become a better FileMaker developer, have a look at the course schedule now.

Get Started with FileMaker Pro

This introductory course is scheduled and confirmed in:

  • Melbourne 1-2 November 2018
  • Brisbane 8-9 November 2018

It is also open on the schedule for Sydney 28-29 November 2018.

Check the full course outline here and book here.

Building Effective FileMaker Solutions

This intermediate course is scheduled and confirmed in:

  • Brisbane 18-19 October 2018
  • Sydney 29-30 October 2018

It is also open on the schedule for Melbourne 11-12 December 2018.

Check the full course outline here and book here.

NEW: FileMaker Scripting in Depth

We are also offering a new advanced course in FileMaker scripting. The full course outline is available here. And the schedule is open for:

  • Sydney 3-4 December 2018
  • Melbourne 13-14 December 2018

FileMaker Solution: logging usage – Part 2

At uLearnIT, we love solving your problems. Here is Part 2 of the latest problem solved for a client using our mentoring services. If you missed Part 1, it is here.

Request

In an existing FileMaker solution, we would like to know if certain scripts and layouts are being used. Over the years, the solution has been added to and updated in a haphazard manner and we would like to clean it up. How can we find out which scripts and layouts are in use?

Solution

From Part 1, the key to this solution is to automatically log the use of scripts and layouts by triggering a script. The script will record the name of the layout or script used and then create a log record. The log record will be created in a special table for the purpose.

Script use

The script to log script use will be called at the start of each script – using the Perform Script script step. It will pass the currently running script name as a script parameter that will be entered into a log record. To set up the script step in any script:

  1. Open the Script Workspace and open a script.
  2. Add the script step Perform Script to the start of the script.
  3. Specify the Log script use script.
  4. Set the Optional script parameter to Get ( ScriptName )
Perform Script with script selected and script parameter defined

The script used to Log Script Use is as follows:

New Window [ Style: Card; Using layout: “uselog” (uselog); Height: 100; Width: 100; Top: 0; Left: -1000; Dim parent window: No ]
New Record/Request
Set Field 
[ uselog::name; Get ( ScriptParameter ) ]
Set Field [ uselog::item; "script" ]
Close Window [ Current Window ]

In the script above, the operation is performed in a card window off the user screen. This ensures that it does not interfere with the user’s normal actions or that of any running script. Since the parent screen does not dim, the user should not notice the script run. 

The script creates a new card window and goes to a layout to create a new log record. It uses Set Field script steps to set the name field with the script parameter (in this case the script name), and the item field with the text string script.

Add the Perform Script step (simply copy and paste between scripts) to every script for which you want to log use. Over time as the solution is used, the log records build an account of script use. This can then be analysed to see which script are most commonly used and which scripts are rarely or not used.

Addendum

A suggestion tweeted by @tonywhitelive was to also record Script Parameter, File Name and Layout Name when logging scripts. 

The file name would be useful when logging scripts running outside of the file where the log record exists. The script parameter and the layout name will provide some useful data about how and where the script was triggered.

So we will need to add three more fields to the log record – parameter, file, layout. And then adjust the script logging script to unpack and apply the various values.

When calling the new script, the parameter will use the List function to capture multiple pieces of data as a list of values:

List ( Get(ScriptName); Get(FileName); Get(LayoutName); Get(ScriptParameter) )

These values can then easily be unpacked when received using the GetValue function.

Parameter values unloaded into variables and then used to populate log fields

So there you have it – even more functional script use logging. Thanks Tony!

Do you need uLearnIT to solve a problem?

We can do the same for you. Just let us know what you are struggling with and we will provide a cost effective solution. We have saved our customers hours of Googling answers and frustration trying things that never worked for them.

We can do the same for you.

Contact us now before you waste any more time!

FileMaker Solution: logging usage – Part 1

At uLearnIT, we love solving your problems. Here is the latest one solved for a client using our mentoring services.

Request

In an existing FileMaker solution, we would like to know if certain scripts and layouts are being used. Over the years, the solution has been added to and updated in a haphazard manner and we would like to clean it up. How can we find out which scripts and layouts are in use?

Solution

It is unreliable and unwieldy for users to manually log their usage. Often, they do not know which scripts and which layouts they use. If they report the use of buttons, then they also have to report which screen and then the developer has to decipher the use.

The key to this solution is to automatically log the use of scripts and layouts. This can be done by triggering a script. The script will need to record the name of the layout or script used and then create a log record. The log record will be created in a special table for the purpose. The log record will have fields for:

  • item (type) – layout or script
  • name – of the layout or script
  • account – name of the account currently in use
  • creation – timestamp when created

The item will be set by the script; name will come from the script parameter; account and creation will be auto-entered.

Layout use

The script to log layout use will be triggered OnLayoutEnter – after a layout is loaded. It will pass the layout name as a script parameter that will be entered into a log record. To set this trigger for any layout:

  1. Navigate to the layout and enter Layout mode.
  2. Select Layouts > Layout Setup… and then Script Triggers.
  3. Select the OnLayoutEnter event and select the required script.
  4. Set the Optional script parameter to Get ( LayoutName )
Layout Setup – Script Trigger configuration

The script used to Log Layout Use is as follows:

New Window [ Style: Card; Using layout: “uselog” (uselog); Height: 100; Width: 100; Top: 0; Left: -1000; Dim parent window: No ]
New Record/Request
Set Field 
[ uselog::name; Get ( ScriptParameter ) ]
Set Field [ uselog::item; "layout" ]
Close Window [ Current Window ]

In the script above, the operation is performed in a card window off the user screen. This ensures that it does not interfere with the user’s normal actions or that of any running script. Since the parent screen does not dim, the user should not notice the script run. 

The script creates a new card window and goes to a layout to create a new log record. It uses Set Field script steps to set the name field with the script parameter (in this case the layout name), and the item field with the text string layout.

Repeat the layout script trigger for every layout for which you want to log use. Over time as the solution is used, the log records build an account of layout use. This can then be analysed to see which layouts are most commonly used and which layouts are rarely or not used. 

Use log for layout items

See Part 2 for scripts…

In Part 2 of this post, we will look at how to log the use of scripts in a solution. It is a simple extension of the above method for layouts.

Do you need uLearnIT to solve a problem?

We can do the same for you. Just let us know what you are struggling with and we will provide a cost effective solution. We have saved our customers hours of Googling answers and frustration trying things that never worked for them.

We can do the same for you.

Contact us now before you waste any more time!

FileMaker Solution: entering partial dates

At uLearnIT, we love solving your problems. Here is the latest one solved for a client using our mentoring services.

Request

In our Equipment table there is a field for DOM (date of manufacture). It is defined as a text field since the data is just a month and a year such as 03/10 or 11/15. We would prefer the service techs to enter the data as written. We don’t want them rejecting the process because it’s uncomfortable.

But then we need to perform simple calculations on the DOM. For example,  if the DOM is more than 5 years past, a “Level 4” service is due.

Solution

Part 1: Data Entry

It would be great to allow users to enter a month and year as the DOM in a number of ways. So let’s allow them to enter say March 2015 as any of 315, 0315, 3/15 or 03/15. And then reset the data on field exit to the standard 03/15. 

This is done with a field option which is an auto-entered calculation. Importantly, the option Do not replace existing value of field (if any) was unchecked – so that the calculation is evaluated when leaving the field.

Case ( 
Length ( Self ) = 3;
"0" & Left (Self; 1) & "/" & Right (Self; 2);
PatternCount (Self ; "/") and Length (Self) = 5;
Self;
PatternCount (Self ; "/") and Length (Self) = 4;
"0" & Self;
Length ( Self ) = 4;
Left (Self; 2) & "/" & Right (Self; 2);
"Error"
)

In this Case statement, there are four tests:

  1. if the length of the data is 3 characters, then add a leading zero and insert a slash character – so 315 becomes 03/15
  2. if there is a slash and the length is five characters, then leave as is – so 03/15 would be left as is (returns Self)
  3. if there is a slash and the length is four characters, then add a leading zero – so 3/15 becomes 03/15
  4. if the length is four characters, then insert a slash in the middle – so 0315 becomes 03/15

And if all those tests fail, it returns the result “Error”. That may happen if you enter 35 or 032015. 

Exceptions could be handled in other ways but it was not needed here.

Part 2: Calculate a proper date

Now that we have some standard data to work with, we can easily convert that into a proper date with a calculation. The decision was made to standardise on the first of the month.

So we set up a calculation field (DOM as date) using the expression:

Let ([
raw = DOM; // the field entered as MM/YY
themonth = GetAsNumber (Left (raw ; 2));
theyear = GetAsNumber (Right (raw ; 2));
theyear = theyear + 2000 - If(theyear >50; 100);
// convert to four digit
thedate = Date (themonth ; 1 ; theyear)
// date of first of month ]; thedate )

The process in the Let statement is:

  1. The field (DOM) is set into a variable called raw.
  2. Then raw is processed to get the month and the year – first and last two characters respectively. 
  3. Then the month and year are used to get the date of the first day of the month.
  4. The year is converted into four digits.
  5. The Let function returns the proper date (calculation result type is date)

QED

So that is the simple solution that lets techs enter the date quickly and easily, and then use it to move forward into other calculations to work out service requirements and more.

Do you need uLearnIT to solve a problem?

We can do the same for you. Just let us know what you are struggling with and we will provide a cost effective solution. We have saved our customers hours of Googling answers and frustration trying things that never worked for them.

We can do the same for you.

Contact us now before you waste any more time!