//by default we'll use an ascending sort var sortOrder2 = "asc"; //by default, we'll use a textnocase sort var sortType2 = "textnocase"; //by default, use ascii character 30 as the delim var delim2 = "."; //if this is a time sort, then what is the date field? Default to "eventdate" var datefield2 = "eventdate"; //make an array to hold the sort stuff var sortArray = arraynew(1); //make an array to return var returnArray = arraynew(1); //grab the number of elements in the array (used in the loops) var count = arrayLen(arguments.aOfS); //make a variable to use in the loop var ii = 1; //if there is a 3rd argument, set the sortOrder if(structKeyExists(arguments, 'sortOrder')) sortOrder2 = arguments.sortOrder; //if there is a 4th argument, set the sortType if(structKeyExists(arguments, 'sortType')){ //If we are sorting by time then set the type to textnocase if (arguments.sortType eq 'time') sortType2 = 'textnocase'; else sortType2 = arguments.sortType; } //if there is a 5th argument, set the delim if(structKeyExists(arguments, 'delim')) delim2 = arguments.delim; if(structKeyExists(arguments, 'datefield')) datefield2 = arguments.datefield; if(structKeyExists(arguments, 'sortType') AND arguments.sortType eq 'time'){ //we are doing a time sort //loop over the array of structs, building the sortArray //construct the array using the date of the specified date field and the time portion of the specified key for(ii = 1; ii lte count; ii = ii + 1) sortArray[ii] = left(arguments.aOfS[ii].values[datefield2],11) & mid(arguments.aOfS[ii].values[arguments.key],11,11) & delim2 & ii; } else { //loop over the array of structs, building the sortArray for(ii = 1; ii lte count; ii = ii + 1) sortArray[ii] = arguments.aOfS[ii].values[arguments.key] & delim2 & ii; } //now sort the array arraySort(sortArray,sortType2,sortOrder2); //now build the return array for(ii = 1; ii lte count; ii = ii + 1) returnArray[ii] = arguments.aOfS[listLast(sortArray[ii],delim2)]; //return the array return returnArray; var fieldQuery = queryNew("fieldID,fieldName"); var rtnStruct = structNew(); // get the formID for this Custom Element var formID = getFormIDByCEName(arguments.ceName); var itm = 1; var thisField = ""; if( (len(formID)) and (formID GT 0) ) { // get the field query for this element fieldQuery = getElementFieldsByFormID(formID); // loop through the query and build the default structure for( itm=1; itm lte fieldQuery.recordCount; itm=itm+1 ) { // replace the FIC_ from the beginning thisField = ReplaceNoCase(fieldQuery.fieldName[itm], "FIC_", "", "all"); // add this field in if( not structKeyExists(rtnStruct, thisField) ) rtnStruct[thisField] = ""; // TODO would be nice to get the default data for this field } } var getCacheQueryID = queryNew("temp"); var deleteCacheQueries = queryNew("temp"); var deleteCacheHits = queryNew("temp"); var deleteCacheInstances = queryNew("temp"); var queryIDList = ""; var ceFormID = getFormIDByCEName(arguments.ceName); SELECT DISTINCT QueryID FROM TypedCacheInstances WHERE ControlTypeID = DELETE FROM TypedCacheQueries WHERE DELETE FROM TypedCacheHits WHERE DELETE FROM TypedCacheInstances WHERE // get the formID for the Page Mapping element var formID = getFormIDByCEName(arguments.ceName); var pageIDs = 0; var csSecurity = server.ADF.objectFactory.getBean("csSecurity_1_2"); var isCS9Plus = (val(ListLast(ListFirst(Request.CP.ProductVersion, "."), " ")) >= 9); pageIDs = getPageIDForElement(formID); if (listLen(valueList(pageIDs.pageID)) GT 0) return deleteCE(valueList(pageIDs.pageID)); var i = 1; var currPageID = 1; var formID = 0; var elementFields = ""; var j = ""; var csSecurity = server.ADF.objectFactory.getBean("csSecurity_1_2"); var isCS9Plus = (val(ListLast(ListFirst(Request.CP.ProductVersion, "."), " ")) >= 9); // initialize the variables var data_i = 1; var dataArray = ArrayNew(1); var retQuery = ""; var CEFormID = getFormIDByCEName(arguments.customElementName); var CEFieldID = ""; var searchCEFieldName = ""; var searchCEFieldID = ""; var ceFieldName = ""; var getPageIDValues = QueryNew("temp"); var retTempDataArray = ArrayNew(1); if (LEN(arguments.customElementFieldName) OR Len(arguments.searchFields)) { // check if queryType is Search if ( arguments.queryType EQ "search" OR arguments.queryType EQ "multi" ) { // get the id's for each item in the list and create a new list of id's for (data_i=1; data_i LTE ListLen(arguments.searchFields); data_i=data_i+1){ searchCEFieldName = "FIC_" & TRIM(ListGetAt(arguments.searchFields,data_i)); searchCEFieldID = ListAppend(searchCEFieldID, getElementFieldID(CEFormID, searchCEFieldName)); } } // convert the CE Field Name Arg to the field ID // check if the field name starts with 'FIC_' if (arguments.customElementFieldName CONTAINS "FIC_") CEFieldID = getElementFieldID(CEFormID, arguments.customElementFieldName); else { ceFieldName = "FIC_" & arguments.customElementFieldName; CEFieldID = getElementFieldID(CEFormID, ceFieldName); } } // special case for versions if ( arguments.queryType eq "versions" ) getPageIDValues = getPageIDForElement(CEFormID, CEFieldID, arguments.item, "selected", arguments.searchValues, searchCEFieldID); else getPageIDValues = getPageIDForElement(CEFormID, CEFieldID, arguments.item, arguments.queryType, arguments.searchValues, searchCEFieldID); // Check that we got a query back if ( getPageIDValues.RecordCount gt 0 ){ // Loop over the query of page ids for( data_i=1; data_i LTE getPageIDValues.RecordCount; data_i=data_i+1 ) { if (isNumeric(getPageIDValues.PageID[data_i])) { // if we want to return the data version then call different function if ( arguments.queryType eq "versions" ){ // get the data versions for the page id dataArray[data_i] = getElementInfoVersionsByPageID(getPageIDValues.PageID[data_i], CEFormID); } else { // get the data for the page id dataArray[data_i] = getElementInfoByPageID(getPageIDValues.PageID[data_i], CEFormID, true); } } } } // Check if we are processing the selected list if ( arguments.queryType EQ "selected" and len(arguments.customElementFieldName) and len(arguments.item) ){ // Order the return data by the order the list was passed in dataArray = sortArrayByIDList(dataArray, arguments.customElementFieldName, arguments.item); } /* 2010-09-17 - MFC - Updated */ // Check the query type for "selectedList" else if ( arguments.queryType eq "searchInList" ){ // Loop over the records to do a list find for the data for( data_i=1; data_i LTE ArrayLen(dataArray); data_i=data_i+1 ) { // Compare the data values list with the arguments items list. // If the list returned has a length then the item is in the data values list. if ( ListLen(variables.data.ListInCommon(dataArray[data_i].Values[arguments.customElementFieldName],arguments.item)) ) ArrayAppend(retTempDataArray, dataArray[data_i]); } // Set the temp data array back to the return value dataArray = retTempDataArray; } SELECT FormInputControl.FieldName, FormControl.FormName, Data_FieldValue.FieldValue, Data_FieldValue.MemoValue, Data_FieldValue.FormID, Data_FieldValue.FieldID, Data_FieldValue.VersionID, Data_FieldValue.listID, Data_FieldValue.DateAdded, Data_FieldValue.DateApproved, Data_FieldValue.AuthorID, Data_FieldValue.OwnerID FROM FormInputControl INNER JOIN FormInputControlMap ON FormInputControl.ID = FormInputControlMap.FieldID INNER JOIN FormControl ON FormControl.ID = FormInputControlMap.FormID INNER JOIN Data_FieldValue ON Data_FieldValue.FieldID = FormInputControlMap.FieldID WHERE (Data_FieldValue.PageID = ) AND (Data_FieldValue.FormID = ) AND (Data_FieldValue.VersionState = 2) AND (Data_FieldValue.VersionID = ) // Initialize the variables var elementFields = queryNew("temp"); var selectFieldID = queryNew("temp"); // Check if the fieldname starts with "FIC_" if ( UCASE(ListFirst(arguments.CEFieldName,"_")) NEQ "FIC" ) arguments.CEFieldName = "FIC_" & arguments.CEFieldName; elementFields = getElementFieldsByFormID(arguments.CEFormID); select fieldID from elementFields where UPPER(fieldname) = SELECT FormInputControl.FieldName, FormInputControlMap.FieldID, FormInputControl.Params, FormInputControl.Type FROM FormControl INNER JOIN FormInputControlMap ON FormControl.ID = FormInputControlMap.FormID INNER JOIN FormInputControl ON FormInputControlMap.FieldID = FormInputControl.ID WHERE (FormInputControlMap.FormID = ) ORDER BY FormInputControl.FieldName // Initialize the variables var getElementInfo = queryNew("temp"); var getElementFields = queryNew("temp"); var dataValuesStruct = StructNew(); var elmt_i = 1; var retStruct = StructNew(); var elmt_j = 1; var dataFldName = ""; var dataFldVal = ""; // Get the Form ID by the page ID if not passed in if(arguments.formID eq -1){ arguments.formID = getFormIDFromPageID(arguments.pageid); } // Query to get the data for the custom element by pageid // [MFC 2/11/09] Added formid argument to function call getElementInfo = getDataFieldValueByPageID(arguments.pageid, arguments.formid); // Get ALL the fields for the custom element // getElementFields = getElementFieldsByFormID(getElementInfo.FormID[1]); getElementFields = getElementFieldsByFormID(arguments.formid); // Load the field data into a struct with the fieldID as keys for( elmt_i = 1; elmt_i LTE getElementInfo.RecordCount; elmt_i=elmt_i+1 ) { // [MFC 2/11/09] Check if the key is already in the struct if ( NOT StructKeyExists(dataValuesStruct, "#getElementInfo.FieldID[elmt_i]#") ) { //check if the value is too large for fieldValue if ( LEN(getElementInfo.FieldValue[elmt_i]) ) StructInsert(dataValuesStruct,getElementInfo.FieldID[elmt_i],getElementInfo.FieldValue[elmt_i]); else StructInsert(dataValuesStruct,getElementInfo.FieldID[elmt_i],getElementInfo.MemoValue[elmt_i]); } } // Set the getElementFields query into a structure with the CE fields as keys // initialize the variables retStruct.pageid = arguments.pageid; retStruct.formid = getElementInfo.FormID[1]; retStruct.formname = getElementInfo.FormName[1]; retStruct.dateadded = getElementInfo.dateadded[1]; retStruct.dateapproved = getElementInfo.dateapproved[1]; retStruct.authorID = getElementInfo.AuthorID[1]; retStruct.ownerID = getElementInfo.OwnerID[1]; // check if we want the values struct separated if (arguments.separateValueStruct) retStruct.values = StructNew(); // loop over the form fields for( elmt_j = 1; elmt_j LTE getElementFields.RecordCount; elmt_j=elmt_j+1 ) { // trim the 'fic_' from the field name dataFldName = RIGHT(getElementFields.FieldName[elmt_j],LEN(getElementFields.FieldName[elmt_j])-4); dataFldVal = ""; // if the fieldname is a key in dataValuesStruct, then data exists for the field // set the field to dataFldVal for the struct insert if ( StructKeyExists(dataValuesStruct, "#getElementFields.FieldID[elmt_j]#") ) dataFldVal = dataValuesStruct[getElementFields.FieldID[elmt_j]]; // insert the data field name and value key pair into the struct // check if we want the values struct separated if (arguments.separateValueStruct) StructInsert(retStruct.values,dataFldName,dataFldVal); else StructInsert(retStruct,dataFldName,dataFldVal); } return retStruct; // Initialize the variables var getElementInfo = queryNew("temp"); var getElementFields = queryNew("temp"); var dataValuesStruct = StructNew(); var dataValuesArray = ArrayNew(1); var ver_i = 1; var elmt_i = 1; var elmt_j = 1; var versionStruct = StructNew(); var dataFldName = ""; var dataFldVal = ""; var dataVersions = ''; // get the version query for the CE dataVersions = getElementVersionsForPageID(arguments.pageid, arguments.formid); for( ver_i = 1; ver_i LTE dataVersions.RecordCount; ver_i=ver_i+1 ) { // check if we only need a specific version returned if ( (arguments.versionid EQ 0) OR (arguments.versionid EQ ver_i) ) { // clear the structs dataValuesStruct = StructNew(); versionStruct = StructNew(); // Query to get the versions data from the custom element by pageid getElementInfo = getDataFieldValueByPageID(arguments.pageid, arguments.formid, false, dataVersions.versionid[ver_i]); // Get ALL the fields for the custom element getElementFields = getElementFieldsByFormID(getElementInfo.FormID[1]); // Load the field data into a struct with the fieldID as keys for( elmt_i = 1; elmt_i LTE getElementInfo.RecordCount; elmt_i=elmt_i+1 ) { // [MFC 2/11/09] Check if the key is already in the struct if ( NOT StructKeyExists(dataValuesStruct, "#getElementInfo.FieldID[elmt_i]#") ) { //check if the value is too large for fieldValue if ( LEN(getElementInfo.FieldValue[elmt_i]) ) StructInsert(dataValuesStruct,getElementInfo.FieldID[elmt_i],getElementInfo.FieldValue[elmt_i]); else StructInsert(dataValuesStruct,getElementInfo.FieldID[elmt_i],getElementInfo.MemoValue[elmt_i]); } } // initialize the variables versionStruct.pageid = arguments.pageid; versionStruct.formid = getElementInfo.FormID[ver_i]; versionStruct.formname = getElementInfo.FormName[ver_i]; versionStruct.versionid = getElementInfo.versionid[ver_i]; versionStruct.dateadded = getElementInfo.dateadded[ver_i]; versionStruct.dateapproved = getElementInfo.dateapproved[ver_i]; versionStruct.authorid = getElementInfo.AuthorID[ver_i]; versionStruct.ownerid = getElementInfo.OwnerID[ver_i]; versionStruct.values = StructNew(); // loop over the form fields for( elmt_j = 1; elmt_j LTE getElementFields.RecordCount; elmt_j=elmt_j+1 ) { // trim the 'fic_' from the field name dataFldName = RIGHT(getElementFields.FieldName[elmt_j],LEN(getElementFields.FieldName[elmt_j])-4); dataFldVal = ""; // if the fieldname is a key in dataValuesStruct, then data exists for the field // set the field to dataFldVal for the struct insert if ( StructKeyExists(dataValuesStruct, "#getElementFields.FieldID[elmt_j]#") ) dataFldVal = dataValuesStruct[getElementFields.FieldID[elmt_j]]; // insert the data field name and value key pair into the struct StructInsert(versionStruct.values,dataFldName,dataFldVal); } // if no specified version id then build the whole array if ( arguments.versionid EQ 0 ) dataValuesArray[ver_i] = versionStruct; else if ( arguments.versionid EQ ver_i ) // else build 1 term array for specific version dataValuesArray[1] = versionStruct; } } return dataValuesArray; SELECT DISTINCT VersionID, PageID FROM Data_FieldValue WHERE (PageID = ) AND (FormID = ) ORDER BY VersionID SELECT ID FROM FormControl WHERE (FormName = OR FormName = ) AND (FormControl.action = '' OR FormControl.action = 'custom_form_element' OR FormControl.action is null) // Initialize the variables var itm = 0; var getListItemIDs = queryNew("temp"); var getPageIDForFormID = queryNew("temp"); var theListLen = 0; var theItem = ""; var searchValuesALT = ""; // Make the search case to lowercase arguments.searchValues = LCASE(arguments.searchValues); SELECT DISTINCT listID FROM data_listItems WHERE SELECT DISTINCT PageID FROM Data_FieldValue WHERE FormID = AND AND fieldValue = AND FieldID = AND AND fieldValue <> AND FieldID = AND PageID NOT IN ( SELECT DISTINCT PageID FROM Data_FieldValue WHERE AND AND VersionState = 2 ) AND AND ( LOWER(fieldValue) LIKE OR MemoValue LIKE OR LOWER(fieldValue) LIKE OR MemoValue LIKE ---> ) AND PageID IN ( SELECT DISTINCT PageID FROM Data_FieldValue WHERE FormID = AND FieldID = AND LOWER(fieldValue) = AND VersionState = 2 ) AND fieldID = AND AND fieldID = 0 AND fieldID = AND fieldValue > AND fieldID = AND ( fieldValue > AND fieldValue < ) AND fieldID = AND ( LOWER(fieldValue) = OR LOWER(fieldValue) LIKE OR LOWER(fieldValue) LIKE OR LOWER(fieldValue) LIKE OR ) ( LOWER(fieldValue) IS OR LOWER(fieldValue) = ) AND VersionState = 2 AND PageID <> 0 application.CacheInfoCache.InvalidateByID("element",0,0,0,arguments.pageID,arguments.controlID); // Initialize the variables var qCustomElements = QueryNew("id,formname,state"); var controlType = "custom"; // remove any non-numeric values from the passed in value var stList = REReplace(arguments.stateList,"[^,0-9]","","ALL"); // If remaining value does not not have at least one value, set it back to the default to only return active records if ( ListLen(stList) EQ 0 ) stList = 0; SELECT ID, ShortDesc AS FormName, ElementState AS state FROM AvailableControls WHERE Name = AND ORDER BY ShortDesc var retArray = ArrayNew(1); var i = 1; var j = 1; var arrayCount = 1; var fldVal = ""; // loop over the idList for (i = 1; i lte #ListLen(arguments.idList)#; i = i + 1) { // loop over the arrayOfStructs to find the matching item with the ID for (j = 1; j lte #ArrayLen(arguments.arrayOfStructs)#; j=j+1) { // [MFC 2/20/09] Check if the values struct exists. Set fldVal variable. if ( structKeyExists(arguments.arrayOfStructs[j], "values") ) fldVal = arguments.arrayOfStructs[j].Values["#arguments.idFieldName#"]; else fldVal = arguments.arrayOfStructs[j][arguments.idFieldName]; if ( ListGetAt(arguments.idList,i) eq fldVal ) { retArray[arrayCount] = arguments.arrayOfStructs[j]; arrayCount = arrayCount + 1; } } } return retArray; SELECT distinct FormID FROM Data_FieldValue WHERE PageID = AND ControlID = AND VersionState = SELECT FormName FROM FormControl WHERE ID = AND (action = '' OR action is null OR action = 'custom_form_element') var retArray = ArrayNew(1); var array1PageIDList = ""; var array2PageIDList = ""; var i = 1; var j = 1; // Set the array1 to be the larger array var largeArray = arguments.array1; var smallArray = arguments.array2; // Validate that both arrays have LENGTHS if ( ArrayLen(largeArray) AND (NOT ArrayLen(smallArray)) ) { return largeArray; } else if ( ArrayLen(smallArray) AND (NOT ArrayLen(largeArray)) ) { return smallArray; } else if ( (NOT ArrayLen(largeArray)) AND (NOT ArrayLen(smallArray)) ){ return retArray; } // Check if array2 is larger if ( ArrayLen(arguments.array2) GT ArrayLen(arguments.array1) ){ largeArray = arguments.array2; smallArray = arguments.array1; } // Loop over largeArray for ( i = 1; i LTE ArrayLen(largeArray); i = i + 1) { // Loop over the smallArray for ( j = 1; j LTE ArrayLen(smallArray); j = j + 1) { // check if the current largeArray pageid has any matches in smallArray if ( largeArray[i].pageid EQ smallArray[j].pageid ) { ArrayAppend(retArray, largeArray[i]); break; } } } var retArray = arguments.array1; var i = 1; var j = 1; // Loop over array1 //for ( i = 1; i LTE ArrayLen(retArray); i = i + 1) for ( i = ArrayLen(retArray); i GTE 1; i = i - 1) { // Loop over the array2 for ( j = 1; j LTE ArrayLen(arguments.array2); j = j + 1) { // check if the current array1 pageid has any matches in array2 if ( retArray[i].pageid EQ arguments.array2[j].pageid ) { // Delete the item from the list ArrayDeleteAt(retArray, i); break; } } } // Get the fields for the CE var fieldsQry = getElementFieldsByFormID(arguments.formID); var i = 1; // Loop over the fields to determin if any types are RTE for ( i = 1; i LTE fieldsQry.RecordCount; i = i + 1) { if ( fieldsQry.Type[i] EQ fieldType ) { return true; } } return false; var ceJoinQry = QueryNew("tmp"); var csCmdApiVersion = 9; var useCMDapi = (val(ListLast(ListFirst(Request.CP.ProductVersion, "."), " ")) >= csCmdApiVersion); var catComponent = ""; var ceComponent = ""; var catsQry = QueryNew("tmp"); var ceQry = QueryNew("tmp"); catComponent = Server.CommonSpot.api.getObject('Categories'); ceComponent = Server.CommonSpot.api.getObject('CustomElement'); // Get the Categories for Custom Elements using the CMD API catsQry = catComponent.getList(type='Element'); // Get All of the Custom Element using the CMD API ceQry = ceComponent.getList(); SELECT catsQry.ID AS CategoryID, catsQry.Name AS Category, ceQry.Name AS FormName, ceQry.ID FROM ceQry, catsQry WHERE ceQry.CategoryID = catsQry.ID AND ceQry.State = 'Active' AND catsQry.IsActive = 1 AND LOWER(catsQry.Name) = AND catsQry.ID = ORDER BY catsQry.Name, ceQry.Name SELECT ControlCategoryLookup.ID, ControlCategoryLookup.Category, FormControl.FormName, FormControl.ID FROM AvailableControls INNER JOIN ControlCategoryLookup ON AvailableControls.CategoryID = ControlCategoryLookup.ID INNER JOIN FormControl ON AvailableControls.ID = FormControl.ID WHERE (AvailableControls.ElementState = 0) AND lcase(ControlCategoryLookup.Category) = AND ControlCategoryLookup.ID = ORDER BY ControlCategoryLookup.Category, FormControl.FormName var fldVal = ""; // Get the data struct for the formid and datapageid var dataStruct = getElementInfoByPageID(arguments.datapageid, arguments.formid); // Check if the key exists for the field name if ( StructKeyExists(dataStruct.Values, arguments.fieldName) ) fldVal = dataStruct.Values[arguments.fieldName]; var itm = ''; var ceDataList = ""; var ceData = getCEData(arguments.ceName); ceData = arrayOfCEDataSort(ceData,arguments.fieldName,'asc','textnocase','^'); for ( itm=1; itm LTE arrayLen(ceData); itm=itm+1 ) { ceDataList = ListAppend(ceDataList,StructFind(ceData[itm].values,arguments.fieldname)); } return ceDataList; SELECT DISTINCT pageid FROM data_fieldvalue WHERE fieldValue = AND formid = var elementExists = false; if( getFormIDByCEName(arguments.elementName) neq 0 ) elementExists = true; var ceDataAry = arrayNew(1); var getPageIDValues = queryNew(''); var pageIDList = ""; select pageID, formID, fieldValue, memoValue, itemID, listID, fieldID from data_fieldValue where fieldID = and versionState = 2 var fieldIDList = ""; var fieldIDQuery = queryNew(''); select ID from formInputControl where type = var countQry = QueryNew("tmp"); // Get the form and field ID's var formID = getFormIDByCEName(CEName=arguments.ceName); var fieldID = getElementFieldID(CEFormID=formID,CEFieldName=arguments.fieldName); SELECT DISTINCT PageID FROM Data_FieldValue WHERE FormID = AND FieldID = AND FieldValue = var elementInfo = structNew(); elementInfo.elementData = structNew(); elementInfo.elementData.propertyValues = arguments.ceData; var data = arguments.theArray; var colTempArray = arrayNew(1); var qColumns = arrayNew(1); var qData = ""; var columns = ""; var i = 0; var x = 0; var y = 0; var c = 0; var currFormid = ""; var currFormName = ""; var addTopLevelKey = true; // init the qColumns Arrays qColumns[1] = arrayNew(1); qColumns[2] = arrayNew(1); // Get the FormID for the element if one does not exist in the data array if ( ArrayLen(data) AND !StructKeyExists(data[1],"formID") AND StructKeyExists(data[1],"pageid") AND IsNumeric(data[1].pageid) ) { currFormID = getFormIDFromPageID(data[1].pageid); if ( IsNumeric(currFormID) ) currFormName = getCENameByFormID(currFormID); } // store all the top level keys colTempArray = structKeyArray(data[1]); // Build the List of Query Columns form the Top level Struct Keys // Remove any "fic_" keys and any of the excluded keys for ( c=1; c LTE ArrayLen(colTempArray); c=c+1 ) { addTopLevelKey = true; if ( arguments.excludeFICfields AND LEFT(colTempArray[c],4) EQ "fic_" ) addTopLevelKey = false; if ( addTopLevelKey AND LEN(TRIM(arguments.excludeTopLevelFieldList)) AND ListFindNoCase(arguments.excludeTopLevelFieldList,colTempArray[c]) ) addTopLevelKey = false; // Do we Add the current Key Field to the Key array if ( addTopLevelKey ) arrayAppend(qColumns[1],colTempArray[c]); } // store all the values sub structure keys qColumns[2] = structKeyArray(data[1].values); // add all the top level keys to the list columns = arrayToList(qColumns[1]); // remove the "values" list element, we don't need it columns = listDeleteAt(columns,listFindNoCase(columns,"values")); // add all the values sub structure keys to the list columns = listAppend(columns,arrayToList(qColumns[2])); // add in the FormID and FormName to the query column list if needed if ( LEN(TRIM(currFormID)) ) columns = listAppend(columns,"formID"); if ( LEN(TRIM(currFormName)) ) columns = listAppend(columns,"formName"); // create new query object with our column list qData = queryNew(columns); // size the query based on the size of the data array passed in queryAddRow(qData,arrayLen(data)); // loop over the data array passed in for( i=1; i lte arrayLen(data); i++) { // Add in the FormID value and FromName value if needed if ( LEN(TRIM(currFormID)) ) querySetCell(qData,"formID",currFormID,i); if ( LEN(TRIM(currFormName)) ) querySetCell(qData,"formName",currFormName,i); // loop over the keys for( x=1; x lte arrayLen(qColumns[1]); x++ ) { // if the key is "values" if( qColumns[1][x] eq "values" ) { // loop over the values sub-structure for( y=1; y lte arrayLen(qColumns[2]); y++ ) { querySetCell(qData,qColumns[2][y],data[i][qColumns[1][x]][qColumns[2][y]],i); } } else { querySetCell(qData,qColumns[1][x],data[i][qColumns[1][x]],i); } } } return qData;