Improve Game Night with the Power Platform: Data Structure

As you learned yesterday, I wanted to learn about PowerApps and decided to do this by improving the game night board game selection process.

As a prerequisite for this learning experiment I exported our board game library from BoardGameGeek (to be honest, most of this was my husband's idea and he maintains this list which he exported for me).

Then I set up a free trial to use. If you need help getting your trail set up, check out my earlier post: How to Set up Your Free Trial.

Now it's time to get started with the "easy" bits. I call these easy because these are the pieces that I already know how to handle. There are a few things we need to create.

1. New "Board Game" entity with fields to hold:
1. Name of the game
2. Minimum play time
3. Maximum play time
4. Average Play time
5. Minimum number of players
6. Maximum number of players
7. Year Published
8. Type (Standalone or expansion)
2. New "Rating" entity with an N:1 link to the "Board Game" entity. This will capture the date (created on) and an integer score. An argument could be made for this to be a custom Activity type but in this case I think that would be a bit of overkill
3. New Rollup fields on the "Board Game" entity to get us the average score for the game

Let's start with a solution to keep things classy. I also created a publisher so I didn't have new_ in my screenshots.

Publisher Details

Solution Creation

The solution is created from settings > Solutions > New. Then I created the publisher by using the New option from the lookup field. You can also create your Publishers from Settings > Customizations > Publishers. For more information on Solutions, check out this CRMUG Webinar: Solution Management, Your Customization's on the Move.

Alright, let's create the new Entity. In the solution, click New > Entity. I entered the details as shown below, saved and published.

Board Game Entity Set Up

I set the ownership to Organization because all games will be visible to all users with access to that entity. I made it visible in the sales area and turned off the Communication and Collaboration features. We may decide to use Notes in the future but this can be enabled at a later time.

Now for the new fields. Creating fields can be tedious. There are a few options to speed this up. We could do the import and create new fields on the way in. This is generally not recommended as it is very easy to make a mistake. You can also  create the fields via XrmToolBox.

In most case, I would recommend you create the fields manually. Since these fields are simple and this blog is just for learning and experimentation, that is why I chose to go the exciting route.

So I launched XrmToolBox, connected to my trial and navigated to the Attribute Editor (you may have to download from the plugin store). If you are not familiar with XrmToolBox, check out: Getting Started with XrmToolBox.

In the Attribute editor:

1. Click "Refresh Entities". This will load all the entities from the trial including the new one we just created.
2. Select the "Board Game" entity and click "Download Template"

   

   Download Template from Attribute Editor

   
   
3. This creates an Excel file with the details of all the existing fields. I filled in the details for my new fields. I did not include the details for the Option Set as that seemed likely for disaster.

   

   Add new fields in the downloaded file

   
   
4. Save the updated file
5. Back in XrmToolBox, navigate to the file click the "Upload Template" button. This will show you the details of the new fields from the file

   

   Re-import the field details

6. Click Save and Publish to push these changes to your D365 environment (or if you are like me: get an error, see your silly mistake, fix it and retake screenshots so it seems like you did it right the first time)

Now back in D365, I see all my new fields and just need to create the final Option Set.

Create Option Set

Ok almost there. Let's create one more entity in the solution.

Create Rating Entity

Then we can create the few fields manually.

Lookup to Board Game Entity

Score Field

Now for the fun stuff! We want a rollup field to give us the average score for this game. So back in the "Board Game" entity, I created a new "Average Rating" Rollup field. This will rollup any related "Ratings" to the "Board Game" as an Average. Later we can decide if we need additional rollup or calculated fields for other uses.

Rollup of Scores from related records

Rollup Details

Finally, I updated the main forms for both entities to contain the new fields I created.

If I was doing this in a real environment, there would be several other things I would do as icing on the cake. First of all, create a workflow to populate the Name field on the "Rating" records. I am always irritated to find an entity where the name field is blank. Then there is nothing visible when doing a lookup, default Advanced Find views contain no data, etc. So The workflow would just set this field to the created on date appended with the name of the game it is linked to. Secondly, I would update the icons of these entities to make them look pretty.

Now that the entities are created, we can proceed with importing the data tomorrow!