locked
Feeding two Sharepoint lists from an InfoPath form

    Question

  • Hello,

    I would be interrested to feed data in two SharePoint lists from one InfoPath form.
    (still using InfoPath 2003, but I could move to InfoPath 2007)
    The purpose is to represent a simple master-detail relation like (purchase order) - (items of the PO).

    I did not find any related paper except this one dealing with only one single list:

     http://msdn.microsoft.com/en-us/library/cc162745(office.12,printer).aspx

    It looks quite complicated and I wonder why?

    Would there be any hope then to feed two list from a master-detail scheme?

    I would also enjoy any suggestion to get an equivalent result in a different way.

    Thanks,

    Michel

     

    • Moved by Mike Walsh FIN Sunday, May 23, 2010 8:45 AM InfoPath questions go to the InfoPath forum. (From:SharePoint - General Question and Answers and Discussion (pre-SharePoint 2010))
    • Changed type Clayton Cobb Saturday, May 14, 2011 7:48 PM q
    Sunday, May 23, 2010 7:57 AM

Answers

  • Unfortunately, InfoPath does not submit to lists - only libraries.  InfoPath also doesn't have a way of natively putting its repeating data into SharePoint as separate line items.  So, to populate lists with InfoPath repeating data, you have to use the CAML method or custom code, and I'm not sure you can use the CAML method for submitting to multiple lists.  Qdabra has a tule called qRules, and their toolset includes a "Submit to SharePoint List" rule that does this for you with some configuration.  They've written the code, and it does introduce code to your form, but you don't have to write it yourself.  I think that no matter what, though, trying to submit to two lists in a master-detail relationship will be challenging.
    SharePoint Architect || Microsoft MVP || My Blog
    • Marked as answer by Seven M Friday, June 04, 2010 7:04 AM
    • Marked as answer by Clayton Cobb Saturday, May 14, 2011 7:48 PM
    Sunday, May 23, 2010 5:06 PM

