Custom Fields in RAW Data

Reporting on custom field values can be complex in Vitruvi. If using the RAW endpoints, the custom field values are located in a separate data model from the entities themselves. All custom field values can be found here: /api/v1/raw/core/customfieldvalue/ but there are a number of important considerations when querying from this model.

1. This table can be very large. It is highly recommended that calls to it are filtered in the query to reduce the size of the data set.
2. The table contains the following columns: id, created, modified, value, settings_id, settings_parent, settings_name, settings_data_type, content_type_id, object_id.
3. For the purposes of reporting, only the following fields are essentially needed: settings_id, object_id, value.
   1. When calling the fVitruviPagedQuery function on this table, be sure to set the Fields list to only these values to keep the query response small.
   2. object_id will be the id field of the related data model (for example, designelement.id if looking for the custom field values for design elements).
   3. There must be a filter applied to the content_type_id field. As this table contains all the customfieldvalue's for all entities, it is important to filter the non-relavent content types out of the data set for performance. To get the list of content types, please see Power Query Content Types.
4. The settings_id will be referenced to the customfieldsettings model. If not all settings are needed, it is highly advisable to create a filter of settings_id's to reduce the size of the data that gets returned.

The settings_name field for the custom field settings is created automatically from the user entered label and cannot be changed in the future. This occasionally creates some confusion when a user then subsequently "renames" a custom field setting after creation as the label may no longer resemble the value in the settings_name field.

To overcome this, it is recommended that the settings_id's be joined to the customfieldsettings model and the label field be used. This will be the field that is used in the front end and that the end user will be most familiar with.

To get the list of customfieldsettings, an example query has been provided at the bottom of this page.

Custom Fields in _Query Data

If you are using the _query endpoints, the custom field values will be included in the same rows as the data for the related entity as a sub-field of JSON data.

However, unlike the data returned from the RAW endpoints, the JSON data for the customfieldvalues will use the settings_name as the key field as opposed to the settings_id.

Again, it is recommended that these fields be renamed to use the setting's label field from the customfieldsettings model as this will be the text that the end user will be most familiar with.

Missing Values for Custom Fields

It is important to note that Vitruvi does not store records for custom field values if no value has ever been entered for it for it. This can cause problems in PowerBI in a couple of ways:

1. If using the RAW endpoints, when the data is pivoted.
2. If using the Query endpoints, when the customfieldvalues sub-field is _expanded.

In both cases, the new columns are created automatically by PowerBI by using only the data in the preview view. This preview often includes only the first 1000 rows of data, and not a complete set of data and the list of custom field names will be incomplete.

To overcome this, the query used to pivot or expand the values may have to be augented manually to include the additional custom field setting IDs, labels or names. In this way, when the full query is performed, all the desired settings will be displayed as expected.

Example - Custom Field Settings Query

let 

    // Get the list of custom field settings
    Settings = fVitruviPagedQuery("/api/v1/raw/core/customfieldsettings", 3000, null, 
            {"id","data_type","name","label","content_type_id","network_operator_id"},
            {"Custom Field Settings Id","Data Type","Settings Name","Label","Content Type Id","Network Operator Id"}),

    // Get the list of content types
    ContentTypes = fVitruviPagedQuery("/api/v1/raw/contenttypes/contenttype/", 3000, null, 
            {"id","app_label","model"},
            {"Content Type Id","App Name","Model Name"}),

    // Join them
    MergeContentTypes = Table.NestedJoin(Settings, {"Content Type Id"}, ContentTypes, {"Content Type Id"}, "Content Type IDs", JoinKind.LeftOuter),
    ExpandModelName = Table.ExpandTableColumn(MergeContentTypes, "Content Type IDs", {"Model Name"}, {"Model Name"}),
    
    // some of the content types for the settings differ from those for the values. Rename them to make it match
    RenameElementCategory = Table.ReplaceValue(ExpandModelName,"elementcategory","designelement",Replacer.ReplaceText,{"Model Name"}),
    RenameWorkPackageTemplate = Table.ReplaceValue(RenameElementCategory,"workpackagetemplate","workpackage",Replacer.ReplaceText,{"Model Name"}),
    RenameWorkOrderTemplate = Table.ReplaceValue(RenameWorkPackageTemplate,"workordertemplate","workorder",Replacer.ReplaceText,{"Model Name"}),

    // after the above fields are renamed, the Content Type Id is not applicable anymore
    RemoveContentTypeId = Table.RemoveColumns(RenameWorkOrderTemplate,{"Content Type Id"}),
    // re-merge the content types table, this time on model name to get the new content type id
    MergeNewContentTypes = Table.NestedJoin(RemoveContentTypeId, {"Model Name"}, ContentTypes, {"Model Name"}, "Content Type", JoinKind.LeftOuter),
    ExpandContentTypeId = Table.ExpandTableColumn(MergeNewContentTypes, "Content Type", {"Content Type Id"}, {"Content Type Id"})
in
    ExpandContentTypeId