The Setup

For this example we are going to use the standard report “Customer Account Details” as a base report (renumbered so we don’t overwrite anybody’s default report). This report already has the option to show customer account details in the customer’s own currency, however it does not include the region specific currency formatting except for the number of decimal places. For kicks, and because it provides a better example, we are also going to take the dates and format those to the localized short date format. Note that this latter customization is one that might cause confusion if used in an actual client environment, especially if you have a particularly diverse set of customer regions being reported on with a single report.

It is worth mentioning that this example does not make any changes to the behavior of the classic version of this report except to add some hidden data fields for the RTC to use. To the best of my (admittedly newbie) knowledge, formatting of this type is not possible using the classic version without creating a complex function to apply the formatting prior to outputting the data to the report.

The Hoops

First Hoop – Letting your report use external assemblies

If you look at the MSDN page regarding currency formatting codes you will see that references are made to a “CultureInfo” object. Yet if you try to define such an object or make use of one per the VB examples shown, you will get an error. This is because we have to add the assembly for it. Before we can do that though, we need to edit the report properties to tell the report to allow external assemblies. Load up the report in classic, put the cursor on a blank line and hit ALT+F4 to bring up the report properties. Once you have that up, set the “EnableExternalAssemblies” option to “Yes”.

If you forget this step then later on you will see an error about ‘not a trusted assembly’ that looks a lot like this:

Second Hoop – Adding the assembly to the RTC version of the report

Once you have external assemblies enabled, the next step is to actually add that assembly to the report so that we can call upon the CultureInfo object within in.

To do this you have to load up the report designer (using the “View | Layout” menu option) and right click on the top left corner of the report in the report designer and select properties:

In the properties dialog, click on the “References” tab. Click on the ellipses to bring up the list of modules, click on the “.NET” tab and locate “sysglobl”:

Note: If you’ve not used sysglobl before, you may have to use the Browse tab the first time.  Browse to your local Microsoft.NET folder and locate a “sysglobl.dll” in the relevant Framework folder.

Once you have added the globalization assembly, it should appear in the list as shown below:

Next we have to add in some code to let us make use of the CultureInfo object.

Third Hoop – Creating functions to use the format codes with the globalization object

In a future blog post we will discuss some of the interesting ways to use the ability to add code to the report. For now though, we just want to create two custom functions that will let us format dates and currencies to a specific localization. As we did in Fig 4., above, we are going to right click the top left corner of the report and select properties. This time, instead of going to the “References” tab we are going to go to the “Code” tab.

Inside the code tab, we will define two functions:

FormatCurr (ByVal Amount as Decimal, ByVal Precision as Integer, ByVal Region as Integer, ByVal BlankNegZero as Boolean)

FormatDate (ByVal TransDate as Date, ByVal Region as Integer)

The first function will take the decimal amount to be formatted, the number of digits of precision (which we will pass in from the Currency.”Amount Decimal Places” setting), the Windows integer region code (taken from Language.”Windows Language ID”), and whether or not to return blanks for zero or negative values. For the second function we do not need to worry about rounding precision on a date so we just pass in the date in question and the Windows integer region code.

The code itself is very simple VB .NET code:

Public Function FormatCurr(ByVal Amount As Decimal, ByVal Precision as Integer, ByVal Region as Integer,

 ByVal BlankNegZero as Boolean)

   Dim TempString as String

   if (Amount <= 0 and BlankNegZero) then

     return ""

   end if

   TempString = "C" + Precision.ToString

   Return Amount.ToString(TempString, New Globalization.CultureInfo(Region, False))

End Function

Public Function FormatDate(ByVal TransDate As Date, ByVal Region as Integer)

   Return TransDate.ToString("d", New Globalization.CultureInfo(Region, False))

End Function

Now all we have to do is add some data to the classic version of the report and adjust some of the RTC fields and we will be done.

Making the customization

In the classic version of the report, we now have to add decimal precision and the Windows language ID as hidden fields. The decimal precision we can look up and pass from the Currency table (ID 4), the Windows language ID we will first have to do a lookup on the customer’s language in the Customer table (ID 18) and use that language code to make a lookup into the Language table (ID 8 ) for the Windows language ID.

Both of these lookups make the most sense to do in the Customer – OnAfterGetRecord of the classic report (the .fob of the report is included at the bottom of this post, search on “SSS ABS” in that module to find the relevant modifications).

