Jump to content
Search In
  • More options...
Find results that contain...
Find results in...

[Article] How to add database relationsship with design in invision community

Jimi Wikman

Recommended Posts

  • Owner

Database relations in Pages is a very powerful way to bring content from different databases into entries of ther databases. In this example I will show you how I added the People in project area in My Projects.

Screenshot 2020-07-18 11.51.36.png

Creating the Database

I started by creating a new database called "People Profiles". It will be a database just to hold the data as it will not be publicly presented anywhere outside of the My Projects area. just to make it easier to work with I created a page and added the database to it. I also made the page available only to me so I can use it, but it will not be visible to anyone else.

Then I decided on the fields that I wanted to use. I want to use an image, so I activated the record image. Then I went through the data I wanted to add:

  • Name
  • Title
  • Awesome URL
  • Linkedin URL
  • Instagram URL
  • Twitter URL
  • Homepage URL
  • Working area

These are the basic fields. I realized thad I will probably have multiple versions of the profiles depending on when in time I worked with them. To search for the correct profile I would need more information, so I also added a Long Title that i se as the title field. I also added a Notes field to act as the Content field in case I wanted to scribble something down for myself.

Screenshot 2020-07-18 12.02.47.png


Setting up a Database Relationship

After I added a few profiles it was time to bring them from the People Profiles database into the My Projects database. The first step was to add a new field into the My Projects database of the type "Database Relationship". When creating that I have to choose what database I want to create the relationship to, so I selected People Profiles. In the settings for display options I set a template key so I can reference it later and I unchecked the show in listing template and show in display tempate.

Screenshot 2020-07-18 12.09.12.pngScreenshot 2020-07-18 12.09.03.png


Adding the database relationship in the template

As I have selected not to display anything in the listing or display templates nothing will happen yet. So new we have to add this to our template for My Projects so we can show the data where we want it. So we head over to Templates in Pages where I have created my own template set for My Projects.


Screenshot 2020-07-18 12.18.01.png


Adding custom fields are done by adding a code line. There are some variations on this, but I will not into it in this post. This is the code:

{$record->customFieldDisplayByKey('your custom field key', 'display')|raw}

As you only want to show this field if it is actually not blank, then we wrap that in a condition to only show if it is not blank:

{{if $record->customFieldDisplayByKey('your custom field key')}} 
        {$record->customFieldDisplayByKey('your custom field key', 'display')|raw}

In my case I also wanted to add some styling and a header. So my code looks like this:

<!- People in Footer -->   
      {{if $record->customFieldDisplayByKey('project_people')}} 
  <div class="project_people_footer">
  		<h3>People in the Project</h3>
  <div class='ipsGrid ipsGrid_collapseTablet'>
        {$record->customFieldDisplayByKey('project_people', 'display')|raw}


Defining the output in basicRelationship

Now that we have included the data from the People Profiles database you will see that it is just a link. We want to have more data than that so now we must define what data we want to pull from that database and how we want that to be displayed. We have to do that be editing a theme file called basicRelationship. So we head over to our Theme folder and click the "Edit HTML and CSS" icon to get into the templates. Then under CMS->Global you will find the basicRelationship file.


Screenshot 2020-07-18 12.20.55.png


This file is a bit tricky because it defines all database relations. In order for us to target specifically the data coming from People Profiles we need to figure out what ID that database has. We can do that from Pages under Content->Databases which will list all database. If you hover over the edit button over your selected database, then you can see the URL at the bottom of your screen with the ID of the database at the very end.


Screenshot 2020-07-18 12.27.29.png


With the ID defined we can add a bit of code to make sure we only target specific databases with our changes:

{{foreach $items as $id => $item}}

{{if $item::$customDatabaseId == 19}}
<!-- People database -->
{template="BasicRelationship_PeopleProfiles" app="cms" group="basic_relationship_templates" params="$item"}

{{elseif $item::$customDatabaseId == 16}}
<!-- Author database -->
{template="BasicRelationship_author" app="cms" group="basic_relationship_templates" params="$item"}

<!-- all other databases -->
<a class="ipsPages_csv" href="{$item->url()}">{$item->_title}</a>



Creating Theme Templates instead of just using basicRelationship

In this code I have added 2 databases (19 and 16) and then I have a fallback for all others at the end that will show the default link. While it is very possible to add the code directly into this template I have used a different approach and instead created separate templates outside and then referenced them in the basicRelationship. This way I can work on the content for each database in a more focused way and the basicRelationship becomes a bit easier to overlook.

In order to create a new template you go to Create New at the bottom of the template listings. Select HTML template and then fill out the form accordingly.

  • Name - the name of the template.
  • Variables - We add $items here since that is what is defined in the foreach loop in basicRelations.
  • Location - Here we select front to place the template in the correct section.
  • Group - I suggest you create your own group here so it is easier for you to find later.
  • Application - Here we select Pages

