Understanding the Tables Search Service

Owned by Marty Lechleider
Last updated: Oct 19, 2018
3 min read

Note: The application used in the video is testtablesearch.nitro_s

 When you add a Table item to your form, any entries added to that table will be stored in its own table in the FEB database.  When you access the Service Description dialog you will also see two new services for each table in your form, a search and retrieve for items in that table:


There are a few limitations to these services that are not well understood.  When you setup a search service for a table object you can only return the table entries of ONE form submission, the service (as of 8.6.2) does not allow you to return all the entries added to this table across all of the form submissions.

I have learned that this is actually quite a common request and it is something that we are investigating for a future release.  In the meantime I have developed a process where you could retrieve ALL the table entries across all the form submissions.  The example that I built actually provides a mechanism to filter the table entries by several different criteria.

The basic steps used in this approach are:

1. Use a service to get the RIDs of the main records to a temporary table (will be hidden from the users).
2. Using javaScript, iterate the table and for each record use the table search service to pull the values from the table object into another temporary table.
3. When the service is complete, iterate the secondary table and copy all the rows (or those that meet your criteria) into the main table that will be used to show the consolidated results.
4. Move on to the next row of the main table.

Let's take a look at how we accomplish this.  The process is started when the user enters a value into any of the three fields (name, color, number), the following code is in the onItemChange event(s):


if(BOA.getValue() !== '') { //only proceed if the field is not empty, this insures the service is not triggered when a user clears a field
  BO.F_Table1.setValue(new Array()); //clear the main table
  app.getSharedData().currRow = 0; //reset global counter
  form.getServiceConfiguration('SC_MainRecords').callService(); //call the service to get the main records
}

The rest of the code is in the form onLoad event.  Let's break it down:


app.getSharedData().currRow = 0; //the global variable used to track which row of the main records we are processing
form.getPage('P_NewPage1').F_Section1.setVisible(true); //hide section when ready

var srv = form.getServiceConfiguration('SC_MainRecords'); //when the service is finished and returned the results, we execute this code
srv.connectEvent("onCallFinished", function(success)
{
  if(success) {
   //walk table and call service for each child
   var tl = BO.F_Table2.getLength();
   if(app.getSharedData().currRow < tl) {
     //copy rid into temp field
     BO.F_SingleLine11.setValue(BO.F_Table2.get(app.getSharedData().currRow).F_SingleLine7.getValue());

     //call service to get its sub table records
     form.getServiceConfiguration('SC_ChildRecords').callService();
   }
  }
});

var srv2 = form.getServiceConfiguration('SC_ChildRecords');
srv2.connectEvent("onCallFinished", function(success)
{
  if(success) {
   //walk table and call service for each child
   var tl = BO.F_Table3.getLength();
   for(var i = 0; i < tl; i++) {
     //does the row match the search inputs?
     var s_name = BO.F_SingleLine1.getValue();
     var s_color = BO.F_SingleLine2.getValue();
     var s_number = BO.F_SingleLine3.getValue();

     var addRow = true;

     if(s_name === "*") {
       addRow = true;
     } else {
       //if any of the other non-empty fields don't match then don't add the row
       if(s_name !== "") {
         if(s_name !== BO.F_Table3.get(i).F_SingleLine8.getValue())
           addRow = false;
       }

       if(s_color !== "") {
         if(s_color !== BO.F_Table3.get(i).F_SingleLine9.getValue())
           addRow = false;
       }

       if(s_number !== "") {
         if(s_number !== BO.F_Table3.get(i).F_SingleLine10.getValue())
           addRow = false;
       }
     }

     if(addRow) {
       var r = BO.F_Table1.createNew();
       r.F_SingleLine4.setValue(BO.F_Table3.get(i).F_SingleLine8.getValue());
       r.F_SingleLine5.setValue(BO.F_Table3.get(i).F_SingleLine9.getValue());
       r.F_SingleLine6.setValue(BO.F_Table3.get(i).F_SingleLine10.getValue());
       BO.F_Table1.add(r);
     }
   }

   //increment main row counter
   app.getSharedData().currRow++;

   //see if there is another row to process
   var tl = BO.F_Table2.getLength();
   if(app.getSharedData().currRow < tl) {
     //copy rid into temp field
     BO.F_SingleLine11.setValue(BO.F_Table2.get(app.getSharedData().currRow).F_SingleLine7.getValue());

     //call service to get its sub table records
     form.getServiceConfiguration('SC_ChildRecords').callService();
   }

   //clear temp table and temp rid field
   BO.F_Table3.setValue(new Array());
   BO.F_SingleLine11.setValue();

  }
});