How Microsoft Dynamics NAV fits a consultancy company

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 | June 2010 | 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.

In this article by Mark Brummel, author of  Microsoft Dynamics NAV 2009 Application Design, we will learn how Microsoft Dynamics NAV fits a consultancy company. Most consultancy companies have project related processes. They take on larger projects that take a certain time to complete. Some consultancy companies also purchase and sell items.

We will discuss four different project scenarios and see how Microsoft Dynamics NAV can be used to support those. To do this we will create some modifications along the way.

(Further on Microsoft Dynamics NAV:here.)

The process

The two main processes for Microsoft Dynamics NAV partners are implementing new projects and providing services such as support and upgrades to existing customers. A third process is selling infrastructure and assembling computer systems but this is an extra service, not the core business.

To support the projects (jobs) the company needs people, software licenses and hardware. The people (resources) need to be carefully planned on the projects as they are the least flexible part of the company. Hardware (items) and software licenses (G/L accounts) will be purchased from vendors such as Microsoft.

The projects can be divided into large and small projects. The larger projects are new implementations and upgrades. Smaller projects include implementing small features and helping users with regular support issues.

Invoicing can be done in various ways. New implementations and small projects can be invoiced per billable hour while upgrades are sold fixed price. For hardware we will use items. Licenses are invoiced directly to the General Ledger.

Large projects also have budgets and a plan that need to be maintained. If the budget is fully used and the planning milestones have not been reached there should be a new budget created in order to complete the project.

To support this process we will use the Jobs functionality with some customizations. Projects are called Jobs in Microsoft Dynamics NAV so we will use that term from now on.

The Jobs module has been completely redesigned by Microsoft for version 5. In this article we will use a lot of the new functionality where we would have done customizations in the older versions of Microsoft Dynamics NAV.

Fits

The registration of the Jobs can be done using the standard functionality of Microsoft Dynamics NAV as well as the budgeting and planning.

The standard software also allows us to invoice Jobs both fixed price and on time and materials. We can also purchase items for our Jobs.

Gaps

The Jobs module in Microsoft Dynamics NAV is often referenced as a framework that almost always needs some changes. Fortunately, it is designed to be easily changed and we will do so to support our processes.

Resource groups

Although many companies work this way; budgeting on Resource Groups is not possible. We will create a solution for that. We will also make it possible to see the total number of planned, used and invoiced hours.

Time registration

The standard software allows us to register hours but it does not have a real-time sheet application. We will create one.

Item calculation

We will create a solution calculating the system assembling. As hardware specifications are changing rapidly we do not want to create a new item for each system when we may only sell that particular configuration once or twice.

Issue registration

Our support team needs a single point for registration of all support issues for all customers and follow up their workflow. For this we will also create the functionality to register and follow up issues.

Getting started

Before we start creating any new jobs, we should have a look at the data and posting model of the Microsoft Dynamics NAV Jobs module.

How Microsoft Dynamics NAV fits a consultancy company

The starting point is the Job table, which has Job Tasks and Job Planning Lines we can use for budgeting and planning. Each job can have its own prices.

The Job Planning Lines get invoiced through the standard Microsoft Dynamics NAV Sales functionality which then creates Job Ledger Entries.

How many jobs

The first step is setting up a new job. There can be different angles on setting up jobs.This depends on how we want to work with the system. The minimum requirement is to have at least one job per Bill-to Customer. This enables us to do the invoicing.Some companies use jobs this way to use it as a pre-invoice engine.

Another angle can be to set up new jobs nicely for each project we do for the customer. In our case this starts with the basic Dynamics NAV implementation.When this is finished we close the job. If the customer has any new requirements we will need to start a new job. This way we can keep better track of what issues we have outstanding with each customer. The downside of this methodology is that it requires some work to set up a new job every time.

Most companies end up with a solution in the middle. It is common to set up a new job for larger jobs and to have a job for support issues. This also allows us to set up different invoicing strategies for each job. We will use this strategy.

Job card

Let's have a look at the Job Card and the important fields there.

How Microsoft Dynamics NAV fits a consultancy company