Screenshot 2020-07-18 12.38.15.png

If you have done this as I have then you will have your new template located under CMS->Front->basic_relationship_templates. If you have selcted another group, then that is where you will find it instead.


Screenshot 2020-07-18 13.01.22.png


Adding data to the theme template

Now that we have a template for our connection between the databases, then we can start adding the data to it that we want to show in My Projects. This is done in a very similar way as when we add the data to the entry templates. Instead of using $record however we use $item:

{{if $item->customFieldDisplayByKey('your custom field key')}} 
	{$item->customFieldDisplayByKey('your custom field key', 'raw')}

As I added the default record image that is called a bit differently:

{file="$item->_record_image_thumb" extension="cms_Records"}

You can also reference the title field and the content field with a shorter tag:



In my current code I have nested the fields a bit and I have used the field for working area pretty sloppy, but I think you get the general idea.

<div class='ipsGrid_span2 people-profiles_card'>

  <div class="people-profiles_image {{if $item->customFieldDisplayByKey('working-area')}}{$item->customFieldDisplayByKey('working-area', 'raw')}{{endif}}_image">
    <img class="ipsImage   {{if $item->customFieldDisplayByKey('working-area')}}{$item->customFieldDisplayByKey('working-area', 'raw')}{{endif}}" src="{file="$item->_record_image_thumb" extension="cms_Records"}" class="
    {{if $item->customFieldDisplayByKey('working-area')}} 
            {$item->customFieldDisplayByKey('working-area', 'raw')}
    " />

  <div class="people-profiles_Name">
            {{if $item->customFieldDisplayByKey('people-profiles_Name')}} 
            {$item->customFieldDisplayByKey('people-profiles_Name', 'raw')}

<div class="people-profiles_Title">
            {{if $item->customFieldDisplayByKey('people-profiles_Title')}} 
  <span class="{{if $item->customFieldDisplayByKey('working-area')}}{$item->customFieldDisplayByKey('working-area', 'raw')}{{endif}}">{$item->customFieldDisplayByKey('people-profiles_Title', 'raw')}</span>
  <div class="people-profiles_links">
    {{if $item->customFieldDisplayByKey('people-profiles_Awesome')}} 
    <a href="{$item->customFieldDisplayByKey('people-profiles_Awesome', 'raw')}" class="people-profiles_Awesome"><i class="fas fa-id-card"></i></a>
    <i class="fas fa-id-card"></i>
    {{if $item->customFieldDisplayByKey('people-profiles_Linkedin')}} 
    <a href="{$item->customFieldDisplayByKey('people-profiles_Linkedin', 'raw')}" class="people-profiles_Linkedin"><i class="fab fa-linkedin"></i></a>
    <i class="fab fa-linkedin"></i>
    {{if $item->customFieldDisplayByKey('people-profiles_Instagram')}} 
    <a href="{$item->customFieldDisplayByKey('people-profiles_Instagram', 'raw')}" class="people-profiles_Instagram"><i class="fab fa-instagram-square"></i></a>
    <i class="fab fa-instagram-square"></i>
     {{if $item->customFieldDisplayByKey('people-profiles_Twitter')}} 
    <a href="{$item->customFieldDisplayByKey('people-profiles_Twitter', 'raw')}" class="people-profiles_Twitter"><i class="fab fa-twitter-square"></i></a>
   <i class="fab fa-twitter-square"></i>
       {{if $item->customFieldDisplayByKey('people-profiles_Homepage')}} 
    <a href="{$item->customFieldDisplayByKey('people-profiles_Homepage', 'raw')}" class="people-profiles_Homepage"><i class="fas fa-home"></i></a>
   <i class="fas fa-home"></i>


This guide should help you to bring in the data from any database into another database with the styling of your choice. I know this is a pretty short and not very detailed guide, but I hope it was useful anyway. Please add questions and I will improve upon the guide where I am jumping a bit to fast.

Happy coding!

View full blog article

Link to comment
Share on other sites

  • 4 months later...
  • 4 weeks later...

Thanks @Jimi Wikman!

One thing I didn't understand was how the related data is added to the parent record. With a little experimenting, I figured out that while editing the parent record, in the the related field you start typing the desired search terms and matching related records will appear. Voila!

It seems that from the parent record template, one call to the related field calls basicRelationship template which gives us one option to display what fields we want from the child record.

So in your example above, I can see how you would use the BasicRelationship_PeopleProfiles to output all the desired PeopleProfile data at one time, like your example, as a card.

But what if you want to use only a portion (1 or 2 fields) of the child record in different parts of the parent template? For instance, if your Project was authored by a People Profile record, how could one add a PeopleProfile->Name field at the top of the Project Description, and a PeopleProfile->Bio at the bottom of the Project Description?

