InfoPath Dev

Hello, 

I am building a "summary of expenses" form that will allow the user to select "Office", and a range of dates to display a repeating table with purchase information from Sharepoint library.  That all works fine, except now I want to allow the user to "filter" by listbox, the status of each order instead of displaying ALL orders.  The listbox (StatusSelection) has a data source from the same document library with 5 statuses to choose.

Right now the I'm using conditional formating to hide the repeating table so entries outside of the selected dates are not included.  There must be a similar method for something like this.

Make any sense?

I've now included my sample form as I realize my entire approach may be flawed.  I am including the entire secondary datasource as a repeating table, and using conditional formatting to only show the rows that meet the date criteria set at the top.

Problem is I also can't SUM the totals because I can't filter to only sum the totals based on the date range selected. 

Maybe someone could take a quick look and set me on the right track???

Hi -

Sure, you can filter the field that is used in your Sum formula - using the same logic as you use to filter your table, pretty much, just changing to a relative path to DateOrdered. See attached - the formula I tried resolved in the formuila editor window, so hopefully it is close to right....

 Hi Hillary,

Thank you.  I adjusted the formula like your example, to update the path but the sum is 0 even when multiple rows appear.  Formula doesn't error any more.   Any idea?

 number(concat(substring(../d:Date_x0020_Ordered, 1, 4), substring(../d:Date_x0020_Ordered, 6, 2), substring(../d:Date_x0020_Ordered, 9, 2))) < number(concat(substring(xdXDocument:get-DOM()/my:myFields/my:StartDate, 1, 4), substring(xdXDocument:get-DOM()/my:myFields/my:StartDate, 6, 2), substring(xdXDocument:get-DOM()/my:myFields/my:StartDate, 9, 2)))


number(concat(substring(../d:Date_x0020_Ordered, 1, 4), substring(../d:Date_x0020_Ordered, 6, 2), substring(../d:Date_x0020_Ordered, 9, 2))) > number(concat(substring(xdXDocument:get-DOM()/my:myFields/my:EndDate, 1, 4), substring(xdXDocument:get-DOM()/my:myFields/my:EndDate, 6, 2), substring(xdXDocument:get-DOM()/my:myFields/my:EndDate, 9, 2)))

How about my other question, if you don't mind....I want the user to be able to select the "status" from the list and filter results based on that.  I don't know of any way to do that!

 Fixed my silly mistake on the SUM formula and that's working.  Now I just need to figure out how to limit the query results based on the criteria selected in the listbox.

What about if you select "Any occurrance of StatusSelection" is equal to the status field in the secondary data source? After you do that, if you change the first dropdown to "The expression" it should give you the XPath you need to add to get your sum formula right...

 Hi Hillary,

Thanks so much, I hope you stay with me just a little longer...

I have the formula working for the sum, but now I'm trying to understand how I can filter the repeating table to only show the rows which have a "OrderStatus" equal to one of the selections in the ListBox.  This repeating table source is the secondary data source.  Would I need to create fields in the main data source in a repeating table and set the individual values of those fields to the secondary data source?

I've tried a number of things but I either get NO rows, or ALL rows.

Did you try what I was showing in the screenshot in the post above this? Where in the first drop down for the conditional formatting you select any occurance of the field bound to the list box, then "is equal to" and then, in the third field, you select the OrderStatus from the repeating table data source? Attach a copy of the form with what you are trying that is not working, and I'll take a peek.

 This is what i tried:

 I added a formatting rule on the repeating table (same as the date ranges) like you mentioned. 

Any occurrence of "StatusSelection"  is not equal to OrderStatus Hide this control.  (StatusSelection is the listbox in the main datasource, and OrderStatus is the field from the secondary data source (sharepoint)).  The 4 options in the listbox are "Order pending approval" "Order approved" "Order pending delivery" "Order complete".

When I do that and preview, the rows are all hidden no matter what options I select  in the listbox.  Although the SUM stills adds all rows that meet the Office, and date selections. 

If there was some way to specify in the query of the secondary data source, what to return

I can't help but think my whole theory of how to approach this is wrong...

You have Query fields in your SharePoint list connection, so I'm assuming you are using ShP 2010. Query fields are only for equivalency, but if you have SharePoint 2010, you may have OData set up (http://ShPServerName/_vti_bin/listdata.svc - replace the bold text with your ShP Server). OData allows for complex REST queries - and then you may be able to just pull back the data you need.

 :-(  unfortunately I only have WSS 3.0 to work with.

Okay - I led you astray. I tried this in my environment, and what finally worked was this:

 not(xdXDocument:get-DOM()/my:myFields/my:group1[my:field1 = current()/d:Employee])

Which, for your list and data source, would be a condition like this for your repeating table from your secondary data source:

xdXDocument:get-DOM()/my:myFields/my:group1[my:StatusSelection != current()/d:OrderStatus]

Looking at the expression that IP built for you, the issue is the lack of current() - your existing condition looks like this:

xdXDocument:get-DOM()/my:myFields/my:group1/my:StatusSelection[. != xdXDocument:GetDOM("PurchaseOrder")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:OrderStatus]

And this would most likely work:

xdXDocument:get-DOM()/my:myFields/my:group1/my:StatusSelection[. != current()/d:OrderStatus]

 Ok, so this does kind of the OPPOSITE of what I what to accomplish, so we must be close! 

I want to HIDE the repeating table rows when StatusSelection does not equal OrderStatus.

So, as an example I changed the 

xdXDocument:get-DOM()/my:myFields/my:group1/my:StatusSelection[. != current()/d:OrderStatus]

to

xdXDocument:get-DOM()/my:myFields/my:group1/my:StatusSelection[. = current()/d:OrderStatus]

and changed the formatting to Fill in Red just to show you what I mean

Now when I select "Order Pending Delivery" I get the correct formatting in red.  - see screenshot attached

If I use the Not Equal I get no results at all no matter the selection, when using Hide this control as the action.

And again, I had it in my first sample, then went awry and dropped the "not" - sorry, my brain seems to have rolled under my desk this week. Try this:

not(xdXDocument:get-DOM()/my:myFields/my:group1/my:StatusSelection[. = current()/d:OrderStatus] )

 THANK YOU!!

Something so simple.  Thank you for your patience working through this.  I guess I was close, but not quite close enough in my first attempt!