Plataan

You do not have permission to delete the MBS MenuSuite object.

Sometimes when you are upgrading a Dynamics NAV database towards Dynamics NAV 5 or Dynamics NAV 5 SP1 with the Upgrade Toolkit you might receive this error when executing Task 5: Delete objects of the Upgrade Toolkit:

The issue occurs because the Upgrade Toolkit tries to delete all MenuSuite objects, but your partner license does not have permissions to delete the MBS MenuSuite object.

Possible solutions:

• You can delete all the Objects except for the Objects you do not have permission for manually.
• Alternatively change the code in Codeunit 104002 – "Delete Objects Excl. Tables" to not delete MenuSuite Objects and handle only these Objects manually. This is not difficult, you just have to remove MenuSuite from the Filter of objects to be deleted:
  
  
  Then after you Import the file containing the Dynamics NAV 5 Objects, make sure that you choose "Replace All" in the Import Worksheet.
• The best solution is that you have your license updated. So to resolve this issue, please send a request to Microsoft Business Solutions Licensing Team to update your license file.

Be careful with Editable SumIndexFields!

When you define a SumIndexField on a table, sometimes people forget to make the field Not Editable. As a consequence, on forms where the SIFT is shown, the user has the possibility to write something into a SIFT field.

What happens in this case and why is this 'dangerous'?

Let's take as an example the Cronus Demo Database, Dynamics NAV 5.1. (Native) In the Customer table (18) there's a field called: "Balance (LCY)". This field displays, in LCY, the customer's current balance. Dynamics NAV automatically calculates and updates the contents of the field using the entries in the Amount (LCY) field in the "Detailed Cust. Ledger Entry table".

When you take the Customer table into design, you can see this calculation in the Calculation Formula of the FlowField:

Now, just for fun, let's make the FlowField Editable:

Ok, now go to the Customer Card and open customer 60000:

Our Balance (LCY) field has now become an editable field. When you click on the Drilldown this is shown:

Now, enter 100 in the Balance (LCY) field:

When we click on the Drilldown, the list is still empty:

But, when you open the "Detailled Cust. Ledg. Entry" table:

You immediately notice that a line was added here, containing only an "Entry No.", "Customer No." and an "Amount (LCY)"!

This is because the FlowField was editable, and for the newly entered total of 100 to be correct, Dynamics NAV went into the underlying table and created an entry on its own…

This is why it is strongly advised to make FlowFields NOT Editable when showing them on Forms.

Confused by WIP? Not anymore!

Two always know more than one. So we put our heads together, trying to figure out how these new WIP methods in Microsoft Dynamics NAV 5.0 actually work. This is what we found out…

Actually it is fairly simple. First of all you have to make the following decision: Do I recognize completed job tasks during the project? And if so, do I recognize the costs or the sales prices?

	WIP –METHOD
No	Completed contract
Yes – cost	Cost Value or Cost of Sales
Yes – price	Sales Value or Percentage of Completion

So the question that was bothering us, what is then the difference between Cost Value and Cost of Sales and between Sales Value or Percentage of Completion. Let's explain with the following example:

STEP 1: CREATE JOB

This is a job divided over several tasks, where each task contains one or more planning lines. The job is sold to the customer for a fixed price (the job planning lines of type schedule are not equal to those of type contract).

STEP 2: REGISTER JOB USAGE & INVOICE THE CUSTOMER

Through the job journal we register the hours performed by Davy on 01/01/08 (job task 1000) and on 02/01/08 (job task 1001). We also register the items used on 02/01/08 (job task 1001), but the cost of these items has increased since the planning to € 800,00 per item. On 31/01/08 we send the customer an invoice, but only for the performed hours of Davy. This results in the following job overview.

STEP 3: CALCULATE WIP

Try out the different WIP Methods on the Job card for the calculation of the WIP Amounts. 

WIP Method	Cost value	Sales value	Cost of sales	Percentage of completion	Completed contract
Total WIP Sales Amount	0,00	2.488,63	0,00	4.167,19	-1.328,00
Total WIP Cost Amount	2.122,27	0,00	1.626,25	0,00	2.144,50
Recog. Sales Amount	1.328,00	3.816,63	1.328,00	5.495,19	0,00
Recog. Costs Amount	22,23	2.144,50	518,25	2.144,50	0,00

Cost Value vs Cost of Sales

Both WIP methods will recognize the revenue of the job at the moment of invoice to the customer. Both WIP methods will do this based on the costs related to the job. So in both cases we will get the same basic formula to calculate the WIP Cost Amount:

Total WIP Cost Amount = cost of consumption – cost of invoice

Notes:

• you can compare the cost of consumption with the batch-job 'Calculate Job WIP Value' in NAV 4.0 and the cost of invoice with the batch-job 'Calculate Job Recognition' in NAV 4.0