Link to comment
Share on other sites

  • Owner

Correct, you use the database relationship field to fetch what objects you want to display from another database.

I have not tried to place the information in different places to be honest. I have some ideas on how that might be possible to do, but I have to test it first to see if it works. The Database Relationship field is not really intended to be multiple data points, but it should be possible to get that effect if we wanted it.

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Similar Content

    • By Jimi Wikman
      Last night I could not sleep, so I played around a bit with this idea I have had for a while to have a section for quick links to interesting blog posts or news. I realized that not only would this be fairly easy to build, but the functionality to promote, or like, things was already built and I had the code for it installed! I just need to adjust the code a bit and give it some design love and it should be good to go.
      Anyone who remember the old Digg site probably remember the many great things you could find there in the early days. While I have no ambition to reach anywhere near the same popularity, I hope that some people might find it useful at least.  With so much content, it is hard to keep up with all the great things, so this can at the very least be a nice way for me to drop in great articles that I find.
      The idea behind this setup is fairly simple. Provide a simple way to add links to specific articles or news, so content creators can get some links back to their websites. Use a simple boost button to let people vote up good content and a bury button to vote things down. Allow for the users to see things based on last added content, curated content or based on ratings.
      In short, it will be a news aggregator like the good old Digg and it's many copycats. While it will not have as many bells and whistles, I will add a few things that I have not seen before. The perhaps most interesting thing will be the connection to other databases. This will allow me to not just share links in the Stories section, but also to cross post to for example products, companies and later on people and certain add-ons like the Atlassian add-ons.
      For now, I am just playing around in Figma to see what kind of designs I can come up with. All the heavy lifting has already been done by Ralf Herrmann, an amazing IPS creator that provide some of the best plugins for Pages. His plugin SuperVote have all the features I need to build this, even if I will hack it quite a bit.
      For now, I have an idea in my mind where I will keep the same style as for other parts of the website, which means that I will color each story based on category. I am also playing around a bit with RSS imports for certain sites that I want to keep tabs on. These will be added as hidden and then manually edited for now. Later on, I plan to have them be fully automatic, but I want to control them a bit first. Images, for example, will only be added if uploaded by the owner of the links, or if I have their permission.
      I will start small and then see what happens.

    • By Jimi Wikman
      While working on the components for the new About Me section, I realized that I needed a proper way for people to communicate with me. After considering the options for a while, I decided to build my own ticket system. This is fairly easy and I could have done things in several ways, but decided to use some tricks from my Task Manager to make it a little more dynamic.
      In essence, this is just a database where I have defined the setup so that you only see your own entries. As the owner, I of course see all entries, which makes it easy to work with. I have added a few fields to help sort things using filters. These fields include Status of course, type of request as this setup does not include any categories, Priority for escalation and SLA, which is just a date field I can set for due date that is then calculated to show time to completion.
      I also have a field for Assignee, even though it is a bit of an overkill for my purposes. I have added it thought for others to use that have more than one person managing the tickets.
      Database fields and Templates
      While working on this new set of templates, I realized that I probably should add some variables for dealing with the field numbers. In most cases you use template names, so it's fairly easy to manage, but in the case of adding the ability to update fields in the frontend we use field ID's instead. Since everyone has a different ID sequence, this can be a bit annoying to work with, so I am thinking I will add variables instead at the beginning of the templates. This way they will work just like CSS, so you can change one variable and it will update across the template.
      Easy creation
      This setup is nothing fancy and it is just a first iteration. I will probably build more on it as I see the need for it, but for now it will suit my need. All in all, it took about a week to complete, but that was mostly because I did not have much time to commit to it. I will still do a new form for this one, but other than that I think this one is done. It is nice because I only needed to customize two templates and the form, instead of all five templates.
      Tasks integrations
      I have a field for database relations that I will add later. Not that I think I will actually need it, but more because I want to write a guide on how to do it and also so I can use it if I want. I also want to make sure that if someone else wants to use both together, they have a way to do it out of the box.
      Onwards with About Me
      With this, I now only have to figure out how I want to set up the History part about me. I have some ideas for it that include setting up a database where each category will become a full width component, or just use blocks for it. I'll ponder on it some more in the coming weeks to see where I might land on that.
      Always moving forward 🙂
    • By Jimi Wikman
      View File Rank Icons for Invision Community
      A set of rank icon shapes.
      It is the same icon shapes used here on this site, but without symbols.
      You can download for free and use as base for your own Rank Icons.
      Submitter Jimi Wikman Submitted 06/26/2021 Category Design  
    • By Jimi Wikman
      A set of rank icon shapes.
      It is the same icon shapes used here on this site, but without symbols.
      You can download for free and use as base for your own Rank Icons.
    • By Jimi Wikman
      Going over requirements and making time estimations and technical solutions for the front end part of the project.
  • Create New...