Let's see these fields in more detail:

  • No.: This is the unique number of a job. We can use different number series strategies for this, from simple sequential numbering to linked number series for different job types or manual numbering.
  • Description: This should be a logical description of the job for internal use. Most people will search on this field so make sure to have certain rules for naming. This will make searching for old jobs easier in the future.
  • Bill-to Customer No.: Each job has one Bill-to Customer. If we want to invoice multiple customers for one job we will need to customize the application.
  • Search Description: By default this will be populated with the value of the description field but can be changed to another value if required.
  • Person Responsible: This is an informative field indicating who is responsible for this job.
  • Blocked: If this field is checked, it is not possible to make new entries for this job. Use this for closed jobs.
  • Job Posting Group: This refers to the G/L Accounts that are used for the Work In Progress postings (WIP). There can be different G/L Accounts for different types of jobs or WIP methods.
  • WIP Method: Each job can have one Work in Progress method. We wild discuss this briefly later in this article.
  • Status: The jobs have a limited set of status fields. The only available status values are Planning, Quote, Order, and Completed.
  • Most companies want to have more sub statuses for the order phase.The best approach for this is to add a new status field that maps with the standard status field. This requires minimum changes to the application while creating new workflow possibilities.

  • Allow Schedule/Contract Lines: If this field is not checked it is not possible to create planning lines, which have both schedule and contract options. When planning lines are created they will be split into a schedule and a contract line.
  • Starting and Ending Date: These are informative fields that are only used to calculate the currency exchange rates for the job.
  • Foreign Trade: In the jobs module it is possible to send calculate and create invoices in a currency other than the local currency. This will increase the complexity of the implementation and should be used carefully.

Job task and planning lines

When the Job is created, the next step is to create Job Tasks and Planning Lines.These can be used in different ways.

Using job task lines we can cut the job into smaller pieces, which we can then schedule and invoice. The more detailed the job tasks are, the better we can measure the progress of the job, but the more work they require to maintain. Balance is the key to success here.

How Microsoft Dynamics NAV fits a consultancy company

The Job Tasks can be created with the same structure as the Chart of Accounts, meaning the actual Task Lines can be grouped using Begin and End Total lines. Each level can be indented for better readability.

The Job Planning Lines are the detail lines of each job task. This defines what we will do and how this will be invoiced. A job planning line can be linked to the master data types Resource, Item, G/L Account, or Text.

Job Tasks and Job Planning Lines can be copied very easily from other jobs. This allows us to reuse them and even create template jobs for frequently used combinations.

The line type in the job planning line defines how it will be invoiced. There are three types:

  • Schedule: The amounts on this line will only be used in for budgeting purposes. When invoicing we need to post one or more job journal lines that will be invoiced or we can create another job planning line with the invoice amount. Schedule lines should be used when billing on time and materials.
  • Contract: This line will be invoiced with the exact amounts. However the amounts do not show up in the budget. This can be used when invoicing fixed price jobs in a schedule, for example: 50% when signing the contract and 50% on job completion.
  • Both Schedule and Contract: This line will be invoiced exactly the same way as the contract lines but the amount will also show up in the budget.

Job journal

When the job tasks and job planning lines are set up we can start the job. During the job we will consume resources and items from our company. This should be registered using the Job Journal.

When creating a job journal line a few fields are particularly important for the process:

How Microsoft Dynamics NAV fits a consultancy company

  • Line Type: This has the same options as the job planning line, Schedule,Contract, and Both Schedule and Contract. When the job journal line should be invoiced, the type should be Contract. When the job journal line is part of a fixed price the line type should be left blank. When the line type is Schedule, the system will create additional Job Planning Lines of this type which may corrupt our budget for the customer asthey are already created.
  • Unit Cost and Unit Price: These fields will determine the cost of the job and the price that will be invoiced to the customer if the line type is Contract.This information is also used in the calculation of the Work in Progress.

 

Job examples

Let's go through some different job scenarios to see how we can use this functionality.

Chapter objects

The chapter objects contain both the changes we discuss in this article and as the example jobs we will use. After importing chapter 8.job, run page 123.456.700 Jobs Add-on Setup and then run Initialise Application.

How Microsoft Dynamics NAV fits a consultancy company

When this completes, restart the Role Tailored Client. You should now see the Project Manager Role Center.

1 | The new implementation

