Task

Establish all required dependencies, in order to allow

• the selection of clear text values in the foreign key fields (xxx_ID), via dropdown or lookup;
• the usage of keywords (fields MANUFACTURER and COLOR);
• the usage of currencies.

tip

Find a generic description of dependencies in chapter The Basics and in its sub-chapter Providers and Consumers

---

Solution

As you have learned before, dependencies between Entities are established via the models "Provider" and "Consumer" - optionally including a "Parameter".

At first, we will implement this principle in order to allow the selection of contact persons via CarDriver_entity's field CONTACT_ID: Create a new Consumer for CarDriver_entity and name it "Persons". Set its Property entityName to "Person_entity", and fieldName to "Contacts" (this is a predefined Provider of the ADITO xRM project). Then, set property consumer of CarDriver_entity’s field CONTACT_ID to "Persons". Result: In field CONTACT_ID, you can now select a person via the PersonLookup_view.

The n:m relation between cars and car drivers is established via car reservations. Therefore, car reservation has foreign key fields on both ends: CAR_ID and CARDRIVER_ID. These 2 EntityFields contain the UIDs of CARID and CARDRIVERID. However, if you create a reservation, it makes no sense to work with UIDs. Rather, we want to select

• the car with the help of the EntityFields MANUFACTURER and TYPE;
• the car driver with the help of LASTNAME, FIRSTNAME, and SALUTATION of the PERSON dataset, which is connected via CONTACT_ID.

Let's start with car: Create a Provider for Car_entity (-> Navigator: RMB > "New Provider") and a Consumer for CarReservation_entity (-> Navigator: RMB > "New Consumer"). Name both "Cars". Connect Provider and Consumer via the Consumer's properties entityName and fieldName. Specify the Consumer in the EntityField CAR_ID's property consumer.

If you deploy, for testing purposes, you will see that, in the EditView of Context CarReservation, a combobox with empty lines is displayed for the EntityField CAR_ID. The reason for this is that the Consumer identifies the car datasets on the basis of their "content title". However, as we have not filled the Car_entity's contentTitleProcess yet, there are no content titles yet, which results in empty lines.

Therefore, we now fill the contentTitleProcess with suitable code that allows to distinguish the cars:

import { result, vars } from "@aditosoftware/jdito-types";

let contentTitle = vars.get("$field.MANUFACTURER") + " " + vars.get("$field.TYPE");

result.string(contentTitle);

contentTitleProcess of Car_entity

A further improvement would be to specify a lookupView for Context Car. For reasons of simplification, we use CarFilter_view for this purpose. Then, this LookupView will be used to select a car instead of the contentTitleProcess:

To enable lookup filtering, set property isLookupFilter to true for the RecordFieldMappings of all EntityFields that should be used as filter criteria.

Nevertheless, the contentTitleProcess should remain set, as it will be used

• to display the car after selection;
• as content title (= as part of the title of a dataset in the MainView):

Now, create Provider, Consumer, and Consumer assignment also for the other foreign key field, CARDRIVER_ID. In this case, the contentTitleProcess of CarDriver_entity is a little more complex, as FIRSTNAME, LASTNAME, and SALUTATION are not EntityFields of CarDriver_entity, but of the related Person_entity, see this diagram.

import { logging, neon, result, vars } from "@aditosoftware/jdito-types";
import { newSelect } from "SqlBuilder_lib";

var contactId = vars.get("$field.CONTACT_ID");
if (contactId)
{
    var displayData = newSelect("SALUTATION, FIRSTNAME, LASTNAME")
        .from("PERSON")
        .join("CONTACT", "CONTACT.PERSON_ID = PERSON.PERSONID")
        .where("CONTACT.CONTACTID", contactId)
        .arrayRow();
    var salutation = displayData[0];
    var firstname = displayData[1];
    var lastname = displayData[2];
    result.string(salutation + " " + firstname + " " + lastname);
}

contentTitleProcess of CarDriver_entity

In the CarDriverFilter_view, the related contact is displayed only as UUID.

Of course, in practice, the contact should here appear in clear text, not cryptic. Therefore, in the RecordContainer, we define a displayValue for CONTACT_ID (property expression of RecordFieldMapping CONTACT_ID.displayValue). Fortunately, the ADITO xRM project already includes a suitable function for building the required SQL subselect:

import { result } from "@aditosoftware/jdito-types";
import { PersUtils } from "Person_lib";

result.string(PersUtils.getResolvingDisplaySubSql("CONTACT_ID"));

CarDriver_entity.db.CONTACT_ID.displayValue.expression

warning

As an alternative approach, we could also have used property displayValueProcess of the EntityField CONTACT_ID. However, when the FilterView is opened, this process is then executed multiple times - one time for every single dataset. Therefore, for performance reasons, the above solution in the RecordContainer should generally be preferred (if possible).

Now that we have configured a displayValue for CONTACT_ID, we could simplify CarDriver_entity's contentTitleProcess like this:

import { logging, neon, result, vars } from "@aditosoftware/jdito-types";

var contactDisplayValue = vars.get("$field.CONTACT_ID.displayValue");

if (contactDisplayValue)
{
   result.string(contactDisplayValue);
}

CarDriver_entity.contentTitleProcess (simplified)

A similar problem is visible in CarReservationFilter_view: CAR_ID and CARDRIVER_ID appear as UUID:

CarReservationFilter_view

Of course, instead of the UUIDs, clear text should be displayed, e.g.:

• Car: Manufacturer and type
• CarDriver: Salutation and full name (the same as in CarDriverFilter_view)

For this purpose, similar to CarDriver_entity, we add displayValues in the RecordContainer of CarReservation_entity. Now, one solution would be to write subselects, in order to reach the required sql columns. However, in cases like our strongly linked car pool Entities, best practice is to

