Custom Fields (Columns)

Custom fields can be known by various names in different applications - here are just a few that I am aware of.

1. Custom Fields

2. Additional Attributes

3. Analysis Codes

4. User Definable fields\columns

5. Multicolumn attributes

6. Tags

…… I could go on.

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.

So the data in our very simple example may look a little like;

I could and probably will create other columns above into my Panintelligence table, but I’m going to keep it simple here.

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 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.

Organisation 1 Stocks and sells Fish.

Organisation 2 Stocks and sells Fridges.

🐳 The Fishmonger wants to record;

• Fish Species

• Where the fish was caught

• Whether it was netted, or line caught

👩‍🔧 The Fridgemonger wants to record;

• The energy rating.

• The fridges height, width, depth.

• It’s capacity in litres.

😭 If you keep making changes to your application every time a customer would like a new field then it quickly becomes unmanageable.

1. It’s expensive, as it requires a software developer to make a change.

2. It is slow, and customer does not to wait.

3. It makes you application complicated, the fishmonger does not want to have to trawl (Sorry for the pun!) through a list of fields about fridges to capture the vital details about there fish.

So at some point you add flexibility to your application and allow a user of your application to add their own fields. In fact you probably allow them to add new fields to different tables within your application.

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.

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 name = ‘Fish Species’ as a filter and I get this data;

I can even rename the column headers so it is easier to read

So good so far, but now I want to add in where the fish was caught,

So I add another filter item in the list to say atrtibute name in (‘Fish Species’,'How Caught') and I get this back.

This is probably not what I want, I was probably expecting

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 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 a lot of solutions end up looking like this;

In Panintelligence - you’d add the attributes table multiple times as an alias. You’d make sure that the objects are in a category so that they are only visible to the relevant organisations.

You can now use the objects together, as filters, restrictions sorts. It looks simple to the customer. It’s a good solution.

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;

You can use merge’s, subqueries, Linked data connections, heck you can even generate everything through the pi API and in doing so make objects automatically created without any human intervention, you hear a pre-2024.09 pi consultant cry.

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.

1. Engineer Date Of Birth - They like to give their engineers the day off on their birthday.

2. Engineer In Probation - When a new engineer starts they have a probation period.

3. Engineer Dependants - How many dependants the engineer has. So they now that they may need more flexible shift patterns.

4. Product Colour - Not everything is white in the world of white goods.

5. Job Location Steps - Engineers hate steps, this lets them add more time to a job.

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.

We have 3 tables we will consider.

1. A USERS table - this holds details of the engineers, name, email etc.

2. A table name ADDITIONAL_ATTRIBUTES. This holds the definition of custom fields created within the service IQ application.

3. 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.

Essential

• The name of the attribute (Dependants, Birthday, Probation, Colour……)

• What the value is (2,16/08/1973,No,Pink……….)

• The foreign key that links it back to the core (USERS,PRODUCTS,JOBS) tables.

Good To Have

• The table it links to ie, 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 perfromance)

✍️ 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 recomend)

In my example I am going to create a view.

I create a view V_CUSTOM_FIELDS

It contains the columns I need (I have all 6 here, the essential and the good to have)

Feel free to ignore but the SQL (MySQL) that combines the 2 Attribute tables into the view looks a little like. Feel free to ignore my SQL.

create or replaceview `v_custom_fields` as
select
    `a`.`name` as `Custom_Field_Name`,
    `a`.`attribute_type` as `Custom_field_Type`,
    `a`.`entity` as `Custom_Field_Table`,
    `v`.`Attribute_id` as `Custom_field_Link`,
    `v`.`value` as `Custom_field_value`,
    `a`.`tennant_id` as `tennant_id`
from
    (`additional_attributes` `a`
join `additional_attributes_values` `v` on(`a`.`id` = `v`.`table_row_id`));

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.

All very standard so far.

Now I am going to add the user custom field for Birthday, Dependants and Probation. Normally I’d add these as three Alis’d tables. But this time I’ll add it once.

I open the metadata panel to examine the database as per normal. I find the table or view that contains my data, I then give it a nice Alias name (No spaces / No weird characters etc). Now I click on the type in the top right, and change the table type to Custom Fields.

🤔 Notice I have not selected any columns here like I normally would.

Then as usual I click generate.

Then I drag the table to where I want it (I can of course move it at anytime).

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.

Notice I can change it’s type here as well

But let’s select the Objects section

And click add object + on the right.

Ultimately it is the value that we want so I’ll check that.

And then generate.

This object view looks very similar to regular objects but there is an additional field for the Dynamic Attribute Column, and some non applicable fields are also removed.

I need two of the mandatory fields to be in my object.

So I my Example I end up with

Then we need to use the third mandatory item to join our table back to the Engineer (USERS).

For this we are going to put the custom field link in, and link it back to the id in the engineers 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.

I can start building a table as per normal

But notice in my data connection I have a folder called Custom_field_value

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.