Adeptris

Contracting the Choices

david.cox@adeptris.com — Mon, 04 Jan 2010 12:50:00 GMT

2009 was the first year from thirteen that I experienced a couple of periods with no Dynamics NAV work, a mixture of the recession and the market being saturated with new contractors the likely causes.

Towards the end of the year there were a number of enquiries so things may pick up in 2010, as I have not blogged for a while I thought I would post a few reasons why companies could look at contractors for new and ongoing projects.

More...

UK Dynamics NAV Freelance Developer

david.cox@adeptris.com — Fri, 03 Jul 2009 12:10:28 GMT

My current Position as a Dynamics NAV Contract Developer with a Reseller in Surrey finished at the end of May 2009, after running for 6 Months.
The last two contracts covered 33 months included 27 months in the City of London, this contract started out as a two month contract, for a city based medium sized Microsoft Gold Reseller of Dynamics, my role was as an office based Dynamics NAV developer/consultant, the reseller also had other consultants that carried out some development, with the customer base being quite diverse.

More...

Employing a Dynamics NAV Contractor

david.cox@adeptris.com — Thu, 02 Jul 2009 20:37:59 GMT

Working as a Dynamics NAV development freelance contractor in the UK, the contracts will almost always come through third party agencies, I have a good working relationship with a few of these, the agencies work on a commission basis for both placing permanent employees and contractors.

With an employee the companies will know what commission the agency is charging as it is based on the employees salary this is usually around 15% of the annual salary, however with a contractor the company will get charged a fixed day rate and the agency will deal with paying the contractor or the contractors umbrella company.

More...

Prepayment and Deferred Revenue Report

david.cox@adeptris.com — Tue, 30 Jun 2009 13:19:51 GMT

To follow on from the blog posting: Dealing with Prepayments and Deferred Revenue

The head of accounts would like a function for creating the recurring journals for the prepayments and deferred revenue, there is a download for the report in the downloads section (Version 5), I would urge you to read on to understand how this report is designed and how it works, this report is designed as a learning aid for developers.

More...

Digital Artists new Logistics Model

david.cox@adeptris.com — Tue, 09 Jun 2009 11:32:28 GMT

Digital Artists

Digital artists upload their assets to many sites, spending lots of hours preparing and uploading, often different sites requiring different formats, attributes and keyword data, then digital artists receive different amounts of revenue per download per agency, artist with sales are paying for all the free space given away by agents to other digital artists, I think it is time for a new logistics model, one where the artist keeps controls their assets, the goal is to look at a better model for artists.

Upload just the once to their own online storage space
Have easy access to one tool that sets all the required data attributes
Set their own fair prices with price bands and levels
Be able to submit a thumbnail and media data once to a single search engine

Agencies and Merchants

Digital agencies, merchants and direct buyers currently have heavy overheads when acquiring digital media content, the costs of reviewing contributor applications and media submissions, marketing to attracting the right artists to the website, what we need is a model that will act as a pipeline to connect agents to the artists, giving the agents more time for marketing to customers, the artists could submit image data to a search engine where the agent or merchant can filter for the right content, and include this on an item by item basis to thier library.

Central search engine via an API for all media types by price band, level, licence type and attributes
Filter options by any required attributes, keywords and detail
Cost price would be set per media by artists and agents would mark-up from base price
Data storage costs would be cut to a minimum
Updates sent when suitable content comes online based on agents and merchants saved searches

Concept

To create initial discussions about a model that will provide the tools to connect the digital artists, who are the asset vendors to new and existing customers, either via direct website sales or through the approved agents and merchants, and using default data templates with a search engine where the vendor will retain full control of thier assets until the point of delivery.

What the industry does not need is another large new agency, but a new logistics model one that will not hold main assets at anytime, and will not at any point in the transaction have control over the artist's or customers revenue.

Current Model

To monetize thier digital assets many artists will upload to many different agencies or merchant sites, the existing model does not help the digital artist control and manage thier assets, often the submission rules vary from agency to agency and often the format of the asset and or asset data has to be changed to satisfy the different guidelines.

The artist will spend many hours uploading to agents websites, just to have some of thier digital assets declined and often as not being what the agency needs at this time, when the Asset is accepted and a sale takes place the artist often has to wait until sales of thier assets reach a preset revenue amount before they receive any payment, a large number of digital artists do not reach the payment threshold and will never be paid the royalties due on thier assets.

Uploading is free now?

This is the statement that is often used to defend the large percentages some existing agencies charge, it is much a false statement and one that artists need to think about more, as more and more digital assets become available the revenue is diluted more and more, where the agencies and merchants are also under pressure to compete in the market and will reduce prices and squeeze the artists even more.

The artists that do have sales are now paying for all the free accounts and storage of assets that may never sell, and as overhead costs grow and the traditional revenue falls the digital artists will be squeezed further.

How could a new model work?

