Displaying Data

What is a grid?

There are three elements involved in answering this question, a Table, a View and the Grid itself. The illustration below shows the table at the centre, this is where the data is stored. Views are created on the table that allows the data to be seen. Tables can have multiple views that show different attributes. Finally, we have the grids, these are instances of the view that exist on a screen.

Tables vs. Views vs. Grids

Each item has its own properties which can influence how the data appears - this is detailed below:

Accessing Tables, Views and Grids

Tables can be found in the repository:

• Under the heading of your application in the tables section.
• Within any packages associated with your application.
• Under the All section within the repository. It is recommended that you only use data within your application or an associated package. However older PhixFlow applications may reference items in this way.

Views. Open the properties of any table and on the properties tab there is a Views section which lists all views associated with the table. These can be opened by double-clicking any of the views in the list. It is also possible to get to the view backing a grid via the grid's properties.

Grids et al. Grids can be selected on a screen and the grid properties will open. This is also true for other instance types such as pie charts and bar graphs. It is also possible to right-click on a grid to see the View by selecting Show the View Configuration. In the same way, a Show the Table Configuration option is also available that will open the table backing the view associated with the grid or any instance of a view such as a pie chart. 

Displaying Data Setup

The information provided above is very useful, but if you are new to PhixFlow it's helpful to see it applied in a practical example. We will look at making a number of changes that will show how this information gets used in practice.

Default Data Range

The Default Data Range defines what Recordsets to provide to the View, Form or Card Container. The option can be set at the table, view and grid level, each inheriting the data range from the item before. 

Setup

For our example, if you recall we save some changes to a company and the grid then only displayed the latest record. We need to see all the records.

1. Open the properties for the Companies table.
   1. Right-click on the grid and select Show the Table Configuration.
2. Under Analysis Options, find Default Data Range.
3. Select All.
   1.  Additional Information

      Select which recordset data is displayed.

      • All: displays the records from all recordsets. Recommended when recordsets are updated incrementally and therefore have a Period of Transactional or Variable.
      • Latest: displays the records from the latest recordset only.
      • Custom: displays the records from the set number of recordset periods stipulate once selected. This option is available for view and grids.

      Include History Records, is an additional Data Retrieval Option which stipulates whether to show historic records such as items that have been replaced. This can be particularly useful when creating a screen which displays changes to a record over time as History records will display all the changes recorded over the lifecycle of a record

   2. All views associated with this table will now inherit this setting as their default setting. It is possible to override this option in your view's settings see View.
4. Save your changes. The grid will update and show all records including the changes made earlier in the course. It is often a good idea when creating a new table to set it to display all data in this way.

Filtering

Filtering cascades down from the view to the grid. When a filter is created it is owned by the table and is listed in its properties. Note that screens can also have a background filter which impacts a form, as we saw earlier, we can set a filter on the screen to filter the results returned to only show the selected record.

There are two kinds of filters we use in Phixflow:

• Background Filter: This is applied and cannot be removed by the end user. There is no indication to the end user that this is applied.
• Default Filter: This is applied when a grid or chart loads but an end user can remove it. The name of the filter is applied to the grid or chart name to indicate it has been applied.

Configuring either filter follows the same process, and filters can be used interchangeably between the two. For our example, we will apply a background filter.

Setup

1. Open the Companies List screen.
2. Right-click on the Companies grid and select Show the View Configuration.
3. In the properties click  next to the Background filter. We will create a filter which hides rejected companies.
4. Settings
   1. Name, Approved Companies.
   2. Filter Details, Select an Option, Status.
   3. Enter Text, Approved.
      1. In a previous filter, we switched this to be a PhixScript box, here we want to check each record has the string value Approved in its Status column, therefore we leave this as a literal (sting/text value).
   4. 
5. Save your changes on both the filter and view.
6. The Companies grid will dynamically update and only display records with a Status value equal to Approved.

Sorting

The records are returned in the order they appear in the database which is typically by the primary key. We will set our sort order to be by alphabetical by Company Name, then by Industry and finally by updated time.

Setup

