As always, many thanks in advance for any assistance provided here–greatly appreciated!
I am developing a medical research website designed to capture standardized volunteer participant survey responses. There are 7 different surveys that each participant must complete multiple times over the course of the research. Each survey is stored in a separate collection table as each one has different questions. The study administrators need to review each submission and update a “review status” field accordingly. To facilitate that, I need to be able to generate an administrator “work list”, preferably in a single table, displayed on a page that indicates which submissions by each participant has a non-finalized (“completed”) review status. Each survey has these fields in common: Participant ID, Submission Date and Review Status. What is not in common is the survey name. These are the fields I need to provide capture in the output.
I have come up with the following code which will generate a “pseudo” report that I can paste into a crude textbox, but I really need to be able to populate these results into a single table.
So my question is, without linking a table to a specific collection or dataset, how can I populate rows on the fly from these 7 iterated queries into a table?
Here is my code so far:
import wixData from ‘wix-data’ ;
let completedId = “” ; //initialize a global variable which will hold the a reference ID used for filter an “include” collection
let surveys = [ “DHI” , “EAT-10” , “MDADI” , “VHI” , “BartelIndex” , “NCCN” , “HN35” ] //these are the names of the 7 surveys taken by each participant multiple times.
let theReport = //variable to hold a “stringified” concatenation of all the query results
$w . onReady ( function () {
doSearch ()
});
async function doSearch () {
getCompletedID () //get the link reference ID for the value “Completed” so we can exclude those records from our worklist
for ( let s = 0 ; s < surveys . length ; s ++) {
await mainQuery ( s ) //iterate/send the name of each survey collection to the query that searches for items with study administrator “incomplete” review statuses
}
//push the final results array into a text paragraph element for visiual display – how can this output be directed to a table instead?
$w ( “#reportResults” ). text = theReport . toString ()
}
async function getCompletedID () { //review statuses are stored in a linked table–so we need to get the reference _id
await wixData . query ( “ReviewStatus” )
. eq ( ‘title’ , “Completed” ) //we will want to exclude all survey responses that have a “review status” reference that links to “completed” in the Review Status collection
. find ()
. then (( results ) => {
completedId = results . items [ 0 ]. _id //set the reference ID for the “completed review” value to a variable for future filtering use
});
}
async function mainQuery ( s ) {
await wixData . query ( surveys [ s ]) //query the current survey collection by the name passed into the function
. ne ( “reviewStatus” , completedId ) //reference to the linked field which is linked in each survey table to the Review Status table
. include ( ‘reviewStatus’ ) //reference to the linked Review Status table
. find ()
. then (( results ) => {
let cnt = results . totalCount //get the total count of survey entries with non-finalized reviews
let item = results . items
let myVar = “” ;
if ( cnt > 0 ) {
for ( let x = 0 ; x < cnt ; x ++) { //cycle through each detected non-reviewed survey and push the necessary fields into an array
//this is where I really want to push the results into a dynamic table instead of turning the results into a string
myVar = "Participant ID: " + item [ x ]. studyIdNumber + ", Survey: " + surveys [ s ] + ", submitted: " + item [ x ]. submissionTime . substring ( 0 , 25 ) + “, Status: " + item [ x ]. reviewStatus . title + " \n\n”
//push the string into the array variable for display when queries for all 7 surveys are complete
theReport . push ( myVar )
}
}
});
}