A new model could greatly increase the royalty revenue return the artist gets, one where the digital artist is only required to upload their asset only once to a web space that the digital artist controls, this model would cut down on time spent preparing and uploading, and gives the control of the main asset back to the digital artist including setting the base price for each asset, and then once the artist has set the attribute data for the asset they could just submit the asset details to the search engine, the asset then stays under the control of the Artist only small samples and data is submitted to the search engine.

We now have a search engine index full of just asset data that the agencies would be able to search by specific terms, there currently such a huge cost to both the digital artist, agencies and merchants in uploading and reviewing digital media assets that are then not required because the agent has to many similar assets, so instead of the agencies looking for different artists and artists looking for new Agencies, the search engine would enable agents to access all the data they have done a specific search for, from many different artists, and be able to select on an item by item basis which ones to include in their library.

Who should pay what?

A new model could work on micropayments for transactions and spread the costs between the artist, agencies and merchants.

This would mean the cost is spread with the artist paying for their online storage with one of the main companies like Amazon or Microsoft, and this then gives the search engine access to the samples and data packets, this also acts for the digital artist as an online backup service for their assets, the artist would pay to submit the image samples and data and these charges would only be micropayments, larger samples would only be generated when there is a request for the data packet from an agency.

The agency would pay for their own transactions and again these would be micropayments, the agency will retrieve thumbnails when they carry out a search, they would be able to request a sample or composite for any digital asset already in their collection, when a sale happens and a full size image is requested the search engine will only carry out requests when it has received a confirmation notice or payment notification

The artist would be paid 100% of the set price by the agent at the time of delivery, the agent will retain the marked up percentage or amount they have added to the digital artists price.

Environmental Considerations

Another consideration is our carbon footprint, our digital assets when stored on many agents websites servers has no consideration for data-storage efficiency, this new model would reduce the data duplication by using the single-instance storage, and reducing storage needs by eliminating redundant assets and many servers. as only one unique instance of the image is actually retained on storage media until the point of sale, and the full digital asset uploads are replaced with a sample or thumbnail and a file pointer to the original digital asset. as an example, a digital photographer with 100 image instances of 4mb and contributing these to 6 stock imaging sites, at present this is requiring 400MB storage space per server, With data duplication this becomes 6 x 400mb, 2.4GB which will use fossil fuel to store these on the servers, with only one instance of the images being stored online and each subsequent instance just referenced back to the single saved copy, the example of 2.4GB storage demand can be reduced to only 400MB.

Get Involved

I have created a Group website to discuss this model and a logistics website, after input from artists I would like to turn it into reality, the websites can be found at:

Discussion Group: http://digitalmediaartists.collectivex.com/

Digital Logistics: www.digitalweblogistics.com

-------------------------------------------------------------

Keeping active between Contracts

david.cox@adeptris.com — Tue, 09 Jun 2009 07:35:38 GMT

While I am between Contracts I have had the time to tidy up my websites, the first being this one, I took the old one down and started again, all my websites are run on DotNetNuke this is an open source modular website management system, the back end is the Microsoft 2005 SQL database, there are a lot of modules included and many commercial ones that can be added on.

The Logo is new and was designed and purchased from www.inspiredlogos.com at a cost of just under £100, this is quite a good service after paying and giving the website details I received a call to discuss the requirement, they sent 6 designs and I chose one, they were also were good at keeping me informed as to progress, the result is at the top of this page.

www.photographerstripod.com photography is my hobby and this is just a hobby website, it is a Photographic Community website, where photographers can upload images, join groups, network, add events, articles and downloads, the downloads are a set of tool I have created in Visual Studio and Adobe Flash for Image Management, when I am learning or keeping up with a development package, I will look to create a functional application, that is the way I prefer to learn ‘hands on’, I package create a tutorial and make the applications free to download just like the tutorials and downloads on the downloads page here

www.digitalweblogistics.com this website is just a concept idea I am floating to see if it is viable, as photography is my hobby I started to upload to stock imaging website that licence your images and pay you a commission, this is very big business for the stock sites but hard work for the Photographers, each site has a set of requirements and as a Photographer you spend many hours preparing and uploading the same image to many sites, often just to have the image rejected on some websites and accepted on others, which is time wasting and frustrating for both the stock websites and Photographers

The concept of www.digitalweblogistics.com is to standardise the preparation and uploading of an image to one instance, then via a search engine allow the imaging stock website to search for images they would like to include in their library, rather than having Photographers uploading many images that they do not require for their library, the photographers would pay for their own image storage submit a thumbnail and the image data including keywords and pricing band to the search engine, the buyers would pay to access the search engine, these would be micropayments and transactions would be between Photographer and Customer, Agent or Merchant

Dynamics NAV Accruals dealing with Prepayments and Deferred Revenue

david.cox@adeptris.com — Thu, 04 Jun 2009 07:32:23 GMT