1. Right-click on the Companies grid and select Show the View Configuration.
2. In the properties click the  next to the Default Sort Order.
3. Settings
   1. Name, By Company, Industry and Date.
   2. IMPORTANT, click  Apply. This is necessary as the sort order must be created before we can add attributes to it.
   3. Click the  Attributes button.
   4. From the dialogue box that appears drag, CompanyName, Industry and UpdatedTime into the area below Sort Order Attributes.

      1. Sorting is applied in the order the attributes appear in the list. It is possible to drag them to change their order.

   5. In the row for UpdatedTime, in the direction column click the arrow. This will set the order for UpdatedTime to be descending, resulting in the most recent records will appear first.
4. Save all your changes.
5. The Companies grid will dynamically update and sort the data based on our criteria.

Presentation

Column Width Setup

1. Click on the City column header to open the  Properties tab for the attribute.
2. In the Grid Settings section we can set the Grid Default Width to be a specific size.
   1. In PhixFlow version 11.1 onwards, Grid Settings can be found on the  Styles tab
3. Set this to 80.
4. Save your changes.
5. Alternatively, it is possible to simply drag the columns to the desired width.

Flexible Column Width Setup

We can set columns to make their size flexible so that they take up the available space. This is useful for columns that are large.

1. Right-click on Address.
2. Select Set Flex Column.
3. This will set this column to consume the available space in a grid. As we have lots of columns there is not very much available space. This will change as we move through this chapter.

Hiding Columns

Some columns are useful to have access to for filtering such as the CompanyID, but less useful to an end user. Rather than removing this column, we can simply hide it from the user.

1. Click on the CompanyID column on the grid.
2. In the properties go to the Grid Settings section.
   1. In PhixFlow version 11.1 onwards, Grid Settings can be found on the  Styles tab
3. Tick Always Hidden.

Conditionally Hiding Columns

It is possible to conditionally hide columns. For example, we could set an action to switch a view between a simple and an advanced view. This could be achieved by using a button on our screen that sets a value in a hidden field, viewType, to be either simple or advanced. The field can then be referenced using, _form.viewType. To conditionally hide the Address column, we would then write in the Hide If evaluates to True field to _form.viewType == "simple". This field uses PhixScript expressions to find out more see, Expressions and PhixScripts. We will not set this up for our example.  

Freezing Column

The first column, or columns, can be frozen so that if a user scrolls these columns stay in view. This is particularly useful for large complex grids.

1. On the Companies grid, right-click CompanyName.
2. Select Freeze Column.
3. This column will remain in view when scrolling right.
4. To remove the freeze, right-click the column.
5. Select Unfreeze Column.

Labels

Column headers on a grid take their value from the attribute they represent on a table. These names are not always user-friendly names or clear in purpose to a user. For this reason, PhixFlow enables a label to be set on a column:

1. On the Companies grid click Web.
2. In the properties in the Basic Settings section, set Label to Website.
   1. The label can also be set using an expression. This relies on a PhixScript expression returning a string value, to find out more see, Expressions and PhixScripts.

Formatting Column Data

As we have seen with form fields it is possible to set formatting for an attribute:

1. On the Companies grid click Turn Over.
2. In the Basic Settings section, click the drop-down in Number Format and select P_SG - Currency GBP (£) Millions.
3. In the Backing Attribute section.
   1. Click the Name link above TurnOver.
   2. This opens the backing attribute on the table. We need this as we will amend the data type to be a decimal. Note that it is not always possible to switch data types.
   3. In the Type field, change this to Decimal.
   4. In the Decimals Places field, change this to 1.
   5. Save your changes and close these properties.
4. Save your changes to the view attribute and the grid will refresh.
5. This is a shorter format, to see its configuration click the Number Format hyperlink (the Number format label itself) and its configuration is shown.
   1. Note this is a shared format that could be used in other locations. Therefore it is not advised to edit this configuration, if you do require a new format we recommend you create a new one.

It is possible to set other formats such as dates. Click on any of the grid headers to see the formatting options in the basic settings section and update them to suit.

Column Order

Columns in a grid can be reordered by dragging them to the desired location or changing the Order value in the column properties. Take a moment to order your columns to suit your requirements.

Adding Columns / Attributes

Adding Existing Attributes

To add an attribute which exists on a table.

1. Right-click the Companies grid.
2. Select Add Attributes to "Companies".
3. The attribute selection dialogue pops up. Here we can select any attribute from the table or related tables.
4. In the Related Tables section, click ISOCountryCodes.
   1. The attributes for this table are displayed.
   2. Drag Country Name onto the Companies grid.
   3. Close the popup.