We will add the data in hidden text boxes in the section designer in the Customer, Body (8) section.

Once we have those fields added we can then go to Visual Studio and make changes to the expression for the currency fields. It is worth mentioning that I have left most of the original function intact, such that the report will print with the existing default formatting if the “Print Amounts in Customer’s Currency” option is left unchecked. Some of the expressions are thus greatly expanded.

For example, the original expression for a debit amount is as follows:

=Code.BlankNegAndZero(Fields!AmountToPrint.Value)

And the modified version is shown below (with spacing added for readability):

=iif(NOT Fields!PrintAmountsInLocal.Value,

Code.BlankNegAndZero(Fields!AmountToPrint.Value),

Code.FormatCurr(Fields!AmountToPrint.Value,Fields!CurrPrecision.Value, Fields!LanguageID.Value,TRUE))

The last step before we are ready to roll is to look at the formatting of the table cells. While the classic version of the data is likely going to follow currency conventions, it will not include any special currency symbols that can help make it obvious what currency we are looking at. If the user selects not to show local currency we should retain the original formatting, but if they do use the culture specific formatting, then our FormatCurr and FormatDate functions will handle that instead of the Format property. For the same cell as used in the above sample for the expression of the debit amount, the “Format” field of the cell that originally reads:

=Fields!AmountToPrintFormat.Value

Will be modified to this:

=iif(Fields!PrintAmountsInLocal.Value, nothing, Fields!AmountToPrintFormat.Value)

Results

We can now look at the output and see what looks different when we show amounts in local date and currency and not in local date and currency and compare that with the base version of the report.

I am running the standard USA Cronus database filtering only on customers 01445544 (USD), 32656565 (Euro) and 42147258 (Czech koruna). The base version of the report will display as follows with the option to show amounts in customer currency:

The modified version now displays with region specific date and currency formatting:

Conclusion

If you have an international client that does want some multiple region-specific formatting on the same report or even formatting for a single region that is not the client’s installed region, this method will do the trick. The code functions are also easy enough to copy and paste into a new report, which means that once you jump through the hoops of letting the report use the sysglobl assembly, it is pretty easy to do again on another report.

Download Modified Customer Account Detail Report

For a full list of the formatting options, the MSDN site has a handy reference page for both numbers and dates/datetimes/times. I will not be covering all of the various formatting options in my example due to the fact that there are so many of them, but the most commonly used ones are represented.

Cautions

The RDLC formatting options typically will use your default localization info for formatting. If you are a USA developer working for a French client you may need to change your current locale in order to see the formatting options as your client will see them. One of the nice things about the standard formatting options vs. the custom formatting strings is that they will take care of this change for you without you having to write a lick of code and without you having to define multiple formatting strings for each locale you service.

It bears mentioning that if you need to include multiple different locale formats in a single report there are some hoops to jump through to get that to work properly. We will cover that subject in a blog post very soon. Suffice it to say that it will not work to do multiple locales with just the standard formatting options.

How To

Create your report data items in classic as you normally would. Our example report skips some of the good design steps for localization and multi-language in favor of simplicity. The entire report is just several different type data items in a single section:

Once you fire up RDLC and place your data item in the desired location you will want to look at the properties, specifically the “Format” property:

In the example above, I am formatting a decimal value to the locale default currency format (denoted by the “C”). Since I have my locale set to the USA, it will print in $#,###.00 format: Leading USD dollar sign, comma group separator, period decimal place designator and two decimal place precision. As I mentioned previously, if I send this report off to a French client I will not have to redo the report to print properly in Franc or Euro, the localized default format for currency will automatically handle that for me (Note: This does not convert the amount, only changes the format).

If, for some reason, I have to call a Code.SomeFunction to do some special processing and want to return the formatted text from that function it is worth noting that you can use these standard formatting codes in the VB.Net Format function call like so:

Public Function SomeFunction(ByVal Value As Decimal)

…processing code here…

Return Format(Value,”C”)

End Function

See the attached sample report and PDF printout of the report that shows the most commonly used codes for more information.