One of the most common search terms that brings people to my website is “Accruals Prepayments and Deferred Revenue”, and the search engine brings up many complicated answers, so I will attempt to bring an explanation in plain English for pre-payments and deferred revenue

A simple one line summary, they are just goods and services that have been paid for but not yet supplied

Lets look at a simple example that we all know, once a year we get the invoice for our car or household insurance, this will cover a period of 12 months, and we often pay this a few weeks before it starts, lets say our premium is 487.67 starts on the 15th January 2010 and we want to account for this, so we post the whole invoice value to the Insurance General Ledger Account on the 15th January 2010

First thing we do is to look at the the whole value a short term asset, as we have not had the services and if we cancel we will ask for a refund, the year is 12 months, and we have 11 full months and two part months so we divide the value by 12, giving us 11 at 40.64 and one at 40.63

In Dynamics NAV we could deal with this simply using a Recurring Journal with eight lines:

The first two lines deal with moving the whole value to a pre-payments account lets say that the premium is due on the 15th January 2010 and the invoice has been posted on this date, the first two line would take the whole 487.67 from the Insurance account and move it to the pre-payments account, the recurring type is variable, frequency ‘1M’ using the same document number and the Expiration Date would be the 16th January 2010 so it only runs once

The next two lines deal with the 17 days of January and would take 22.28 from the 40.63 (40.63 / 31 * 17) and post this from the pre-payments account to the Insurance account, the recurring type is variable, frequency ‘1M’ using the same date, document number and the Expiration Date would be the 16th January 2010 so it only runs once

The next two lines deal with the 11 Months of February to December and would take 40.64 and post this from the pre-payments account to the Insurance account, the recurring type is variable, starting date 1st February 2010, frequency ‘1M’ using the same document number and the Expiration Date would be the 2nd December 2010 so it only runs 11 times

The next two lines deal with the 14 days of January 2011 and would take the remaining 18.35 and post this from the pre-payments account to the Insurance account, the recurring type is variable, starting date 1st January 2011, frequency ‘1M’ using the same document number and the Expiration Date would be the 2nd January 2011 so it only runs 1 time

As for deferred revenue like support contract values received by your company for services not yet given, what we do here is to look at the the whole value as a short term liability, as we have not provided the services and if the customer were to cancel they would ask for a refund, we would follow the same process but using a deferred revenue account to hold the liability until realized

At any point in the year we can set a date filter and look at pre-payment and deferred revenue values as assets or liabilities

Dynamics NAV Contracting

david.cox@adeptris.com — Tue, 02 Jun 2009 10:44:48 GMT

I find myself out of contract after a run of 33 months, having always found it easy to get another position this is not yet to much of a worry, having spoken to several agents it looks like there is not much going on at the moment, some have suggested a permanent role could be an option, but this is not one I would consider as I prefer working as a contractor, this is a personal choice, but many think the rewards of a contractor to be a lot higher than a permanent employee

An employer can expect about 220 working days from a possible 260 days from an employee, and a Contractor can expect 170 chargeable days from the 220 so lets look at the rates for a contractor:

http://www.itjobswatch.co.uk/contracts/uk/dynamics%20nav.do

The current rate in GBP from ITJobsWatch is reported at 275 GBP a fall from 400 GBP for the same period last year, most companies will want to work through and Agency, this add 50 GPB – 100 GBP a day onto the contractors rate, so lets say currently 325 GPB a day for the 170 Days, and if a contractor worked the 170 days in a year on a contact that is 55,250 GBP, this to anyone working as a permanent worker seem high but you need to look at the full cost of employment

Currently the rates for a Dynamics NAV Employee average at 42K GBP http://www.itjobswatch.co.uk/jobs/uk/dynamics%20nav.do

Looking at http://www.techscribe.co.uk/ta/cost-of-employment.htm there is a spreadsheet on this website which is based on a 42K Salary, where the true cost is calculated at 400 GBP per day for an employee so there is not much of a bottom line cost difference

Working as a contractor:

You do not get paid for Holiday, Sickness, Paternity Leave, Expenses etc:, there are no additional benefits, car allowance, training, pension contributions, the employers can terminate the contract by an agreed notice period without reason, so there are no claims of unfair treatment

The employers get a skilled resource that can hit the ground running, which they can drop at any time if the project changes or work runs dry, which is harder if employing just employees, in all my contracts I have passed on skills and added to the team in a positive way and have not had any negative feedback about how employing me as a contractor has changed the dynamic of the existing team.

If you are an employer thinking about using a contractor for an existing or planned project, feel free to contact me from the About page

David

Dynamics NAV - G/L Accounts - Dynamic Fiscal Year with Thirteen Periods

david.cox@adeptris.com — Tue, 02 Jun 2009 07:20:02 GMT

