How-To Create Relational Databases in SharePoint

If you have been working with SharePoint as long as we have, you may be frustrated with the fact that SharePoint does not provide the ability to create a relational data model.  In fact, all SharePoint list data is stored in a single table called AllUserData. Wow, that’s some type of crazy!  So, what if you want to create a relational data model?  Simply put, you can’t.

So, what if you’ve been asked to create a solution in SharePoint?  What if your solution has a need for one-to-many data or many-to-many data?  If you are using out-of-the-box SharePoint, well, you could create a column as a lookup, and that’s about it.  But if you are a Nintex customer, we actually have a solution for you! 

The solution requires you have Nintex Forms for on-premise and for you to also have the TekDog Productivity Controls.  You can learn more about our productivity controls here…..

If you haven’t used Nintex Forms before then you, my friend, have been missing out.  Nintex Forms provides you the ability to replace the standard SharePoint form for Display, Edit and New forms.  Nintex provides additional functionality for layout, business rules, and the ability to also do custom coding on the form using JavaScript.  All-in-all, Nintex Forms is a pretty amazing tool.  That being said, Nintex Forms still has SharePoint limitations regarding relational data.  Its solution of having a Repeating Section with data bound in XML, means making relational data models are just not possible. 

That’s where the TekDog Productivity Controls for Nintex Forms can take over.   Our form controls allow for you to create parent-child relationships using native SharePoint lists.  Our control allows you to setup a child record set and for that record set to be displayed and manipulated in a Nintex Form.  The resulting data is stored with appropriate keys and foreign keys in SharePoint lists.

In the image below, we have created two lists, one for the Parent (ExpenseReports) and the second for a child record set (Expenses).

Using Nintex Forms, we can create a new form for our parent record set (ExpenseReports).  You will notice that a new column is added to every form when you have our Productivity Controls installed.  That column allows for us to create a Key field for our parent record. 

Next, we can drag our Subform Control onto the Nintex form. 

Next, we can simply configure the form control to reference our Expenses list as our child data set. 

Lastly, we need to simply Delete off our TekDogExpenseReportGuid field from our form and Publish our Nintex Form.  Add a new Expense report and see the magic!

blog-parentchild5.PNG

And the best part… all the data is being stored in SharePoint lists!  See below:

Our parent record is added to the parent list.  Please note the TekDogExpenseReportGuid which is the Primary Key (PK) for the record.  

Our child record set is displayed below.  Please notice the Foreign Key (FK) relationship being displayed in the TekDogFkExpenseReportGuid field.  

As you can see from this example, you can, in fact, create relational data models in SharePoint using Nintex Forms and TekDog Productivity Controls.  If you would like to get a trial of Nintex Forms, TekDog Productivity Controls, or both, please fill out the form below!

Try our Productivity Controls, risk-free for 45 days!