Microsoft Dynamics NAV: Customizing Relationship Management

With this book you’ll quickly learn to develop complete Dynamics NAV applications and gain the versatility to design for different enterprise needs and industry sectors. With a user-friendly, fully focused approach it’s an indispensable tutorial.

 

Microsoft Dynamics NAV 2009 Application Design

Microsoft Dynamics NAV 2009 Application Design

A focused tutorial for Microsoft Dynamics NAV application development

        Read more about this book      

(For more resources on Microsoft Dynamics, see here.)

RM is a pretty complete module that is not often highly customized or verticalised. However, we will describe some possible changes and how to integrate an add-on, in our case the Squash application, with Relationship Management.

All examples in this article are part of the objects downloaded for the article, Microsoft Dynamics NAV 2009: Apply reverse engineering to customize our application.

Salutation formula types

By default, the system has two salutation formula types— formal and informal, allowing us to print Dear Mrs. Brown, or Dear Angela. But, what if we want to print Attn. Mrs. Brown?

For this, we need to first add an option to the Salutation Type field in the Salutation Formula table.

Add the option

Support the formula

Next, we want to use the formula when printing a Contact Cover Sheet. This uses the Format Address functionality from Codeunit 365.

This Codeunit is the single point in Dynamics NAV where all the address formatting is done.

The formatting of contact persons is done in the function ContactAddrAlt. We should make the following change.

ContactAddrAlt()
...
ContIdenticalAddress:
WITH ContAltAddr DO BEGIN
GET(Cont."Company No.",CompanyAltAddressCode);
FormatAddr(
AddrArray,"Company Name","Company Name 2",
Cont.Name,Address,"Address 2",
City,"Post Code",County,"Country/Region Code");
END;
(Cont.Type=Cont.Type::Person) AND
(Cont."Company No." <> ''):
WITH Cont DO
FormatAddr(
// AddrArray,ContCompany.Name,ContCompany."Name 2",
// Name,Address,"Address 2",
AddrArray,ContCompany.Name,ContCompany."Name 2",
GetSalutation(5, Cont."Language Code"),Address,
"Address 2",City,"Post Code",County,
"Country/Region Code")

Always comment out the original line of code before you make a change. This will enable you to always go back to standard code and help when upgrading this solution to a newer version. Most NAV partners and developers have their own way of documenting and commenting. The example in here is the Minimum comment requirement.

The GetSalutation function

In our modification, we use the GetSalutation function in the Contact table (5050) instead of the Name field. Let's have a look at that function and analyze what it does.

GetSalutation()
IF NOT SalutationFormula.GET("Salutation Code",LanguageCode,
SalutationType)
THEN
ERROR(Text021,LanguageCode,"No.");
SalutationFormula.TESTFIELD(Salutation);
CASE SalutationFormula."Name 1" OF
SalutationFormula."Name 1"::"Job Title":
NamePart[1] := "Job Title";
SalutationFormula."Name 1"::"First Name":
NamePart[1] := "First Name";
SalutationFormula."Name 1"::"Middle Name":
NamePart[1] := "Middle Name";
SalutationFormula."Name 1"::Surname:
NamePart[1] := Surname;
SalutationFormula."Name 1"::Initials:
NamePart[1] := Initials;
SalutationFormula."Name 1"::"Company Name":
NamePart[1] := "Company Name";
END;
CASE SalutationFormula."Name 2" OF
...
END;
...
FOR i := 1 TO 5 DO
IF NamePart[i] = '' THEN BEGIN
SubStr := '%' + FORMAT(i) + ' ';
IF STRPOS(SalutationFormula.Salutation,SubStr) > 0 THEN
SalutationFormula.Salutation :=
DELSTR(SalutationFormula.Salutation,STRPOS(Salutation
Formula.Salutation,SubStr),3);
END;
EXIT(STRSUBSTNO(SalutationFormula.Salutation,NamePart[1],
NamePart[2],NamePart[3],NamePart[4],NamePart[5]))

The function uses two parameters: SalutationType and LanguageCode. With these values and the salutation code of the contact, it checks if there is a valid formula. Since we only added a new option, the code still works because at database level, the Option field is translated to an Integer.