This Tutorial is available to download from the downloads page, there are two versions one for the GB localised version of Dynamics NAV and the other for the worldwide version, in the download you will find the NAV objects and the tutorial in PDF format

Scenario

We have a requirement in from the Finance Director at Cronus International Ltd, they have acquired two companies as part of their growth strategy, and they will be moving the accounts information across to their MBS Dynamics NAV® database and the two new companies and all the transactions, will then go through these companies.

As these companies use different Fiscal year and accounting period start dates, to Cronus International Ltd, our implementation consultant has set these up ready for them in the Accounting Periods table.

The Finance Director is familiar with the Accounts Schedules but wants a report made from the G/L Accounts table with these options:

Grouping totals by General Business Posting Group
Must be able to deal with the new Accounting Periods
Whole system year and the twelve periods printed onto a landscape view
Accounting year with up to thirteen periods printed onto a landscape view
Rounding Factors of None, 1, 1000 and 1000000
Budget values only
Amount values only
Amount and Budget all
Amount and Budget only where the budget has a value

Analysis

Grouping totals by General Business Posting Group, no real problems here the wizard will do this for us.
Must be able to deal with the new Accounting Periods, I have seen some code to deal with this on the Customer Statistics card form, I will look there.
Whole system year and the twelve periods printed onto a landscape view, This we can do from the data table
Whole fiscal year and the thirteen periods printed onto a landscape view, I think I can squeeze in the 16 columns, just a lot of work copying and pasting.
Rounding Factors of None, 1, 1000 and 1000000, the Accounts Schedules can do this I will look there for the solution.
Budget values only, option box on the report.
Amount values only, option box on the report.
Amount and Budget, option box report
Amount and Budget only where the budget has a value, flag on the report

So we need to look at three things, which fields to use from the G/L Accounts table, how to get and set the date filters and how to do the output rounding.

There are several flowfields on the G/L Accounts table lets look at some of these and how they work and choose which to use in out report.

We have three Amount fields and two Budget fields in the image, there are others but we will look at these, as this flowfield structure is repeated throughout the system, in the Inventory, Jobs and Resources modules, so this could be useful to you later on.

Balance: this value is affected by the applied filters except the date filter, so if I put in a date filter of 01/01/2006..01/31/2006, this field would give the sum total value of all the transactions for the G/L Account.

Balance at date: this value is affected by the applied filters and the maximum Date Filter value, so if I put in a Date Filter of 01/01/2006..01/31/2006, this field would give the sum total value of all transactions up to the 01/31/2006.

Net Change: this value is affected by the applied filters and the maximum Date Filter value, so if I put in a Date Filter of 01/01/2006..01/31/2006, this field would give the sum total value of the selected transactions from the 01/01/2006 up to and including the 01/31/2006.

So we need to use the Net Change and Budgeted Amount fields for our report, we will look at this in the next section.

How to get and set the accounting periods, I said earlier that there are standard functions to do this so let’s take a look at one of the pieces the code we need to use:

CurrentDate := WORKDATE;

DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],CustDateName[1],CurrentDate,0); DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],CustDateName[2]

,CurrentDate,0);

We have 4 variables

CurrentDate, a date variable to hold the base date.
CustDateFilter an array of text fields
CustDateName an array of text fields
DateFilterCalc Codeunit DateFilterCalc

Set the current date to the workdate

CurrentDate := WORKDATE;

Get the FiscalYear for the current date

DateFilterCalc.CreateFiscalYearFilter(CustDateFilter[2],

CustDateName[2],CurrentDate,0);

Get the accounting period for the current date

DateFilterCalc.CreateAccountingPeriodFilter(CustDateFilter[1],

CustDateName[1],CurrentDate,0);

This is enough for us to work with the accounting Periods

Now for the Standard Calendar we need a different structure

We have 2 more variables

PeriodFormMgt codeunit PeriodFormManagement
CalendarSource

Setting which calendar type to use, 0=Day, 1=Week, 2=Period, 3=Quarter, 4=Year and 5=Accounting Period

Get the date record for the Calendar Year

CalendarSource=4

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

Get the date record for the Calendar Month

CalendarSource=2

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

So we can use these date structures for our report, as the company changes accounting periods or the structure, our report will still work, as Cronus International Ltd, want both views we will just drop on a Use Accounting Period flag on our report.
All we have to look at now is the rounding, so let’s look at the code in the Account Schedule Management and the function FormatCellAsText

FormatCellAsText(VAR ColumnLayout2 : Record "Column Layout";Value : Decimal) : Text[30]

IF NormalFormatString = '' THEN BEGIN

IF NOT GLSetupRead THEN BEGIN

GLSetup.GET;

GLSetupRead := TRUE;

END;

NormalFormatString := Text014 + GLSetup."Amount Decimal Places" + Text015;

END;

WITH ColumnLayout2 DO

IF Value <> 0 THEN

CASE "Rounding Factor" OF

"Rounding Factor"::None :

