SharePoint Integration

Re: Repeating table and SP2010 lists?

rajkumar.bathula — Tue, 31 Jul 2012 18:18:17 GMT

 
Hi Patrik,

Need your help.

my requirement is: dynamic multi select lookup list based on dropdown value change. and both these
 controls are in repeating section. using InfoPath 2010 and code behind file. 


I am not able to find any steps or code to populate multiselect lookup with some dynamic values that i am getting from a SP List.
I am having a dropdown control and in its changed event, i am executing code to get splistitems and frm which i want to add to 
multi select lookup control. when dropdown value changed, then respective items will be re-populated in multi select list box by clearing old values.

i tried out of methods like below.
1. using REST service. however, i achieved fine in dev environment. but on production and staging, i  am 
getting state service error msg. and so left this method after trying various fixes.

2. using xml file. that i am going to bind to multi select lookup. and in drop down change event code, i am 
populating xml with option node with value and display items. however, this wont fulfill my requirement. because, my
dropdown and multiselect lookup are in a repeating section. Means user can insert one more section to select another 
value in dropdown and populate one more set of values in multi select. Because, i am using same xml file as source.

please help me out with some other solution, in making my requirement fulfill.

thanks in advance.

Re: Repeating table and SP2010 lists?

Patrick Halstead — Fri, 14 Jan 2011 11:56:58 GMT

Glad to hear you downloaded some of the accelerators.

We don't make much money from the accelerators (and none from this site which costs us a lot every year). We're here to help you bridge the InfoPath gaps. Let us know id you need help. We're here to make you shine.

Re: Repeating table and SP2010 lists?

emcandrew — Thu, 13 Jan 2011 23:22:48 GMT

Patrick Halstead:

Hi,

We map repeating data to SQL all the time using our Database Accelerator (DBXL). It's simple to install and use the free trial and it's very inexpensive if you decide to go with it. We'd be happy to show you a 10-15 minute demo of how you can set it up (either on a local computer or a server) to map your forms and create simple reports. You can still use SharePoint as a dashboard onto the data (we recommend the Data View webparts). Send email to Support at Qdabra dot com if you want a quick demo.

Of course, you could create multiple SharePoint lists too and use qRules to map repeating data to them. It's more work to setup and change down the road and you can't point Excel at multiple, but it will work.

Cheers,

Thank you Patrick, I very well may do this as I have a lot more left to do with this project. I thumbed around on the Qdabra site with the DBXL options earlier. Looks like I may need to explore further. I also downloaded qRules, but haven't really messed with it yet.

Re: Repeating table and SP2010 lists?

Patrick Halstead — Thu, 13 Jan 2011 22:39:30 GMT

Hi,

We map repeating data to SQL all the time using our Database Accelerator (DBXL). It's simple to install and use the free trial and it's very inexpensive if you decide to go with it. We'd be happy to show you a 10-15 minute demo of how you can set it up (either on a local computer or a server) to map your forms and create simple reports. You can still use SharePoint as a dashboard onto the data (we recommend the Data View webparts). Send email to Support at Qdabra dot com if you want a quick demo.

Of course, you could create multiple SharePoint lists too and use qRules to map repeating data to them. It's more work to setup and change down the road and you can't point Excel at multiple, but it will work.

Cheers,

Re: Repeating table and SP2010 lists?

emcandrew — Thu, 13 Jan 2011 22:33:03 GMT

Lets say in Visual Basic or ASP...

I would have created 3 tables in my database, 1 for the Employee data, 1 for the Language data, and one for the Score data. Then I would build the relation in code through the use of joins or create a view for the joins. I would populate the language list with its respective table data and hard code the score drop down. I would then submit back into the Score data table with (user_id,language_id,score). Then pull the relation at run time or during reports.

I wouldn't try to embed multiple rows within one table row. No excuse for why I was trying to do it with this list.

For what it's worth, my real brain buster was that I was hung up on thinking I needed repeating tables for the functionality of what I would normally do with style sheets (hiding controls). And then of course, couldn't build a relationship. It was a given all along that I could do multiple fields, but I didn't see how I could hide them from the user (until I found that I could do this with rules).

Re: Repeating table and SP2010 lists?

warrtalon — Thu, 13 Jan 2011 22:14:52 GMT

I know you keep referring to this as being very easy outside the confines of SharePoint, but unless I'm completely missing something, then I'm not sure that is the case. The reason I say this is you referred to keeping it as close to a relational database as possible, but the design you've been going for is not how a relational database works. Database don't have repeating fields either. You've been trying to insert one record into SharePoint that has two fields with multiple rows of data within them. IN a pure database scenario, there would be one record for each language/score pair, right? Also, each language/score would be associated/related to a user object from another table. That's two tables - one with unique records for each user and another with unique records for each language/score pair that also includes a foreign key connection to the user table. In SharePOint, you're trying to add both of those into one table - a single user object with multiple language/score pair rows embedded within the same row. That's not relational, so I don't think it's valid to say that this can be done with databases. This is why I suggested two methods: one horizontal and one vertical.

Horizontal is what you've chosen. This means there is one record per user with many fields going across the row (two for each language/score pair).
Vertical is where each language/score pair is a separate record (just like a database) but the foreign key relation to the user object is handled by the Created By field, which is basically a relationship to the User Information List (user object table)

I don't know how a database would be able to handle multiple rows embedded within one row of a table, which is how I've been understanding your request.

Re: Repeating table and SP2010 lists?