In Microsoft Dynamics NAV 2009 RTC Report Design, matrixes can provide a lot of easy functionality for a newbie report writer who wants to collate a lot of data without doing a lot of work. With that in mind, there are some significant drawbacks to using a matrix if your report has certain hoops to jump through that often mean that it may be easier to use a table. Note that while advanced NAV report designers can likely get around some of these obstacles by employing some of the less intuitive tricks available, this article is intended for novice users looking at whether or not a matrix control would be faster and easier to implement than the more traditional table control.

The what and how of building a matrix report

If you are not already familiar with what a matrix control is, the easiest explanation is that it is a way to bring the versatility of a spreadsheet pivot table to a report. The joy of working with pivot tables is that you can essentially take a mass dump of data and then organize it however you like and the pivot table logic takes care of the collating and summation without you having to write a lick of code. It bears mentioning that if you’re doing a onetime-only report that requires this sort of processing, you may actually be better off creating a dummy report that simply outputs all your uncollated data and then use the “About this report” feature to dump it into Excel and use an Excel PivotTable to have your way with it. Excel in that particular case is, in my opinion, far more versatile and flexible for the neophyte than trying to figure out a matrix.

For the novice, I do strongly recommend reading up on Small Square Services’ own introductory “how to” for setting up a matrix. If you are like me and you like to have points of comparison so that you can see how multiple authors approach matrixes I can also recommend Claus Lundstrøm’s tutorial on the MSDN blog.

Why use a matrix?

While the focus of this post is to show when not to use a matrix, it’s important to understand why you would want to use one in the first place. Simply put, matrixes are easy to make. You don’t have to process, sort or collate data – all you have to do is create a simple classic report layout that contains all the data you need and then put a matrix control with the proper groups on a page. For many folks that don’t favor writing a lot of processing code, this is certainly an appealing notion, especially as you can define much of the look, feel, and content through the visual designer interface. If you want fancy processing but do not want to spend a lot of time on it, a matrix can be a “dream come true” control in the Visual Studio Report Designer.

So why the blue pill?

If a matrix is so powerful, why not use one all the time? What are some of the features of a report that would cause a newbie report designer to bang their head against the nearest solid object?

Non-uniform output

One of the great things about matrixes is that they collate data and create dynamic rows and columns to display that output. When something is created at run time, it greatly limits our ability to modify how that data is shown, if some of the data is to be singled out for special treatment. You can do some very useful things with the expression based attributes of matrix cells but if you have anything in a matrix that needs to have different data (as opposed to different appearance) in certain columns, then a matrix is going to likely not be the best solution. An example would be if you want to provide monthly totals with quarterly totals and averages. Trying to add those averages into a mix of totals is possible to do, but you’ll have to employ some more advanced tricks. If that’s what you want to do, tables are probably going to be easier in the long run than trying to squeeze this square peg into a round hole. It is worth mentioning that the very snazzy looking Tablix* control will eventually remove this concern, allowing us to mix matrixes with tables with impunity.

This reason is likely the most important one of the three I list here. It is possible to spend a lot of time wrestling with the matrix control to try to get it to insert an “average” column after the main body but before the totals. Or if you have three columns per reporting item but your totals only make sense for two of those columns and you want to eliminate or hide the third.

Summation columns that are not simple totals

Matrixes provide very handy “subtotal” columns that you can add for any column or row grouping. If all you want are subtotals across a data slice, then this is just another marvelous feature of matrixes. If, however, you want totals for some of the data and averages for some of the data, then you’ve essentially got to provide code to the InScope function that will dynamically do one or the other, which likely exceeds the newbie threshold.

Your data is not repetitive or does not need to be collated

Matrixes are primarily a tool for collating repetitive data. If your data is relatively flat and/or doesn’t feature repeating categories, then a table with groups is probably going to offer more flexibility with less hassle than a matrix.

Matrix is a very powerful control, and learning how to use it will be key for great Report Design in Microsoft Dynamics NAV 2009, but always make sure to think if it’s the right powerful tool for the job.

*RDL 2005 is used in Microsoft Dynamics NAV 2009 for the RTC Report Designer. RDL 2008 has more powerful options for controls (such as the Tablix control), grouping, and various other improvements. Hopefully the next major release of Microsoft Dynamics NAV will feature the RDL 2008 improvements.

In this post, we’ll talk about how to bring together diverse data into a dynamic table – we’ll use some Item Sales data for customers by month, which has always been a pest to accomplish in the Classic Report Sections. We’ll be going over:

• How to setup the raw data for the RTC
• Creating the basic Matrix
• The basic idea of Row and Column Grouping
• Controlling Visibility
• Introduction to Totals
• Cleaning Up

This one topic will probably spawn a host of follow-up posts to explain parts, or expand on other possibilities. I even considered having this post broken out into the 5 parts above because it’s a lot of ground to cover.

How to setup the raw data in Classic

Firstly, we’ll need to bring together all the data requested:

• Customer Info
• Item Info
• Sales Invoice Info
• Month info (yes, this is key)

Note: You may want to review our RTC vs Classic data structure post here.

We’ll start out with a data structure like so:

1. Setup your filter request fields on them as appropriate.
2. None have a DataItemLink linkage to each other.
3. On the Date DataItem, we’ll set the DataItemTableView to limit the results to Months and ask the user for a Period Start filter.
4. On the Sales Invoice Line, we’ll set the DataItemTableView to limit the results to Type=ITEM. On the Sales Invoice Line, we’ll want to manually handle our filtering in OnPreDataItem, like so:

SETRANGE("No.",Item."No.");
SETRANGE("Sell-to Customer No.",Customer."No.");
SETRANGE("Posting Date",MonthLoop."Period Start",MonthLoop."Period End");

Note: This assumes all data is version 2009+ data. The Sales Invoice Line table got the Posting Date field in version 2009, so if your customer has older data, you’ll either want to populate the Sales Invoice Line Posting Date field with the header data or you’ll have to have a Sales Invoice Header item.

You’ll then want to setup the Sections to display these elements, like so:

Make sure to set easy to follow DataSetFieldName properties on each. Notice how MonthLoop has both the “Period Name” (aka Month Name in our case) and the “DATE2DMY(“Period Start”,3)” function (Year integer). If your customer wants to run the report over the course of multiple years, you’ll need that Year value to differentiate one January from another.

Once you have all the data you’ll want, make sure to save the report, then Create Layout Suggestion.

Note: Consider adding the standard Page Headers here so Suggest Layout takes care of them for you. Our example plans to go to Excel, so no headers/footers are required.

Create the Basic Matrix

Once you see your suggested layout, you’ll wonder why we had you do that. Not sure why, but something happens in that sequence that links the DataSet to things behind the scenes. Without that step, we have not been able to get a Matrix to work correctly.

Go ahead and delete the unhelpful table:

Now, let’s create a Matrix in the space we have. On the Toolbox item to the far left, pick a matrix and drag it onto the Body:

You should get an empty matrix:

Not much to look at, so let’s add some content.

The Basic Idea of Row and Column Grouping

So, let’s review what we need to accomplish: We need Item Sales Quantity and Sales Amount by Customer and Item by Month and by Year. Hopefully you see how that equates out to 2 row groups (Customers and Items) and 2 column groups (Year and Month), with somehow 2 columns of data inside each.

With both the Row and Column Groups, we always get one of each to start with, so we’ll focus on adding/setting the Row Groups first.

Customer and Item Rows

Right-click on the Rows box and select Insert Group. You’ll be asked to set the properties of the new group:

Go ahead and change the name part RowGroup2 to ItemGroup for clarity. In the Group On, Expression area, click in the first row. You’ll be able to select from the Fields:

Select your Item Number field from the list (your field names may vary based on how you set the DataSetFieldNames).

Note: If you don’t see these fields, close up the Designer, close NAV, reopen NAV, reopen the report, delete the Layout, and redo Create Layout Suggestion.

That’s all we’ll set on this for now, though we’ll be back. Click OK. You should still see the ‘Rows’ field as blank, but a new column added:

Adding the Customer Group:

1. Right-click the field that is marked (1) in the picture above and select Edit Group.
2. Change RowGroup1 to CustomerGroup for clarity.
3. Set the Group On Expression to Customer No.
4. Click OK.
5. You’ll see the exact same thing as above. In this case, we’ll need to also set the Value property manually to the correct field:

Adding the Year and Month Columns

A very similar process is used here. We’ll Insert a Column Group for Month, then set the existing Column Group to Year. No pictures, just quickly stepping through:

1. Right click on the Columns block and select Insert Group.
2. Setup ColumnGroup2 as MonthGroup based on the Month Name as the Group On Expression
3. Right click on the Columns block again and Edit Group.
4. Setup ColumnGroup1 as YearGroup based on the Year Integer as the Group On Expression
5. Set the Value property of the Year Group to the Year Integer