ValueAsText := FORMAT(Value,0,NormalFormatString);

"Rounding Factor"::"1" :

ValueAsText := FORMAT(ROUND(Value,1));

"Rounding Factor"::"1000" :

ValueAsText := FORMAT(ROUND(Value/1000,0.1),0,Text016);

"Rounding Factor"::"1000000" :

ValueAsText := FORMAT(ROUND(Value/1000000,0.1),0,Text016);

END;

EXIT(ValueAsText);

If we look at what happens here, a value gets passed in as a decimal, then based on which rounding factor is selected, passed back as a formatted string, we can see that most of what we need is in this function, we could copy this function and 3 text constants over to our report later, and just modify the code to suit our own requirement, but we will write our own function in this tutorial.

So we now know what we need to do, where we can find examples of how to do it, we can start by just using the Wizard to give us the fields and the structure of the report.

As this tutorial is aimed at all levels of developers, I am going to go through the Wizard steps one by step for anyone who is not familiar with using the Wizard.

Step 1 New report dialog choose the G/L account Table from the lookup list

Step 2 we only need four fields on our new dynamic report these include the two flowfields we looked at earlier, and we will be using in our report Net Change and Budgeted Amount

Step 3 Choose which key we want to run the report on

We want to group our report on the General Business Posting Group so we can total

We want totals for both our financial fields

Accept the default values

Save the report, we will save the report at each stage, this is a good habit to get into as there is nothing worse that loosing hours of work.

At this point we are not interested in the sections, we need to add some variables and code first, so to add our variables, view C/AL Globals and add the following

We will get to look at the variables later, but for now we need to add the array and Option values

Arrays:

DateFilter = 15

DateName = 15

ChangeAmtTxt = 15

BudgetAmtTxt = 15

ChangeAmt = 15

BudgetAmt = 15

To put in the array value, select the variable DateFilter then open the properties sheet for this variable Shift+F4, repeat this for the other 5 variables

Option Strings

RoundingFactor = None,1,1000,1000000

WhichValue = Amount,Budget,Both

To put in the option string value, select the variable RoundingFactor then open the properties sheet for this variable Shift+F4

Save your Report

Now we will add two functions, for this one you could copy and change the code, from Account Schedule Management but we will hand code it

Press F9, Select to the Functions tab

Type into the name field FormatCellAsText, now press the button Locals
Select the Parameters tab then add the Name = Value and DataType: Decimal.
Now select the variables tab and add Name = ValueAsText and DataType = Text with the Size = 30
Close the dialog box, and select TextConstants and add the following 3 lines
Text014
Text015 >
Text016
close the dialog box and save the report

Now we can add our Code

Press F9 to bring up the code window write the following

FormatCellAsText(Value : Decimal) : Text[30] Section

IF NormalFormatString = '' THEN BEGIN

IF NOT GLSetupread THEN BEGIN

GLSetup.GET;

GLSetupread := TRUE;

END;

NormalFormatString := Text014 + GLSetup."Amount Decimal Places" + Text015;

END;

IF Value <> 0 THEN

CASE RoundingFactor OF

RoundingFactor::None :

ValueAsText := FORMAT(Value,0,NormalFormatString);

RoundingFactor::"1" :

ValueAsText := FORMAT(ROUND(Value,1));

RoundingFactor::"1000" :

ValueAsText := FORMAT(ROUND(Value/1000,0.1),0,Text016);

RoundingFactor::"1000000" :

ValueAsText := FORMAT(ROUND(Value/1000000,0.1),0,Text016);

END;

EXIT(ValueAsText);

Save the Report

So what have we got here?

FormatCellAsText(Value : Decimal) : Text[30] Section

Note: ValueAsText is a local variable and is empty each time

If the NormalFormatString string is empty I.E first call to this function then fill it

IF NormalFormatString = '' THEN BEGIN

If we haven’t already got the G/L setup record then get it

IF NOT GLSetupread THEN BEGIN

GLSetup.GET;

GLSetupread := TRUE;

END;

In the UK the resulting string will be

NormalFormatString := Text014 + GLSetup."Amount Decimal Places"

+ Text015;

END;

If we have a financial value passed in, format it to a string and return the value

Text016 will Round to 1 decimal place E.G = 1200 with factor 1000 will return 1.2

IF Value <> 0 THEN

CASE RoundingFactor OF

RoundingFactor::None :

ValueAsText := FORMAT(Value,0,NormalFormatString);

RoundingFactor::"1" :

ValueAsText := FORMAT(ROUND(Value,1));

RoundingFactor::"1000" :

ValueAsText := FORMAT(ROUND(Value/1000,0.1),0,Text016);

RoundingFactor::"1000000" :

ValueAsText := FORMAT(ROUND(Value/1000000,0.1),0,Text016);

END;

Return the formatted string

EXIT(ValueAsText);