Implementing Microsoft Dynamics NAV 2009 is not an easy task and many things need to be taken care of before we can use the product. We will implement Microsoft Dynamics NAV for Packt Publishing. The job for this example is EXAMPLE1.

For the implementation we will create various job task groups. Each part of the implementation gets a code. Because the sorting is done on this field we will create codes using numbers and a logical name. For example, 0200. SETUP and 0210. FIN.

Leave enough space in the numbers to add additional lines if required. This will avoid renaming which is an expensive task for the database engine and users will have to wait until it is completed..

Our consultants will help the customer to install the system, help with the setup and convert the data from the old system. When this is done we will help them with testing and train them for using Microsoft Dynamics NAV. The consultants will be set up in the system as Resources, which are in turn entered into the job planning lines.

When everything is working as expected we can schedule a go-live weekend and help them in the first period using the system.

How Microsoft Dynamics NAV fits a consultancy company

Invoicing a job like this is done using a budget. We will make a pre calculation of the number of hours we think are necessary and start with that. During the job we need to measure the used budget and compare it with the progress.

Budgeting

The budget is created using the Job Planning Lines. During this phase of the job we do not yet know which resource will be used for the job tasks and it might even be done by more than one resource. This is why we want to use Resource Groups in our budget.

This is not possible in the standard application so we have created a modification which we will discuss at the end of this article.

How Microsoft Dynamics NAV fits a consultancy company

The Line Type of these job planning lines is Schedule. This means that these lines are just for budgeting and schedule purposes. The system will invoice the actual consumption posted in the Job Journal.

2 | The infrastructure

To use Microsoft Dynamics NAV 2009 Packt Publishing needs new infrastructure.Their current systems do not meet the requirements for Microsoft Dynamics NAV 2009.

For this job we could create new Job Task Lines in the implementation job, but for a clearer overview we will create a new job, EXAMPLE2.

Our company builds and sells its own computer systems. We can build both servers and desktop systems. Because none of the systems are exactly the same and available components switch regularly we do not want to create an item and a bill of materials for each system. Instead we use a Calculation system that allows us to determine a price for a system. For other products like switches, routers, printers, and laptops we use items which we purchase from vendors.

The Job Tasks and Job Planning Lines for this job look like this:

How Microsoft Dynamics NAV fits a consultancy company

The installation costs in this job are Resource Groups with line type Schedule,just as in the previous job, so we invoice actual hours spent on the job.

The other lines are of type Both Schedule and Contract. This means we will invoice exactly what is in the budget. The job journal lines for these tasks should be posted with a blank line type.

3 | The upgrade

Our customer requests an upgrade from Navision version 3.70 to Microsoft Dynamics NAV 2009. We can do this for a fixed price but we require a fee for analyzing the system.

For this job, EXAMPLE3, we can start with a limited number of Job Task Lines, just for the quote. When the customer agrees to do the upgrade we can add new job task lines.

Both the Quote and the Upgrade are fixed price and posted directly to the general ledger. This does not mean we cannot have our resources to register the actual hours using the job journal but the line type should be blank.

Another part of the upgrade is not done fixed price. The systems needs some redesign, a conversion to SQL Server 2008, and the customer wants additional training and support.

How Microsoft Dynamics NAV fits a consultancy company

The fixed price part of the upgrade is invoiced in three phases. When the job starts we invoice 50%, when we deliver the test system we invoice 40% and 10% is invoiced three months after go-live.

This is done using lines of Both Schedule and Contract line type.

4 | The support team

For the support team, our policy is to create one job per fiscal year per Customer.We will use this job, EXAMPLE4, for invoicing the maintenance of the license and all support issues.

The support issues can be both little questions customers call us for, such as changing a report or a page, or implementing new features that requires only a few days work.

Each issue and new feature will be created as a job task line. The new features will be created by the account manager who sells the feature. We can then decide if the invoicing is done fixed price, using contract lines, or on time and materials using schedule lines.

Our support team also needs to use the job system, but we do not want them to manually create a new job task line for each support call, and we also want them to view all outstanding issues for all customers easily. For this we have created a new issue registration system which we discuss at the end of this article.

Each issue in the system is linked to a job task. When Support Engineers create a new issue, the job task line is automatically generated for them and they can use it in our time and billing system.

 

