Microsoft Dynamics NAV: Customizing Relationship Management

Exclusive offer: get 50% off this eBook here
Microsoft Dynamics NAV 2009 Application Design

Microsoft Dynamics NAV 2009 Application Design — Save 50%

A focused book and ebook tutorial for Microsoft Dynamics NAV application development

$35.99    $18.00
by Mark Brummel | May 2011 | Enterprise Articles Microsoft

Microsoft Dynamics NAV 2009 is a very complete ERP package, but unlike other ERP packages it has a design capable of providing an open structure and a development platform. The idea is to provide 80% of the solution out of the box and allow the other 20% to be designed by qualified business application developers.

Relationship Management software is a typical result of what ERP applications have achieved. Relationship management has been part of Microsoft Dynamics since version 2.0 and was dramatically changed and improved in version 3.0. The current Microsoft Dynamics NAV RM software is mostly the same as in that version except for the Microsoft Outlook integration that keeps changing in every version. In this article by Mark Brummel, author of Microsoft Dynamics NAV 2009 Application Design, we will take a look at customizing relationship management.

 

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.

Microsoft Dynamics NAV 2009 Application Design A focused book and ebook tutorial for Microsoft Dynamics NAV application development
Published: June 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:
        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:


Microsoft Dynamics NAV 2009 Application Design A focused book and ebook tutorial for Microsoft Dynamics NAV application development
Published: June 2010
eBook Price: $35.99
Book Price: $59.99
See more
Select your format and quantity:

About the Author :


Mark Brummel

Mark Brummel is an all-round Microsoft Dynamics NAV specialist. He started in 1997 as an end user but quickly moved to the other side of the table. For ten years he has worked for resellers, specializing in designing and maintaining add-on systems. Some of these add-on systems exceed the standard product in size and complexity. Also coaching colleagues and troubleshooting 'impossible' problems are a passion and part of his day-to-day work. Mark has trained most of the experienced NAV developers for the NAV 2009 product in The Netherlands and Belgium and he has been hired by almost every NAV reseller in the Benelux.

Mark works as a freelancer. His main focus area is helping out end users in implementations all over the world.
Mark was the first worldwide to use the NAV 2009 (CTP3) product in a production system, feeding back valuable information to Microsoft. Today he is still involved in projects to provide input for future versions and test new CTP releases.

A special project has been performance tuning of the Dynamics NAV product on SQL Server. From 2007 to 2009 he was involved in the development of the 'SQL Perform Tools' as business partner of SQL Perform Benelux. As a unique specialist he has done break-through research in improving the performance of Dynamics NAV on SQL Server.

When time is left, Mark maintains his blog on www.brummelds.com. This blog contains a wide range of articles about both the Microsoft Dynamics NAV and SQL Server products. He is also a frequent speaker at Microsoft events and writer for independent Dynamics NAV websites and user groups. In 2006 Mark was rewarded by Microsoft with the Most Valuable Professional award for his contribution to the online and offline communities. In 2007 and 2009 he reviewed Programming Microsoft Dynamics NAV and Programming Microsoft Dynamics NAV 2009.

Books From Packt


Microsoft Dynamics NAV 2009 Programming Cookbook
Microsoft Dynamics NAV 2009 Programming Cookbook

Microsoft Dynamics NAV Administration
Microsoft Dynamics NAV Administration

Microsoft Dynamics GP 2010 Reporting
Microsoft Dynamics GP 2010 Reporting

Microsoft Dynamics Sure Step 2010
Microsoft Dynamics Sure Step 2010

Microsoft Dynamics GP 2010 Cookbook
Microsoft Dynamics GP 2010 Cookbook

Microsoft Dynamics GP 2010 Implementation
Microsoft Dynamics GP 2010 Implementation

Microsoft Dynamics AX 2009 Administration
Microsoft Dynamics AX 2009 Administration

Implementing Microsoft Dynamics NAV 2009
Implementing Microsoft Dynamics NAV 2009


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
N
i
v
V
u
S
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software