Press F9, Select to the Functions tab

Type into the name field BuildDateFilter, now press the button Locals
Select the Parameters tab then add the Name = Calendar, DataType: = Record and SubType = Date.
Select the tab return value then enter return type = text and length = 50
close the dialog box and save the report

Now we can add our Code

Press F9 to bring up the code window write the following

BuildDateFilter(Calendar : Record Date) : Text[50]

WITH Calendar DO BEGIN

IF "Period End" = 0D THEN

"Period End" := 31129999D;

"Period End" := NORMALDATE("Period End");

EXIT(FORMAT("Period Start") + '..' + FORMAT("Period End"));

END;

What does the function do, when you pass in the Calendar record?

BuildDateFilter(Calendar : Record Date) : Text[50]

WITH Calendar DO BEGIN

If there is no end date set the date to 12/31/9999

IF "Period End" = 0D THEN

"Period End" := 31129999D;

Set the Period end date from a closed date to a normal date

"Period End" := NORMALDATE("Period End");

Create a filter string IE: 01/01/2006..01/31/2006 and return the value

EXIT(FORMAT("Period Start") + '..' + FORMAT("Period End"));

END; ends the with statement

Now we will add the code, to the report, select a blank data item line and press F9 to view the C/AL code

Now add the following code

IF "G/L Account".GETFILTER("Date Filter") <> '' THEN

CurrDate:= "G/L Account".GETRANGEMAX("Date Filter")

ELSE

CurrDate := WORKDATE;

GLFilters := "G/L Account".GETFILTERS;

IF UseAccPeriods THEN BEGIN

DateFilterCalc.CreateFiscalYearFilter(DateFilter[14],DateName[14],CurrDate,0);

EVALUATE(Calendar."Period Start",COPYSTR(DateFilter[14],1,8));

EVALUATE(CurrDate,COPYSTR(DateFilter[14],11));

CalendarSource := 5;

END ELSE BEGIN

Calendar."Period Start" := CurrDate;

CalendarSource := 4;

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

DateFilter[14] := BuildDateFilter(Calendar);

DateName[14] := Calendar."Period Name";

CurrDate := NORMALDATE(Calendar."Period End");

CalendarSource := 2;

END;

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

DateName[1] := Calendar."Period Name";

DateFilter[1] := BuildDateFilter(Calendar);

MaxColumnNo := 1;

FOR i := 2 TO 13 DO BEGIN

IF PeriodFormMgt.NextDate(1,Calendar,CalendarSource) <> 0 THEN

IF NORMALDATE(Calendar."Period End") <= CurrDate THEN BEGIN

MaxColumnNo := MaxColumnNo + 1;

DateName[i] := Calendar."Period Name";

DateFilter[i] := BuildDateFilter(Calendar);

END;

What are we doing here?

Make sure we have a date to work with if a filter was input use that else use workdate

IF "G/L Account".GETFILTER("Date Filter") <> '' THEN

CurrDate:= "G/L Account".GETRANGEMAX("Date Filter")

ELSE

CurrDate := WORKDATE;

Copy all the filters the user may have entered

GLFilters := "G/L Account".GETFILTERS;

If the user elected to use accounting periods then we action this code

IF UseAccPeriods THEN BEGIN

Use the function to return our last column filter and name

DateFilterCalc.CreateFiscalYearFilter(DateFilter[14],DateName[14],CurrDate,0);

We now have a date filter to cover the fiscal year IE 01/01/06..12/31/06

Now we chop the date filter and cast the values into 2 date variables

Period Start 1 to 8 = 01/01/06 and CurrDate from string position 11 = 12/31/06

EVALUATE(Calendar."Period Start",COPYSTR(DateFilter[14],1,8));

EVALUATE(CurrDate,COPYSTR(DateFilter[14],11));

Set the calendar Source to Accounting Period

CalendarSource := 5;

END ELSE BEGIN

This code is run for the system year

Set the Period start with the CurrDate

Calendar."Period Start" := CurrDate;

Set the Calendar Source to Year

CalendarSource := 4;

Find the relevant year

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

Use our function to make our Date filter

DateFilter[14] := BuildDateFilter(Calendar);

Return the period name IE 2006

DateName[14] := Calendar."Period Name";

Set this to the last day of the year so we can use it later

CurrDate := NORMALDATE(Calendar."Period End");

Set our source to Month

CalendarSource := 2;

END;

Now we have a calendar source of Month or Accounting period based on the Use Accounting Periods flag, find the first period within the Year

PeriodFormMgt.FindDate('',Calendar,CalendarSource);

DateName[1] := Calendar."Period Name";

DateFilter[1] := BuildDateFilter(Calendar);

Now we loop and fill in our Date filter and date name for the other periods

MaxColumnNo := 1;

FOR i := 2 TO 13 DO BEGIN

If a record is returned then action the code