emcandrew — Thu, 13 Jan 2011 22:02:32 GMT

Yes, I see that now... Evidently "English" is not one of the languages that I read well, even though I'm from the states... :)

I've been in a major fog over this stuff man. I've been doing VB, C# and many other languages for years, but am just now going back into SharePoint and subsequently InfoPath. Haven't messed with SharePoint since it was FrontPage extensions. It's very frustrating knowing that I can "whip" something up in mere minutes for something as simple as this if I wasn't trying to stay within the confines of a SharePoint list. Suffice it to say that once I get the skeleton up and running I'll be looking at some straight development methods.

Re: Repeating table and SP2010 lists?

warrtalon — Thu, 13 Jan 2011 21:37:27 GMT

That was one of the two suggestions I gave much earlier in the thread.

warrtalon:
InfoPath list forms cannot have repeating data like this, so you need to decide if you're going to be ok with having a separate list item per language per person or not. If not, then you're going to have to create a non-repeating table with 12 rows and a header that contains one column for language and another for score

Re: Repeating table and SP2010 lists?

emcandrew — Thu, 13 Jan 2011 21:33:28 GMT

Thank you for all of your replies. After much thought, I have given up on it and come up with an acceptable work around. I've created 8 static fields for languages and 8 static fields for scores. Presumably, it would be a very rare occasion that one person would know more than 8 languages. I then used some rules to hide the controls based on the input of the previous control. Adding this all to one cell gives the illusion of the dynamic controls that I was looking for.

Thanks again...

Re: Repeating table and SP2010 lists?

emcandrew — Thu, 13 Jan 2011 16:14:40 GMT

Hi Mel...

Thank you for the reply. My main data source itself is a sharepoint list. I can (and have previously in testing, and then again now following your steps) add secondary data connections and populate the lists accordingly. The problem is, that I need the repeating table behavior of allowing my end user to "add a new row" so that as the linguist info is being entered, they can select all applicable languages from a dropdown and affix a corresponding language score to it. I basically need the exact functionality that is in the template, but then write the data to a sharepoint list.

As an aside, I did look into using libraries which is what I've seen recommended for more complex forms. The downside (as far as I can tell) with using a SharePoint library is that each entry appears to be a document of its own. I'm really trying to keep this as close to a relational database as possible. I think I'm going to investigate straight up database options. This just seems to be a pretty trivial need.

Re: Repeating table and SP2010 lists?

Mel Clemente — Thu, 13 Jan 2011 10:35:53 GMT

Hi,

I looked at your template and have tried to achieve the same functionality using a SharePoint list. I might be wrong in understanding what the real goal is, but here are some steps you can try, and then let me know if that's what you want.

Add a data connection to your SharePoint list > set it to automatically retrieve when the form opens.
Right-click on your first drop-down list box (language) and select properties.
In the List box choices section, select 'Get choices from an external data source' - the data source would be your SharePoint list; Entries is the repeating node from your list, Value/Display Name should correspond to the language field/column.
Do the same for the second drop-down (score) pointing to the corresponding score column on your list.

Doing the above, your main data source is being populated from your SharePoint list; the values you select will be stored in your form.

You'll be needing qRules if:

You wish to copy the items from your SharePoint list into your main data source to store all the items in your form (CopyTable command)
You wish to be able to submit and update your SharePoint list items using the form (SubmitToSharePointList command)
Anything else you wish to do that SharePoint/InfoPath won't allow without code - let us know if there's any!

Hope that helps... again, please let me know if I'm misunderstanding.

Cheers!

Re: Repeating table and SP2010 lists?

emcandrew — Wed, 12 Jan 2011 21:59:52 GMT

emcandrew:

My apologies for the delay in replying. Our office was closed due to inclement weather.

I've tried the repeating sections trick outlined earlier. I created a new list and selected the option to "Manage multiple list items with this form". Perhaps I'm still failing to understand how to utilize this properly, but it seems to want to repeat all of my sections/controls when the user "Adds" additional languages?

Attached is a template on what I would like to do, but within a SharePoint list. I believe the core problem is that I can't add repeating groups to my Main data connection in a list as I understand that SharePoint doesn't support this. I don't understand any way to work around it but it seems such a simple need that I find it hard to believe that SharePoint/InfoPath can't facilitate it in some fashion.

Will qRules do this by chance?

Re: Repeating table and SP2010 lists?

emcandrew — Wed, 12 Jan 2011 16:52:18 GMT

My apologies for the delay in replying. Our office was closed due to inclement weather.

I've tried the repeating sections trick outlined earlier. I created a new list and selected the option to "Manage multiple list items with this form". Perhaps I'm still failing to understand how to utilize this properly, but it seems to want to repeat all of my sections/controls when the user "Adds" additional languages?

Attached is a template on what I would like to do, but within a SharePoint list. I believe the core problem is that I can't add repeating groups to my Main data connection in a list as I understand that SharePoint doesn't support this. I don't understand any way to work around it but it seems such a simple need that I find it hard to believe that SharePoint/InfoPath can't facilitate it in some fashion.

Re: Repeating table and SP2010 lists?

ErnestoM — Mon, 10 Jan 2011 16:12:41 GMT

To attach a file, click Reply, then switch to the Options tab. You'll find the attachment control there.

Re: Repeating table and SP2010 lists?

emcandrew — Mon, 10 Jan 2011 15:13:47 GMT

OK cool... I'll give it a re-read. I'm sorry.