For documentation purposes, we could also implement the new option value in this function. The downside of that would be that we do a modification that is not technically necessary, but needs to be maintained and upgraded.

Depending on the order of the formula, the necessary name fields are combined and used as the return value of the function.

Set up the salutation formula

If we want to use our new Salutation formula, we need to set it up first. We will do this for F-MAR to test it with CT100191 Megan Sherman from American Wood Exports.

Microsoft Dynamics NAV: Customizing Relationship Management

Test the solution

After adding the new formula, we print a cover sheet from the Contact Card using the Contact Cover Sheet option from the Report actions. The result will look like this:

Microsoft Dynamics NAV: Customizing Relationship Management

Customer and vendor numbering

Another common requirement from end users is to maintain the same number when creating a customer or vendor from a contact.

This can be done by adding one line of code to the CreateCustomer function in the Contact table.

CreateCustomer()
...
CLEAR(Cust);
Cust.SetInsertFromContact(TRUE);
//* Maintain Contact No. >>>
Cust."No." := "No.";
//* Maintain Contact No. <<<
Cust.INSERT(TRUE);
Cust.SetInsertFromContact(FALSE);

This works, because by populating the No. field the number series functionality in the OnInsert trigger does not start.

OnInsert()
IF "No." = '' THEN BEGIN
SalesSetup.GET;
SalesSetup.TESTFIELD("Customer Nos.");
NoSeriesMgt.InitSeries(SalesSetup."Customer Nos.",
xRec."No. Series",0D,"No.","No. Series");
END;
...

Disabling direct creation of customers and vendors

When using this option, it should be disabled to directly create a customer or vendor. This can be done easily by removing the No. series from the Sales & Receivables setup and Purchases & Payables setup. This results in a runtime error message when creating the customer or vendor.

        Read more about this book      

(For more resources on Microsoft Dynamics, see here.)

Sharing contact information across companies

When more companies have their administration in Microsoft Dynamics NAV, they most often have the same owner or group of owners that want their Contact data to span across their companies.

This can be achieved by sharing some tables over all the companies and changing some business logic.

Share tables

By default, Microsoft Dynamics NAV will create a separate instance of each table for each company. This can be changed with the DataPerCompany property in the Table Designer.

Microsoft Dynamics NAV: Customizing Relationship Management

The following tables should be shared across the database since they contain the main contact information and the link to the customer and vendor data.

  • 5050 - Contact
  • 5051 - Contact Alt. Address
  • 5052 - Contact Alt. Addr. Date Range
  • 5053 - Business Relation
  • 5054 - Contact Business Relation

This will allow us to reuse contact data in all companies. Sharing other tables is optional, but sharing them might be useful in some cases.

By sharing the Contact Profile Answer table other companies can see how a customer is doing within the group.

The Segment tables could be shared in order to cross company slice and dice information. This also requires the Criteria tables to be shared.

When you share the profile or segment tables, the reports that calculate them should be started for each company individually in the database.

Campaigns and Opportunities should not be shared since that interfaces with the ERP system. Never share financial tables like the value entry or document tables.

Interaction log entries could be shared but we should realize that most table relations to sales and purchase documents will not work when we are in the wrong company.

Business relations

When sharing contacts across companies, we are interested to see in which company the contacts are customers and vendors. We also want to maintain those tables when the contact information changes.

This means that besides sharing the Contact Business Relation table, we should also add a field indicating the company and add this field to the primary key.

Microsoft Dynamics NAV: Customizing Relationship Management

C/AL code modifications

The functions that created the customer and vendor records that we saw in the article, Microsoft Dynamics NAV 2009: Apply reverse engineering to customize our application, should also be checked.

For example, the function CreateCustomer in the Contact table.

CreateCustomer()
...