• cost of consumption and cost of invoice are no NAV-fields, but used to explain the calculation

However, the way in which these costs of consumption and costs of invoice are calculated are slightly different.

Cost Value

The cost of consumption is initially represented by Usage (Total Cost). However this cost is corrected to represent the part of the agreed sales price versus the planned sales price.

Cost of consumption = Usage (Total Cost) * ( Contract (Total Price) / Schedule (Total Price) )

The cost of the invoice should be interpreted as the planned cost of the job, the costs that you would normally make with all consumptions and is initially represented by Schedule (Total Cost). However this cost is corrected to represent the part of the invoiced sales price versus the sales price of planned consumption.

Cost of invoice = Schedule (Total Cost) * ( Contract (Invoiced Price) / Schedule (Total Price) )

→ Total WIP Cost Amount = [ Usage (Total Cost) * ( Contract (Total Price) / Schedule (Total Price) ) ] - [ Schedule (Total Cost) * ( Contract (Invoiced Price) / Schedule (Total Price) ) ]

Example:

Cost of consumption = (297,00 + 1.847,50) * ( (664 + 7.291,60 + 332,00) / (498,00 + 5.686,60 + 166,00) ) =2.798,595

Cost of invoice = (297,00 + 2.838,24 + 99,00) * ( (664,00 + 664,00) / (498,00 + 5.686,60 + 166,00) ) = 676,3252

Total WIP Cost Amount = 2.798,595 – 676,3252 = 2.122,27

Cost of sales

The cost of consumption is simply represented by Usage (Total Cost). The actual consumption is placed onto the balance accounts, without a correction.

Cost of consumption = Usage (Total Cost)

To recognize the invoiced job costs, we look at the initially planned costs, represented by Schedule (Total Cost). This is the same as for the WIP method Cost Value. However, a different correction of this cost is taken into the result. A percentage is calculated, based on the invoiced sales price versus the sales price of planned invoicing.

Cost of invoice = Schedule (Total Cost) * ( Contract (Invoiced Price) / Contract (Total Price) )

→ Total WIP Cost Amount = Usage (Total Cost) - [ Schedule (Total Cost) * ( Contract (Invoiced Price) / Contract (Total Price) ) ]

Example:

Cost of consumption = 297,00 + 1.847,50 = 2.144,50

Cost of invoice = (297,00 + 2.838,24 + 99,00) * ( (664,00 + 664,00) / (664,00 + 7.291,60 + 332,00) ) = 518,2527

Total WIP Cost Amount = 2.144,50 – 518,2527 = 1.626,25

Conclusion

Use Cost of Sales in a time & material project. Use Cost Value in a fixed price project.

If scheduled prices are equal to contracted and invoiced prices, then the Cost of Sales and the Cost Value will calculate the same Total WIP Cost Amounts.

Sales Value vs Percentage of Completion

Both WIP methods will recognize the revenue of the job at the moment of invoice to the customer. Both WIP methods will do this based on the sales prices related to the job. So in both cases we will get the same basic formula to calculate the WIP Cost Amount:

Total WIP Sales Amount = expected sales price – price of invoice

Notes:

• you can compare the expected sales price with the batch-job 'Calculate Job WIP Value' in NAV 4.0 and the price of invoice with the batch-job 'Calculate Job Recognition' in NAV 4.0
• expected sales price and price of invoice are no NAV-fields, but used to explain the calculation

In both cases, the price of invoice is easy to retrieve, since it is represented by Contract (Invoiced Price). No corrections are necessary here.

Price of invoice = Contract (Invoiced Price)

However, the way in which the price of consumption is calculated is slightly different.

Sales Value

The expected sales price is initially represented by the Contract (Total Price). However, a correction is made based on the expected price of actual consumption versus the prices of planned consumption.

