General

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Fri, 29 Jul 2011 20:03:54 GMT

I ended up not using the SQL based solution, and instead did the following to number my contracts sequentiually:

-Created a new list called NumberedList

- Upon submit, the form does the following via a rule:

1)Query NumberList

2)Set the Contract Number to Max(ID)+1 (max id in the numberlist)

3)Create a new item in Numberlist using CAML (followed this tutorial: http://blogs.msdn.com/b/infopath/archive/2007/03/26/submitting-to-a-sharepoint-list.aspx)

4)Submit the form to the library

This solution will work good enough for me as the only chance for overlapping numbers is if items are submitted at the same time (highly unlikley given we only do a few contracts a week)

Thanks very much hilary for all your help with this.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Thu, 28 Jul 2011 20:06:40 GMT

I'm using 2008...It appeared to be a firewall issue. Now I am getting a login failed for user error...lol, I can't win! Edit: I think I understand. I'm using Win Authentication, so it won't take any other credentials but what I'm logged in as.

I thought I would be able to use any Active directory login, but it doesn't like it.

This is going to present an issue where everyone who is using the form needing to have access to the db. But I think I can work through that.

One last question-If I wanted to run the code in the tutorial you linked (thank you by the way) from a rule, (instead of on save as you have in that) how do I do this?

Or, Using submit code? The code you provided doesen't appear to work....I get all kinds of problems when I try to paste it into where it says "put your submit code here".

What am I doing wrong?

Thanks!

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Thu, 28 Jul 2011 19:57:50 GMT

Here's a tutorial from MSDN - for SQL 2005, not sure what version you are using?

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Thu, 28 Jul 2011 19:47:07 GMT

Awesome! Thanks Hilary! I am just having an issue with one part. When I try to connect to the sql database (to create the data connection) I get a server does not exist or access denied error. Strange, because I'm using the correct credentials. I'll keep fooling with it.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Thu, 28 Jul 2011 17:21:15 GMT

Here's one approach for sequential numbering.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Thu, 28 Jul 2011 17:17:02 GMT

No change with a new view =(

I actually have another issue i'm trying to overcome, and I seem to remember seeing your name on one of the forum posts regarding this- I'm trying to get sequential numbering for my forms. I understand the most reliable way to do with is to use an SQL database to store and retrieve the numbers....do you have any thoughts/suggestions on this? Or any articles you know of they explains how to do this?

If I'm mistaken, and it wasn't you, then please disregard =)

Thanks again for all your help.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Thu, 28 Jul 2011 16:35:00 GMT

Try a clean view in the form. Add a new view, just copy in your section w/ the categories and other drop down... just on the off chance it is something else in the view causing this.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Thu, 28 Jul 2011 16:26:11 GMT

Hilary, Repeating groups would be great, but I'm actually taking values from this form and generating a word doc, and I didn't know how to retrieve values from specific rows in a repeating table, so I just did this. My secondary source is not large at all, it only has maybe 50 entries. Converting to dropdown had no effect. I really, really appreciate all the thought and time you've put into trying to help me. I think I may just be stuck with the lag. Thanks again for trying to help!

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Thu, 28 Jul 2011 15:42:34 GMT

Does the rule work on the drop down in the client? If so, you need to adjust your postback settings on the control. But you are right, this type of scenario won't work in a repeating section, or a set up like I see in your form, where you have multiple drop downs bound to the same data source (by the way - next time, you may just want to use a repeating group.... you can set how many instances are included by default in the default values if you need 9).

I'm starting to wonder if your secondary data source is very large? Lots of rows?

Otherwise, I'm about out of ideas, I'm afraid..... What happens if you change those combo boxes to drop down boxes - any change?

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Thu, 28 Jul 2011 14:57:56 GMT

Hilary, I figured it out (see my edits above) For whatever reason the rule to query data isn't working on the dropdown.

I made a button to do the query, and that does work, however it causes the same flash/lag we were trying to avoid in the first place.

I also realized that your method (while brilliant) won't work for this application, because I have several lines of the same dropdowns. (my fault for not mentioning that) The reason I am not using a repeating table for these is an even more complicated story, but suffice it to say I can't use a repeating table for this particular application.

It appears that the reason for the flash/lag is the query. Why that is, I don't know. =(

I would be happy to attach the form so you could take a look, and it would be very much appreciated!

Please note that only the first set of dropdowns are using your method, the rest are using my old method.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Thu, 28 Jul 2011 14:36:39 GMT

Drag the data nodes from your secondary data connection on your second list onto your form so that you can see what data is returned. You won't want any filtering on your dropdown with this method - in theory, you should be returning "pre-filtered" data from you list. If you would like me to take a look at your form, just attach it under the options tab to a reply.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Thu, 28 Jul 2011 13:37:58 GMT

Hilary, I've done your suggestion, however the second dropdown (service description) field does not have anything in it. I'm not sure if I did it right, here is what i did:

-Category Dropdown uses Data Connection 1

-When the Catagory dropdown changes, a rule says to set the query field for "category" in Data Connection 2 to what is currently in the category dropdown, then to query data connection 2.

-The Service description dropdown is set to use data connection 2, and is set to display the Service description data field.

Did I set this up right?

I should point out that both the data connections point to the same list....is this going to be a problem?

EDIT: Nevermind, even with two separate lists (one with just categories) the second dropdown still has nothing in it. But, it's obviously filtering in some way, just not correctly =(

Thank you again for taking the time to help!

Another edit: I figured out the issue. when I change the filed, it's not querying. If I set a button to do the query, the filtering works. (though the form still flashes)

Any ideas why the query rule isn't working? This is like pullin teeth =(

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Mon, 25 Jul 2011 16:56:43 GMT

So, you'd have one data connection that your category drop down uses - and another data connection is used by the drop down with the service descriptions.

You'd set the Category field in the query fields for the new data connection to the value of Category in your main data source (this is going to be in rules on the Category field in your main data source). And then run the query. And the data fields from that second data connection should be used for the drop down options in your Service Desccription - your instinct is correct.

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

johnnyr — Mon, 25 Jul 2011 14:21:44 GMT

Hilary, Thank you for the suggestion, but I'm a little unclear on how this should work. So I should set a rule on the Category dropdown box, so that when it changes, to set the query field for the second data connection to....what? And I should use the second data connection for the Service Description dropdown, but use the data fields? Thanks for taking the time to help!

Re: Cascading Dropdowns using Filtered Data from sharepoint - causes form to flash/lag?

Hilary Stoupa — Sat, 23 Jul 2011 13:04:46 GMT

SharePoint 2010, right? There are query fields in list connections now.... so you may want to try a second connection to the list to return the Service Description data. Don't run it on load. When Category changes, set the query field in your list connection to the Service Description to that value, then query the data source. Oh, and set the Service Description drop down to use the new data source.

I've not actually tried this before - but I remember using a list connection to ShP 2010 and being surprised by the presence of query fields - can't imagine why they'd be there if not for dynamic queries.... Another option for dynamic ShP 2010 queries is a REST connection to the OData ListData.svc, but I'd try the list data connection first, since you have a straightforward "=" scenario.