Skip Ribbon Commands
Skip to main content

Colorado SharePoint Users Group (COSPUG)

:

InfoPath & SharePoint Designer:Here's one for the new InfoPath MVP :FlatUse SHIFT+ENTER to open the menu (new window).Open MenuOpen Menu

Please feel free to ask your InfoPath and SharePoint Designer questions here for both 2007 and 2010.  Clayton Cobb will be here to answer them or ensure they get answered...
Posted ByUse SHIFT+ENTER to open the menu (new window).
  
Post
Started: 4/1/2010 10:23 AM
Picture Placeholder:
Here's one for the new InfoPath MVP...

Hi Clay,

 

I'm curious to know how you would set this up.  Here's the situation.

 

I have an InfoPath 2010 form that has two drop downs on it.  One drop down contains cities (Denver, Dallas, etc) and the other contains Office locations within the cities.  Both are based upon external lists within a SharePoint 2010 environment.  The backend to the external lists is SQL 2008.  The Offices table has a foreign key representing the ID of the Cities table.

 

What I'd like to see happen would be, if you select a city in the Cities drop down, it would pull only the offices related to that city in the Offices drop down.

 

If there is a blog post or forum post that relates to this topic, I'd be appreciative - I didn't find one in my Bing search.  Is there some logic/functionality I can utilize in InfoPath that would do this type of filtering for me?  Or, would it be more efficient to set it up through the external content types?  Or is there something I am missing?

 

Thanks in advance.

Posted: 4/1/2010 3:46 PM
Picture: Clayton Cobb
Clayton CobbNo presence information

I doubt there is any sort of blog post out there for this, but if there were to be one, it would most likely be on the InfoPath team's blog site.

 

I haven't tried this yet with External List data connections, but the approach that first comes to mind is to use the new parameterized query ability within our SharePoint list data connections to pre-filter the results set with a query parameter defined in the form, such as your Cities dropdown.  Basically:

 

1) Create a receive data connection to the Offices list but set it not to automatically retrieve

2) Set up your Cities and Offices dropdowns.  Upon form load, only the Cities one should populate.

2) Add a rule to your Cities dropdown with actions

3) First action is to set the query parameter of the Offices DC (set a condition that Cities is not blank.  If it is blank, then set Offices to null)

4) 2nd action is to query the Offices DC

5) Now, upon selection of a City, the Offices DC should only return values that match the City foreign key

 

I haven't tried this yet, but it sounds like a nice scenario for me to try.  Let me know if any of the above steps don't jive.

 

If you weren't using External Lists, I would suggest using Rest for it's dynamic querying abilities that can be morphed based on form metadat.

Btw, who wrote this nice question?  Didn't know folks were out there rocking out with IP2010 and BCS.  =)

 

-Clay


PS.  Thanks for people starting to use the forums - this is great!
Posted: 4/1/2010 4:55 PM
Picture Placeholder:

Hi Clay,

 

Thanks for the response - I'm going to try that tomorrow. 

 

The solution I'm creating is sort of a POC/Learning/possible user group presentation for utilizing SP 2010, InfoPath, External Lists, workflow and document sets to create a process for on-boarding employees into a company.  I've spent the last day or two creating the content type structure and now am starting to create the associated forms and such.  The one I'm asking about will be based on ordering computer equipment for a new employee.

 

I'm trying to not make it too involved so 1) I can finish it quickly and 2) easily presented and explained within an hour.  It's tough, because there are so many new and cool things to try in 2010!

 

Anyway, thanks for the direction.  This is Greg DeRoeck, by the way. :)

Posted: 4/9/2010 7:59 AM
Picture Placeholder:
Just beginning to venture into SP2010 and IP2010, but here are a few resources that might provide some ideas:

Cascading drop-downs via lists:
Datacogs
Patrick Clarke blog entry

XML example - Greg Collins - InfoPathDev

InfoPath 2003 Help (shows a SQL connection)

InfoPath 2010 - filters

Hope this helps a little bit,
Brian
Posted: 4/9/2010 11:50 AM
Picture: Clayton Cobb
Clayton CobbNo presence information
Brian, thanks for the links.  Cascading dropdowns themselves aren't difficult and just require a simple filter (although this was not available in 2007 browser forms), but in this case, we're talking about External lists, which are only a feature in 2010.  If the two dropdowns are calling data from the same table but different columns, then it's a simple filter on the same data connection, and those links help with that
 
For that last link, although the InfoPath team changed the blog name to Microsoft InfoPath 2010, the article itself is not using 2010.  It's actually from 2004, but really, the concept of cascading dropdowns is still the same.  In Greg's case, I was assuming he had two separate external lists feeding data, which is why I recommended using the new parameterized query functionality, but that again is only 2010 and not mentioned in these resources.
 
Greg, let us know what pieces are relevant for you.  I have actually tested this and have it working very nicely.  I've done it with two separate external lists that have a foreign key relationship, and I've done it with one SharePoint list and one External list (SQL DB) that have a foreign key.  In both cases, the parameterized query method of IP2010 worked beautifully, and it allows me to display the content in a mashup with a dynamic Bing map and everything.
 
I look forward to seeing where you get with the Document sets.
 
-Clay