Expected sales price = Contract (Total Price) * ( (Usage (Total Price) / Schedule (Total Price) )

→ Total WIP Sales Amount = [ Contract (Total Price) * ( ( Usage (Total Price) / Schedule (Total Price) ) ] – Contract (Invoiced Price)

Example:

Expected sales price = (664,00 + 7.291,60 + 332,00) * ( (498,00 + 2.426,60) / (498,00 + 5.686,60 + 166,00) ) = 3.816,634

Price of invoice = 664,00 + 664,00 = 1.328,00

Total WIP Sales Amount = 3.816,634 – 1.328,00 = 2.488,63

Percentage of completion

The expected sales price is initially represented by the Contract (Total Price). This is the same as for the WIP method Sales Value. However a different correction of this price is taken into the result. The correction is based on the cost of actual consumption versus the cost of planned consumption.

Expected sales price = Contract (Total Price) * ( (Usage (Total Cost) / Schedule (Total Cost) )

→ Total WIP Sales Amount = [ Contract (Total Price) * ( Usage(Total Cost) / Schedule (Total Cost) ) ] – Contract (Invoiced Price)

Example:

Expected sales price = (664,00 + 7.291,60 + 332,00) * ( (297,00 + 1.847,50) / (297,00 + 2.838,24 + 99,00) ) = 5.495,188

Price of invoice = 664,00 + 664,00 = 1.328,00

Total WIP Sales Amount = 5.495,188 – 1.328,00 = 4.167,19

Conclusion

Sales Value and Percentage of Completion calculate the same Total WIP Sales Amounts, unless the price or cost of the usage should change since the planning.

If you want to take into account changes in sales prices, then use Sales Value.

If you want to take into account changes in costs, then use Percentage of Completion.

Completed Contract

This last method will not recognize the revenue of the job untill the job is completed. Both realized costs and realized sales prises are taken into balance as the job progresses.

→ Total WIP Sales Amount = Contract (Invoice Price)

→ Total WIP Cost Amount = Usage (Total Cost) 

Example:

Total WIP Sales Amount = 664,00 + 664,00 = 1.328,00

Total WIP Cost Amount = 297,00 + 1.847,50 = 2.144,50

STEP 4: HOW THE WIP-TOTAL FIELD INFLUENCES YOUR WIP AMOUNTS !

Now let's take a look how the field 'WIP-Total' on the job task lines. So set the field on 'Total' for each job task line and recalculate the WIP amounts for each WIP method.

WIP Method	Cost value	Sales value	Cost of sales	Percentage of completion	Completed contract
Total WIP Sales Amount	0,00	2.447,49	0,00	4.082,33	-1.328,00
Total WIP Cost Amount	2.037,53	0,00	1.589,04	0,00	2.144,50
Recog. Sales Amount	1.328,00	3.775,49	1.328,00	5.410,33	0,00
Recog. Costs Amount	106,97	2.144,50	555,46	2.144,50	0,00

You will notice that the calculated amounts are slightly different, while the WIP methods and the used formulas are not different than in the previous step. However, the WIP methods are now applied per job task line instead of for the entire project.

Total WIP Cost Amount = ∑ Total WIP Cost Amount / job task line

Total WIP Sales Amount = ∑ Total WIP Sales Amount / job task line

Example - Cost Value

Job Task No. 1000 Total WIP Cost Amount = ( 297,00*(664,00/498,00) ) – ( 297,00*(664,00/498,00) ) = 0,00

Job Task No. 1001 Total WIP Cost Amount = ( 1.847,50*(7.291,60/5.686,60) ) – ( 2.838,24*(664,00/5.686,60) ) = 2.037,53

Job Task No. 1002 Total WIP Cost Amount = ( 0,00*(332,00/166,00) ) – ( 99,00*(0,00/166,00) ) = 0,00

Total WIP Cost Amount = 0,00 + 2.037,53 + 0,00 = 2.037,53

Conclusion

You can combine or separate job task lines for WIP calculation by setting the WIP-Total to Total.

You can exclude job task lines for WIP calculation by setting the WIP-Total to Closed.

Plataan changes Open Kalender Financials NAV 5.0!

Great news! Plataan has made some small changes in its open kalendar trainings concerning financial topics. We have combined some of the financials trainings, so that you can save time and at the same moment are completely prepared to take the Microsoft Dynamics NAV 5.0 Financials Certification Exam.

We have combined these 3 courses in the new training called 'Financials Track', taking you only 5 days, which you would normally spent 6 days to complete.

• Microsoft Dynamics NAV 5.0 Application Setup
• Microsoft Dynamics NAV 5.0 Finance
• Microsoft Dynamics NAV 5.0 Business Intelligence for Information Workers

Although not necessary for the financials exam, you can also follow these onsite trainings to increase your financial knowledge to an expert level:

• Microsoft Dynamics NAV Resources & Jobs
• Microsoft Dynamics NAV Fixed Assets

Check out our trainingcalendar for the exact locations and dates and enroll today!  

Noisy Van

I wanted to bring this new & exiting blog to your attention: http://noisyvan.wordpress.com

It contains a number of very interesting items about for example How to:

• Avoid "not a valid time unit" Errors with Multilanguage Calcdates
• Show Shipment Information on Sales Invoices
• Make Exporting Excel Formulas Fully Multilanguage Aware
• Storing Password in SSIS Packages
• Avoid Too Many Invoice Copies
• …

The CaptionClass property

Not so long ago we were investigating a strange problem with a report. Every time the report was run, the label of one of the fields on a section was not printing what it was supposed to print according to its Caption/CaptionML property, or according to the Caption/CaptionML property of the related field in the table it was linked too. Somehow, something was dynamically changing the label???

After a while we suddenly noticed that the property CaptionClass was filled in, in the properties of the field in the table, and this was the cause of this 'strange behavior.

So, how does this CaptionClass property works?

If the CaptionClass property of a field or a control is defined, the function trigger CaptionClassTranslate (ID 15) in Codeunit 1 (ApplicationManagement) is called by the system every time the field or control is shown. The purpose of this function is to replace the caption, as defined in the design of the field or control, with another string.

2 parameters are passed to this function:

• LANGUAGE

  The LANGUAGE parameter is automatically mentioned by the system as is the Windows Language ID of the active language in Navision.
  If the active language in Navision is English (United States), LANGUAGE will hold the value 1033.

• CAPTIONEXPR

  The CAPTIONEXPR parameter holds the content of the CaptionClass property of the field or control.

In a way, the function trigger CaptionClassTranslate (ID 15) is a system trigger:

This standard code analyzes and unravels the CaptionExpr parameter. This parameter has the following syntax:

• CAPTIONEXPR := <CAPTIONAREA>,<CAPTIONREF>

Depending upon the value of the CAPTIONAREA, different procedures are called. Either:

• DimCaptionClassTranslate(Language, CaptionRef)

  or

• VATCaptionClassTranslate(Language, CaptionRef)

This is the way the standard functionality in Dynamics NAV deals with the CaptionClass property. Every field or control with a defined CaptionClass has a string in this property with the syntax described earlier.

The following diagram illustrates this sequence:

So, knowing this and with a little bit of imagination, you could extend the code in the CaptionClassTranslate trigger to be able to dynamically translate/change labels in Dynamics NAV.

The importance of accounting periods in fixed asset depreciation

Recently I discovered that you better create your accounting periods before you start depreciating your fixed assets. It could seriously affect the calculation of the depreciation amount, but how?

 

First of all, if you do not create an accounting period, then Microsoft Dynamics NAV 5.0 will try to appoint your depreciation to the last existing accounting period, resulting in a depreciation over more than 360/365 days. This could be a problem if your depreciation book does not allow depreciation over more than 360/365 days. If you have already SP1 installed, you will be notified of this issue when you run the report Calculate depreciation by the following warning:

 

 

Even if your depreciation book allows depreciation over more than 360/365 days, a second problem occurs when you are using the depreciation method of declining balance with a switch to straight line. Microsoft Dynamics NAV 5.0 SP1 will remain to depreciate according to the declining balance method.

 

Let's take a look at the following example, with a fixed asset that is setup as follows:

 

 

The expected depreciation:

Straight line : 1.000 * 0,2 = 200

Declining balance:            

Y1:    1.000 * 0,4 = 400

Y2:      600 * 0,4 = 240

Y3:     360 * 0,4 = 144 < 200 The switch is made from declining balance to straight line.

Y4:     160

 

If you haven't created the correct accounting periods, the following depreciation will calculated:

Y1: 400

Y2: 240

Y3: 144

Y4: 86,4

…

 

 

So make sure you have those accounting periods setup, before calculating your fixed asset depreciation!

 

 

 

Sql Server 2008 IntelliSense

A couple of days ago, after hours, when it was raining here in Barcelona (yes that can happen also here), I downloaded, installed and started playing around in the the evaluation edition of Sql Server 2008 (KATMAI).

One of the first things I wanted to double-check was the IntelliSense. Yes, as from Sql Server 2008 IntelliSense has finally arrived!

In the CTP versions of Sql 2008 there was some confusion about backwards compatibility of this IntelliSense. In some CTP's it also worked on Sql2005 databases and in others it didn't. I'm a bit sad to announce that in the final product, it will only work with Sql 2008 databases. So if you use SSMS 2008 on a Sql 2005 or Sql2000 database, there's no IntelliSense.

Anyway, I think it is a really cool and useful feature, especially when you have to write queries in SSMS (Sql Server Management Studio) on Dynamics NAV databases:

(Yes, if you look closely at the image above, I have a Dynamics NAV 5.1 CRONUS DB running on Sql Server 2008 and it seems to work fine, although not officially supported yet…)

You can just start typing your query, and if you want to see the IntelliSense dropdown, just type CTRL + SPACE. Then you can use the arrows or PgUp & PgDn buttons to select the table you are interested in.

This is of course only one simple example of what IntelliSense can do for you, if you want to know more, just have a look in the BOL (Books Online).

For example there's the Complete Word option. If what you have typed has more than one possible match, invoke Complete Word to display a list of possible matches, which you can then use to find the term and insert it into your code.

Another one is the Quick Info option; it displays the complete declaration for any identifier in your code. When you move the mouse pointer over an identifier, its declaration is displayed in a yellow pop-up window:

Another possibility is Delimiter Matching and Highlighting. Automatic matching of syntax pairs gives you immediate feedback on whether syntax elements that must be coded in pairs are correctly paired.

If only we had something like this in the Dynamics NAV C/AL editor…