Adding the Quantity and Amount Data Fields

On the Data field, right click and select Add Column. That will split the single cell into four, like so:

Note: It’s critical that those blank cells are below the Column Group headers. If not, delete the Matrix and start over.

Now you can drag and drop the Fields from the Result list on the left to populate the bottom two empty fields (it’ll auto-caption):

Note: This approach does not bring over the Multi-Language Captions from NAV to try to keep this overview short. Bear in mind your customer needs for multi-language support before you implement exactly as outlined.

Note: This walkthrough also does not address properly formatting fields using the Format DataSet items, so make sure to bear that in mind as well.

You can go ahead and save it as is, and give it a run to see how it looks, it technically works now:

You can see it automatically took care of grouping our Customers and Items by Month and Year, with some data.

Controlling Visibility

Obviously, Items that weren’t sold to a given customer are adding a lot of blank space we don’t need. Thankfully, we don’t have to write any C/AL to SKIP records, we can control the Visibility of Group Rows.

1. Open the report up in the Designer
2. Right click on the Item No field we setup before and Edit Group
3. Let’s look over the Visibility tab:

Change the Initial Visibility to Expression. In my case, the expression will simply be:

=Sum(Fields!SaleAmount.Value) = 0

This may be a trifle confusing. While it’s talking about if it should be visible, it’s setting the Hidden property. In my case, if the Group’s SUM of SaleAmount is zero, then Hidden is TRUE.

The same Visibility property can be set for any Row or Column Group. I’ll set it to my Customer Group as well, but not my Month/Year columns, as the customer wants them to show regardless.

Introduction to Totals

The customer wants there to be subtotals per Customer, a grand total per month, subtotals per year per item, and pretty much everything that can be totaled. Not a problem.

For each Row or Column Group that requires Totals, simply right-click on the relevant group field and select Subtotal:

In our case, we’ll turn them on for everything:

Those grey spaces will be automatically populated by the totals. Let’s break down what totals are which:

1. This will be at the very bottom, a grand total, with totals per month/year across ALL Customers and ALL Items
2. This will be at the bottom of each Customer, with subtotals per month/year across ALL Items
3. This will be at the far right, a grand total, with totals per Customer and per Item for ALL months and years run
4. This will be at the right of each Year, with subtotals per Customer and per Item for ALL months for that year

Any or all of those can be powerful data elements to be able to include with two clicks each. You will want to update the Captions for each, likely, so users understand the data they’re totaling.

Cleaning Up

There are many things we can do from here to tidy up. I’ll list several of them, and then review a couple of them:

1. If this were going to be printed, add header/footer data
2. Resize the columns to better fit the data
3. Align the Column Headers to make more sense
4. Add the Customer Name and Item descriptions to the Rows
5. Apply Border Grids to the whole matrix
6. Apply special formatting to Total Rows/Columns/Fields to better differentiate

Add Name/Descriptions to Number Fields

This one is super easy, but not intuitive if you’re used to C/AL.

Select the Customer No. textbox, then under the Properties panel, select the Value drop-down and double-click Expression:

You can easily use this screen to select additional fields. After the existing expression text, add:

+ ": " +

Double-clicking on your Customer name under Fields will insert the right coding for it.

Note: As you become more comfortable with the Designer, you do not need to open the Expression builder to enter these things, you can just type them right into Value.

Add Borders to the Matrix

• Click on the large empty cell in the Upper Left.
• Hold down Ctrl on your keyboard and select all the other fields. You can’t select the grey space, it will assume you mean the whole matrix control.
• Change the property BorderStyle from None to Solid.

Add Special Formatting to Total Data

If you have already charged ahead and set the Total fields to Bold, you’ll have noticed that the labels were made bold, but the data was not.

You have to click on the little green arrow in the corner of the Total field to set formatting special on the auto-generated cells:

When you have done that selection correctly, you will see the Properties panel set to the Subtotal control instead of a textbox:

You can setup separate Borders and Font settings for the generate total cells. For example, you may want a BorderStyle Top set to Solid, a BorderWidth Top set to 2pt, and FontWeight set to Bold. This will great a strong visual cue that it’s a total row.

Download the text object here.

