FileMaker Data API Workshop – activity seven

Populate ‘latest clients’ table

We want to populate the table at the base of the page with the five most recently added contacts from the database. This means setting the _limit and _sort query parameters appropriately.

If you look closely at the Postman Get multiple records, limit, offset, sort example you’ll see that this pretty much solves this problem directly for us.

There's something pretty quirky that can happen with the FileMaker Data API which never used to be possible in the XML / PHP interfaces in the past - you don't always need a field to be on a layout to make use of it! When we did this task in Postman, we were able to order the records using the Created field without it being on the layout. And you may recall me mentioning this when we were setting the email address from Postman as well.

But in order to better see what's going on, and to be able to get the phone and email data from the records we need to add them to the layout.

Add the following fields to the ContactAPI layout:

Office Phone
Office Email
Created - not necessary from the Data API perspective but it'll be helpful to confirm you're getting the right records

In summary, records only need to be on a layout if you want the content of their fields to be accessible in the response from the Data API. Depending on your use-case, this might be a way to minimise the amount of metered data which goes through your server.

It doesn't matter in this activity if you're using jQuery or fetch as your transport layer because we don't actually interact directly with the FileMaker Data API, only with our performRequest method.

As part of the login process the function Clients.fetchLastFiveClients() is called, and it's that function which we need to complete to get the table to populate. You can find it in assets/clients.js.

The one piece that might cause some issue, if you're using the Postman example as a guide, is how to convert the sort array into something which can go into a URL. For that we need to use one of the JSON methods stringify which will take JSON structures and convert them into a string which can be used in a URL (amongst other places). See the MDN documentation for more on JSON.stringify.

In src/Controller/DefaultController.php you'll see that a check is done to see if the user is logged in, and if so a call is placed to $clientService->fetchLastFiveClients() which needs to be completed to request the client list from the FileMaker Data API. The result of that request is passed to the template templates/default/index.html.twig where the list is displayed (see lines 42 to 48).

The fetchLastFiveClients method is in src/Service/ClientService.php. Use the Postman example as a base, and the TODOs in the code, to help you complete this activity.

The only tricky aspect is the sort which when used as part of a GET request is rather odd because somehow you have to send an array of associate arrays in the url. First of all you'll need to use json_encode() to convert it from a PHP array into a string, and then you'll need to rawurlencode() that to ensure that it will be in the correct format for a URL.

Solutions

It doesn't matter in this activity if you're using jQuery or fetch as your transport layer because we don't actually interact directly with the FileMaker Data API, only with our performRequest method.

In assets/clients.js complete the fetchLastFiveClients to look like this:

fetchLastFiveClients: function() {
    let url = '/layouts/ContactsAPI/records';

    // add that we only want five records
    url += '?_limit=5';

    // Sort by creation order, this means passing in an array of arrays, which have the keys
    // 'fieldName' and 'sortOrder'
    let sort = [{
        'fieldName': 'Created',
        'sortOrder': 'descend',
    }];

    // now we need to convert this to a string to go in the URL - honestly, it's hinky! This method
    // works fine when combined with a _find because we would be POSTing this data, but when it's
    // being sent as a URL parameter it's just weird!
    url += '&_sort=' + JSON.stringify(sort);

    FileMaker.performRequest('GET', url, [], Clients.displayClients, App.error);
}

In src/Service/ClientService.php replace the fetchLastFiveClients method with this.

/**
 * @return array
 *
 * @throws Exception
 */
public function fetchLastFiveClients()
{
    // Start with the base layout
    $url = 'layouts/ContactsAPI/records';

    // add that we only want five records
    $url .= '?_limit=5';

    // Sort by creation order, this means passing in an array of arrays, which have the keys
    // 'fieldName' and 'sortOrder'
    $sort = [
        [
            'fieldName' => 'Created',
            'sortOrder' => 'descend',
        ]
    ];

    // and then url encoding the json encoding of that array - honestly, it's hinky! This method
    // works fine when combined with a _find because we would be POSTing this data, but when it's
    // being sent as a URL parameter it's just weird!
    $url .= '&_sort='. rawurlencode(json_encode($sort));

    // Make the call - we don't need any parameters for this because
    $clients = $this->fm->performRequest('GET', $url, []);

    return $this->extractClientData($clients);
}

< Back to activity six On to activity eight >

FileMaker Data API Workshop – activity seven