Advanced Data Handling

This chapter focuses on the more advanced elements of data structures and how we interact with them. The topics that will be covered in this chapter should be considered a toolbox that we can call on, should the standard development patterns not be suitable.

Data structures represent physical entities in the SQL Server database, which means they are much harder to change later – especially after they contain data. Due to this, we need to make the right design choices during the design phase. Knowing the benefits and drawbacks of using inheritance and date-time effectiveness is important when designing a technical solution.

In this chapter, we will cover the following recipes:

• Implementing table inheritance
• Using menu items to create a record for a table that uses inheritance
• Using date-time effectiveness
• Creating a view with a query and computed...

Technical requirements

You can find the code files for this chapter in this book's GitHub repository at https://github.com/PacktPublishing/Extending-Microsoft-Dynamics-365-Finance-and-Supply-Chain-Management-Cookbook-Second-Edition/blob/master/Chapter%207.axpp.

Implementing table inheritance

Table inheritance can be implemented so that we have a base table with common attributes, as well as specializations that extend that table and add their own attributes and methods. This is similar to classes, but only in concept—although the base table can have many child tables, only one physical table is created in the SQL Server. The physical table in the SQL Server contains all of the fields from the base table and its child tables.

A good candidate for this is the vehicle table, where we could have a base vehicle table and a child table for each type, such as Bike, Car, and Truck. This is suitable as long as we are sure these can be considered physical data structures, and not a categorization. For example, we can change a product from inventory to non-inventoried by changing the inventory model group, but we cannot change a vehicle...

Using menu items to create records for tables that use inheritance

When we create a new record on a form whose data source uses table inheritance, the system offers a dialog asking us to select a record type. This is suitable in most cases, but we may wish to add specific menu item buttons for each record type, such as New car, New Bike, and so on. If we were using a create form, the system would show two dialog forms: one for the record type selection and then the create dialog. This would be irritating to the users as it adds unnecessary clicks to the process.

We will add a menu item button to the vehicle form for each vehicle type.

Getting ready

This recipe is a continuation of the previous recipe, but to start, we need...

Using date-time effectiveness

Date-time effectiveness is used to state that records are valid for a specific period of time. This can be to enforce that every change to a record is time-stamped, allowing us to see the state of the record at any point in time. It can also be used to plan future states, such as a planned pay rise.

In our case, we will create an odometer table so that the user can enter the odometer setting for the vehicle and allow them to see the current and historical states of the record.

Getting ready

The example in this recipe builds on the vehicle table and form, but we can use this pattern to create a table and form that implements date-time effectiveness in any SCM project.

...

Creating a view with a query and computed columns

Views are great high-performance ways to bring data from multiple tables together. We can start with a query and then add fields from that query to a field list in order to create a view that is then created in SQL and is usable as a read-only table anywhere in SCM.

This is great for fact panes, reports, and inquiries. Although we will only cover a simple view in this recipe, the key point is to show how we can create view methods that are used as computed columns in the view. Just like we could add a display method to a table, which is then used in a form, we can also do this in a view. The difference is that the work is done by the SQL Server and comes with two big advantages: they are much quicker to calculate and you can sort and filter on this calculated result. As far as SCM is concerned, this calculated view field is just...