Custom fields can be known by various names in different applications - here are just a few that I am aware of.
...
However the good news is that they often follow a very similar pattern.
Why are custom fields a thing?
In our first example we have a system which sits over a database. In this database we have a main ‘core’ table. That is a table which holds data that is common to everyone who uses the system.
We have a product table.
This table holds information about the products that we sell. For example, the Products Name, and it’s Price. It is fair to assume that these are fields that all users of the system would require. There are probably other columns as well.
...
Now my software starts to become very popular, and I get lots of new customers (organisations / tenants). Each of these customers uses my software to mange manage the stock they hold. Each of them runs a business that sells different types of products. Every time I make a sale to a new customer they ask to add attributes or columns to my table to help them describe the products that they sell.
...
Fish Species
Where the fish was is caught
Whether it was netted, caught in a net or line caught
👩🔧 The Fridgemonger wants to record;
...
Life is great everyone is happy! except your Business Intelligence developer. 😖
Traditional Solution
How this is solved historically in Business Intelligence tools.
Pattern 1 - You just model the database as is
I Can can now look at any of my objects by any value.
So I create a query - imagine a table in Panintelligence, I put Product Name, Price And Attribute Value in the column list, and add atrtibute attribute name = ‘Fish Species’ as a filter and I get this data;
...
So I add another filter item in the list to say atrtibute attribute name in (‘Fish Species’,'How Caught') and I get this back.
...
Common Name | Price | Species | How Caught |
|---|---|---|---|
Cod | £2.89 | Gadus Chalcogrammus | Netted |
Herring | £1.27 | Clupeidae | Line |
Again, you could achieve this like it by creating two tables in Panintelligence and merging them.
Now say I want to create a filter for a customer because they want a list of all fish caught by line in Scotland. I’d have to again create multiple tables and use them as subqueries. Perfectly achievable in Panintelligence (and sometimes could the be the right solution), but all just a little too complicated.
This is not a great solution if you are wanting to enable your customers to be able to self-serve.
Pattern 2 - Bespoke Elements for each organisation
You end up having to create bespoke content for each organisation. You can short cut this in Panintelligence if you want to by creating linked data connections.
...
But. We’ve now introduced the issue we had in our own applications in the first place. Every time a customer adds a field, they need to ask for it to be added to the data connection. You can make this self service and use Linked data connections to allow each organisation to do it for themselves (can be a good solution). But but but…. That doesn’t fit with your application. They don’t need to do anything there, they just add it. Now they have an additional step!
Pattern 3 - You Pivot the data in the database
You can ‘pivot’ the data within your custom table to create columns within a new table/view. A common way to achieve this is by using UNION statements.
You would then end up with
As in Pattern 2, you could use categories - to show and hide columns from different organisations. This pattern has the same advantages and disadvantages as Pattern 2. It has a small advantage over Pattern 2 in that the config is simpler + it may have a significant performance advantage.
Common Flaws in the Patterns
Well it’s just the fact that they can ever be very limited (Pattern 1), or that they require manual intervention, but;
...
😇 Wouldn’t it be great if you didn’t have to do any of the above! - wouldn’t it be great if it just happened automatically. Shouldn’t a world exist where fishmongers and fridgemongers could live in a happy unison.
How to configure Custom Fields / Table
The good news is, there is a special table type that will automatically detect and create objects on the fly for your custom fields.
An Example
In this example we are going to build against the service IQ database. The Service IQ application manages teams of service workers on the road fixing ‘jobs’ and installing ‘products’. The Service IQ application allows users to add new fields to engineers, jobs, products etc. A customer (organisations / tennantstenants) within the application called Freezing Point, has added the following custom fields. Freezing point install, maintain and fix commercial fridges and freezers.
...
These custom fields are not common to other Customer (organisations / tennantstenants). They only have meaning to Freezing point.
The Database Structure
This database contains a set of structures called additional attributes. These can be joined to multiple tables. This may be a simpler or more complicated structure than you have, as it uses multiple tables.
...
A table ADDITIONAL_ATTRIBUTE_VALUES, this holds all of the values entered into the attributes. Here we can see entries about the engineers (USERS) number of dependants.
...
Your Structure Should Look Like
Now your structure is not going to be called additional attributes, and it probably has a different structure, but pi wants to know the following.
...
Good To Have
The table it links to iei.e, USERS, PRODUCTS or JOBS
The Column Type i.e. Date, String (text) or Number. …. Anything you can deal with types
A Customer / Organisation / Tenant ID - (You can use user restrictions - but this will help perfromanceperformance)
✍️ Now what you must do;
If these things exist in a single table for you then - great. You have what you need. If like in our example above - we (pi) need you to combine these into a single view / materialized view / table. (yes you can do an inline view / but we are going to this in the database itself which I highly recomendrecommend)
In my example I am going to create a view.
...
😍 Now we have what we need.
Adding a Custom Table to pi
I start by creating a normal data connection, adding tables in the standard fashion.
...
I can see that pi knows that it is a special type as it has a CF (Custom Field) in the top right corner.
...
Adding Custom Fields (Columns)
I can now click into the CF table.
...
For this we are going to put the custom field link in, and link it back to the id in the engineers engineer's table.
...
So my data connection looks something like this.
...
Building a Chart With Custom Fields
Now all the ‘complexity’ sits in the data connection, To our chart designers, they don’t have to worry about anything that we did above.
...
If I drop this down, pi automatically creates dimensions for any custom fields it detects.
I can click on these like any other dimension and add them to my table.
...
I can easily use the custom fields in chart as well.Folder
...
Folder Name
You may notice that the generated folder name in our example is not very pretty.
Custom_field_value is not very user friendly. This folder name can be easily changed by changing the name of the object in the data connection.
...
Then makes the list in the chart editor look much nicer.
Restricting By Custom Field Table Mapping
In our example we have all of our custom fields listed under the User Fields. This is not correct. Colour should not be here as it relates to Products.
If we remember the objects we specified.
| Info |
|---|
Essential
Good To Have
|
...
You will now have the correct lists that apply to the correct table.
NOTE: you may need to hit the refresh button jumping in and out and making change to the definition as we are here.
Multiple Custom Fields
Above we removed the Colour for Product. Lets add it back in linked to the product table.
...
Then my object list in the edit chart screen will look like
Using A Custom Field As a Measure
One of our object Dependants we may like to use as a measure. i.e. I want to know the total number of dependants for engineers in and out of their probabation probation period., (Don’t ask me why - just go with me!)
...
Now treat it as a measure, changing the object type to measure, The data type in this case to be a number, then use the data object type column to restrict via the where clause to a number. Also, we can set the Decimals and Separator etc. Notice that I have added a where clause now to the previous object to limit it to Strings and Dates (My decision about types).
...
Now I can use the objects to build my chart. The folder lists follow the colour conventions for dimensions (blue) and measures (red).
These objects can be used in any way you want (they just act like any other dimension or measure)
...
Making Sure Tenants Can Only See Their Own Objects
In a multi-tenanted environment we want to make sure that the fields we show are the ones allowed / relevant to the tenant.
This can be achieved using regular user restrictions
or
Restricted by variable.
User restriction limit objects, normal user restrictions apply.
...
User restrictions are observed / enforced when fetching any part of the data connection including the user fields.
Variable limit objects
Simply add the restriction referencing the variable / or variable conditions in the table where clause.
...
The variable could be on the pi user, role, organisation or global / any combination of these.
...
...
Where Can I
...
Use Custom Fields In Charts / Tables
...
?
I can use / manipulate custom fields in;
As Data points
In Filters
In Sorts
Additional Data
Colours
Drill Paths - Standard
Not Yet Dynamic drill paths
{{Variable}} replacements. ({{Employees+cf=gender}})
Chart Types - All
Custom Fields In Category Objects
So, you want to filter dashboards by custom fields don’t you - of course you do!
...
And they then operate as any other category object. You add default values / [[VARIABLE]] values as you would with any other category objects.
...
Temporary Category Objects
...
The custom fields are added as the they are in any other part of the application, so users can just add them as they require.
Passing Custom fields through the URL for external filtering
Custom fields can be controlled through the URL as per any other category object, the syntax is
[[User Fields+cf=Probation]]{{Yes}}. We use the same syntax in the URL as we do in {{object}} replacements using +cf to indicate this is a customer field.
So…
[[Object Name+cf=Custom Field Name]]{{Value}}
...
Custom Fields In A User Restriction
Any custom fields can be used in a user restriction. Add the object as per normal user restrictions.
...
And add the value - This could be a [[VARIABLE]] if required.
Considerations For
...
Performance
Custom fields creates create on the fly in line selects with the query from clause. This is a convenient way of adding dynamic data items, but could create an overhead.
| Code Block |
|---|
SELECT
Engineers.role AS TC4,
UserCustomFields_U1988n.Custom_field_value AS TC1,
CONCAT(Engineers.first,' ', Engineers.last) AS TC0,
SUM(UserCustomFields_U2314s.Custom_field_value) AS TC3,
UserCustomFields_U1869y.Custom_field_value AS TC2,
UserCustomFields_U1988n.Custom_field_value AS TC5
FROM
service_iq.users Engineers
JOIN (select *
from service_iq.v_custom_fields UserCustomFields
where UserCustomFields.Custom_Field_Table = 'Users' AND UserCustomFields.tennant_id = '3388e492-dbf9-11ec-bf45-0242ac170003'
and UserCustomFields.Custom_Field_Name = 'Probation'
and UserCustomFields.Custom_field_Type In ('String','Date')) UserCustomFields_U1988n ON Engineers.id = UserCustomFields_U1988n.Custom_field_Link
JOIN (select *
from service_iq.v_custom_fields UserCustomFields
where UserCustomFields.Custom_Field_Table = 'Users' AND UserCustomFields.tennant_id = '3388e492-dbf9-11ec-bf45-0242ac170003'
and UserCustomFields.Custom_Field_Name = 'Dependants'
and UserCustomFields.Custom_field_Type = 'Number') UserCustomFields_U2314s ON Engineers.id = UserCustomFields_U2314s.Custom_field_Link
JOIN (select *
from service_iq.v_custom_fields UserCustomFields
where UserCustomFields.Custom_Field_Table = 'Users' AND UserCustomFields.tennant_id = '3388e492-dbf9-11ec-bf45-0242ac170003'
and UserCustomFields.Custom_Field_Name = 'Birthday'
and UserCustomFields.Custom_field_Type In ('String','Date')) UserCustomFields_U1869y ON Engineers.id = UserCustomFields_U1869y.Custom_field_Link
WHERE
( UserCustomFields_U1988n.Custom_field_value IN ( 'Yes' ) )
GROUP BY
CONCAT(Engineers.first,' ', Engineers.last),
UserCustomFields_U1988n.Custom_field_value,
UserCustomFields_U1869y.Custom_field_value,
Engineers.role |
Indexing
In our own testing we have run items from tables containing 100k + rows.
...
| Info |
|---|
You will need to experiment on your database - and tweak for perfromanceperformance. It could be the case that using composite indexes including multiple columns could provide the most optimal queries for you. |
Number of objects
As every object creates an additional inline SELECT statement, if a query where to contain as large volume of these selects then this could also have a an impact on query perfromanceperformance. At very high volume you may still need to consider instantiating the data into a normalised Data Warehouse structure.
Additional Notes
When creating a custom table, you will notice that the table alias get gets enclosed in `backticks` regardless of the database types. Please leave these in place. Panintelligence uses these backticks as a reference point for table alias substitution.
...