Time sheets

For all the jobs in our examples it is critical to have a solid registration of resource hours. In the standard Microsoft Dynamics NAV job application resources need to post a job journal for each combination of Job, Job Task, and Posting Date. This is not the way most people want to register their hours; therefore we have created a Time Sheet application.

Data and transaction model

The Time Sheet application is layered above the job journal line and is created using resources and job tasks.

How Microsoft Dynamics NAV fits a consultancy company

There is an approval process for the person responsible for the job allowing them to make corrections.

How Microsoft Dynamics NAV fits a consultancy company

The time sheet is designed to be created for each week. The system automatically generates the week starting date and creates the description. After that the resource can create time sheet lines for each job task line, and populate the number of hours each day of the week.

How Microsoft Dynamics NAV fits a consultancy company

If we look at this time sheet we can see, after it is updated, that Wednesday is missing two hours.

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:

(Further on Microsoft Dynamics NAV:here.)

Purchasing

For some jobs it might be necessary to purchase items specifically for that job.Unfortunately job planning lines are not used in the requisition worksheet, so we need to create the purchase orders manually.

Expanding the requisition worksheet business logic for job planning lines is not recommended for two important reasons. First reason is that the code is very complex and cuts deep in the item reservation process. The second, more obvious reason, is that most companies that use jobs do not use the requisition worksheet as it is designed for production and trade businesses.

A better way of automatically creating the purchase orders for jobs would be to copy the drop shipment functionality from sales.

For this example we will create a manual Purchase Order looking like this:

How Microsoft Dynamics NAV fits a consultancy company

When purchasing for a job, the Job No. and Job Task No. fields should be used. If we set the Job Line Type to contract this item will be invoiced to the customer. Usually this is not required since it should have been in the Job Planning Lines already.

 

Item costing versus work in progress

After we post this purchase document and navigate from the purchase invoice we can see that the system has created two Value Entries for this item (as shown in the following screenshot):

How Microsoft Dynamics NAV fits a consultancy company

This is very important for costing. Purchased items for jobs are not calculated as inventory but used for the Work in Progress calculation.

Invoicing

When everything in our jobs is set up as required and the job journal is used to post the usage, creating the invoices is a simple task.

In the Job Manager Role Center we can see if a job is due to be invoiced. This is done using a flow filter on the Planning Date field of the Job Planning Lines.

How Microsoft Dynamics NAV fits a consultancy company

Each job planning line has a planning date. This can be used to schedule our resources but is far more useful for invoice scheduling. Each job planning line that is ready to be invoiced should get the invoice date in the planning date field.

The invoices can then be created using the batch report Job Create Sales Invoice(1093) but we can also preview the invoice by using report Job Suggested Billing(1011). This report can be started from each job.

How Microsoft Dynamics NAV fits a consultancy company

The sales invoice lines are created using the same description as the job planning lines.To clarify information for the customer we can use extra text lines to add information to the invoice.

When the invoice is created and posted, the job task is updated with the actual invoicing information.

Calculating Work in Progress

As most jobs are not completed in a day or a week it is important to be able to determine the status of each job at any given point in time. This can be measured both in quantity and financially. In quantities we can see how much of the budget is being used by looking at the job task page. For financial progress we can calculate the Work in Progress or WIP.

WIP calculates the costs we incurred and the sales we invoiced on the job and creates job WIP entries for this. This can then be posted to the general ledger if required.The WIP amounts depend on the WIP method. Microsoft Dynamics NAV has five possible WIP calculation methods:

  • Cost Value: The Cost Value is leading for WIP calculation. The WIP is the total percentage of job completion multiplied by the cost value. Job completion is calculated using ((Real Cost/Scheduled Cost) – (Really Invoiced/Scheduled Price)).
  • Sales Value: The Sales Value is leading for WIP Calculation. The WIP is the total percentage of job invoicing multiplied by the cost sales. Invoiced % is a field in the job task.
  • Cost of Sales: The total cost is calculated for the WIP calculation.
  • Percentage of Completion: The scheduled costs are multiplied by the job completion percentage.
  • Completed Contract: All completed contract entries are used for WIP.

The online help shows some good explanations of the WIP methods.

 

