none
Complex forms in SharePoint 2010 site

    Question

  • I have to develop a website in SharePoint 2010. The website will have many complex forms from multiple database tables. I am new to the sharepoint and don't know how to start it and which features will be used.

    1- Should I first design my database in SQL Server or is there any option in the SharePoint for database design? 
    2- How to develop a form with SharePoint2010 which may update multiple tables or get record from multiple tables. It may have some dropdown fields also.
    3- How to authenticate the users with stored users in database tables.

    Thanks for help.

    Wednesday, April 25, 2012 3:22 PM

Answers

All replies

  • usually, you don't need to design SQL server database table structure for SharePoint, SharePoint will handle database storage itself. Instead, you just plan for Lists and Content Types, concepts in SharePoint simliar to table in SQL server.

    And once lists and content types a created, default forms come with them too, although you can customize forms with SharePoint designer or InfoPath.

    If you already have existing SQL server tables in use and you cannot migrate them to SharePoint lists, you can consider external content type with BCS: http://msdn.microsoft.com/en-us/magazine/ee819133.aspx

    to authenticate with existing user database, you can create custom membership provider (http://msdn.microsoft.com/en-us/library/6tc47t75.aspx) and configure SharePoint web application to use Form Based authentication; or create custom STS http://msdn.microsoft.com/en-us/library/ff955607.aspx and configure SharePoint to trust it.
    Thursday, April 26, 2012 6:02 AM
  • Thanks for replying GuYuming. The website involves complex data forms where information is saved in steps. I don't know about the infoPath, can we design forms in Visual Studio? In SQL Server while database designing we can specify the keys and foreign key relations, how we can do that with SharePoint Lists.

    Wednesday, May 02, 2012 9:42 PM
  • 1- Should I first design my database in SQL Server or is there any option in the SharePoint for database design? 

    SharePoint is a different animal. The basic/core concept in SharePoint is list.  Lists are different from traditional tables. In SharePoint, lists are separated from each others.
    2- How to develop a form with SharePoint2010 which may update multiple tables or get record from multiple tables. It may have some dropdown fields also.

    If you really want to do so, you will need to use a combination of InfoPath, Workflow, BCS, VS.NET programming
    3- How to authenticate the users with stored users in database tables

    In a typical way, you will wrap anything in BCS (Business Connectivity Services)

    I don't know about the infoPath, can we design forms in Visual Studio?

    No, you cannot, you can only design InfoPath forms in InfoPath Designer.

    In SQL Server while database designing we can specify the keys and foreign key relations, how we can do that with SharePoint Lists.

    SharePoint is a different animal.  SharePoint doesn't enforce relationships between lists even you can define a lookup field from one list to another.

    It sounds to me that you are familiar with traditional asp.net programming. If you want to do something with SharePoint, you will have to switch your mindset.

    Enforcing foreign key relationship between SharePoint lists will need extra effort.

    Wednesday, May 02, 2012 10:01 PM
  • Thanks for replying Jason Li. I am using asp.net since last 8 years but just a few days back I started the SharePoint, its my first project.

    "If you really want to do so, you will need to use a combination of InfoPath, Workflow, BCS, VS.NET programming"

    Which part will be completed in InfoPath, which one in Workflow, which one in BCS and which one in VS.NET programming. and how all these will be put together to for a working form? In VS.Net which template need to be used?

    Wednesday, May 02, 2012 10:21 PM
  • For setup relationship between SharePoint 2010 lists, you need the lookup column. Please refer to http://msdn.microsoft.com/en-us/library/ff798514.aspx in http://msdn.microsoft.com/en-us/library/ff798364.aspx

    Monday, May 07, 2012 7:55 AM
  • Option 1:

    In VS.NET  - develop web services to update multiple tables or get records from multiple tables.

    In InfoPath - consume these web services by defining data connections to use them.

    Option 2:

    In VS.NET -  develop workflow custom actions to update multiple tables or get records from multiple tables.

    In Workflow - invoke these workflow custom actions

    Option 3:

    BCS -  consume tables via BCS

    Monday, May 07, 2012 5:33 PM
  • 2- How to develop a form with SharePoint2010 which may update multiple tables or get record from multiple tables. It may have some dropdown fields also.

    Relational database application tends to use highly normalized table design, which reduce data redundancy, but increase the difficulty for user interface creation. 

    Suppose you three related tables: Country, State/Province, City. If you denormalize them into a single Excel worksheet, although data redundancy increase, but Copy, paste and AutoFill is so handy in Excel, so total effort for maintaining the data decrease.

    SharePoint lists is usually no so highly normalized. If you still need to update multiple list, you can create ASP.net page in VS2010 and use the SharePoint object model. Or you can make use of SharePoint event handler or workflow. You can also use create use tools such as Access 2010 or Infopath 2010.

    Tuesday, May 08, 2012 2:58 AM