In closing, there’s always more you could do with a report like this, but hopefully we’ve whet your appetite for Matrix reports.

Microsoft Dynamics NAV classic reports are highly iterative. For example, this Customer/Item Statistics report is a great example:

The engine will loop through the Customers that match the user supplied filters. For each Customer, it will go through some related set of Value Entry records (for item sales data). This is a very clear teamwork in the results:

What is more interesting is that the RTC reporting engine does not work in a hierarchy at all. It smashes the data flat into one giant resultset. Header items are simply repeated for every row. In our above sample report, we’d have a resulting table like so (subset to be more readable):

You can see the flattened results for yourself. Run a report in the RTC. Under the Help button, select About This Report, like so:

You may get a message like so:

Do as it instructs. When you head to About This Report again, you’ll see something akin to:

That’s a giant List of all the data the report is using to produce the results. You’ll see all sorts of things in there you may not realize would be, such as Captions, Formats, Header info. This feature can be particularly helpful, since under the Actions menu, you can Send To -> Excel all the raw data. If you ever doubt your report’s data, you can analyze it in this way.

Understanding that the data is giant and flat may help you better understand why some layouts will have things such as =First(SomeField) or =Sum(SomeField), like so:

It’s also a key understanding for making the most out of the RTC format’s powerful Table and Matrix controls, which we’ll go into depth on this week.

In our case, we have a report that is printing a 2^nd page with nothing on it but headers. For our example, we’ll use the Top 10 Customer report.

When we preview the report, it looks fine:

Nothing unusual, looks like it would fit on our standard page size here. Yet, if you switch from Interactive Mode to Print Layout, we get a mystery second page:

The usual things to check are:

• NewPagePerRecord property
• NewPagePerGroup property
• Any CurrReport.NEWPAGE calls
• Footers that might cause extra whitespace

None of those are present in our report. What’s the culprit? White space, not to the bottom, but to the right:

That random extra inch of space on the right that someone forgot to neaten up is being treated as printable space, which means for each page of the report, we’ll have an ‘extra’ page to the right. Once you close that space back down to the printable elements, it’s back to a single page.

“Navision is big. You just won’t believe how vastly, hugely, mind- bogglingly big it is. I mean, you may think it’s a long way down the road to the chemist’s, but that’s just peanuts to Navision.”

One of my goals as the newbie programmer at Small Square Services is to leave a record, a path if you will, for those that come after me to learn without getting as lost as I have sometimes become. Accordingly, I’ll be posting some of the things I’ve learned with an emphasis on a very inexperienced view of how to get around in Navision. Writing about shortcuts, insights into the underlying structure of Navision, and how to find some of the more tucked away components will be my emphasis. For those of you reading this that are already experienced NAV developers, this may not teach you much, but hopefully if you ever have to train somebody else you can point them this way to get them started.

It is worth noting that while the competition for newbie Navision programming blogs is sparse, I am not a pioneer in this arena. I highly recommend the Navision Girl blog for an already well populated set of articles and tutorials.

You can quickly see all the Vendors, with the columns for their name, address, and contact info. As a developer, you can quickly and easily add or remove fields to this list. In our example today, the customer doesn’t want to see the Phone No. field, but wants to see the Purchaser
Code.

To make this change, we’ll want to open up the Classic client and design the Vendor table. Under the View menu, you’ll have an option called Field Groups:

This will open up a new little Field Groups window:

As you can guess, we can edit the Field Group DropDown to change what the DropDown will show when Vendors are involved. (If you’re working with a new custom table, simply add a new Field Group called DropDown. Make sure to include the primary key.)

Based on our requirements, our Vendor DropDown field group now looks like:

When we return to our Purchase Order and try to select a Vendor, we’ll see this list (you may have to adjust your column widths):

You can include a FlowField like Balance, but bear in mind that the DropDown may be much slower to open due to the increased calculation load.

Now you’ve gotten another request from that client that will involve updating the Payment Terms Code on Customers when certain things happen in the system, such as they go overdue. You’ve made the changes they’ve requested without a hitch, but two months later, they call, very disturbed, because your process doesn’t update the Change Log. What happened? You called VALIDATE on the field, that’s all that’s needed, right?

Unfortunately, no. The Change Log will automatically log all User Interface changes, but changes made in code are handled differently. As the developer, you have the responsibility to register those changes with the Change Log yourself.