Example

Let's create an example and calculate the WIP for each method.

We created a job with a total price of 1000 and total cost of 500. We used four resource hours worth 500 and cost 250. We invoiced nothing.

 

Recog. Cost

Recog. Sales

WIP Cost

WIP Sales

Cost Value

125

0

125

0

Sales Value

250

250

0

250

Cost of Sales

 

 

250

 

Percentage of Completion

250

250

0

250

Completed Contract

 

 

250

 

Now we send an invoice to the customer for the hours spent. We invoice 500.

 

Recog. Cost

Recog. Sales

WIP Cost

WIP Sales

Cost Value

375

500

-125

0

Sales Value

250

250

0

-250

Cost of Sales

500

500

-250

 

Percentage of Completion

250

250

0

-250

Completed Contract

 

 

250

-500

In the last example we will use an item with cost of 250 which we cannot invoice. We now have 500 costs and 500 sales.

 

Recog. Cost Recog. Sales WIP Cost WIP Sales
Cost Value

500

500

0

0

Sales Value

250

250

0

-250

Cost of Sales

500

500

0

 

Percentage of Completion

500

500

0

0

Completed Contract

 

 

500

-500

When the WIP is positive it means that we have done more than we have invoiced, when the WIP is negative we have invoiced more than we have done.

Each company that uses Microsoft Dynamics NAV should make their own decision on what WIP method to use. WIP methods can change for each job and even change during a job.

WIP post to general ledger

Some accountants want to post the WIP amounts to the general ledger. The benefit of doing this is to have all the financial information in one place for easier reporting.

The G/L accounts for the WIP posting are set up in the job posting group.

When posting WIP to the general ledger there is always a reversal posting. When a company does monthly reporting the WIP is posted on the last day of the month and reversed on the first day of the next month.

Changing jobs

In this article we have used some changes to the job functionality in order to make it work for Cronus International Ltd. to sell Microsoft Dynamics NAV.

Quantity budgeting

For some companies it is important to know the total number of hours required for a job and the number of hours used rather than the exact amounts.

For this we have created new flow fields in the Job Task table:

How Microsoft Dynamics NAV fits a consultancy company

The flow field definition is special.

Sum("Job Planning Line"."Quantity (Base)"
WHERE (Job No. = FIELD(Job No.),
Job Task No. = FIELD(Job Task No.),
Job Task No. = FIELD(FILTER(Totaling)),
Contract Line = CONST(Yes),
Planning Date = FIELD(Planning Date Filter)))

How Microsoft Dynamics NAV fits a consultancy company

The Totaling field is for the lines of type End-Total. The ValueIsFilter property ensures that the field will be interpreted as filter instead of a value.

How Microsoft Dynamics NAV fits a consultancy company

The result is visible in the Job Task page (1002).

Resource groups

For scheduling we have implemented the possibility of using Resource Groups in the Job Planning Lines as well as Calculations. This is done by adding two new fields: Add-on Type and Add-on No..

How Microsoft Dynamics NAV fits a consultancy company

These fields replace the standard Type and No. fields on the pages allowing users to select these new options. The caption of the new fields matches the replacement fields.

Add-on No. - OnValidate()
CASE "Add-on Type" OF
"Add-on Type"::Resource, "Add-on Type"::
Item, "Add-on Type"::"G/L Account", "Add-on Type"::Text:
BEGIN
VALIDATE(Type, "Add-on Type");
VALIDATE("No.", "Add-on No.");
END;
"Add-on Type"::"Resource Group":
BEGIN
TESTFIELD("Line Type", "Line Type"::Schedule);
VALIDATE(Type, Type::Text);
VALIDATE("No.", '');
ResGroup.GET("Add-on No.");
Description := ResGroup.Name;
"Resource Group No." := ResGroup."No.";
GetJob;
ResCost.SETRANGE(Type,
ResPrice.Type::"Group(Resource)");
ResCost.SETRANGE(Code, ResGroup."No.");
IF ResCost.FINDFIRST THEN BEGIN
"Unit Cost" := ROUND(
CurrExchRate.ExchangeAmtLCYToFCY(
"Currency Date","Currency Code",
ResCost."Unit Cost","Currency Factor"),
UnitAmountRoundingPrecision);