IF PeriodFormMgt.NextDate(1,Calendar,CalendarSource) <> 0 THEN

Check to see if the period end date is in range

IF NORMALDATE(Calendar."Period End") <= CurrDate THEN BEGIN

MaxColumnNo := MaxColumnNo + 1;

DateName[i] := Calendar."Period Name";

DateFilter[i] := BuildDateFilter(Calendar);

END;

Next we will add our code to the “G/L Account” in dataitem view select the line and press F9 to view the C/al code

Now add this code

G/L Account - OnPreDataItem() Section

LastFieldNo := FIELDNO("Gen. Bus. Posting Group");

SETRANGE("Date Filter");

CurrReport.CREATETOTALS(ChangeAmt,BudgetAmt);

G/L Account - OnAfterGetRecord() Section

CLEAR(ChangeAmt);

CLEAR(BudgetAmt);

HasValue := FALSE;

FOR i := 1 TO MaxColumnNo DO BEGIN

SETRANGE("Date Filter");

SETFILTER("Date Filter",DateFilter[i]);

CALCFIELDS("Net Change","Budgeted Amount");

ChangeAmt[i]:= "Net Change";

BudgetAmt[i]:= "Budgeted Amount";

IF NOT HasValue AND NOT ShowOnlyIfBudget THEN BEGIN

IF WhichValue IN[WhichValue::Amount,WhichValue::Both] THEN

HasValue:= ChangeAmt[i] <> 0;

END;

IF NOT HasValue THEN BEGIN

IF WhichValue IN[WhichValue::Budget,WhichValue::Both] THEN

HasValue:= BudgetAmt[i] <> 0;

END;

IF NOT HasValue THEN

CurrReport.SKIP;

SETRANGE("Date Filter");

SETFILTER("Date Filter",DateFilter[14]);

CALCFIELDS("Net Change","Budgeted Amount");

ChangeAmt[14]:= "Net Change";

BudgetAmt[14]:= "Budgeted Amount";

FOR i := 1 TO 14 DO BEGIN

IF ChangeAmt[i] <> 0 THEN

ChangeAmtTxt[i] := FormatCellAsText(ChangeAmt[i]);

IF BudgetAmt[i] <> 0 THEN

BudgetAmtTxt[i] := FormatCellAsText(BudgetAmt[i]);

END;

What’s it all doing?

G/L Account - OnPreDataItem() Section

Navision’s wizard wrote this line

LastFieldNo := FIELDNO("Gen. Bus. Posting Group");

Clear the date filter

SETRANGE("Date Filter");

Tell Navision we want group totals for our financial fields

CurrReport.CREATETOTALS(ChangeAmt,BudgetAmt);

G/L Account - OnAfterGetRecord() Section

Clear our variables

CLEAR(ChangeAmt);

CLEAR(BudgetAmt);

HasValue := FALSE;

Fill our Financial Array with the filtered values

FOR i := 1 TO MaxColumnNo DO BEGIN

SETRANGE("Date Filter");

SETFILTER("Date Filter",DateFilter[i]);

CALCFIELDS("Net Change","Budgeted Amount");

ChangeAmt[i]:= "Net Change";

BudgetAmt[i]:= "Budgeted Amount";

Now we want to set our flag based on our selections of Amount or Both

IF NOT HasValue AND NOT ShowOnlyIfBudget THEN BEGIN

IF WhichValue IN[WhichValue::Amount,WhichValue::Both] THEN

HasValue:= ChangeAmt[i] <> 0;

END;

Now we want to set our flag based on our selections of Budget or Both

IF NOT HasValue THEN BEGIN

IF WhichValue IN[WhichValue::Budget,WhichValue::Both] THEN

HasValue:= BudgetAmt[i] <> 0;

END;

If our array variable is empty move on to the next record

IF NOT HasValue THEN

CurrReport.SKIP;

Add our fiscal year values

SETRANGE("Date Filter");

SETFILTER("Date Filter",DateFilter[14]);

CALCFIELDS("Net Change","Budgeted Amount");

ChangeAmt[14]:= "Net Change";

BudgetAmt[14]:= "Budgeted Amount";

Convert our Values to Text

FOR i := 1 TO 14 DO BEGIN

IF ChangeAmt[i] <> 0 THEN

ChangeAmtTxt[i] := FormatCellAsText(ChangeAmt[i]);

IF BudgetAmt[i] <> 0 THEN

BudgetAmtTxt[i] := FormatCellAsText(BudgetAmt[i]);

END;

So now we can look at our wizard sections.

Delete and change the section width to 262 and delete the following sections

G/L Account.GroupHeader (3)

G/L Account.Groupheader (5)

G/L Account.GroupFooter(7)

Now we will set some properties for our text boxes

G/L Account, Body (4) Field “Net Change” SourceExpr = ChangeAmtTxt[1]

G/L Account, Body (4) Field “Net Change” Width = 1350

