...
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.
...
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 now look at any of my objects by any value.
...
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.
...
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 / tennants) 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 / tennants). 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.
...
If this does not make sense - we’ll happily help you, or I’m sure you favourite AI LLM (ChatGPT) would get you your logic pretty quickly.
Now I have what I 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.
...
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.
...