In the C/AL code we can make sure that when users select the values available in the standard product, the normal code is executed. If a user selects a resource group we execute our own business logic.

To make sure everything works as expected we use the type Text in the background.The Line Type is mandatory set to Schedule because we do not want to invoice resource groups, we just want them to be budgeted.

The Unit Cost and Unit Price are calculated using the Resource Cost and Resource Price tables, which support the use of Resource Groups. This is an inheritance from the previous Job functionality prior to version 5.0.

The pages Job Planning List (1007) and Job Planning Line Sub form (1022) are changed to show our add-on fields instead of the normal fields.

To completely finish this functionality we would also need to change the reports that show the Job Planning Lines and the C/AL code that creates the Job Planning Lines, when posting a Job Journal Line.

Calculations

Some companies using the Job functionality have a need for flexible calculations. In our example we use it to calculate the price of a computer system but other examples are book publishers or construction companies.

They want to know what it costs to create a product without exactly knowing which screws, hinges, or color of chipboard is used.

For these companies we designed a simple but effective calculation module.In our database there are two example calculations, a server and a desktop system.

How Microsoft Dynamics NAV fits a consultancy company

The Calculation is designed using a header/line construction with a Number Series and a Line Number. The calculation lines are Items.

When a new Calculation is created some lines are automatically inserted. This is done in a C/AL function that is called from the OnInsert trigger.

The OnInsert trigger will also copy the default Unit Price for Hours from our setup table.

