InfoPath Dev

Hello,

I'm trying out qRules for the first time to see if it will do the trick for us.  I have a text box (Invoice Number) on an InfoPath form.  After being filled, I would like to display all other invoices in the same library with the same Invoice Number in a repeating table below.  I have followed the example in the "Filter a SharePoint List on the Server" document by doing the following:

• I have added a rule to my InvoiceNumber text box that sets the Command value when the field is not blank to:

concat("FilterOwssvr /dsname=FilterOwssvr /filter=Invoice_x0020_Number /value=", .)

• I have created a secondary datasource called FilterOwssvr based off an XML document I retrieved from the server as per the instructions.  I have indicated NOT to automatically load this datasource on form load.
• I have added a repeating table below that is bound to the FilterOwssvr secondary datasource and displays 4 columns.
• I added QdabraRules.xml directly from my local machine and asked it to automatically load on form load.  When I "Injected" Qdabra into my XSN, it pointed to a path on my SP server but couldn't actually find the file there so I redirected it to my local machine.

When I run the form and modify the Invoice Number field, the repeating table remains empty.  If I add an action to Query for Data and choose FilterOwssvr, the table DOES get populated but shows everything from the list, not filtered on matching Invoice Numbers.

What am I doing wrong?  It seems that merely setting the Command value of QdabraRules has no effect. 

Thanks in advance!

Dima.

Hi Dima,

The first thing that catches my attention is the way you describe injection.

When a form template is injected with qRules, the QdabraRules.xm secondary data source is automatically added to the form. There is no need for manual steps to add this. Did you follow the steps in the qRules User Guide to inject your form?

The second thing I would recommend is adding the Success, Error and Result nodes into the canvas, so that you can see the values of these fields as you design and troubleshoot the command.

Once you have reinjected your form and added these fields to the  canvas, please run the command again and let us know if the Error/Sucess/Result fields are returning any additional information.

Thank you.

Hi:

First, make sure qRules is functioning correctly in your form.

Drag the QdabraRules data source onto your form someplace where you can see it. Preview the form and type GenerateGuid into the command field. Tab out -- do you have a GUID in the result field? If not, you may have accidentally added custom code to your form (if you are intentionally using custom code with your form, you may want to review this document and make sure you have it all configured correctly). You'll want to remove any custom code from the form and reinject (under Form Options / Programming, there is a button for removing code).

If GenerateGuid works, perhaps there is an issue with your command. Preview with the QdabraRules data source on your form someplace and run your command. Do you have an error when you run the command?

Ernesto also makes a valid point. I've seen some instances where the QdabraRules data connection gets modified to use an external file, but I've not been able to repro. You definitely want that XML as a resource file in your form (not a local file, not an external XML on SharePoint).

Thanks to both of you for replying so quickly! 

I have not re-injected my form yet.  I did however put the command, result, success, error boxes on the form.  When I type in GenerateGuid, I get a valid GUID in the result box and true in the Success box.  When I type in concat, I get "Invalid command specified: concat" in the Error box and success code of False.  It should be noted I'm running InfoPath 2010.  When I type in DateDiff for fun, the error message is quite detailed:

"Usage is: DateDiff /date1=The start date /date2=The date to subtract from date1 /component=Desired portion of the date difference (days | hours | minutes | seconds | milliseconds | totaldays | totalhours | totalminutes | totalseconds | totalmilliseconds)"

So for some reason concat doesn't seem to get recognized as a valid function...  what's up with that?

Thanks,

Dima.

Ah, that's good news. No need to reinject then, I don't think.

concat needs to be entered as a function. It can't be typed into the Command node. So you'll want to set a field's value (the Command field) and then click the fx button to enter the concat function to generate the command.

Another quick question while I'm at it.  I would now like to create a filter not just on the Invoice Number but also the Vendor Name.  What does that syntax look like given my existing value looks like this:

concat("FilterOwssvr /dsname=FilterOwssvr /filter=Invoice_x0020_Number /value=", .)

I have tried the following but get a syntax error: 

concat("FilterOwssvr /dsname=FilterOwssvr /filter=Invoice_x0020_Number /value=", ., " /filter=Invoice_Vendor /value=", VendorName) 

concat(concat("FilterOwssvr /dsname=FilterOwssvr /filter=Invoice_x0020_Number /value=", .), " /filter=Invoice_Vendor /value=", VendorName)

The error returned each time is:

Action: FilterOwssvr. Arguments: dsname: FilterOwssvr filter: Invoice_x0020_Number value: 1111 . Details: An item with the same key has already been added.. Stack: at System.ThrowHelper.ThrowArgumentException(ExceptionResource resource) at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add) at Qdabra.Tools.RulesLibrary.Commands.SplitCommandLine(String commandline) at Qdabra.Tools.RulesLibrary.Commands.ExecuteCommand()

Separate your filters and values with commas:

Thanks so much!  That worked.

Now that I'm burning through my obstacles, which of your documents should I read to find out how to retrieve a distinct list of Vendors from the existing SP List to make them available in a drop down?  I don't want to bring back all rows and then exlude duplicates because in future this list will contain thousands of entries.

Thanks,

Dima.

You are smart to be thinking about that in advance.

What is the source for the Vendor field in your list? Are people hand typing that in? If so, you can end up with a bunch of different spellings, abbreviations, etc, all meaning the same vendor:

Smith Frozen Foods
Smith Frzn Fds
Smith Frozen Foods, Inc

And so on. Is there some sort of master vendor list that won't have duplicates? If so, you'll want to use that. A list like the one above is pretty easy for a human to understand ("Gee, those are all the same vendor!") but not so easy for a computer.

If not -- well, owssvr data connections can connect to a specific view. It seems like you may be able to create a special view of your list that just displays distinct vendors and use that for your data connection, but my guess is that you'll have to use a data view web part for that, and I've not tested pointing an owssvr.dll data connection to one of those before....

You are, of course, correct (not about me being smart).  I should be using a vendor list with unique names and disallowing manual entry on forms.  I was really just trying to see what your control was capable of and unique filtering would have been a nice touch.

That said, when I deployed the form to my server, the FilterOwssvr code doesn't appear to run at all.  I can see the Command variable getting populated with the right stuff but the rest of the variables remain empty and the repeating table remains blank.  Do I need to do some SP Admin magic to allow this to work?  Happy for you to point me at the appropriate doc if I've missed it.

Thanks,

Dima.

Starting on about page 10 of the user guide, you'll see info on admin approving your form -- is this a browser form?

You also may need to adjust the post back settings on the control that run the command to "always".

Ok, I have gone through the manual, deployed the form as an Admin, added the "Always" check.. and made some progress but not enough.

Now when I make a modification to my VendorNumber field that kicks off the data refresh, Success is set to false and Error is set to "failed to execute query". I have confirmed that qRules are working by typing in various commands into the Command box to see appropriate error messages are displayed.

Any other thoughts?

If you are using SharePoint 2010, the url you use for owssvr.dll will need to include "noredirect=true" on the url -- could it be that?