Lesson 4-3

Materials and Services prices

When we are adding a new Material or Service to the MaterialsAndServices tabular part of Services document we have to specify the price.

For now a user inputs price manually which seems to be not a very good idea. Even if he knows all the prices by heart and doesn’t make a mistake during manual input, he spends some time typing number. And by the way, how does he know the prices? Where did he get them in the first place? As a matter of fact, there has to be such thing as a price list containing the current selling price for every material and service in our company. And these prices have to appear in the Price field automatically when a user select a specific material or service.

So, how do we implement this?

The first thing that comes to mind: how about adding a new attribute (Price) to the MaterialsAndServices catalog?

Looks good, but what happens when we need to change the price? The old price will be replaced with the new one and, basically, disappear from the system. At the same time, the old documents with old prices will stay put. What if we need to check some old document price? How do we know if they are consistent with the standard price that was in effect back then?

Looks like we need not an attribute but a list - a history of all price changes in the system. OK, what if use Catalog tabular section like this?

This solution works but has some unobvious backlash. Let’s think how do we register a price changes? Of course we can allow users to edit the tabular section directly but how do we trace these changes back? How can we figure out who and why changed this particular price, say, six months ago?

OK, what if we simply add some more attributes to the tabular section:

In the ChangedBy attribute, we will store the reference to the Employees catalog and in the Reason attribute - string description of the circumstances under which the changes took place.

Sounds good so far but let’s think one step ahead. How will it work if we need to set a whole bunch of new prices for a list of materials. Say, we have a seasonal sale for all PVC pipes or special Christmas offer for disposal connectors. All these changes are to be made at the same date by the same person and for the same reason. This functional requirement is hardly compatible with our decision to store the price changing history in a tabular section, because:

• We are to store the same data more than once. Say, we have the R22 refrigerant, and the Extension cord repriced on Aug 31 by our price-manager George Clooney on the ground of Halloween discount program. To reflect this fact we need to place this information in both catalog objects, i.e. duplicate it.
• We need to implement an interface to allow a user to select and reprice many materials at once. Otherwise, a user is going to repeat this input manually, which doesn’t seem to be a very good idea usability-wise.
• We need to implement the repricing algorithm that will put the repricing information into every selected material.
• This algorithm is to cycle through the selected materials, read and change them one by one and then write them. From the performance perspective, this approach is a disaster (we discussed this round-trip effect in Lesson 1).

This is why we rule out the usage of a tabular section for these purposes.

Here is a much more natural solution that is free of all the problems listed above.

Setting prices using a separate document

What if instead of storing the prices in the MaterialsAndsServices catalog we create a new metadata object for this purpose? This object is to register a price changing action that is basically another type of business transaction in our system. So, as it was explained in Lesson 1, we need another document here.

The date of the document is to represent the date of the price changing transaction. In the header of the document, we will store the reference to the manager who made the decision (CatalogRef.Employees) and the reason for the change (String 200). We will use the document’s tabular section to store all materials and services being repriced (CatalogRef.MaterialsAndServices) along with the prices themselves (Numeric).

This is our new document structure:

We also need to include the document in a subsystem (Documents subsystem looks like an appropriate place).

This document will serve as a primary data source for all price-changing activity but we also can use it to manage access rights (we don’t want all users to be able to change the prices).

Let’s create a new Role “Price changer” and grant it all rights to the document:

Note, that the Admin role gets all the rights to the new object as soon as we added it. The thing is that the Platform adds these rights automatically because of “Set rights to new objects” checkbox selected.

At the same time, the Sales role didn’t get any access rights to the document automatically (the checkbox is deselected).

Now. How do we get the actual price when we fill out the Services document?

Let’s say we have a “PVC pipe” that was repriced ten times in the course of the last year. So, we have ten different documents, containing this material and we need to find the latest one to read the current price. Obviously, there is no way we could implement this functionality effectively using the Object Technique because we would need to read and check all the documents in a cycle.

OK, what about query? Here is a query text we could use:

SELECT TOP 1
	TabularSection.NewPrice
FROM
	Document.PriceChange.MaterialsAndServices AS TabularSection
WHERE
	TabularSection.MaterialOrService = &MaterialOrService
ORDER BY
	TabularSection.Ref.Date DESC

Here we select all mentions of this material in all PriceChange documents, sort the list in descending order and get the first (i.e. latest) record. This query gets the result we need, but it’s far from being perfect performance-wise. The detailed explanation of this topic is far beyond the scope of the course but just to get a general idea consider the following. How would you execute this query is you were a DBMS engine?

Well, I would scan the tabular section table looking for the records having the material I need, then I would LEFT JOIN the document table and get the Date system attribute from it, then I would sort the resulting recordset on Date and return the first row.

How many records did we just read? At least as many as all tabular section records in all Sales documents. There are some ways of reducing this number, but you never can read the only record you need without scanning the records you don’t.

So, here is a rule of thumb: avoid using tabular sections in queries that transcend the boundaries of one object. In other words, you can SELECT from the tabular section table if you use WHERE Ref = &Ref condition i.e. limits the scope of search with a single document tabular section content. If the functionality you are developing requires to query a tabular section without this condition, you should look for a better solution.

This better solution is called an Information register.

 Lesson 4-2 | Course description | Lesson 4-4