5. The attribute is added to the end of the grid. Move it to just after Country Code.
6. Note that there is a link icon on the column header. This indicates the attribute is a relational attribute from another table.

Removing Attributes from a View

1. Right-click the Companies grid.
2. Select Show the View Configuration.
3. In the properties, go to the View Attributes section.
4. Select CountryCode.
5. A delete button will appear on the section header. Delete this attribute. 

Add New Attributes

To add a completely new attrite it must be added to the table and then added to any views.

1. Right-click the Companies grid.
2. Select Show the Table Configuration.
3. In the properties, go to the Attributes section.
4. Click 
5. Add the following:
   1. Name, TelephoneButton.
   2. Expression, _NULL
      1. We set this to _NULL as good practice, to indicate to users and PhixFlow that the value is expected to be empty.
   3. Click  Apply.
      1. We will use this later to add an action that will make a phone call.
6. Add a second attribute:
   1. Name, IndustryIcon.
   2. Expression, _NULL.
   3. Click  Apply.
      1. We will use this later to add an icon for the specific industry.
7. Add a third attribute:
   1. Name, StatusIcon.
   2. Expression, _NULL.
   3. Click  Apply.
      1. We will use this later to add an icon for the company status.
8. Scroll down to the Views section.
   1. Double-click Companies to open the view configuration.
   2. In the View Attributes section click  Attributes.
   3. Add the new attributes added above.
   4. Move the attributes to positions 3, 4 and 5.

Check Point

At this point, your screen should look similar to the example below. Click on the image below to see a larger version:

Grid Icons

We can add icons to the grid to provide visual information such as the approved status of a company or an industry sector. Icons can be added to the view or the grid. Adding to the view means if you reuse the view the icons will appear on all grids that reference it. Adding to the grid means only this instance of the grid will have the icon(s). For our example, we will add a status and industry icon to the view. 

1. Download this zip file and extract the images somewhere you can easily find them: images.zip
2. Right-click on the Companies grid and select Show the View Configuration.
3. In the View Attributes section:
   1. Double-click the StatusIcon attribute to open its properties.
   2. Click on the Styles tab.
   3. Click  in the Formatting Rule section.
   4. Rule Expression, _current.Status == "Approved".
      1. The expression uses _current to access the record line, and Status provides access to the attribute's value.
         1. _current is an internal variable, see Internal Variables
      2. If this expression is true we will apply the selected style.
   5. Click  next to Style.
      1. Name, StatusIcon_Approved.
      2. Background Image,
         1. Click 
         2. Name, Tick.
         3. Click Select Image.
         4. Navigate to the tick.png file downloaded earlier.
         5. Click Open.
         6. Click  Apply and Close.
   6. Background Repeat, no-repeat.
   7. Background Position, center
      1. American spelling as this is CSS code. 
   8. Background Size, 20px 20px
   9. Click  Apply and Close.
4. We will now repeat these steps for the rejected companies using a second Formatting rule and style.
   1. It is recommended to set the Rule Expression to, _current.Status != "Approved", as this will catch everything that is not approved.
   2. Use the redCross.png image.
5. With your columns setup, we can now hide the column Status and set the label of StatusIcon to be Status.
6. Set the label for IndustryIcon to be Ind.

Testing

1. Earlier we set a background filter to hide non-approved records.
2. Remove the Background Filter from the view and set it as a Default Filter.
   1. 
3. This means we still obscure the rejected companies from the user but they can also be accessed if necessary by clearing the filter.
4. For the testing, remove the filter manually from the grid:
   1. Click  Filter
   2. Select Clear Applied Filter.
5. Scroll down the companies view and you should see both Approved and Rejected statuses with the appropriate icons. For Example:
   1.  

Additional Icons

Using the techniques shown above we will now apply icons for each industry type. There are 4 industry types each has an associated logo. Use the following logic to set the icons in the IndustryIcon column:

*This is a catch-all where there is an unrecognised industry value. Set the expression to be true and make this the first Formatting Rule.

Check Point

The screen created so far will look similar to this:

Grid Styling

We will revisit the subject of grid styling later in this course once we have learnt about styles. Grids can be styled allowing us to set fonts, row heights, colours and many other aspects of the grids.