OnInsert()
CalcSetup.GET;
IF "No." = '' THEN BEGIN
CalcSetup.TESTFIELD("Calculation Nos.");
NoSeriesMgt.InitSeries(CalcSetup."Calculation Nos.",xRec."No.
Series",0D,"No.","No. Series");
END;
"Unit Price Hours (LCY)" := CalcSetup."Unit Price Hours";
InitLines;

The InitLines function creates a Calculation Line for each Item marked as Calculation Item. This is a new field we added to the Item table.

InitLines()
CalcLn.RESET;
i := 0;
Item.SETRANGE("Calculation Item", TRUE);
IF Item.FINDSET THEN REPEAT
i += 10000;

CalcLn."Calculation No." := "No.";

CalcLn."Line No." := i;

CalcLn.VALIDATE("Item No.", Item."No.");

CalcLn.INSERT;
UNTIL Item.NEXT = 0;

In the Calculation we can choose how many we will use from each item, and the system will not only calculate the cost and price, but also the required number of hours that is required. The Unit Cost and Unit Price are used from the Item table.Hours is calculated from a new field, Minutes, which we added to the Item table.

Calculate()
CalcLn.RESET;
CalcLn.SETRANGE("Calculation No.","No.");
CalcLn.CALCSUMS("Unit Cost", "Unit Price", Profit, Hours);
CalcLn.FIND('-');
CalcLn.MODIFYALL(Changed,Calculated::Calculated);
CalcLn.CALCSUMS("Unit Cost", "Unit Price", Hours);
"Unit Cost" := CalcLn."Unit Cost";
"Unit Price" := CalcLn."Unit Price";
Profit := "Unit Price" - "Unit Cost";
Hours := CalcLn.Hours;
Correct;
"Total Price Hours (LCY)" := "Hours (After Correction)" * "
Unit Price Hours (LCY)";
"Total Price" := "Total Price Hours (LCY)" +
"Unit Price (After Correction)";
Calculated := Calculated::Calculated;
MODIFY;
Correct()
"Unit Price (After Correction)" := "Unit Price" +
("Unit Price" * ("Correction % Items" / 100));
"Profit (After Correction)" :=
"Unit Price (After Correction)" - "Unit Cost";
"Hours (After Correction)" :=
Hours + (Hours * ("Correction % Hours" / 100));

When we now use the Calculate function, the system will generate a total Unit Cost, Unit Price, and Hours for this product to be created. Flexibility is added to the system by allowing users to correct hours and usage with a percentage.

The Calculation can be used in a Job Planning Line the same way as the Resource Groups earlier, the only difference is that we use the G/L Account type on the background to invoice a calculation fixed price. Let's look at the C/AL code in the OnValidate trigger of the Add-On No. field in the Job Planning Line:

Add-on No. - OnValidate()
CASE "Add-on Type" OF
"Add-on Type"::Resource ... "Add-on Type"::Text:
...
"Add-on Type"::"Resource Group":
...
"Add-on Type"::Calculation:
BEGIN
Calc.GET("Add-on No.");
IF Calc."Turnover Account No." = '' THEN BEGIN
TESTFIELD("Line Type", "Line Type"::Schedule);
VALIDATE(Type, Type::Text);
VALIDATE("No.", '');
END ELSE BEGIN
TESTFIELD("Line Type",
"Line Type"::"Both Schedule and Contract");
VALIDATE(Type, Type::"G/L Account");
VALIDATE("No.", Calc."Turnover Account No.");
END;
Description := Calc.Description;
GetJob;

To complete this functionality we would create a method to use the hours in the calculation for the Resource planning. This can be done using job planning lines of line type Schedule with no unit cost and unit price.

Issue registration

For our support team we have implemented an issue registration solution. This allows them to have a simple application where they can register issues for all customers, and keep track of their status without going in and out of each Job.

How Microsoft Dynamics NAV fits a consultancy company

The Issue registration is a header/line construction with a number series and a line number. The lines can be used to phrase questions and answers.

When a support engineer creates a new issue, the system will create the Job Task automatically. Let's have a look at the C/AL code that does that:

CreateJobTask()
TESTFIELD("Job No.");
TESTFIELD("Job Task No.", '');
OldJobTask.SETRANGE("Job No.", "Job No.");
OldJobTask.SETRANGE("Job Task Type",
OldJobTask."Job Task Type"::Posting);
IF OldJobTask.ISEMPTY THEN
OldJobTask.SETRANGE("Job Task Type",
OldJobTask."Job Task Type"::"Begin-Total");
OldJobTask.FINDLAST;
JobTask."Job No." := "Job No.";
JobTask."Job Task No." := INCSTR(OldJobTask."Job Task No.");
JobTask.Description := Description;
JobTask."Job Task Type" := JobTask."Job Task Type"::Posting;
JobTask.INSERT(TRUE);
CODEUNIT.RUN(CODEUNIT::"Job Task-Indent Direct", JobTask);
"Job Task No." := JobTask."Job Task No.";

The system searches for the last Job Task of the type Posting in the Job. If that cannot be found, it searches for the last Begin-Total line.

Assuming this line exists we create a new Job Task line using the INCSTR function to increment the number. The description is copied to the job task.

The support engineers can now register their hours on this job lask.

This piece of C/AL code is very simple but shows how effective a small solution

can be without even touching any of the standard Microsoft Dynamics NAV objects.This is a very safe method of development.

 

Time sheet

We already discussed the functionality of the Time Sheet application in this article.Let's have a look at how it was created.

The Time Sheet application is also a header/line construction with a number series and a line number. It is designed to be used weekly and therefore automatically creates the description based on the date. It calculates the week number and the year.

Week Start Date - OnValidate()
IF DATE2DWY("Week Start Date", 1) <> 1 THEN
FIELDERROR("Week Start Date");
Description := Text000 + FORMAT("Week Start Date", 0, '<Week>,
<Year4>');

The user can change the Description if required.

How Microsoft Dynamics NAV fits a consultancy company

To support a workflow the Time Sheet table has a Status field. During the work week a resource can keep track of the things he did and change everything. When the week is finished they can change the status to To Check. Then the person responsible for the Job can check whether everything is valid, and change the status to Approved. The Time Sheet can then be Registered, this is done using a codeunit we will discuss later.

The Mo., Tu., We., Th., Fr., Sa., and Su. fields are Flowfields used in the Fact Box.

How Microsoft Dynamics NAV fits a consultancy company

The Time Sheet Line allows users to create a line for each Job Task we worked on during the week. Each day of the week has its own column for easy registration.After validating each day the total is calculated using a function.

CalcTotal()
TESTFIELD("Job Task No.");
Total :=
"Mo." + "Tu." + "We." + "Th." + "Fr." + "Sa." + "Su.";

The Total field is used to calculate the total hours per week in a flow field.

Registration

The Time Sheet application has a registration codeunit. As the Time Sheet

application is a document covering the Job Journal, this checks the status of the time sheet, and then creates and posts a Job Journal Line for each day if required.

 

OnRun()
TESTFIELD(Status, Status::Approved);
TimeSheet := Rec;
TimeSheetLn.SETRANGE("Time Sheet No.", "No.");
IF TimeSheetLn.FINDSET(TRUE) THEN REPEAT
Job.GET(TimeSheetLn."Job No.");
IF TimeSheetLn."Mo." <> 0 THEN
CreateJnlLn("Week Start Date", TimeSheetLn."Mo.");
IF TimeSheetLn."Tu." <> 0 THEN
CreateJnlLn("Week Start Date" + 1, TimeSheetLn."Tu.");
IF TimeSheetLn."We." <> 0 THEN
CreateJnlLn("Week Start Date" + 2, TimeSheetLn."We.");
IF TimeSheetLn."Th." <> 0 THEN
CreateJnlLn("Week Start Date" + 3, TimeSheetLn."Th.");
IF TimeSheetLn."Fr." <> 0 THEN
CreateJnlLn("Week Start Date" + 4, TimeSheetLn."Fr.");
IF TimeSheetLn."Sa." <> 0 THEN
CreateJnlLn("Week Start Date" + 5, TimeSheetLn."Sa.");
IF TimeSheetLn."Su." <> 0 THEN
CreateJnlLn("Week Start Date" + 6, TimeSheetLn."Su.");
UNTIL TimeSheetLn.NEXT = 0;
Status := Status::Registered;
MODIFY

At the end of the routine we change the Status to Registered.

CreateJnlLn()
JobJnlLn.INIT;
JobJnlLn."Job No." := TimeSheetLn."Job No.";
JobJnlLn."Job Task No." := TimeSheetLn."Job Task No.";
JobJnlLn.VALIDATE("Line Type", JobJnlLn."Line Type"::Contract);
JobJnlLn."Posting Date" := PostingDate;
JobJnlLn."Document Date" := PostingDate;
JobJnlLn."Document No." := TimeSheetLn."Time Sheet No.";
JobJnlLn.Type := JobJnlLn.Type::Resource;
JobJnlLn.VALIDATE("No.", TimeSheet."Resource No.");
JobJnlLn."Work Type Code" := TimeSheetLn."Work Type";
JobJnlLn.Description := TimeSheetLn.Description;
JobJnlLn.VALIDATE(Quantity, Qty);
JobJnlPostLine.RunWithCheck(JobJnlLn,TempJnlLineDim);

The Job Journal Line records are of type Contract to allow invoicing.

Summary

In this article we learned how to implement the Job functionality of Microsoft Dynamics NAV. We discussed different strategies of setting up Jobs and Job Tasks.

We created several examples with different invoicing methods using the Job Planning Lines in a creative way.

The Job Journal should be used to register usage on Jobs, but we discussed how to enhance this by creating a simple but effective Time Sheet application.

When Purchasing Items for Jobs the Items are not used for Costing, but in the Work in Progress calculation we discussed in detail.

Invoicing is done automatically when everything happens as it should.

Lastly, we designed some small enhancements for the Job module without making big changes in the standard application.

We discussed all possibilities of the application, and how they should be changed without risking breaking anything.


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


Joomla! 1.5: Beginner's Guide
Joomla! 1.5: Beginner's Guide

Refactoring with Microsoft Visual Studio 2010
Refactoring with Microsoft Visual Studio 2010

Microsoft Silverlight 4 Data and Services Cookbook
Microsoft Silverlight 4 Data and Services Cookbook

Microsoft Silverlight 4 Business Application Development: Beginner’s Guide
Microsoft Silverlight 4 Business Application Development: Beginner’s Guide

Programming Microsoft Dynamics NAV 2009
Programming Microsoft Dynamics NAV 2009

Microsoft Dynamics AX 2009 Programming: Getting Started
Microsoft Dynamics AX 2009 Programming: Getting Started

Oracle JRockit: The Definitive Guide
Oracle JRockit: The Definitive Guide

Microsoft Dynamics AX 2009 Development Cookbook
Microsoft Dynamics AX 2009 Development Cookbook


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
a
d
7
b
q
p
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