• enter all 3 tables in property linkInformation of CarReservations_entity's RecordContainer:
• join the tables in the RecordContainer's property fromClauseProcess:

import { result } from "@aditosoftware/jdito-types";
import { SqlBuilder } from "SqlBuilder_lib";

var sql = new SqlBuilder()
    .from("CARRESERVATION")
    .join("CAR", "CAR.CARID = CARRESERVATION.CAR_ID")
    .join("CARDRIVER", "CARDRIVER.CARDRIVERID = CARRESERVATION.CARDRIVER_ID");

result.string(sql.toString()); 

CarReservation_entity.db.fromClauseProcess

These JOINs allow us to directly use columns of the other tables in the respective displayValueProcesses:

import { result } from "@aditosoftware/jdito-types";

result.string("CONCAT(CAR.MANUFACTURER, ' ', CAR.TYPE)");

CarReservation_entity.db.CAR_ID.displayValue.expression

For resolving the CARDRIVER_ID, we can simply copy the code that we use in CarDriver_entity.db.CONTACT_ID.displayValue.expression:

import { result } from "@aditosoftware/jdito-types";
import { PersUtils } from "Person_lib";

result.string(PersUtils.getResolvingDisplaySubSql("CONTACT_ID"));

CarReservation_entity.db.CARDRIVER_ID.displayValue.expression

Keywords

The approach to connect Entities via Provider and Consumer is also used for configuring keywords.

Manufacturer

Let's start with Car_entity's field MANUFACTURER. Instead of typing free text, we want to select from a list of car manufacturers' names.

First, in the client administration, we add a KeywordCategory named "CarManufacturer" and assign KeywordEntries holding the names of the manufacturers.

(As you can see, the keys are usually written in capital letters and begin with the keyword category, or an abbreviation of it - like "CARMAN", in this case.)

Now we can use this keyword in the project:

Create a new Consumer for Car_entity and name it, e.g., "CarManufacturers". Assign the Consumer to EntityField MANUFACTURER (property consumer).

Next we set the Consumer's properties in order to connect it with the respective Provider of the keyword logic:

Property	Value
entityName	KeywordEntry_entity
fieldName (name of the provider)	SpecificContainerKeywords

After specifying the Provider, the target Entity's Parameters appear subordinated to the Consumer. (Only those Parameters appear that have set their property flag exposed to "true".) Here, we only need the Parameter named ContainerName_param. Double-click on it, in order to initialize it. Then, we enter the KeywordCategory's name as result of the Parameter's valueProcess. We could do this explicitely, like this:

import { result } from "@aditosoftware/jdito-types";

// bad example
result.string("CarManufacturer");

Car_entity.Consumers.CarManufacturers.ContainerName_param.valueProcess

Although this works for now, the best practice is to use a function that returns the car manufacturer's name, similar to a constant. For this reason, we create a project-specific library that contains all keyword categories and all keyword entries used in our carpool-related code:

function $CarPoolKeywords()
{}

$CarPoolKeywords.carManufacturer = () => "CarManufacturer";
$CarPoolKeywords.carManufacturer$Bmw = () => "CARMANBMW";
$CarPoolKeywords.carManufacturer$Mercedes = () => "CARMANMERCEDES";
$CarPoolKeywords.carManufacturer$Ford = () => "CARMANFORD";
$CarPoolKeywords.carManufacturer$Toyota = () => "CARMANTOYOTA";

process.libraries.CarPoolKeywords_registry.process

On the basis of these definitions, we can now improve the valueProcessof ContainerName_param (cf. above).

import { result } from "@aditosoftware/jdito-types";
import { $CarPoolKeywords } from "CarPoolKeywords_registry";

// good example
result.string($CarPoolKeywords.carManufacturer());

Car_entity.Consumers.CarManufacturers.ContainerName_param.valueProcess

Now, repeat these steps in order to implement the usage of keywords for

• car color
• currency

Car color

$CarPoolKeywords.carColor = () => "CarColor";
$CarPoolKeywords.carColor$Black = () => "CARCOLORBLACK";
$CarPoolKeywords.carColor$Blue = () => "CARCOLORBLUE";
$CarPoolKeywords.carColor$Golden = () => "CARCOLORGOLDEN";
$CarPoolKeywords.carColor$Green = () => "CARCOLORGREEN";
$CarPoolKeywords.carColor$Red = () => "CARCOLORRED";
$CarPoolKeywords.carColor$Silver = () => "CARCOLORSILVER";

process.libraries.CarPoolKeywords_registry.process

import { result } from "@aditosoftware/jdito-types";
import { $CarPoolKeywords } from "CarPoolKeywords_registry";

result.string($CarPoolKeywords.carColor());

Car_entity.Consumers.CarColors.ContainerName_param.valueProcess

Currency

As for currency, we do not use keywords, although there might be a (deprecated/deactivated) keyword category "Currency". Instead, there is a separate Context "Currency", and we will establish a dependency to Currency_entity:

Create a new Consumer "Currencies" and connect it with Provider "Currencies" of Currency_entity. Then, set "Currencies" in property consumer of Car_entity's field CURRENCY.

Note: This Provider has its property lookupIdfield set to column ALPHA3, which defines that this field is to be used as ID instead of the primary key AB_CURRENCYID. This results in the ALPHA3 (ISO) code ("EUR", "USD", etc.) being saved in column CAR.CURRENCY.

note

The above instructions are a simplified implementation. In practice, some more configuration is usual (e.g., a preselection via valueProcess or the exclusion of inactive currencies). Furthermore, currency rates can be included. You can use xRM Contexts like Offer as model for learning the details.