This week, we’re going to revisit a blog post from early 2018, Validating with the XBRL Object. This was a relatively straightforward script. It hooks itself into the validate function and adds an additional layer of validation for XBRL files. Having seen a couple of issues with blank element names in XBRL causing problems this past week, I figured it would help to create a script that enhances our default validation to detect these blank names and add in a temporary placeholder name so that it can be removed.
Friday, October 25. 2019
LDC #158: Validating for XBRL Custom Elements With No Name
The first question we have to address then, is what is a blank element name? If you’ve done any extensive editing in XBRL with GoFiler, you may have occasionally seen a message like this:
Warning: Row 122 - Element "" is not used in the report.
This message means that the underlying XFR file doesn’t have a name set for this particular custom element anymore. The element is not used, but since it has no name, it cannot be deleted from the sheet, because the delete function in regular XBRL will return an error if you try to delete an element with no name. This leaves us in a bit of a pickle. The only way to fix this is to open the XFR file up in text view, add an element name, re-open the XFR file, and then remove the element after we have given it a temporary name. This is a very simple task, and because we don’t really have to think about what we’re doing (there’s no decision making in this process, it’s always the same) it’s a prime candidate for Legato automation.
To understand what we need to actually change then, let’s look at the underlying XFR code. The XFR file is broken up into multiple presentations, each presentation containing data that mirrors a presentation in the XBRL report. GoFiler has a presentation specifically for custom elements in XBRL, creatively named the “Custom Fields” presentation. If we look into this presentation, each element is defined like this:
<ROW POS="121" FLAGS="0x00000414">
<CELL POS="A121">custom:WeightedAverageExercisePriceAbstract<ATTRIBUTES>,,"custom:WeightedAverageExercisePriceAbstract"</ATTRIBUTES></CELL>
<CELL POS="B121">xbrli:stringItemType</CELL>
<CELL POS="C121">xbrli:item</CELL>
<CELL POS="D121">Yes</CELL>
<CELL POS="E121">Yes</CELL>
<CELL POS="F121">duration</CELL></ROW>
This code represents the element “custom:WeightedAverageExercisePriceAbstract”. After the element name on the first cell, we can see the Attributes tag, and inside of it, we have a few fields. The only important one for our purposes today is the third one... this is the display name of the element. If the element is “broken”, and doesn’t have a name, that field will be blank. So our script is going to have to detect warning messages in our log, then open the file as text, go to the custom fields table, look for attributes tags, and find ones that have no value in the third column and put something in there. What we put in really doesn’t matter, as long as it’s unique, because the user will then remove the row with GoFiler afterwards.
This validation script is something I wrote a while ago, but when I did it I made it modular on purpose, so I could add extra validations to the XFR file. The function run_tests in the script is triggered when the user presses the validate button. Each test it runs is a separate function, so to add a new test all I need to do is modify the run_tests function to include a call to my new function, run_blank_custom_check. This means we only need to look at that new test and its sub function, fix_blank_elements, to have a complete picture of what was changed from the previous version to the new version, since the framework doesn’t need to be altered. Let’s start by looking at some new defined values that these functions use.
#define FIX_BLANK_Q "File contains blank custom elements that cannot be removed. Do you want to repair these elements? This will require closing and re-opening the file." #define BLANK_CUSTOM_ERR "Element \"\" is not used in the report." #define NEW_CUSTOM_PREFIX "custom:test%d" #define CUSTOM_PRES "NAME=\"Custom Fields\"" #define ATTR "<ATTRIBUTES>,,," #define ATTR_TEMPLATE "<ATTRIBUTES>,,\"%s\","
Defines like these are necessary because we want to be able to update our script easily if the XFR structure changes due to a software update. FIX_BLANK_Q is the question the user is asked when we detect this problem. BLANK_CUSTOM_ERR is the error message we are looking for in the log file. If we see this value in the log, we know there is a problem. NEW_CUSTOM_PREFIX is the template we’ll use for a new custom element name. The value doesn’t really matter; it can be anything, it just needs to be unique. To help with that, we put a ‘%d’ at the end of it, so we can use FormatString to modify it into a unique value. CUSTOM_PRES is the name of the custom fields presentation we’re searching for. We can’t just start doing find/replace operations unless we’re sure we’re in the right area, or we can break parts of the file we don’t want to. ATTR is the attributes string with three blank columns. If the row has a problem, it will have this code in it. ATTR_TEMPLATE is the string we’re going to use (with a FormatString function) to create a replacement for the ATTR define, to give our custom element a temporary name.
With the defines in place, let’s look at our new test function, run_blank_custom_check.
/****************************************/
void run_blank_custom_check(int f_id, string mode){ /* check for blank custom elements */
/****************************************/
... omitted declarations ....
window = GetActiveEditWindow(); /* get the edit window */
wType = GetEditWindowType(window) & EDX_TYPE_ID_MASK; /* get the type of the window */
if (wType != EDX_TYPE_XBRL_VIEW){ /* if the type isn't xbrl view */
return; /* exit */
} /* */
if (mode != "postprocess"){ /* if not postprocess */
return; /* return */
} /* */
s1 = GetMenuFunctionResponse(); /* response from validate */
log_tab = MakeHandle(GetParameter(s1,"InfoViewTab")); /* get the log tab */
log = InfoViewGetLogObject(log_tab); /* get log file */
if(IsValidHandle(log)==false){ /* if the log isn't a valid handle */
MessageBox("cannot get handle to log"); /* display error message */
return; /* return */
} /* */
The first thing our test must do is grab the active window, and test to see if it’s XBRL view. If it’s not XBRL view, we can just return, because we’re only interested in XBRL. Unlike most functions we’ve done, we also need to check to see if it’s running in anything other than postprocess mode. The validate function must have already been run so that we can look into the results. If it’s not postprocess, we can just return here and wait for the validate to finish. After the validate finishes running, we can use GetMenuFunctionResponse to get the result string from the validate. We don’t care about most of it, but there’s a parameter in the response string called “InfoViewTab”, which contains a text representation of a handle to the validation report in the Information View window. We can take this string and use MakeHandle on it to get a handle to the validation tab, and then use InfoViewGetLogObject to get the validation report within that tab. If we don’t have a valid handle, we can display an error and return, because we can’t go any further.
t_log = LogToTable(log,""); /* get the log table */
size = ArrayGetAxisDepth(t_log); /* get num rows in log */
for(ix=0; ix<size; ix++){ /* for each object in log */
if(IsInString(t_log[ix]["message"],BLANK_CUSTOM_ERR)){ /* if string contains blank custom err */
rc = YesNoBox('q',FIX_BLANK_Q); /* query user if fixing blank */
if(rc == IDYES){ /* if user presses yes */
fix_blank_elements(GetEditWindowFilename( /* get filename */
GetActiveEditWindow())); /* get filename */
return; /* return */
} /* */
else{ /* if user doesn't press yes */
return; /* return */
} /* */
} /* */
} /* */
} /* */
Now that we have our log, we can get the log as a table with LogToTable, and iterate over each row of it. If the line has the BLANK_CUSTOM_ERR that represents the error we’re looking for, we can ask the user if they want to fix it. If they click “Yes”, we can enter our sub function fix_blank_elements to actually repair the file, then return. If they don’t click “Yes”, then they either clicked “No” or closed the dialog, so we can just return without doing anything.
/****************************************/
void fix_blank_elements(string f_name){ /* fix the blank elements */
/****************************************/
... omitted declarations ...
/* */
RunMenuFunction("FILE_SAVE"); /* save the file */
RunMenuFunction("FILE_CLOSE"); /* close the file */
mtext = OpenMappedTextFile(f_name); /* open the mtext file */
size = GetLineCount(mtext); /* get the number of lines */
for(ix=0; ix<size; ix++){ /* for each line */
line = ReadLine(mtext,ix); /* read the line from mtext */
if(IsInString(line,CUSTOM_PRES)){ /* if this is the start of custom pres */
in_custom = true; /* we are in the custom pres */
} /* */
The fix_blank_elements function starts out by saving the file and then closing it, since we need to re-open it as a Mapped Text object with OpenMappedTextFile. Mapped Text objects are a great way to manipulate files with Legato, making IO very easy. Once we have the Mapped Text object open, we can iterate over each line of it, until we find our CUSTOM_PRES string. If that string is found, our parser is in the custom fields presentation, so we can set the in_custom boolean flag to true.
if(in_custom){ /* if we're in custom pres */
if(IsInString(line,ATTR)){ /* if it has a blank name */
s1 = FormatString(NEW_CUSTOM_PREFIX,cx); /* build new custom element name */
s2 = FormatString(ATTR_TEMPLATE,s1); /* build new attributes template line */
line = ReplaceInString(line,ATTR,s2); /* modify the line */
ReplaceLine(mtext,ix,line); /* replace into mtext */
cx++; /* increment counter */
} /* */
} /* */
} /* */
if(cx > 0){ /* if any changes were made */
MappedTextSave(mtext,f_name); /* save the file */
} /* */
CloseHandle(mtext); /* close the handle to mtext */
RunMenuFunction("FILE_OPEN","Filename: "+f_name); /* re open the file */
} /* */
/* */
When in_custom is set to true, we can search the current line for the ATTR string. If we find that, we can use FormatString to build the new string we’re going to use to replace the ATTR string, and then execute a ReplaceInString operation to build our new line for our Mapped Text object. After we have our new line, we can simply use ReplaceLine to swap out the old line in the Mapped Text object for our new one, and increment our changes counter. When the loop has finished iterating over the entire file, if we have any changes we can run the MappedTextSave function, then close our Mapped Text object (it’s very important to always close this when done, otherwise the file handle stays open and the file is locked), and re-open our file with RunMenuFunction.
With under 100 lines of relatively simple code, we can easily automate a task that normally takes 5-10 minutes to complete. This script reduces the time this task will take a user to about 30 seconds and requires very little specialized knowledge on the user’s behalf. This sample also demonstrates the importance of making code modular enough that you can add new features with minimal effort. I was able to pick this script up after 18 months, and without having to study it to figure out what’s going on, add some new functionality to it. Whenever possible, keep your code as simple and clean as possible. It makes it a lot easier to make changes going forward!
#define HIGH 30
#define MID 15
#define HIGH_MSG "Using a high percentage of extended elements is not recommended. Use more taxonomy elements."
#define MID_MSG "Custom element usage is about average, if possible replace some custom definitions for taxonomy elements."
#define CYC_ERROR "Found cyclic presentation relationships. See the 'Check Presentation Elements' tab for more information."
#define CYC_ERROR_TAB "Found cyclic presentation relationships. This means you have a dimension or member in a context that is used as a parent of itself in the presentation properties."
#define FIX_BLANK_Q "File contains blank custom elements that cannot be removed. Do you want to repair these elements? This will require closing and re-opening the file."
#define BLANK_CUSTOM_ERR "Element \"\" is not used in the report."
#define NEW_CUSTOM_PREFIX "custom:test%d"
#define CUSTOM_PRES "NAME=\"Custom Fields\""
#define ATTR "<ATTRIBUTES>,,,"
#define ATTR_TEMPLATE "<ATTRIBUTES>,,\"%s\","
void setup();
void run_custom_check(int f_id, string mode, handle window);
void run_cycle_check(int f_id, string mode, handle window);
void run_tests(int f_id, string mode);
void run_blank_custom_check(int f_id, string mode);
void fix_blank_elements(string f_name);
void debug_msg(string msg);
boolean debug;
void main(){
int ix;
int size;
string windows[][];
handle window;
if (GetScriptParent() == "LegatoIDE"){
debug = true;
windows = EnumerateEditWindows();
size = ArrayGetAxisDepth(windows);
for (ix = 0 ; ix < size; ix++){
if (windows[ix]["FileTypeToken"] == "FT_XFR"){
ActivateEditWindow(MakeHandle(windows[ix]["ClientHandle"]));
RunMenuFunction("XBRL_VALIDATE");
}
}
}
setup();
}
void debug_msg(string msg){
if (debug == true){
AddMessage(msg);
}
}
void setup(){
MenuSetHook("XBRL_VALIDATE", GetScriptFilename(), "run_tests");
MenuSetHook("EDGAR_VALIDATE", GetScriptFilename(), "run_tests");
}
/****************************************/
void run_tests(int f_id, string mode){ /* run all tests */
/****************************************/
run_cycle_check(f_id, mode, NULL_HANDLE); /* run the cycle relation check */
run_custom_check(f_id, mode, NULL_HANDLE); /* check for custom elements */
run_blank_custom_check(f_id, mode, NULL_HANDLE); /* check for blank custom elements */
} /* */
/****************************************/
void run_blank_custom_check(int f_id, string mode){ /* check for blank custom elements */
/****************************************/
handle window; /* window handle */
dword wType; /* the type of the window */
handle log; /* log file */
string s1; /* string */
string t_log[][]; /* log as a table */
int ix,size; /* counters */
int rc; /* response code */
handle log_tab; /* tab of validation */
/* */
window = GetActiveEditWindow(); /* get the edit window */
wType = GetEditWindowType(window) & EDX_TYPE_ID_MASK; /* get the type of the window */
if (wType != EDX_TYPE_XBRL_VIEW){ /* if the type isn't xbrl view */
return; /* exit */
} /* */
if (mode != "postprocess"){ /* if not postprocess */
return; /* return */
} /* */
s1 = GetMenuFunctionResponse(); /* response from validate */
log_tab = MakeHandle(GetParameter(s1,"InfoViewTab")); /* get the log tab */
log = InfoViewGetLogObject(log_tab); /* get log file */
if(IsValidHandle(log)==false){ /* if the log isn't a valid handle */
MessageBox("cannot get handle to log"); /* display error message */
return; /* return */
} /* */
t_log = LogToTable(log,""); /* get the log table */
size = ArrayGetAxisDepth(t_log); /* get num rows in log */
for(ix=0; ix<size; ix++){ /* for each object in log */
if(IsInString(t_log[ix]["message"],BLANK_CUSTOM_ERR)){ /* if string contains blank custom err */
rc = YesNoBox('q',FIX_BLANK_Q); /* query user if fixing blank */
if(rc == IDYES){ /* if user presses yes */
fix_blank_elements(GetEditWindowFilename( /* get filename */
GetActiveEditWindow())); /* get filename */
return; /* return */
} /* */
else{ /* if user doesn't press yes */
return; /* return */
} /* */
} /* */
} /* */
} /* */
/****************************************/
void fix_blank_elements(string f_name){ /* fix the blank elements */
/****************************************/
handle mtext; /* mapped text object */
string line; /* a line of text from the mtext obj */
string s1,s2; /* temp strings */
boolean in_custom; /* true if in custom presentation */
int cx, ix, size; /* counters */
/* */
RunMenuFunction("FILE_SAVE"); /* save the file */
RunMenuFunction("FILE_CLOSE"); /* close the file */
mtext = OpenMappedTextFile(f_name); /* open the mtext file */
size = GetLineCount(mtext); /* get the number of lines */
for(ix=0; ix<size; ix++){ /* for each line */
line = ReadLine(mtext,ix); /* read the line from mtext */
if(IsInString(line,CUSTOM_PRES)){ /* if this is the start of custom pres */
in_custom = true; /* we are in the custom pres */
} /* */
if(in_custom){ /* if we're in custom pres */
if(IsInString(line,ATTR)){ /* if it has a blank name */
s1 = FormatString(NEW_CUSTOM_PREFIX,cx); /* build new custom element name */
s2 = FormatString(ATTR_TEMPLATE,s1); /* build new attributes template line */
line = ReplaceInString(line,ATTR,s2); /* modify the line */
ReplaceLine(mtext,ix,line); /* replace into mtext */
cx++; /* increment counter */
} /* */
} /* */
} /* */
if(cx > 0){ /* if any changes were made */
MappedTextSave(mtext,f_name); /* save the file */
} /* */
CloseHandle(mtext); /* close the handle to mtext */
RunMenuFunction("FILE_OPEN","Filename: "+f_name); /* re open the file */
} /* */
/* */
/****************************************/
void run_cycle_check(int f_id, string mode, handle window){ /* run cycle check */
/****************************************/
string pres_props[]; /* presentation properties */
int id,c_id; /* bad presentation id's, context id's */
handle log; /* log file */
int bad_contexts[][]; /* bad contexts */
handle XBRL; /* XBRL object */
string pres_dimension_props[][]; /* properties of the presentation dims */
string presentations[]; /* list of all presentations */
string pres_contexts[]; /* list of contexts in a presentation */
string all_contexts[]; /* summary of all contexts */
string context[]; /* properties of a single context */
string a_key; /* axis key */
string d_key; /* dimension key */
string cd_key; /* context dimension key */
string cm_key; /* context member key */
dword wType; /* window type */
int bc; /* bad context counter */
int ix, rx, bx, cx; /* iterator variables */
int num_contexts; /* number of contexts */
int num_pres; /* number of presentations */
int num_bad; /* number of bad contexts */
int num_dimensions; /* number of dimensions */
int num_dimension_props; /* number of dimension props in pres */
boolean hooked; /* if running in hooked mode or not */
/* */
if (mode != "preprocess"){ /* if not running in preprocess mode */
return; /* return */
} /* */
if (IsWindowHandleValid(window) == false){ /* if the window isn't valid */
window = GetActiveEditWindow(); /* get the edit window */
wType = GetEditWindowType(window) & EDX_TYPE_ID_MASK; /* get the type of the window */
if (wType != EDX_TYPE_XBRL_VIEW){ /* if the type isn't xbrl view */
return; /* exit */
} /* */
hooked = true; /* store that we're running hooked mode */
} /* */
XBRL = XBRLGetObject(window); /* get the XBRL object */
presentations = XBRLGetPresentations(XBRL); /* get a list of all presentations */
all_contexts = XBRLGetContexts(XBRL); /* get a list of all contexts */
num_pres = ArrayGetAxisDepth(presentations); /* get the number of presentations */
for (ix = 0; ix < num_pres; ix++){ /* for each presentation */
ArrayClear(pres_dimension_props); /* clear array after scan */
ArrayClear(pres_props); /* clear array after scan */
if (XBRLGetPresentationType(XBRL,ix)<500){ /* if the presentation is not pseudo */
debug_msg(""); /* spacer */
debug_msg("Checking presentation: "+presentations[ix]); /* debug message */
pres_props = XBRLGetPresentationProperties(XBRL,ix); /* get properties of presentation */
pres_contexts = XBRLGetPresentationContexts(XBRL,ix); /* get contexts on presentation */
for (rx=1; rx<10; rx++){ /* for all possible dimensions */
d_key = FormatString("DomainElement-0%02d",rx); /* test domain key */
a_key = FormatString("DimensionElement-0%02d",rx); /* test axis (dimension) key */
if (pres_props[d_key] == "" || pres_props[a_key] == ""){ /* if the keys have no valid values */
rx = 10; /* we're done counting */
} /* */
else{ /* else, they have valid values */
debug_msg(" axis : "+pres_props[a_key]); /* debug message */
debug_msg(" domain: "+pres_props[d_key]); /* domsin */
pres_dimension_props[rx-1][0] = pres_props[a_key]; /* store values of this key in table */
pres_dimension_props[rx-1][1] = pres_props[d_key]; /* store values of this key in table */
} /* */
} /* */
num_dimension_props = ArrayGetAxisDepth(pres_dimension_props); /* get the number of actual dimensions */
if (num_dimension_props>0){ /* as long as we have dimensions */
pres_contexts = XBRLGetPresentationContexts(XBRL,ix); /* get the contexts on the presentation */
num_contexts = ArrayGetAxisDepth(pres_contexts); /* get the number of contexts */
for(rx=0; rx<num_contexts; rx++){ /* for each context in the presentation */
num_dimensions = 0; /* reset number of dimensions */
if (pres_contexts[rx]!=""){ /* if we have a context to look at */
c_id = FindInList(all_contexts,pres_contexts[rx]); /* find this context in the list */
context = XBRLGetContext(XBRL,c_id); /* get all context properties */
cm_key = "MemberElement-0%02d"; /* initialize value for member key */
cd_key = "DimensionElement-0%02d"; /* initialize value for domain key */
/* * get num of used contexts */
for(bx=1; bx<10; bx++){ /* for each possible context in the list*/
if (context[FormatString(cm_key,bx)]!=""){ /* if the context isn't blank */
num_dimensions++; /* increment the number of used dims */
} /* */
} /* */
/* for each dimension in the context */
for(bx=0; bx<num_dimensions; bx++){ /* */
cm_key = FormatString("MemberElement-0%02d",bx+1); /* build the member key */
cd_key = FormatString("DimensionElement-0%02d",bx+1); /* build the domain key */
for(cx=0; cx<num_dimension_props; cx++){ /* for each context */
if ((pres_dimension_props[cx][1] == context[cm_key])||/* if the context member is a domain */
(pres_dimension_props[cx][1] == context[cd_key]) || /* or context dimension is a domain */
(pres_dimension_props[cx][0]==context[cm_key])){ /* or context member is an axis */
debug_msg("pres name = %s", /* debug messages */
presentations[ix]); /* * */
debug_msg("pres_domain = %s", /* * */
pres_dimension_props[cx][1]); /* * */
debug_msg("pres_dimension = %s", /* * */
pres_dimension_props[cx][0]); /* * */
debug_msg("context_dimension = %s", /* * */
context[cd_key]); /* * */
debug_msg("context_member = %s", /* * */
context[cm_key]); /* * */
debug_msg(" "); /* * */
bad_contexts[bc][0] = ix; /* store presentation name */
bad_contexts[bc][1] = c_id; /* store context id */
bc++; /* increment number of bad contexts */
cx = num_dimension_props; /* set value to end for loop */
bx = num_dimensions; /* set value to end for loop */
} /* */
} /* */
} /* */
} /* */
} /* */
} /* */
} /* */
} /* */
log = LogCreate("Check Dimension Elements"); /* create log */
AddMessage(log,"Checking Dimension Elements in %s", /* add start message to log */
GetEditWindowFilename(window)); /* * */
num_bad = ArrayGetAxisDepth(bad_contexts); /* get the number of bad contexts */
if (num_bad>0){ /* if there are any */
MessageBox('x',CYC_ERROR); /* display error message */
AddMessage(log,CYC_ERROR_TAB); /* add message to the log */
for(ix = 0; ix < num_bad; ix ++){ /* for each error */
if (id!=bad_contexts[ix][0]){ /* if first time showing this pres */
id = bad_contexts[ix][0]; /* remember context was shown */
AddMessage(log," %s",presentations[id]); /* display presentation name */
} /* */
c_id = bad_contexts[ix][1]; /* get ID of bad context */
AddMessage(log," %s",all_contexts[c_id] ); /* add bad context name to log */
} /* */
} /* */
else{ /* */
AddMessage(log," No errors found"); /* display no errors message */
} /* */
LogDisplay(log); /* display the log */
} /* */
void run_custom_check(int f_id, string mode, handle window){
handle XBRL;
string presentations[];
string elements[];
dword wType;
int fields_pos;
int customs;
int total_elements;
int ix, rx;
int percent;
int size;
boolean hooked;
if (mode != "preprocess"){
return;
}
if (IsWindowHandleValid(window) == false){
window = GetActiveEditWindow();
wType = GetEditWindowType(window) & EDX_TYPE_ID_MASK;
if (wType != EDX_TYPE_XBRL_VIEW){
return;
}
hooked = true;
}
XBRL = XBRLGetObject(window);
presentations = XBRLGetPresentations(XBRL);
fields_pos = FindInList(presentations,"XBRL Financial Fields");
if (fields_pos < 0){
return;
}
elements = XBRLGetPresentationElements(XBRL,fields_pos);
size = ArrayGetAxisDepth(elements);
customs = 0;
for (ix = 0; ix < size; ix++){
if (elements[ix] !=""){
total_elements++;
if (FindInString(elements[ix],"custom:")>(-1)){
customs++;
}
}
}
percent = (customs*100)/total_elements;
if (hooked == false){
AddMessage("Checking Custom Elements in %s",GetEditWindowFilename(window));
AddMessage("Found %d total line items",total_elements);
AddMessage("Found %d custom line items",customs);
AddMessage("%d%% custom line items",percent);
}
else{
if (percent > MID){
if (percent >= HIGH){
MessageBox('x',"%d%% custom elements used as line items. %s",percent,HIGH_MSG);
}
else{
MessageBox('i',"%d%% custom elements used as line items. %s",percent,MID_MSG);
}
}
}
}
 Steven Horowitz has been working for Novaworks for over five years as a technical expert with a focus on EDGAR HTML and XBRL. Since the creation of the Legato language in 2015, Steven has been developing scripts to improve the GoFiler user experience. He is currently working toward a Bachelor of Sciences in Software Engineering at RIT and MCC. |
Additional Resources
Legato Script Developers LinkedIn Group
Primer: An Introduction to Legato
Quicksearch
Categories
Calendar
Feeds
