How to calculate Salesforce field utilization with Node.js

Field utilization is, well, being able to see how much a field is used, not in the metadata, but in the data. e.g., how many records have value in this field?

Many apps exist to calculate this but let's figure out how we can do it ourselves with just a few lines of code.

So let's assume that we are passed a field variable, which contains the full field name, like Account.CustomerPriority__c.

Data structure to return

The first thing we need is a data structure to define the returned value that will help us populate a chart as shown above. This is what I used

let metrics = {
    field,
    object,
    totalRecordsPopulated:0,
    totalRecords,
    recordTypeCount:{}
 };

Then, we need to do is find out how many records exist in that object

let [object,fieldName] = field.split('.');

let restApi = restAPI(connection,logError);
let metadataApi = metadataAPI(connection,logError);

In the code above, I instantiated a client for the REST and Metadata APIs using sfdc-happy-api (but you could and maybe should use jsforce).

Then, we simply use an aggregate SOQL query to see how many records exist in the object that the field belongs. We have the total count in totalRecordsCount.

let countQuery = `select count(id) from ${object}`;
let countResult = await restApi.query({query:countQuery});
let totalRecordsCount = countResult.records[0]['expr0'];

Figuring out if the object has record types

Now, we need to check if this object has record types because if it has, we want the number of records with a field value to be broken down by record type, as shown in the picture above.

To do that, we can use the following SOQL query

let recordTypeQuery = `select developername from recordtype where SobjectType = '${object}'`;

let recordTypeResult = await restApi.query({query:recordTypeQuery});
let hasRecordTypes = recordTypeResult.records.length > 0;

Using Promise.all to query all records by record type

Now, if there are record types, we need to issue one SOQL query per record type, and see how many records meet the condition of field != null.  We can wrap all the SOQL queries inside Promise.all, that way, if the object has 10 record types, we execute all 10 SOQL queries in one go and not one by one.

 await Promise.all(

            recordTypeResult.records.map(async (recordType) => {
            
                let recordsQuery = `SELECT count(id) FROM ${object} WHERE ${fieldName} != null AND RecordType.DeveloperName = '${recordType.DeveloperName}'`;
                let recordsResult = await restApi.query({query:recordsQuery});
                let countRecordsWithValue = recordsResult.records[0]['expr0'];
    
                metrics.totalRecordsPopulated += countRecordsWithValue;
                metrics.recordTypeCount[recordType.DeveloperName] = countRecordsWithValue;
    
                return null;
            })
        );

        metrics.recordTypeCount.empty = totalRecordsCount - metrics.totalRecordsPopulated;

Very important to note that inside Promise.all we are using the map function along with async in order to return an array of functions. This is because you cannot call async inside a traditional for loop. This took me ages to figure out, so take note!

Special case for TextArea fields

Now, this would not work if the field type was TextArea because you cannot use those fields in SOQL queries. So, before we actually execute the above queries, we need to use the Metadata API's readMetadata call to find out what is the field type

let fieldDescribe = await metadataApi.readMetadata('CustomField',[field]);
let isTextArea = fieldDescribe[0].type == 'LongTextArea'

And if it is, then the query must be very different, here's what I did

let recordsQuery = `SELECT ${fieldName} FROM ${object} WHERE RecordType.DeveloperName = '${recordType.DeveloperName}' AND CreatedDate = LAST_N_MONTHS:6 ORDER BY CreatedDate Desc LIMIT 1000`;
let recordsResult = await restApi.query({query:recordsQuery});
let countRecordsWithValue = 0;

recordsResult.records.forEach(record => {
    if(record[fieldName] != null && record[fieldName] != ''){
        countRecordsWithValue++;
        localTotalRecordsWithValue++;
    }
})

Essentially I'm just getting the last 1000 records created in the past 6 months, and manually checking if the field has a value or not. This obviously gives an incomplete picture of the field population, but it's a reasonable tradeoff I think.

How utilization percentage is calculated

The percentage populated is then calculated like this

metrics.percentagePopulated = Math.round((metrics.totalRecordsPopulated*100)/metrics.totalRecords);

Finally, we can call our function in this way

let customField = 'Account.CustomerPriority__c';

let response = await getUsageMetrics(connection,customField);
console.log(response);

End result

And here's the object that we return, with all the information about how many records exist in that object, how many have a value in this field, and a breakdown by record type.

{
  field: 'Account.CustomerPriority__c',
  object: 'Account',
  totalRecordsPopulated: 36,
  totalRecords: 41,
  recordTypeCount: {
    Internal: 12,
    Partner: 15,
    Reseller: 6,
    Customer: 3,
    External_Vendor: 0,
    empty: 5
  }
}

Nice! You can see the rest of the code here.

In a future issue, I'll talk about how to get this format to be displayed nicely in the UI.