ContBusRel.RESET;
ContBusRel.SETRANGE("Contact No.","No.");
ContBusRel.SETRANGE("Link to Table",ContBusRel."Link to
Table"::Customer);
//* Company Sharing >>>
ContBusRel.SETRANGE(Company, COMPANYNAME);
//* Company Sharing <<<
IF ContBusRel.FIND('-') THEN
ERROR(
Text019,

...

And a little bit further up in the C/AL code:

ContBusRel."Contact No." := ContComp."No.";
ContBusRel."Business Relation Code" := RMSetup."Bus. Rel. Code for
Customers";
ContBusRel."Link to Table" := ContBusRel."Link to Table"::Customer;
//* Company Sharing >>>
ContBusRel.Company := COMPANYNAME;
//* Company Sharing <<<
ContBusRel."No." := Cust."No.";
ContBusRel.INSERT(TRUE);

We should also check the code that maintains data integrity which is the CustVendBank-Update Codeunit (5055) that we discussed in the article, Microsoft Dynamics NAV 2009: Apply reverse engineering to customize our application.

UpdateCustomer()
WITH Cust DO BEGIN
//* Company Sharing >>>
CHANGECOMPANY(COMPANYNAME);
//* Company Sharing <<<
GET(ContBusRel."No.");
...
END;

Here we use the CHANGECOMPANY C/AL command to change the company for a specific instance of a variable.

There are more functions impacted such as the UpdateQuotes function in the Contact table. Analyze your database before implementing this feature.

Number series

The last change we should do for a properly working system is create a new instance of the number series functionality.

This can be achieved relatively easily since the number series are an isolated set of objects.

In the Object Designer, we should filter on this set of objects and export them to a .txt file.

  • Table (308) : No. Series
  • Table (309) : No. Series Line
  • Table (310) : No. Series Relationship
  • Report (21) : No. Series
  • Report (22) : No. Series Check
  • Codeunit (396) : NoSeriesManagement
  • Page (456) : No. Series
  • Page (457) : No. Series Lines
  • Page (458) : No. Series Relationships

In this file, we can renumber them and rename them so we get something like this:

  • Table (123456721) : No. Series (Shared)
  • Table (123456722) : No. Series Line (Shared)
  • Table (123456723) : No. Series Rel. (Shared)
  • Report (123456721) : No. Series (Shared)
  • Report (123456722) : No. Series Check (Shared)
  • Codeunit (123456721) : NoSeriesManagement (Shared)
  • Page (123456721) : No. Series (Shared)
  • Page (123456722) : No. Series Lines (Shared)
  • Page (123456723) : No. Series Rel. (Shared)

Where the tables should be DataPerCompany No.

Final steps

When we have shared number series functionality, we can implement this in the existing objects.

  1. The field Contact Nos. in the marketing setup table should change the table relation to the shared No. series table as well as the No. series field in the contact table.
  2. The variable NoSeriesMgt in the contact table should move from NoSeriesManagement to SharedNoSeriesMgt.

Alternative approaches

Sharing contact information across companies is a change that has been implemented by many companies and can be considered safe. Other tables in Microsoft Dynamics NAV are more difficult to share because of financial or operational information.

A typical example in the standard application is the Item table (27). This contains a field Cost is Adjusted (29) that is used when running Cost Adjustment. If this table would be shared across all companies we would create a major issue with running this function.

For this issue there are two commonly implemented solutions:

  • Shared Master Items: We can create a new table called Master Item. This table is shared across all companies and contains the information we share like descriptions and pricing. When the data in this table is changed, it should enable a mechanism comparable to the CustVendBank-Update Codeunit (5055) that updates the Items in the other companies using the CHANGECOMPANY C/AL function.
  • External Synchronization: We could implement something that will export the changes done in company A to an XML file. An application server can run in the background and read this XML file and implement these changes to other companies in the database or even other databases.

The first solution with Master Items looks a lot like the way Contacts work in the standard application and is a perfect example of Look, Learn, and Love using proven data structures in customized solutions.

Add contacts to segments

The last change we are implementing in Relationship Management is adding a table to the Add Contacts functionality in Segments.

We have seen that it is already complete but a vertical solution might want to integrate its ledger entry tables here.

For this example, we will make it possible to filter in the Squash Ledger Entries from the example application in the article, Microsoft Dynamics NAV 2009: Apply reverse engineering to customize our application.

Expand report

The first step is to add the Squash Ledger Entries as a DataItem to the Add Contacts report (5198). We will copy the functionality from the Value Entries as this is comparable functionality.

Always find comparable standard application functionality to learn from. Never just copy and paste this but learn how it's done and apply your own knowledge.

Microsoft Dynamics NAV: Customizing Relationship Management

We cannot copy and paste the table relation from the other Contact Business Relation DataItem since squash players are contact persons, not companies.

Our table relation should be Contact No.=FIELD(No.).

The code in our Contact Business Relation table tells us we need two new variables of type Boolean— SquashFilters and SkipSquashLedgerEntry.

ContactBusinessRelation2 - OnPreDataItem()
IF ContactOK AND ((GETFILTERS<>'') OR SquashFilters) THEN
ContactOK := FALSE
ELSE
CurrReport.BREAK;

ContactBusinessRelation2 - OnAfterGetRecord()
SkipSquashLedgerEntry := FALSE;
IF NOT SquashFilters THEN BEGIN
ContactOK := TRUE;
SkipSquashLedgerEntry := TRUE;
CurrReport.BREAK;
END;

The SquashFilters is determined in the OnPreReport trigger.

Report - OnPreReport()
ItemFilters := "Value Entry".HASFILTER;

//* Squash >>>
SquashFilters := "Squash Ledger Entry".HASFILTER;
//* Squash <<<
...

The code in the Squash Ledger Entry DataItem should look like this:

Squash Ledger Entry - OnPreDataItem()
IF SkipSquashLedgerEntry THEN
CurrReport.BREAK;
CASE ContactBusinessRelation2."Link to Table" OF
ContactBusinessRelation2."Link to Table"::"Squash Player":
BEGIN
SETRANGE("Squash Player No.",
ContactBusinessRelation2."No.");
END;
ELSE
CurrReport.BREAK;
END;
Squash Ledger Entry - OnAfterGetRecord()
ContactOK := TRUE;
IF ContactOK THEN
CurrReport.BREAK;

Make sure we filter on our instance of Contact Business Relation and that we filter on Link to Table Squash player.

The ContactOK indicates that all contact persons connected to this Squash Ledger Entry will be inserted.

Implement criteria filters

To support the criteria filter functionality we need to make two changes. One to the Add Contacts report and one to Codeunit SegCriteriaManagement.

In the add contacts report, we add this C/AL code to the OnPreReport trigger. This will make a call to the SegCriteriaManagement Codeunit (5062).

OnPreReport()
...
SegCriteriaManagement.InsertCriteriaFilter(
"Segment Header".GETFILTER("No."),DATABASE::"Value Entry",
"Value Entry".GETFILTERS,"Value Entry".GETVIEW(FALSE));
//* Squash >>>
SegCriteriaManagement.InsertCriteriaFilter(
"Segment Header".GETFILTER("No."),
DATABASE::"Squash Ledger Entry",
"Squash Ledger Entry".GETFILTERS,
"Squash Ledger Entry".GETVIEW(FALSE));
//* Squash <<<

In the SegCriteriaManagement Codeunit we add this code to the SegCriteriaFilter function which will require a new local variable for Squash Ledger Entry.

SegCriteriaFilter()
...

CASE TableNo OF
...
//* Squash Ledger Entry >>>
DATABASE::"Squash Ledger Entry":
BEGIN
SquashLedgEntry.SETVIEW(View);
EXIT(SquashLedgEntry.GETFILTERS);
END;
//* Squash Ledger Entry <<<
END;

Test solution

Now we can test the solution by trying to add all Squash Players of type Member to a Segment.

The result is a segment with the required squash players:

Microsoft Dynamics NAV: Customizing Relationship Management

This change also needs to be implemented to the reduce/refine functionality which works similar to the add contacts report.

Summary

Relationship management can be very useful to analyze sales data. In this article, we took a look at customizing relationship management.


Further resources on this subject:


Books to Consider

comments powered by Disqus
X

An Introduction to 3D Printing

Explore the future of manufacturing and design  - read our guide to 3d printing for free