Footballer Search Form

A footballer will play for one club at a time, however may move between clubs. A footballer will also be assigned a single nationality in this sample database, but in the real-world, people can have dual-nationality. The restdb.io sample table is called Players, with foreign keys to Position, Teams and Nationality.

We know from the ReST API what the end points and associated security keys we need to get started. We will always start by reading the data, then displaying it, before moving on to editing, creating and finally deleting data.

Differences from Previous Lookup Forms

This lookup form is different from previous lookup forms for three main reasons:

Search Criteria Component

Instead of just a grid with column filters, we will be using a search criteria component which will allow for multiple fields to be searched before populating the grid. This is particularly useful for very large data sets where pulling the entire dataset into the client-side device is not practical because it would take too long and consume too much computing resource.

Paginated Grid

We will be using a technique known as 'pagination' which is where pages of data consisting of a small number of records is pulled from the ReST API 'on-demand'. This drastically increases performance for large data sets reducing the time to load data and keeping client-side computing resource to a minimum. We can also filter and sort data on the server-side so that the grid is responsive at all times.

Paged Data Source Request

Of course the server-side ReST API end-point must also be capable of providing paged data sets. It will typically have URL parameters for specifying the number of records per page, and which page to return. It will know how many records are in the entire data set, as this is how the client-side can calculate and display paged data.

Example

This screenshot of the Footballer Searchform we will build shows the search criteria component above the grid component and how field values are selected before applying the search to the grid:

We will now show how this sophisticated entity lookup/search form is constructed.

Custom Variables for Security Keys

We have already set this up here.

Custom Variables for Key Fields

Data source requests will return a data table of rows with fields. Each row will typically have an identifier for example in this sample the Footballer or Player ID. This will be a long random unique string of characters or numbers generated by the back-end database when records are created.

In order to handle the selection of rows, we need to create a custom variable which will be dynamically set when the end-user selects a specific record in a form or a grid or a field. We will assign this variable to the key field in the data source request.

Because we know that the restdb.io Rest API request will return footballers, we previously created a custom variable called "Footballer-ID" which we will assign to the field later.

Search Criteria Data Source Requests

We know from the design of the sample ReST API that we want to provide the end-user with 5 fields with which to search footballers:

So far, we have created data source requests for all lookup fields except positions.

Positions

In the sample ReST API we created a position table in restdb.io:

Each player record is linked to a position. In order to therefore allow position to be used in a lookup field, we need to create a data source request.

New Data Source Request

Open App Studio, then open the data source configurator.

Create a data source, and a sub-folder hierarchy like this:

RestDB.io/Football/Players

Now create a new data source request called "Positions" using the URL:

https://restdb.trisys.co.uk/rest/position:

Click the Send Request button to return a list of positions and view in the Results-->JSON tab:

We can see that this request is returning all 4 positions.

Click on the Fields tab in order to manage field properties:

Now assign the _id field to the Footballer-PositionID variable. If this does not exist, create it using the custom variables configurator.

Paginated Data Source Request

We have previously created a data source request to integrate the list of footballers pulled from the ReST API with the Nationality form and the Club form. We will NOT be using this specific data set because it does not need pagination, whereas we will be needing pagination for the grid we will be using on this footballer lookup form.

Data Source Configurator

Open App Studio, then open the data source configurator.

Navigate to this sub-folder hierarchy:

RestDB.io/Football/Players

This unambiguously defines that we have a restdb.io data source inside which we have the Football industry depicted as a folder. We will now create a paginated data source request for footballers.

New Request

Create a new request using the drop down menu:

Name this request "Footballers Paged Search":

Edit Request Properties

Edit the new request properties to set the URL to be:

https://football-891b.restdb.io/views/ReadFootballersPaged?page=<#Page#>&rows=<#Rows#>

Note how we have a complex URL with arguments and custom variables? These arguments are defined by the sample ReST API.

It should now look like this:

Send Request

You should now test the request by clicking the Send Request button. You will be prompted to confirm the URL being requested:

Notice how the custom variables have been automatically replaced? You can edit these each time you send a request to test that the back-end ReST API is working as expected.

Results

After the ReST API request has been sent, the Results tab will be selected and the JSON tab will show the columns and this data:

We can see that only a page of footballers data from the ReST API is being returned, including the photo, nation flag and club badges as URL images. This is because our restdb.io view is able to map its media files onto a two-dimensional table for easy consumption by client-side applications.

The next interesting thing about this data set are these pagination fields:

{
    "DataTable": {
        "List": [
            {
                "TotalNumberOfRecords": 122,
                "PageSize": 10,
                "TotalNumberOfPages": 13,
                "PageNumber": 1,
                .....
            }
        ]
    }
}

This example tell us that there are 122 footballers in the entire data set, and that if the page size (number of records per page) is 10, then there will be 13 pages in total. It also says that this data set is page 1 i.e. the first 10 records of the 122 record data set.

Fields

If we scroll back up to the top and click the left Fields tab, we should see a list of the fields accompanying the data.

Clicking on the top right Fields tab will show these Available Fields in a vertical list to the left:

Fields can be re-ordered using the left drag icon, or removed using the right delete button. Selecting any field will show its corresponding properties to the right.

There are may fields in this complex paginated data set, so here is a table specifying how to configure those of interest:

Any of the fields not listed above should be set to be invisible.

Parameters

In order to allow the search criteria fields to be used in the data source request URL, we need to create a number of parameters which will be appended to the URL dynamically when the search is applied.

Each parameter field is the name of a URL argument e.g.

/ReadFootballersPaged?TeamName=Arsenal&Position=Defender