G/L Account, Body (4) Field “Budgeted Amount” SourceExpr = BudgetAmtTxt[1]

G/L Account, Body (4) Field “Budgeted Amount” Width = 1350

G/L Account, GroupFooter (5) Field “Net Change” SourceExpr = ChangeAmtTxt[1]

G/L Account, GroupFooter (5) Field “Net Change” Width = 1350

G/L Account, GroupFooter (5) Field “Net Change” FontBold = Yes

G/L Account, GroupFooter (5) Field “Budgeted Amount” SourceExpr = BudgetAmtTxt[1]

G/L Account, GroupFooter (5) Field “Budgeted Amount” Width = 1350

G/L Account, GroupFooter (5) Field “Budgeted Amount” FontBold = Yes

Next we need to add a field for the Dynamic Headings

View tool box and drop a text box into the G/L Account header 2 Section

Delete the old labels Net Change and Budgeted amount

Properties

SourceExpr = DateName[1]

Width = 1350

FontBold = Yes

VertAlign = Bottom

Now for the boring bit we have to copy each field and paste it back in 13 times, then go to properties and change the index value to match the Column

As in this example DateName[3] being the third column index

So we now have 14 column headings, save the report

Select the G/L Account, Body (5) section bar press F3 and you should get the a dialog box like this, make sure that the

The Options selected should be Body and After Current Section, press ok and repeat these steps, so we have 3 body sections on our report.

Now do the same to our first GroupFooter section adding a new GroupFooter, after current Section, we should now have 3 footer sections

Now do the same to our first Header section adding a new Header, after current Section, we should now have 2 Header sections

Select the body (5) section and BudgetAmtTxt[1] field, and drop it in the 1^st new Body (6) section, now do the same with the GroupFooter BudgetAmtTxt[1] field, move it into the second group footer (9) section.

Now for the boring bit we have to copy each field and paste it back in 13 times, then go to properties and change the index value to match the Columns, in all for sections.

Save after changing each row, if you click off the sections, you may loose work

So at the end you will have:

First Body (5) Section fields = ChangeAmtTxt[1] to ChangeAmtTxt[14]

Second Body (6) Section fields = BudgetAmtTxt[1] to BudgetAmtTxt[14]

First Group Footer (8) Section fields = ChangeAmtTxt[1] to ChangeAmtTxt[14]

Second Group Footer (9) Section fields = BudgetAmtTxt[1] to BudgetAmtTxt[14]

Rows 3 of the Body and 3 of the Group Footer need to be filled in now expand the row and select copy and and paste in the rows from above, so section body section 3 has a row for Amount and a row for budget.

To do this select body (5) then Ctrl+A this selects all the objects in that section, Ctrl+C to copy, select Body (7) and Ctrl+V to paste then in, do the same with the budget section, and place then in section 7 below the amount fields

Your report sections should now look like this, if not adjust them to match, we will be changing the labels next

Now add some text boxes to the header 2 section

As you can see the SourceExpr value for all the boxes are visible, and I will let you add them from viewing the image above, you do not put in the =< or >, so the first box you just type in ‘For the Fiscal year: ‘+DateFilter[14], save the report

Now we have to give our Customer some options View and Request form

Add the controls as in the image and set the sourceexpr as in the image.

The first check box SourceExp value is set to our flag ShowOnlyIfBudget.

The second check box SourceExp value is set to our flag UseAccPeriods.

Save the report nearly there just a bit more coding for you to do, this is to switch our sections on and off, look at the images for each section and add the code.

Start with Header (2) select the section and view code F9, show only on Page 1

Body 5 only show if which value Amount was selected on the request form

Body 6 only show if which value Budget was selected on the request form

Body 7 only show if which value Both was selected on the request form

Group Footer 8, shows if Amount was selected, in the group footer we need to load the group values into the ChangeAmtTxt variables, only if they have a value

Group Footer 9, shows only if the budget was selected, in the group footer we need to load the group values into the BudgetAmtTxt variables, only if they have a value

Group Footer 10, shows only if the both was selected, in the group footer we need to load the group values into the ChangeAmtTxt and the BudgetAmtTxt variables, only if they have a value

All you have to do now is run the report.

DOCUMENT COPYRIGHT

The authors that contributed to this Adeptris.com tutorial are delighted to share their work with others.
Please recognize the hard work and integrity of our contributing authors by giving them appropriate credit and citing references on any material that you share with others.
Permission is granted to reproduce this material in whole or in part for educational purposes only (not-for-profit beyond cost of reproduction) provided that the authors receive acknowledgement and this notice is included:

Any additions or changes to these materials must be pre-approved by the author.
NOTE: On most Adeptris.com documents Author Access information is placed at the bottom of the document

AFD postcode® integration for MBS Dynamics NAV®

david.cox@adeptris.com — Tue, 02 Jun 2009 06:36:46 GMT