Identify fields that reference other modules via the API

[DougVaughan]

We are trying to identify when a field references another application/questionnaire via the API. Related Records and Cross Reference fields can by identified by type, but when a field contains a calculation that refs to another application/questionnaire things become a lot more challenging. 

We've been focused on a way to retrieve the actual calculation from a calculated field via the API. I've been over the documentation and I'm pretty sure the answer is a solid "lol, not gonna happen". I would like a definitive answer though. 

Is there another indicator that would identify that a field contains an external reference that we can get through the API?

[DavidPetty · Accepted solution]

@DougVaughan if you are running 6.13+ you can use the internal (non-documented) API to get the list of calculations of an application, /api/V2/internal/ManageLevels(41)/CalculationDetail.  Just replace the 41 (Control Standards) with the module level id.

This is the output of the API for Control Standard calculated fields.

"@odata.context": "http://localhost/api/V2/Internal/$metadata#EntityModel.CalculationDetail",
    "LevelId": 41,
    "CalculationOrder": [
        {
            "FieldId": 3177,
            "FieldName": "Count of Controls",
            "Formula": "count( [Primary Controls] )",
            "Type": "Numeric"
        },
        {
            "FieldId": 3179,
            "FieldName": "Count of Objectives",
            "Formula": "sum( REF( [Policy], [Count of Objectives], [Section]))",
            "Type": "Numeric"
        },
        {
            "FieldId": 3180,
            "FieldName": "Count of Non-Compliant Controls",
            "Formula": "countif( REF([Primary Controls],[Compliance]), VALUEOF(REF([Primary Controls],[Compliance]),\"Non-Compliant\"))",
            "Type": "Numeric"
        },
        {
            "FieldId": 3181,
            "FieldName": "% of Non-Compliant Controls",
            "Formula": "IF( NOT( OR( [Count of Controls] = 0, ISEMPTY( [Count of Controls]))), round( [Count of Non-Compliant Controls] / [Count of Controls] * 100, 0))",
            "Type": "Numeric"
        },
        {
            "FieldId": 3183,
            "FieldName": "Criticality",
            "Formula": "if( [Criticality Override] = valueof( [Criticality Override],\"Key\"), valueof( [Criticality],\"Key\"), if( [Criticality Override] = valueof( [Criticality Override],\"Non-Key\"), valueof( [Criticality],\"Non-Key\"), if( [Count of Objectives] > 0, valueof( [Criticality],\"Key\"), if( [Count of Auth Sources] > 0, valueof( [Criticality],\"Key\"), if( [Control Weakness] = valueof( [Control Weakness],\"Yes\"), valueof( [Criticality],\"Key\"), valueof( [Criticality],\"Non-Key\"))))))",
            "Type": "ValuesList"
        },
        {
            "FieldId": 3182,
            "FieldName": "Compliance Rating",
            "Formula": "if( [Criticality] = valueof( [Criticality], \"Non-Key\"), valueof( [Compliance Rating], \"N/A\"), if( isempty( [% of Non-Compliant Controls]), valueof( [Compliance Rating], \"Open\"), if( [% of Non-Compliant Controls] >= 50, valueof( [Compliance Rating], \"Low\"), if( [% of Non-Compliant Controls] >= 25, valueof( [Compliance Rating], \"Medium\"), valueof( [Compliance Rating], \"High\")))))",
            "Type": "ValuesList"
        },
        {
            "FieldId": 4678,
            "FieldName": "Next Review Date",
            "Formula": "IF( ISEMPTY( [Published Date]), NOVALUE(), DATEADD( DAY, 365, [Published Date]))",
            "Type": "Date"
        },
        {
            "FieldId": 5092,
            "FieldName": "Inherited Record Permissions",
            "Formula": "This field defines which user and groups can access specific records within the application. Place this field in an appropriate location within the Field Calculation Order list to determine when to define access.\r\n\r\nTo learn more about this field:\r\n\r\n\t1. Go to the Designer tab\r\n\t2. Click the field name in the Object panel to display its Properties.\r\n",
            "Type": "UsersGroupsList"
        },
        {
            "FieldId": 10184,
            "FieldName": "Control Standard Criticality Value",
            "Formula": "SUM( SUM( SELECTEDVALUENUMBER( REF( [Authoritative Sources], [SubSection Criticality Value],[Sub Section]))), SUM( SELECTEDVALUENUMBER( REF( [Authoritative Sources], [Section Criticality Value],[Section]))))",
            "Type": "Numeric"
        },
        {
            "FieldId": 3178,
            "FieldName": "Count of Auth Sources",
            "Formula": "COUNTIF( Ref( [Authoritative Sources], [Criticality],[Sub Section]), VALUEOF( REF( [Authoritative Sources], [Criticality],[Sub Section]),\"Key\")) + COUNTIF( Ref( [Authoritative Sources], [Criticality],[Section]), VALUEOF( REF( [Authoritative Sources], [Criticality],[Section]),\"Key\")) + COUNTIF( Ref( [Authoritative Sources], [Criticality],[Topic]), VALUEOF( REF( [Authoritative Sources], [Criticality],[Topic]),\"Key\")) + COUNTIF( Ref( [Authoritative Sources], [Criticality],[Source]), VALUEOF( REF( [Authoritative Sources], [Criticality],[Source]),\"Key\"))",
            "Type": "Numeric"
        },
        {
            "FieldId": 24514,
            "FieldName": "Approver Review Task Subject",
            "Formula": "CONCATENATE(\"Approver Review for \",[Standard Name])",
            "Type": "Text"
        },
        {
            "FieldId": 24515,
            "FieldName": "Approver Review Task Description",
            "Formula": "CONCATENATE(\"Approver Review for Control Standard \",[Standard Name],\" is required.\")",
            "Type": "Text"
        },
        {
            "FieldId": 24516,
            "FieldName": "Approver Review Task Resolution",
            "Formula": "CONCATENATE(\"Approver Review for \",[Standard Name],\" has been collected.\")",
            "Type": "Text"
        },
        {
            "FieldId": 24517,
            "FieldName": "Reassign Task Subject",
            "Formula": "CONCATENATE(\"Reassign for \",[Standard Name])",
            "Type": "Text"
        },
        {
            "FieldId": 24518,
            "FieldName": "Resubmit Task Subject",
            "Formula": "CONCATENATE(\"Re-Submit for \",[Standard Name])",
            "Type": "Text"
        },
        {
            "FieldId": 24519,
            "FieldName": "Reassign Task Description",
            "Formula": "CONCATENATE(\"Reassign for Control Standard \",[Standard Name],\" is required.\")",
            "Type": "Text"
        },
        {
            "FieldId": 24520,
            "FieldName": "Resubmit Task Description",
            "Formula": "CONCATENATE(\"Re-Submit for Control Standard \",[Standard Name],\" is required.\")",
            "Type": "Text"
        },
        {
            "FieldId": 24521,
            "FieldName": "Reassign Task Resolution",
            "Formula": "CONCATENATE(\"Reassign for \",[Standard Name],\" has been collected.\")",
            "Type": "Text"
        },
        {
            "FieldId": 24522,
            "FieldName": "Resubmit Task Resolution",
            "Formula": "CONCATENATE(\"Re-Submit \",[Standard Name],\" has been collected.\")",
            "Type": "Text"
        },
        {
            "FieldId": 7575,
            "FieldName": "Default Record Permissions",
            "Formula": "This field defines which user and groups can access specific records within the application. Place this field in an appropriate location within the Field Calculation Order list to determine when to define access.\r\n\r\nTo learn more about this field:\r\n\r\n\t1. Go to the Designer tab\r\n\t2. Click the field name in the Object panel to display its Properties.\r\n",
            "Type": "UsersGroupsList"
        }
    ],
    "RecalculationSchedule": null
}

