August 2015 - Posts - Don Stephen Lambatin
in

InfoPath Dev

Don Stephen Lambatin

August 2015 - Posts

  • Report Builder - Filtering data from a repeating table in DBXL

    Have you ever encountered querying data from a repeating table on your form uploaded in DBXL, and adding a filter to your query returns all the data in that document including the ones you don’t need?

    This happens because the Report builder’s filters are per document filters - if a document that matches the filter has multiples rows, the service will return all the data queried from that document.

    Filters can manually be added to the base path if more complex XPath filtering is desired - for example, if you are returning high level form data, but only want forms that meet a particular criteria based on data in a repeating node, you can add the filter manually to the base path:

    /my:myFields[my:group1/my:childField = 'Important']

    Here’s an example of a form's schema where the data about to be filtered is repeating data:

     

    Using the schema sample above, the Report Builder’s Fields to Retrieve section should look like this:

     

    In most cases, filtering the query will simply need a filter similar to this:


     
    However, this will return all the data from all documents in DBXL that will match the filter above when you click on the Perform Query button – including rows from the table being filtered that does not match the filter description:


     
    To remedy this, you can remove the filter and do the filtering in the Base Path:


     
    This should now get you the data you need:

    In the sample used, sorting the phone number doesn't really make any sense. In case you would need to sort the data you are querying, you should add the filter at the repeating group of the node you intend to sort (in the image below my:Product is the repeating group):



Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.