Understanding the Tables Search Service
- Marty Lechleider
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();
}
});