We effectively map these ReST API URL parameters to custom variables and have them dynamically injected into the URL when the search criteria is applied.

Use the Add Parameter button to add those below with custom variables, and the Add Sort Parameters to add Sort-Parameter-Name and Sort-Parameter-Expression.

These fields are documented as follows:

We will assign this data source request to both the search criteria and grid components below.

Footballers Lookup Form

Now that we have the custom variables and data source requests to facilitate searching for footballers, we need to create a lookup form upon which to display the search criteria and a grid showing all matching footballers.

Create Form

Open App Studio then select the Forms configurator.

Add

Use the Add button to create a new form called "FootballerSearchPagination". In the form properties make sure that this form is of type "Lookup":

You do not need to assign a data source to the form as we will do this in the form designer when we add a Grid component.

Click Apply to save the new form:

Design

Open form designer from the form properties popup, and drag a search criteria component into the first panel on the Main Top Region tab:

Click on the Search Criteria component to open the component properties. The Data Sources tab will probably popup a data source selection dialogue where you should choose this specific paginated data source request created earlier:

Click the Apply button now. Also Save the form design to persist these changes.

Search Criteria Configuration

Select the Search Criteria component on the form designer to configure it.

Label Text

Set the label text to allow easier identification of this later:

Additional Data Sources

Click on the Data Sources tab:

There is only the master data source configured. We now need to add an additional data source request for each of the lookup fields we want to use to search the footballers.

Use the Add button to add the following 3 additional data source requests:

Once these have all been added, we can now start assigning these to fields.

In order to 'play-safe' you should Apply your changes, then save the form design to persist these data source requests, then re-open the search criteria properties modal popup.

Search Criteria Tab

Click on the Search Criteria tab:

The Master Request should be set to the correct paginated data source request "Footballers Paged Search" we created earlier. We have no master fields listed. We must refresh the list using the Refresh button:

Master Fields

The fields should now be visible:

Re-Position

In the Master Fields left tab, the fields previously retrieved from the master request ReST API are displayed in the order you designed. Re-order these by using the up/down arrows to be the order you wish the fields to be selected by the end-user at run-time.

Note that the order of the columns displayed in the grid component will be configured later.

Delete Fields

Some of these fields can be safely removed if there are too many fields being displayed.

Text and Number Fields

We can search for any column which is text or number by assigning a custom variable. These are as follows:

Lookup

We will now configure the fields which we want to be available in the search criteria component at run-time. These should be set to be Visible and all other fields should be hidden. Note that ReST API's will typically use identifiers rather than free text URL parameter arguments. Our sample restdb.io ReST API uses ID's also.

Events

The Events tab is available on the left beneath the Data Sources tab:

This component has an Apply button displayed at run-time to apply the selected search criteria to any linked components by triggering the population event. This functionality can also be used to automatically populate dependent components after loading the previously persisted search criteria.

Check the Automatically Trigger Dependent Components check box to force the run-time Apply button fire automatically when the previously retained last search criteria is re-populated. This has the effect of immediately re-populating the attached grid when the form loads.

Apply

Apply the grid properties, then Save the form design to persist the form for later use.

Navigation Bar

We can now add this lookup form to the navigation bar from where it can be opened by the end-user.

Open App Studio then select the Navigation Bar configurator.

Football Industry

The group called "Football Industry" was already created here.

Footballer Search

When the Football Industry group is selected, add a form using the Add Form button. This will popup a modal dialogue where you can select your newly created FootballerSearchPagination form. Clicking Save will show the new form menu:

Change the Caption to "Footballer Search". Then press the Apply & Close button.

Test Search Criteria

We will now test that the Search Criteria component is working. Click on the Footballer Search in the Football Industry group on the nav bar to load the form.

You should check that the Position, Club/Team and Nationality fields can all be selected and show their respective lookups in the lookup drop down combo for example:

You should also check that all requested fields are available for selection:

Footballers Lookup Form: Grid

Now we will design a grid to populate all matching footballers when the end-user applies their search criteria.

Design Form

Open App Studio then select the Forms configurator, then select the FootballerSearchPagination form and Edit, then Design to open the form designer.

We will keep things simple, so open the Components toolbar and drag the "Grid" component beneath the Search Criteria component:

Click on the Grid which will open the "Select Data Source Request: Read" popup form. This is to encourage you to select the data source request which will populate the grid:

Select the "Footballers Paged Search"

Population

Choose "Another Component" in the Population Trigger field:

The Triggering Component field will automatically select the "Footballers Search":

Apply

Apply these grid properties, then Save the form design.

Test Latest Configuration

Click the Footballer Search in the nav bar to open the form, then select a nationality name, and click Apply. A record may appear:

This is indicative that the search criteria component is correctly wired up to the grid, however the grid columns need to be configured.

First thing to double check is whether the pagination columns are correctly setup?

These should be setup as shown:

Once these are re-configured if necessary, and operational, re-opening the form should start displaying data correctly:

You can edit your grid columns to set sizes of images, column widths and ordering to make the grid look exactly how you want it.

Tidy Up Form

We have the most important features working now, so the final things to do are to tidy the form up.

We do not need the containing panel to be visible, so open App Studio, edit the form design and edit the panel:

Hide the title, turn off the border, and turn off striped rows:

Apply the changes, then save the form design.

Test

You can now test your configuration by opening the Football Industry group in the navigation bar and selecting the Footballer Search item.

You should now see the lookup form with the search criteria displayed above the data grid:

Test that column filtering and sorting works as expected.

We will revisit this form later in order to configure drill down.

We are now ready to create a data entry form configured for creating, reading, updating and deleting footballer records.