[DougVaughan]

Sweet! Using undocumented APIs is always dangerous. Any idea of how often this particular endpoint break?

[DavidPetty]

It's used by the Calculation tab in the application builder so it should be stable.  No guarantee if it doesn't change from release to release.

[DougVaughan]

Any other undocumented APIs you want to point me to?

[DavidPetty]

In regards to calculations, that the only that retrieves calculations that I know of.

Any React page you can use the developer tools > network to see the API calls the page makes and go from there.

[JeffLetterman]

@DougVaughan - regarding other useful internal API calls, check out the script and table in description for https://www.archerirm.community/t5/platform-technical-tips-tricks/act-collect-data-a-powershell-script-to-collect-metadata-like-a/ba-p/686500.

[DougVaughan]

Unfortunately, I do not have permissions to download that file.  

[JeffLetterman]

Here is the list of calls that script uses...

Undocumented Internal API (prefix URL with /api/V2/internal/)

Contract AppearanceThemes/GetActive Letterheads Notifications AdminNotifications GlobalNotificationSettings DataFeeds MasterReports ScheduleRules

Workspaces/Light Workspaces(Id)/Dashboards Workspaces(Id)/QuickReference Dashboards IViews/GetIViewList ManageModules ManageLevels(Id)/FieldRows ManageLevels(Id)/CalculationDetail

ManageValuesLists ManageValuesLists/Get ManageLevels(Id)/LayoutRows ManageLayouts(Id) ManageEventRules(Id) ManageLayouts(Id)/ActionRows ManageEventActions(Id) SecurityParameters(Id) AccessRoles