Infopath report/Dashboard with partial-match and split-column filtering and more - InfoPath Dev
in

InfoPath Dev

Use our Google Custom Search for best site search results.

Infopath report/Dashboard with partial-match and split-column filtering and more

Last post 07-13-2013 01:34 PM by Patrick Halstead. 12 replies.
Page 1 of 1 (13 items)
Sort Posts: Previous Next
  • 03-07-2013 06:10 AM

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Infopath report/Dashboard with partial-match and split-column filtering and more

    This post is moving the topic of partial-match filtering, first mentioned here, as it is not related to qRules and I believe it is more appropriate to be in the general Infopath category.  

    Attached are several screenshots of:

    - print-preview of an actual partial-match & split-column filter in a master-detail nested repeating tables.

    - actual running form

    - the design view

    - main connection (not used to save anything).

     Note: for some reason I am not able (or don't know how) to attach multiple files to the same post, thus I will use several replies...


  • 03-07-2013 06:35 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    this is the actual form running.

    Here you can see:

     - a different filter used. It is not case-sensitive and it is a combo-filter for 2 fields, presented as 3 separate filters. Phase and Status actually represent a single field in the top-level repeating table.

     - the top-lelel repeating table, let's call it EVENTS, is bound to a secondary receive connection representing a Sharepoint 2010 Form Library

    - The filtering demo is done only on the top-level, EVENTS table/connection, but could be extended to more data connections

     - dynamically updated record-match counter;

    - the buttons (picture buttons) used for the drill-down part of the report.

    - filters support the empty/Any/All option;

    - The Filters use text boxes for input and are defined in the main connection, which itself is not used for any data saving. The purpose of the demo is represent a report (could be saved to PDF or printed) or a dashboard of a kind.

     ..


  • 03-07-2013 06:56 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    The main connection snapshot shows all the fields used for the demo (only Sharepoint/Infopath used, no SQL or Access databases)

    - SelEvent: keeps track of any selected node in the master(top-level) repating table, EVENTS. It is intended for use in the drill-down when expanding a particular node to show all tasks assigned in the other secondary connection (a SP List or a Task List of my or your choice)

    - dcount: it re-calculates automatically on any change of the filters to show the total number of matching records/events.

    - Phase Filter: While it is not dynamically populated in this demo, I have done it in many other cases. Just use a separate data connection to get all unique values of any SP List, even customized task lists.

    - Status Filter: same as Phase filter, but it represent the outcome part of the progress of a form-based workflow, dynamically generated  form,  which gets its actual data form a real SPD workflow with customized Task Forms (again Infopath).

    - Phase & Status filters, together operate on a single Form Library field, which could be updated by the intended business logic, either in the form-logic or in the SPD workflow. In my demo, both.

      Those two filters are implemented as drop-down list boxes.

    - Title Filter: any partial string to be matched against the Event Title of the Events (Form Library Forms). It is also going to be not case-sensitive.

    - SelectedLine and MultiSelEvent: Those two are part of a separate logic for tracking mutiple events and able to auto-expand their correspoding sections, which is different than the drill-down.

      The demostrated drill-down will only allow a single event to be expanded (having all assigned tasks and their progress or result). That is because the drill-down uses only a single secondary data connection, thus can not keep track of mutiple events.


  • 03-07-2013 08:01 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    The design:

    No code or qRules.

    Only 3 data connections used: a) Main , b) Top_Level, Event, secondary "receive" data connection bound to a SP Form Library, c) Detail-level, event-related tasks, secondary "receive" data connection.

    The demo is intended as report/ dashboard, thus could only be printed or saved to a PDF/XPS. Editing/Updating of the data is possible (more than one data source) either via qRules or CAML. A separate "submit" data connection might need to be defined.

    1. Search Filter section: mostly covered in the previous post:

       - changing the value of the Phase or Status will automatically refresh the repeating tables, activating the filters and recalculating the number of total matching records.

       - typing/editing the Title filter will require either you click outside the edit-box (get out of focus) or use the Refresh button (just to entice you to get out of that Title box).

       - Filtering the Assigned To, Modified By also possible. Filtering on the Description or Comments fields should not be a problem too.

       - We are Not using the "query fields" on those data connections in this demo. You might already know what that implies. It is possible to use them together with the filter to decrease the size of the sample data returned over the network, but you can not have wildcards or if have a complex filter (that one you might be able to do some-thing with CAML though).

     2. Nesting the repeating tables of the two secondary data connections:

        - I think that should be obvious: You drop the repeating node of the top-level (Event) data connection as a repeating table. Remove any not needed columns. Add any necessary calculated fields or even edit boxes right on the form.

       - You can actually see that the Assigned To column of the second, nested  tables itself is a repeating node (Person). Just remove the AccountID and AccountType columns from that mini-repeating table (a third one in the view/demo). You can also use the double-eval trick with a calculated value control to present the names instead.

       - increase the size of the first repeating table and insert the repeating node of the intended "detail" table from that "simulated" master-detail relationship. This is a browser compatible form, thus  there will be no master-detail control in the Infopath Control toolbox, but as you see, it could be done. Repeat the steps of removing/adding columns form/to the nested repeating table.

    - "Can't insert a repeating control here" - those exclamation marks on the nested repeating table: Does not seem to cause a problem, yet.

    - Each repeating table I have also put in section control. Why? Becuase I want to be able to hide-show whole repeating nodes only when there is data or a message otherwise. It was also intended for the multi-event tracking capability (not demostrated here, but that is what the small folder picture button to the left of the green dot button in the master table is for).

    I need to take a break here, but will continue with the details later, where I will post details about rules used for the filters.

    Briefly: Some rules operate on the repeating nodes themselves (the alternate row coloring, hiding/show of the specific rows, etc.)


  • 03-10-2013 05:11 AM In reply to

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    Den12:

    I need to take a break here, but will continue with the details later, where I will post details about rules used for the filters.

     Hi Den12,

    This is great and I have already completed this much. Also I have put some rules but it does not seem to work. I have used formatting rules which have "does not contain" filter. This filter are also working with only two glitches. 1. The rule does not behave as per the Does not contain logic but seems like taking the logic of "does not begin with". 2. It is case sensitive.

    Can you help me to resolve this?

  • 03-10-2013 11:14 AM In reply to

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    Hi Den12,

    Thanks for the great post. Does your form use REST or does it pull all of the entries into the form?

    REST partial queries can be a bit slow when executing on large data sets.

    For the latter approach of filtering in the form, it can lead to lags on load or when the data is queried. Obviously, depends on the amount of data - short lists aren't an issue and for browser forms running on a ShP server where the content DB is on the same box, even a couple thousand items won't be a huge perf hit, unless you display them in a dropdown. For non-browser forms, however, all data will first have to be copied to the local computer, so this would not be a good way to do it.

    Just trying to understand your approach and reconcile it with what we have prescribed...

    Patrick Halstead
    Project Manager at Qdabra
  • 03-11-2013 05:37 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    Yes, my filter is not case sensitive.

    I will should be able to post the rules in the next 24 hours. Unxpected projects poped up, so I apologize for the delay. 

  • 03-11-2013 06:25 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    Hi Patrick,

     First,  I would like to apologize for the delay as a couple of unexpected projects needed my attention.

    Yes, the demo will pull all actual (EVENT records) entries into the form (no miracles, just presentation):

     - I did not use the query fields for the master, EVENT library demo( I could, of course), thus I am not trying to limit the result set delivered to the client, just to present it better.

       I am aware of the potential performance hits. If you ask me, Microsoft obviously ignored the input from the developer community and I will stop at that. I can write a whole article just on this separate topic " What SP should have had ..".

     - Only the Master , EVENTS library is pulled.

     - The detail, drill-down, is pulled on a user click and it does use the query fields.

    Detour: 

    Correct me If I am wrong, but unfortunately SP does not seem to provide a way to query anything directly (especially join tables/list/libraries) and the hoops one has to jump through to get something a simple is...no words for it. Maybe we should create a separate topic for security of over the web queries, which I do not intend to have here.

    I will not even go as far as talk about SP as a "real-time" capable system. As a developer I have used a number of platforms in my past and RDBMS like Oracle and SQL server. They provide a way better methods to handle queries than anything I have seen Sharepoint-related, so far.

    If one has their DB on one of those RDBMS systems, I would have a dedicated web-service allowing for queries and partial-match searches. It may not be universal, but will be able to work for a particular DB schema/project. Not to mention that additional encryption (additional to any SSL) would be needed on any web-service, if you intend for use over the web. Extra security: How about encrypting the SOAP envelope? I have done it (not Infopath) and know it could be useful.

  • 03-11-2013 10:08 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    The attached screenshots here are related to visually filtering the results to show only those matching the criteria selected.

    - The rule is implemented on the repeating node itself. The partial-match filter was intended only for the master query in this case (Events).

    - matching Phase & Status filters is easy as they are either populated with choices (split-choices) from the Form Library Field (Form Status) OR hard-coded to match the options for this field.

    - the third filter seen is using an expression to achieve the non-case sensitivity on the Title.  

      The filter is:

    not(contains(translate(d:CPAR_x0020_Title1, "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), translate(xdXDocument:get-DOM()/my:myFields/my:TitleFilter, "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ")))

     - To achieve the alternating coloring of each row (works on the full result/retrieved dataset) the following formatting rules/conditions can be applied to the same repeating node:

    "count(preceding-sibling::*) mod 2 = 0" - set a color for even numbered rows OR "count(preceding-sibling::*) mod 2 = 1" and set a color for the odd numbered rows.

     - The alternating coloring will work on the whole result dataset, not only on the filter, thus if the filter is used, not every two consecutive rows will actually be colored in alternating colors, which is showcasing exactly the difference between a filter and a query. Filter is usually applied client-side and for visual presentation purposes, Query is to limit the actual number of records sent from the server (DB-server or App server).

      - The drill-down, detail table, is actually executing a query on the "Event Actions" custom SP List. The action is triggered by clicking the green-dot-button and triggering the rule on the button, which queries the secondary list/connection, which is never loaded by default or even on Form Load event. That may alleviate some concerns about performance, as there might be much more event actions 3-10 on average for each Event record in the form library.

    ... the dynamic count is just slightly different and will be shown in the next post


  • 03-11-2013 10:19 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    The dynamic count of the records in the Events (top-level) repeating table:

    As you can see from the screenshot it is implemented as a default, recalculating value on a field bound on the Main connection (dcount).

    The formula is using a filter very similar to the one for hiding the non-matching rows.

       - The first 2 conditions are for Phase & Status on the "dcount" field are  about same , just using contains instead of the 'does not contain" for hiding in the previous post.

       - the third is for the Title filter edit-box:

    "contains(translate(d:CPAR_x0020_Title1, "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"), translate(xdXDocument:get-DOM()/my:myFields/my:TitleFilter, "abcdefghijklmnopqrstuvwxyz", "ABCDEFGHIJKLMNOPQRSTUVWXYZ"))"

    Hope that clarifies any confusion.


  • 03-11-2013 10:41 AM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    A couple of notes on the case-sensitivily and Infopath:

     1) I have not seen or read about anything, besides the translate function shown, which could be facilitated to achieve the non-case-sensitive filter.

     I still can not believe, that for an app as Infopath a full list (there was was a typo, sorry) of the standard string-handling routines could not be included. As a developer, I could only conclude it was done on purpose.

    My reasoning: If Infopath allows including custom code (C# or VC), how more difficult it could have been to add the string- routines. Intended for "power users"? Good, but those do not have access to functions, which if not basic I would expect to be part of any "power user" toolset.

  • 03-11-2013 12:16 PM In reply to

    • Den12
    • Top 200 Contributor
    • Joined on 01-15-2013
    • Posts 57

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    Hi tech_ware,

    I think I can.

    1. to be not case-sensitive you should use translate on both arguments in the comparison. Shown today in my most recent posts.

    2. I did not have problem with the contain/does not contain, but you will notice, that it may afffect the results if you must have non-case-sensitivive comparison. Also, have you tried to use expression instead, the same way I have shown in this thread?

    3. I saw a comment from you about your project. My Phase & Status filter are using drop-down lists, but there is no problem to have either one as a text box. Whatever the text comparison, I believe you should use "translate" function on all arguments. What this big function (translate) is doing is simulate the ToUpper/Uppercase etc. If you swap the two alphabet-set paramaters you will get the ToLower/LowerCase functional representation.

  • 07-13-2013 01:34 PM In reply to

    Re: Infopath report/Dashboard with partial-match and split-column filtering and more

    This is very cool and a great solution when the amount of data is not too great.

    One thing I like about your solution is all of the cool techniques you are using in the view. Bravo!

    For users who have many hundreds of items to filter, I think we'll continue telling them to look at REST and/or qRules since load perf would be better in those cases. 

    Cool stuff! 

    Patrick Halstead
    Project Manager at Qdabra
Page 1 of 1 (13 items)
Copyright © 2003-2019 Qdabra Software. All rights reserved.
View our Terms of Use.