General

Re: Filter data queried from Secondary Source

Hilary Stoupa — Tue, 31 Jan 2012 19:49:22 GMT

You were patient too as I kept hacking up that XPath. :-)

Glad it is all working now!

Re: Filter data queried from Secondary Source

Sailor — Tue, 31 Jan 2012 19:23:25 GMT

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!

Re: Filter data queried from Secondary Source

Hilary Stoupa — Tue, 31 Jan 2012 19:16:21 GMT

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] )

Re: Filter data queried from Secondary Source

Sailor — Tue, 31 Jan 2012 19:12:41 GMT

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]

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.

Re: Filter data queried from Secondary Source

Hilary Stoupa — Tue, 31 Jan 2012 17:43:51 GMT

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]

Re: Filter data queried from Secondary Source

Sailor — Tue, 31 Jan 2012 17:04:45 GMT

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

Re: Filter data queried from Secondary Source

Hilary Stoupa — Tue, 31 Jan 2012 16:44:45 GMT

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.

Re: Filter data queried from Secondary Source

Sailor — Tue, 31 Jan 2012 16:25:54 GMT

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...

Re: Filter data queried from Secondary Source

Hilary Stoupa — Tue, 31 Jan 2012 15:56:36 GMT

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.

Re: Filter data queried from Secondary Source

Sailor — Tue, 31 Jan 2012 15:10:26 GMT

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.

Re: Filter data queried from Secondary Source

Hilary Stoupa — Mon, 30 Jan 2012 22:36:51 GMT

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...

Re: Filter data queried from Secondary Source

Sailor — Mon, 30 Jan 2012 22:30:58 GMT

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.

Re: Filter data queried from Secondary Source

Sailor — Mon, 30 Jan 2012 21:22:16 GMT

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!

Re: Filter data queried from Secondary Source

Hilary Stoupa — Mon, 30 Jan 2012 20:52:39 GMT

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....

Re: Filter data queried from Secondary Source

Sailor — Mon, 30 Jan 2012 20:31:49 GMT

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???