Count the number of relationships used on an object

[VinceFINET]

Count the number of distinct relationships used on an object across the Formula Fields, Validation Rules and Lookup filters.

This is an idea for now and a way to log how we could do this in the tool.

[VinceFINET]

For a specific object:

• get the formula in
  • Formula Fields
  • Workflow Field Update
  • Validation Rule
  • Workflow Rule
• for all the previous formula, clean them (quotes, double quotes, comments), extract the references, get the relationship for each ref, distinct them

As an illustration, on Case, you can get the formulas via the tooling API. And then you could run the following code:

let allReferences = []; 
let formulas = []; 
let COMMENT = /\/\*([^*]|[\r\n]|(\*+([^*\/]|[\r\n])))*\*+\//g; // RegExp to remove comments
// We are interested in formula fields only
CASE_DESCRIPTION.fields.filter((f,i)=>f.calculated && f.calculated === true).forEach(f=>{ 
     // From the formula, 
     //    - remove the comment with replace() and COMMENT regexp, 
     //    - then remove the escaped quote and double quote (that may interfere with the next step) with replaceAll()
     //    - then remove the static strings (between quote or double quote) with split(), filter() and join()
     //    - finally retrieve the references like [Object.]+Field with match()
     //    - remove duplicates with sort() and filter()
     let references = f.calculatedFormula?.replace(COMMENT, '').replaceAll(['\\"', "\\'", "\t"], '').split(/['"]{1}/).filter((v,i)=>i%2===0).join().match(/([\$:a-zA-Z0-9_]+\.)+[a-zA-Z0-9_]+/g)?.sort().filter((x, i, a) => !i || x != a[i-1]);
     formulas.push({label: f.label, name: f.name, references: references }); 
     allReferences = allReferences.concat(references) 
}); 
// Display the fields with label, apiname and references (object(s) and fields)
console.error(JSON.stringify(formulas)); 
// Clean the all references array to retain only the objects and not the fields
allReferences.forEach((v, i, a) => a[i] = v?.split('.').filter((v, i, a) => i<a.length-1).join('.')); 
// Display the distinct object references
console.table(allReferences.sort().filter((v, i, a) => v && (!i || v != a[i-1])));

[pgonzaleznetwork]

@VinceFINET another way of doing this could be:

1. Get all the fields
2. Figure out which ones are formulas (your code already does these 2 steps)
3. Then, for each field, query the MetadataComponentDependency to see what that field references, i.e what it depends on. This will give you all the fields referenced in the formula and their ids.
4. Once you have the field ids, you can query their TableEnumOrId and do a distinct count on them.
5. You could later match those ids to the actual object names via doing a global describe on custom object

This would require less parsing of the formula code.

[VinceFINET]

providing that the DAPI can give you the references for formula fields (it does it) but also Workflow Field Update value formula, Validation Rule error formula, Workflow Rule criteria formula, ...

[pgonzaleznetwork]

Yes, you are right, that would only be for fields. For workflows, you can do something similar to what I do in sfdc-soup

1- Get all the workflows related to a specific object, by using the object id

https://github.com/pgonzaleznetwork/sfdc-soup/blob/d784bc6b1fa4964e4f4a9ac6600c439760fdc1a2/lib/sfdc_apis/metadata-types/utils/workflows.js#L67

2- Once you have the workflows, use the readMetadata() call from the MDAPI, and then inspect the contents of the workflow metadata (in your case, to see if the object or relationship field is there)

https://github.com/pgonzaleznetwork/sfdc-soup/blob/d784bc6b1fa4964e4f4a9ac6600c439760fdc1a2/lib/sfdc_apis/metadata-types/utils/workflows.js#L126

[VinceFINET]

hi @pgonzaleznetwork

Just wondering if the DAPI would count multiple times the use of a hierarchical lookup in formula.

For example using case.parentId and case.parent.parentId count for two relationships right?

And maybe in that example the DAPI will count the use of the parentId field once….

[pgonzaleznetwork]

I don't think the DAPI supports this natively. And I wouldn't use it considering that it is going away.