Custom fields can be known by various names in different applications - here are just a few that I am aware of.
...
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;
...
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.
...
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.
...
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.
...
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.
...
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.
...
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.
...
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
...
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.
...
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)
...
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;
...
Custom Fields In Category Objects
So, you want to filter dashboards by custom fields don’t you - of course you do!
...
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.
...
[[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
...
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.
...
| 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. |
...
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.
...
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.
...