All replies

  • Unfortunately, InfoPath does not submit to lists - only libraries.  InfoPath also doesn't have a way of natively putting its repeating data into SharePoint as separate line items.  So, to populate lists with InfoPath repeating data, you have to use the CAML method or custom code, and I'm not sure you can use the CAML method for submitting to multiple lists.  Qdabra has a tule called qRules, and their toolset includes a "Submit to SharePoint List" rule that does this for you with some configuration.  They've written the code, and it does introduce code to your form, but you don't have to write it yourself.  I think that no matter what, though, trying to submit to two lists in a master-detail relationship will be challenging.
    SharePoint Architect || Microsoft MVP || My Blog
    • Marked as answer by Seven M Friday, June 04, 2010 7:04 AM
    • Marked as answer by Clayton Cobb Saturday, May 14, 2011 7:48 PM
    Sunday, May 23, 2010 5:06 PM
  • Thanks Cobb for your quite useful (clear) answer.

    As I am new to SharePoint, I cannot really understand this situation.

    Why is it that SharePoint is such a big tool when it cannot handle the simplest aspect of a database?
    Would that mean that most applications do not need master-detail relationships?
    Or would that mean that this relationship can be practically handled in a simpler way?
    It's true that forms can store master-detail information.
    Maybe there is no real need to store this kind of information in two different tables (lists).
    But then, I cannot understand why lists cannot store repeating fields and aggregated fields.
    Am I right about that?
    Why would it not be possible to store data in lists with the same flexibility as in forms?
    Would that mean that lists are not really needed and that forms can replace them more effectively?

    Today, for the first time, I feel like a dinosaur: I must have missed a few things these last years!
    Probably I did not get correctly the meaning of xml.

    Thanks for your answer, and maybe for a some further insight,

    Michel

    Sunday, May 23, 2010 7:34 PM
  • I don't know why, but it's the biggest limitation of all when using InfoPath.  I don't know how other form technologies handle it.  Due to this gap, Qdabra is a company who has built an entire database integration tool that shreds InfoPath repeating data into a SQL database, which then allows you to connect to that data from SharePoint via Data View Web Parts.  It's a tool that you have to buy, and it's very powerful, but I think the mere fact that this tool was built and gets purchased shows that it is indeed a limitation of SharePoint.  That company already spent the time to code up all of that, and it still utilizes a separate SQL DB instead of just staying within SharePoint, so I think that illustrates the point.

    I think the reason it seems strange that lists cannot support repeating data fields is because you have to think of them as database tables where each row is a separate line item (this is true), and in a database, you don't have repeating data fields within a single row.  In fact, with a SharePoint list, the list itself is a repeating table of data, and that's how it works.  This is why it's tough for InfoPath repeating data to then be used as SEPARATE line items in a list.  When you submit a form, it creates a single XML file that contains an XML schema within it.  This single file has nodes that can be promoted to SharePoint, but when you promote a repeating node, the best SharePoint can do is concatenate all the data of a repeating node into a single line of text, because the XML file is still a single row.  Of course, what you'd want to do is have the single row for the XML file and then many rows in a separate list (table) with each row associated with that item.  This is what Qdabra can do with its qRules and with its DBXL tool, but InfoPath and SharePoint themselves don't offer this.

    In SharePoint/InfoPath 2010, we are able to now do custom list forms, but all that does is let us customize the New/Edit/Display forms for most lists in place of the OOTB ASPX forms.  It does not let us create a regular form template in a form library and submit that data to a list, though.


    SharePoint Architect || Microsoft MVP || My Blog
    Sunday, May 23, 2010 7:52 PM
  • Very happy from your answers, Cobb.

    I still do not understand fully why things are as they are,
    but at least you made it clear that they are as they are!
    I am now convinced that SharePoint should not (yet?) be seen as a database.

    Sharepoint is very attractive for me,
    because my employer is planning a massive switch to Sharepoint,
    but even more because I see it will help a lot to solve our frustrating data dispersion.

    In my limited area, I tried to improve things a little bit by relying more on (sql) databases.
    The switch to SharePoint rises many questions.
    I first imagined to replace completely the smallest database by lists or by forms.
    Lists are clearly not a replacement, but forms might still be.
    For forms to be used to replace (not to feed) small databses, I simply needs some aggregating functions,
    like doing statistics on repeating items in forms (xml).

    I do hope that in the end Sharepoint will turn out to be more than an improved user interface.
    I would appreciate you comments on this too!
    Since I am working today with SqlServer databases, what would be the added value on SharePoint?
    (except that I will not use MS Access as UI anymore and that I will not need IIS)

    Thanks,

    Michel

     

    Tuesday, May 25, 2010 6:41 AM
  • SharePoint does not replace SQL, so you're right, but there are many cases where you can use SharePoint in place of a full SQL DB, and in other cases, you can just INTERACT with the SQL DB.

    SharePoint offers many, many things that can be done without even needing to worry about SQL, so maybe you will just need to start thinking differently about things.  All lists and libraries in SharePoint are still like tables in a DB, but you probably can't think of it as a database product.  Think of it as a collaboration product that can do many things, but it is not a replacement for the complexity of a true relational database like SQL.  However, there are many ways that SharePoint 2010 can be used to directly integrate with existing or new SQL DBs.


    SharePoint Architect || Microsoft MVP || My Blog
    Tuesday, May 25, 2010 7:11 AM
  • Thanks for this clarity, Cobb.

    Would you know of some reading about how to combine effectively SqlServer databases with SharePoint?

    I already had a fast reading at <cite>blogs.devhorizon.com/reza/wp.../structureddatamossintroduction.pdf</cite> and subsequent papers .
    However I would like to read first about motivations: what benefit to expect, what can be the added value?
    Secondly I would like to know what takes time, what is easily implemented, ..., in order to spare time and to learn effectively.

    For example, I always found it a much too tedious job of watching additions and changes to data from my sql databases.
    I once implemented a simplified mechanism to do that.
    I do not even mention informing people that new waited for data have arrived and eventual workflow aspects.
    Can I expect fast solutions if I combine sql with SharePoint.

    Any good readings about that?

    Thanks,

    Michel

    Tuesday, May 25, 2010 7:05 PM
  • Hi Cobb

    Does SharePoint and InfoPath 2010 bring a solution to this problem? 


    Best regards, David
    Wednesday, February 02, 2011 9:03 AM
  • Does SharePoint and InfoPath 2010 bring a solution to this problem? 


    My name is Clayton.

    In 2010, we can now customize lists with InfoPath, which is really nice, but InfoPath can still only submit to the list to which it's bound.  YOu can't create submit data connections to other lists - only other libraries.


    SharePoint Architect || Microsoft MVP || My Blog
    Planet Technologies || SharePoint Task Force
    Wednesday, February 02, 2011 3:05 PM