Your code needs to go from:

Customer.VALIDATE("Payment Terms Code",'COD');
Customer.MODIFY(TRUE);

To:

xRecRef.GETTABLE(Customer);
Customer.VALIDATE("Payment Terms Code",'COD');
Customer.MODIFY(TRUE);
RecRef.GETTABLE(Customer);
ChangeLog.LogModification(RecRef,xRecRef);

xRecRef and RecRef are both variables of type RecordRef, and ChangeLog is a Codeunit variable pointing at the Change Log Management Codeunit.

The other logging functions work fairly similarly:

• LogInsert
• LogRename
• LogDelete

If the client turns of Change Log or stops tracking the fields you’re programming for, the Change Log Management Codeunit won’t record the changes or give the user any error message, so this code won’t become deprecated when the client changes their mind about auditing.

For users, this means if you wanted the Payments of Customers always due at the end of the current month, you’d setup a new Payment Term with a Formula of ‘CM’.

For developers, this means you can quickly get the current year into a filter like so:

DateRec.SETRANGE("Period Start",CALCDATE('-CY',WORKDATE),CALCDATE('CY',WORKDATE));

We’ll dive into some of the stranger uses of DateFormula in a later post, including how to factor in Weekdays, or the xth day of the month/week.

For developers, some sample code

OBJECT Form 80001 Closing Dates Math Example
{
  OBJECT-PROPERTIES
  {
    Date=04/20/11;
    Time=[ 3:28:41 PM];
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    Width=8000;
    Height=8000;
  }
  CONTROLS
  {
    { 1000000002;TextBox;3630 ;220  ;1700 ;440  ;CaptionML=ENU=Date Basis;
                                                 SourceExpr=MyDates[1];
                                                 OnValidate=BEGIN
                                                              Calc;
                                                            END;
                                                             }
    { 1000000003;Label  ;220  ;220  ;3300 ;440  ;ParentControl=1000000002 }
    { 1000000004;TextBox;3630 ;1100 ;1700 ;440  ;CaptionML=ENU=-CY;
                                                 SourceExpr=MyDates[2] }
    { 1000000005;Label  ;220  ;1100 ;3300 ;440  ;ParentControl=1000000004 }
    { 1000000006;TextBox;3630 ;1650 ;1700 ;440  ;CaptionML=ENU=CY;
                                                 SourceExpr=MyDates[3] }
    { 1000000007;Label  ;220  ;1650 ;3300 ;440  ;ParentControl=1000000006 }
    { 1000000008;TextBox;3630 ;2310 ;1700 ;440  ;CaptionML=ENU=-CM;
                                                 SourceExpr=MyDates[4] }
    { 1000000009;Label  ;220  ;2310 ;3300 ;440  ;ParentControl=1000000008 }
    { 1000000010;TextBox;3630 ;2860 ;1700 ;440  ;CaptionML=ENU=CM;
                                                 SourceExpr=MyDates[5] }
    { 1000000011;Label  ;220  ;2860 ;3300 ;440  ;ParentControl=1000000010 }
    { 1000000020;TextBox;3630 ;4070 ;1700 ;440  ;CaptionML=ENU=CW;
                                                 SourceExpr=MyDates[7] }
    { 1000000021;Label  ;220  ;4070 ;3300 ;440  ;ParentControl=1000000020 }
    { 1000000024;TextBox;3630 ;3520 ;1700 ;440  ;CaptionML=ENU=-CW;
                                                 SourceExpr=MyDates[6] }
    { 1000000025;Label  ;220  ;3520 ;3300 ;440  ;ParentControl=1000000024 }
  }
  CODE
  {
    VAR
      MyDates@1000000000 : ARRAY [10] OF Date;

    PROCEDURE Calc@1000000000();
    BEGIN
      MyDates[2] := CALCDATE('-CY',MyDates[1]);
      MyDates[3] := CALCDATE('CY',MyDates[1]);
      MyDates[4] := CALCDATE('-CM',MyDates[1]);
      MyDates[5] := CALCDATE('CM',MyDates[1]);
      MyDates[6] := CALCDATE('-CW',MyDates[1]);
      MyDates[7] := CALCDATE('CW',MyDates[1]);
    END;

    BEGIN
    END.
  }
}

To read the (very) technical explanation, here’s the MSDN documentation on DateFormula.