Creating a filter guide that loops through a server-side table field

Filter guides are useful for creating filter workflow that steps through the rows in a server-side table field. A server-side table field is a field in a supporting form that allows you to manipulate data on the server and then return the result to the client, rather working with the data directly in the client. This approach improves performance by reducing the amount of network traffic between the client and the server. 

The filter guide workflow goes through every row in the table and then performs selected filter actions on particular rows or sets of qualifying rows. These qualifications can be built with reference to table column values. 

Because filters run on the server, you cannot use server-side table fields to highlight rows for display. You cannot select rows on server-side table fields, like you can on the client side. The user whose search initiates the server-side table loop must have at least hidden permissions to the table field.

This table looping functionality works only inside the filter guide. Filters function on only one AR System server. If a server-side table field points to a form on a remote server, the filter guide does not work.

You can also perform calculations on the columns of the server-side table field, using the COLAVG, COLCOUNT, COLMAX, COLMIN, and COLSUM functions inside a filter guide. Use these functions just as you do with active link guides.  For more information about table fields and functions, see the Defining-tables-to-display-data and Functions.

Server-side table field implementation issues

Consider the following server-side table field limitations:

• When creating workflow for server-side tables, filters with the Call Guide action can be defined to loop over table columns. The values retrieved during the looping always comes from the last row of the table. To retrieve different values, postfix two special characters ( `! ) on the filters within the filter guide. Adding these special characters causes your filter to execute synchronously, instead of deferring into a later filter processing pass. For example, if your filter within the Filter guide is named FormA: Push New Entry to Form B, postfix those special characters to look like this:

  FormA: Push New Entry to Form B`!
• Data conversion between values in a column of one type should be able to be set or pushed to fields of a different type for most data types. But unlike client-side table fields, there are some limitations in server-side tables for the following conversions:
  CHARACTER to DECIMAL
  CHARACTER to REAL
  For example, 123.45 six converted to decimal became 0.00, and converted to real became 0.000000. 
  On the other hand, 123.45 does convert properly to 123.45 and 123.450000.
• Setting values from columns of integer, real, decimal, or currency type (and sometimes character type) to date or time fields does not yield NULL for negative values such as -3 or -3.01. Instead, the lowest values of the date and time type are set.
  For example, the lowest value of date fields is 1/1/4713 BC, while the lowest value of time fields is 12:00:00 AM.

To create a filter guide that loops through a server-side table field

The following procedure constructs a simple loop that searches through all the rows of a server-side table field on a helper form (Form1 ) until it finds all the tickets with a specific user's name on them. The workflow then uses values from a column in the table field to set fields on a second form (Form2 ). The first Set Fields action concatenates all the returned values of the table field column into a character field; the second action uses the COLCOUNT function to display the number of columns returned. 

You can enhance this use case by including additional in-line workflow, for example, notifying a user about the number of rows returned.

1. Create a form (for example, Form1).
2. Create another form (for example, Form2):
   • Add an integer field to display the number of tickets (for example, Tickets).
   • Add a character field to display all the entry IDs of the columns returned (for example, Concatenated Columns). Align these fields side-by-side so that the results show all the tickets for a specific user.
   • Add a table field:
     • Select Form1 as the source form.
     • Enter the qualification 'Submitter' = $Submitter$
        Using this qualification in the table field allows the database to select the entries that match the $Submitter$ field. This can improve performance because neither the AR System server nor the client needs to process the other entries in the form.
     • Add the Request ID field (becomes Column)
     • Add the Submitter field (becomes Column2)
   • (Optional) Hide the table field.
      Because you use server-side table fields exclusively for computation, you should hide them on forms that users interact with.
3. Create a filter (Form2 Set Field):
   • Associate the filter with Form2.
   • Add a Set Fields action:
   • In the mapping table, map the field Concatenated Columns to the value ($Concatenated Columns$ + " ") + $Column$
      The workflow concatenates all the returned columns into the Concatenated Columns field and separate them with a space. $Column$ references the table column that contains the request IDs.
   • Add another Set Fields action, and map Tickets to COLCOUNT($Column$)
      The action uses the COLCOUNT function to fill the Tickets field with the value of how many tickets this user has created.
   • Save the filter.
4. Create a filter guide (Form2 Filter Guide):
   • Associate the filter guide with Form2.
   • In the Filters and Labels panel, add Form2 Set Field to the guide.
   • Save the filter guide.
5. Create a filter (Form2 Call Guide):
   • Associate the filter with Form2.
   • Select the Execution Option Submit.
      This filter executes when you create tickets in Form2 and then display them.
   • Add a Call Guide action and enter Form2 Filter Guide as the guide name.
   • Set Table Loop to All Rows, and then select the table field on Form2 .
      This action activates the guide and causes it to loop through the rows in the table field.
6. To test the guide:
   • Open Form1 in New mode.
   • Create several tickets, including two or three with John as the value of the Submitter field.
   • Open Form2 in New mode and create a ticket for John.
   • Perform a search for John's tickets, then click the request in the Results List.
      The filter guide is triggered and loops through all the rows in the table field until it finds the rows with John as the value of the Submitter field. The workflow then completes the other fields in the form with the number of John's tickets created in Form1. In addition, the concatenated entry IDs from the returned entries appear in the Concatenated Columns field.
     Tickets returned and columns displayed
     
     
     You can use similar functionality to loop through hundreds or even thousands of requests in the database, bypassing both the server and the client. By contrast, you could use client-side processing to calculate this information, but doing so increases network traffic between the client and the server and can impact performance.