In addition to loading and applying the metadata, it is also necessary to apply filter criteria constraints to dynamic queries. ReportHelper.p provides several procedures for adding in these constraints. These procedures have been enhanced to allow for adding in conditions to a DB array field (field which has DBFieldExtend metadata value greater than one), as well as allowing a temp-table field to be associated with a specific index of a DB array field.

To specify a filter field which is associated with a DB array field the meta.xml is specified as follows:

Note that qo_slspsn is a field in qo_mstr which is defined as having an extent of 4. When creating a query for which the qo_slspsn is constrained to the “jp” salesperson the following condition will be added to the query:

To specify a filter field which is associated with a specific index for a DB array field the meta.xml is specified as follows:

In this case the temp-table field (qo_slspsn_1) is associated with a specific indexed field (qo_slspsn[2]) which results in a condition only for that specific indexed field being added as shown below:

Currently the MetaDataUtility which can be used to generate meta.xml files does not support the ability to map temp-table fields to indexed DB fields.

The AddAllConditions is used to loop through all filter fields associated with a specified DB table in the metadata and add in the filter constraints for these fields. The parameters to this procedure are the name of the DB table to retrieve filter constraints for and an input-output character string for the query to append to. This procedure has been modified to automatically include proper handling of extent and indexed DB fields.

Following is an example of the use of AddAllConditions:

The first call to AddAllConditions will find all the searchable constraints for report fields associated with the sod_det DB table and append them to the query string. The second call does the same thing for constraints for searchable report fields associated with the so_mstr DB table. Since the DB table associated with the searchable field is the same as the DB table being queried there is no need to perform the mapping which is provided in the AddSpecificConditions procedure described below.

The AddSomeConditions builds on the previous AddAllConditions but has an additional parameter between the DB table and query string which contains a comma separated list of DB fields to exclude from the filter constraints to be appended to the query string. This variant can be used when it is desirable to manually add some filter constraints within the proxy. This procedure has been modified to automatically include proper handling of extent and indexed DB fields.

Below is an example of how this procedure can be used:

The previous AddCondition call is used to insert any constraints on the sod_site DB field at a specific location in the query (possibly to address performance concerns). The subsequent call to AddSomeConditions will process all the filter constraints for filterable fields except for sod_site. If multiple fields are to be excluded then each name is provided in a comma separated list.

The AddSpecificConditions is almost the opposite of the AddSomeConditions in that it takes a comma separated list of fields to include as filter conditions. However, this procedure supports the ability to specify each field as “{DBFieldName}[:{DB Field}].” This allows for specifying a list of fields for the specified DB table and have the field's name mapped from the original {DBFieldName} to a different {DB Field}. So a constraint on a {DBFieldName} of “sod_part” could be converted to a {DB Field} of “ds_part” to apply the constraint against a ds_det record instead of the sod_det record. This procedure has been modified to automatically include proper handling of extent and indexed DB fields.

In this example the filter criteria for the specified sod_det and so_mstr DB table fields are being used to filter the resulting ds_det and dss_mstr records returned. Note the use of the “{DBFieldName}:{DB Field}” notation to provide mapping between the DBFieldName specified for a reporting field's metadata and the actual DB field name in the query - this is used to convert the name of the field in the query from the metadata DB field name to the correct field for the table used in the query.

The AddExtentConditions is used to bring in all the conditions which apply to an extent DB field. Since this procedure is automatically called from the previous procedures, it will probably not be needed to call directly, but is included here for documentation purposes. The input parameters to this procedure are the name of the temp-table buffer, name of the temp-table field, name of the DB field and number of indexed elements in the DB array field. There is also an input-output parameter for the query string which gets updated with the constraints for this filterable array. This procedure is only intended to be used when an extent DB field is being referenced, it should not be called for non-extent DB fields.

Following is an illustration of how this procedure could be called (note that this example is contrived, since similar behavior would occur from calling AddSpecificConditions):

The call to AddExtentConditions will cause the filter condition applied to the qo_slspsn field to be applied to each indexed element in the DB qo_slspsn field.

In the example program we have been developing, the filter conditions can most easily be implemented by using the following code block in the RunReport procedure.

The AddAllConditions call will suffice to dynamically add the filter conditions for all of our searchable fields, since they are all in the same DB table (so_mstr). The entire source code for this example program is listed at the end of this appendix.