Walkthrough: Creating Advanced Sample Reports

Microsoft Dynamics Nav 2009

This walkthrough includes examples of reports that are more complicated than those described in earlier walkthroughs. These examples are not intended to be complete or ready to run, but are meant to give you examples that might be useful when designing your own reports.

Prerequisites

To complete this walkthrough, you will need:

• Microsoft Dynamics NAV installed with a developer license.
  
• The CRONUS International Ltd. demo data company installed.
  

Using Virtual Tables as the Data Model

C/SIDE includes a number of virtual tables, such as the Integer table and the Date table. The Date table consists of five fields: Period Type, Period Start and Period End, Period No. and Period Name. Period Type can be Date, Week, Month, and so on. Period Start is the starting date of each period and Period End is the last date in the period. Period End dates are closing dates.
In this example, the Date table is used to create a report that prints information from the Cust. Ledger Entry table. For each day in a range of dates that can be chosen by the user when they want to run the report, the report summarizes the entries made on that date.
The report lists the different types of documents that were entered, the number of documents of each type, and the total number of documents entered on each date. The report also displays the total amount entered on each date. Finally, the total number of entries and the total amount entered for the selected date range is printed at the bottom of the report.
You could create the report by grouping the entries according to the Cust. Ledger Entry table alone, but the field that contains the posting date in that table is not part of any key. Creating a special key just for this report is not advisable, because it would slow down all the other transactions that involve this table. Every sales entry would be affected.
This data model contains two data items—one based on the Date table and one based on the Cust. Ledger Entry table.

To use virtual tables as the data model

1. Open Report Designer and create a new blank report with two data items with the Date and the Cust. Ledger Entry tables as the underlying tables.
   
2. Indent the Cust. Ledger Entry data item.
   

   Note
   Indenting the Cust. Ledger Entry data item ensures that the system only searches through the Cust. Ledger Entry table when a date is found in the Date table, that lies within the range specified by the user

3. Select the Date data item and open the Properties window.
   
4. In the Value field of the DataItemTableView property, click the AssistButton to open the Table View window.
   
5. In the Table Filter field, click the AssistButton to open the Table Filter window.
   
6. In the Table Filter window, set a filter that selects records with the Period Type of Date.
   

   Note
   This is an important step, as the iteration of the Date data item would otherwise run through all the records, including those for Weeks, Quarters, Months, and Years.

7. Click OK in the Table Filter window. Click OK in the Table View window.
   
8. In the Properties window of the Date data item, in the Value field of the ReqFilterFields property, enter Period Start. This lets the user enter a range of dates when they fill out the request form before running the report. This date range is then used to filter the information displayed in the report.
   
9. Open the Properties window of the Cust. Ledger Entry data item. In the Value field of the DataItemTableView property, click the AssistButton to open the Table View window.
   
10. In the Key field, use the AssistButton to open the Key List window.
    

    Note
    In this example, an appropriate key is a key that contains the Document Type field, as the definition of a group in this report is based on these fields. Here, a key is selected that has this field as its first component, the other fields that are included in the key are not important. No individual entries will be printed, so they do not need to be sorted in any specific way.

11. Be sure that the DataItemLinkReference property points to the Date data item, this is the default value.
    
12. In the DataItemLink property, you must specify the field that establishes the link between the two data items. In the Value field of the DataItemLink property, click the AssistButton to open the DataItem Link window.
    
13. In the Field field, use the AssistButton to select the Posting Date field from the Cust. Ledger Entry data item. In the Reference Field field, select the Period Start field from the Date data item.
    
14. In the Value field of the GroupTotalFields property, use the AssistButton to select the Document Type field.
    

    Note
    This groups the entries from the Cust. Ledger Entry table according to their document type: Payment, Invoice, Credit Memo, Finance Charge Memo, Reminder, or Refund.

15. In the Value field of the TotalFields property, use the AssistButton to select the Amount field. This adds up the entries in the Amount field for each document type for the date in question.
    

Designing the Sections

So far, this report allows you select a range of dates from the request form of the report. The report runs through the Date table, with a constant filter on the Period Type field that selects only records whose type is Date. If you select a date range, only dates within this range are selected; otherwise all the dates are used. For each date that you select, the records in the Cust. Ledger Entry data item that are posted on that date are selected. The records for the Cust. Ledger Entry data item are grouped according to the value of the Document Type field, and the totals are maintained for the Amount field.

To design the sections

1. Click View, and then click Sections to open Section Designer.
   
2. Click Edit, click New, and then insert a Header section for the Date data item. This header will be used to contain the labels for the columns of data in the report.
   
3. Insert two GroupFooter sections for the Cust. Ledger Entry data item. You create two GroupFooter sections for the Cust. Ledger Entry data item so that you can control their output separately. Both sections contain summarized information about the groups created by this data item.
   
4. Insert a Footer section for the Cust. Ledger Entry data item. This section is used to display the subtotals.
   
5. Insert a Footer section for the Date data item. This section is used to display the grand totals at the end of the report.
   
6. Neither data item requires a Body section so you can delete them.
   

Adding Fields to the Report

The next step is to add the fields that you want to include in the various sections of the report.

To add fields to the report

1. In Section Designer, select the Cust. Ledger Entry, GroupFooter (2) section, click View, and then click Field Menu to open the Field Menu window.
   
2. In the Field Menu window, select the Posting Date, Document Type, and Amount fields.
   
3. In Section Designer, click twice in the Cust. Ledger Entry, GroupFooter (2) section to add these fields.
   
4. Move the labels into the Header section and align the fields.
   
5. In Section Designer, select the Cust. Ledger Entry, GroupFooter (1) section, and then add the Document Type and Amount fields.
   
6. Delete the labels for the fields that you have just added to the Cust. Ledger Entry, GroupFooter (1) section. These extra labels serve no purpose.
   
7. Align the fields.
   

This report is not complete, you still need to add the fields that:

• Contain the number of documents of each type that were posted on each date.
  
• Calculate and display the total number of documents posted on each date.
  
• Calculate and display the grand total number of documents posted in the period covered by the report.
  
• Calculate and display the total amount posted on each date.
  
• Calculate and display the grand total of the amount posted in the period that the report covers.
  
• Display the date range that the user used to filter the information in the report.
  

To accomplish this, you need to write C/AL code in the triggers, define variables, and add the appropriate fields.

Creating a Global Variable with Code

The records in the Cust. Ledger Entry data item contain an amount and the total amount is calculated by using the properties of the data item in the report. You accomplished this in step 12 in the Using Virtual Tables as Data Model section, when you set the TotalFields property of the Cust. Ledger Entry data item to calculate a total based on the Amount field in the Cust. Ledger Entry table. This total is calculated separately for each date and is the basis for the grand total that you will calculate later. Unfortunately, the number of entries cannot be calculated in the same way because there is no field in the data item that contains this information. However, each record in the Cust. Ledger Entry table corresponds to exactly one entry, which means that the number of entries can be calculated by simply counting the records. To calculate the total number of entries made on each date.

To create a global variable with code

1. Select the Cust. Ledger Entry data item in Report Designer, click View, and then click C/AL Code to open the C/AL Editor.
   
2. Create a global variable called Qty and define its type as Decimal.
   
3. Add the following C/AL code to these triggers in the Cust. Ledger Entry data item. To the Cust. Ledger Entry - OnPreDataItem() trigger, add the following line of code. CurrReport.CREATETOTALS(QTY);
   
4. To the Cust. Ledger Entry - OnAfterGetRecord() trigger, add the following line of code. Qty :- 1;
   

The statement in the OnPreDataItem trigger maintains totals for the Qty variable in the same way that the TotalFields property specifies that totals are maintained for a field in a record. The statement in the OnAfterGetRecord trigger assigns a value of one to the Qty variable each time a record is retrieved.
You will use the CREATETOTALS function to maintain totals for each group and a grand total for the iteration of the data item loop. As data items are grouped according to the Document Type field, Qty contains the sum of all the entries of the same document type each time the Cust. Ledger Entry GroupFooter section is printed. When the Footer section is printed, Qty contains the sum of all the entries that were selected, that is, all the records that were entered on the same date.
The variable in the argument of the CREATETOTALS function must be of the Decimal data type because the function is usually used to add up amounts. This is why the Qty variable was declared as a Decimal rather than an Integer.

Adding Fields to the Report

You must now add some fields to the report that will display the totals calculated by the code in the previous section.

To add fields to the report

1. Open Section Designer and open the Toolbox.
   
2. Add a text box to the Cust. Ledger Entry, GroupFooter (1) section and open the Properties window of the text box.
   
3. In the Value field of the SourceExpr property, click the AssistButton to open the C/AL Symbol Menu window.
   
4. Select the Qty variable that you just defined.
   
5. Add a label to the Header section, open the Properties window of the label, and in the Caption property, enter Qty. Be sure that the HorzAlign property is set to Right.
   

The Qty variable that you have just defined is a decimal. You must therefore change the formatting of the Qty text boxes in the sections so that they do not show any decimal places. The format of the Qty text boxes are defined as <2:2> by default when the SourceExpr is of the Decimal data type.

To change the formatting of the Qty text box

1. Select the text box in Section Designer and open the Properties window.
   
2. In the Value field of the DecimalPlaces property change the value to 0:0. The field will no longer display any decimals.
   

The next step is to add the field that will contain the total number of documents of each type that are entered on each date that is printed in the report.

To add the Qty field to the report

1. Copy the Qty text box that you just added to the Cust. Ledger Entry, GroupFooter (1) section.
   
2. Paste the text box into the Cust. Ledger Entry, GroupFooter (2) section. This text box will now display the total number of documents of each type entered on each date.
   

The report now contains fields that list the entries in the Cust. Ledger Entry table according to the date when they were entered and shows how many documents of each type were entered on that date.

Calculating the Total Number of Entries and Total Amount per Date

The next step in designing this report is to ensure that it displays some subtotals that show how many entries were made on each date, as well as the total amount entered on each date. This is why you created the Cust. Ledger Entry, Footer (3) section.

To calculate the subtotals

1. Copy the Qty and Amount fields from the Cust. Ledger Entry, GroupFooter (2) section into the Cust. Ledger Entry, Footer (3) section.
   

   Note
   These fields will now display the total amount entered per date and the total number of documents entered on the date in question.

2. Add a label to the Cust. Ledger Entry, Footer (3) section, open the Properties window of the label, and then in the Value field of the Caption property enter Total.
   

Print the Date in the First Iteration

In each iteration of the Date data item loop, you want to print the Date when the information selected from the Cust. Ledger Entry data item is entered. You could just use the body section of the Date data item and print the date there; the Period Start field of the data item, but this would mean that the date would print on a line by itself. Instead, the date should be printed along with the other information on the first line that comes from the Cust. Ledger Entry data item.
One solution is to use the Date field from the Cust. Ledger Entry data item. Unfortunately, if it is added to the GroupFooter section, the date will be printed on every line. This means that the finished report will not be very attractive and will be cluttered with redundant information.
A better solution is to define two GroupFooter sections for the Cust. Ledger Entry data item. One that includes the Date field and one that does not, and then control when they are output. This is why you created two GroupFooter sections earlier.

To add a date and control

1. Create a global variable called IsDatePrinted and define its data type as Boolean.
   
2. Add the following C/AL code to the OnPreDataItem trigger of the Cust. Ledger Entry data item: IsDatePrinted := FALSE;
   

   Note
   This code initializes the IsDatePrinted variable, with the value FALSE, before each iteration of the data item loop.

3. Add the following C/AL code to the OnPreSection trigger of the Cust. Ledger Entry, GroupFooter (1) section of the Cust. Ledger Entry data item.
   
   IF IsDatePrinted 
   THEN CurrReport.SHOWOUTPUT(TRUE) 
   ELSE CurrReport.SHOWOUTPUT(FALSE);
   

4. Add the following C/AL code to the OnPreSection trigger of the Cust. Ledger Entry, GroupFooter (2) section.
   
   IF IsDatePrinted 
   THEN CurrReport.SHOWOUTPUT(FALSE) 
   ELSE BEGIN CurrReport.SHOWOUTPUT(TRUE); 
   IsDatePrinted := TRUE; 
   END
   

These two pieces of code ensure that:

• When a new iteration of the Cust. Ledger Entry data item begins, a date is not printed.
  
• If the loop generates any output, only the second GroupFooter section containing the Date text box is included as output in the first iteration.
  
• If additional output is generated, only the first GroupFooter section without the Date text box is printed.
  

Calculating the Grand Total Amount and the Grand Total Quantity

To complete this report you must ensure that it displays the total number of documents posted as well as the grand total of the amount posted in the period covered by the report. It should display this information at the end of the report in the Date Footer (2) section.
The next task is to calculate the grand total of all the amounts that are printed in the report. However, these amounts come from the Cust. Ledger Entry data item and not the Date data item. This means that you cannot use the TotalFields property of the Date data item to do the totaling.

To calculate grand totals

1. Declare two global variables: GrandTotalAmount and GrandTotalQty of data type Decimal and Integer respectively.
   
2. Add the following lines of code to the OnAfterGetRecord trigger of the Cust. Ledger Entry data item.
   
   GrandTotalQty := GrandTotalQty + 1; 
   GrandTotalAmount := GrandTotalAmount + Amount;
   

   Note
   The first line adds one to the GrandTotalQty variable when a record is retrieved, while the second line adds the retrieved Amount to the GrandTotalAmount.

3. Open Section Designer and add two text boxes to the Date, Footer (2) section.
   
4. The first text box will be used to calculate the total number of documents in the report. Place it under the other Qty fields and open the Properties window of the text box.
   
5. In the Value field of the SourceExpr property, click the AssistButton to open the C/AL Symbol Menu window.
   
6. Select the GrandTotalQty variable that you just defined.
   
7. The second text box will be used to calculate the grand total of the amounts displayed in the report. Place it under the other Amount fields and open the Properties window of the text box.
   
8. In the Value field of the SourceExpr property, click the AssistButton to open the C/AL Symbol Menu window.
   
9. Select the GrandTotalAmount variable that you just defined. This text box will now display the grand total of all the amounts in the report.
   
10. Add a label to the Date, Footer (2) section and place it under the Total field in the Cust. Ledger Entry, Footer (3) section.
    
11. Change the caption of this label to Total. The Date, Footer (2) section now contains the grand totals for the report.
    

Printing the Selected Range of Dates

The posting dates are used as a header in the left margin of the report, so the report would be complete if the final line could display the range of dates that the user selected before they ran the report.

To print the selected range of dates

1. Create a variable called DateFilter and define its data type as Text, with a length of 100.
   
2. Add the following C/AL code to the OnPreReport trigger of the report.
   
   DateFilter := Date.GETFILTER("Period Start");
   

3. Add a text box to the footer section of the Date data item, open the Properties window of the text box and enter DateFilter as its source expression.
   

   Note
   When the OnPreReport trigger is executed, the RequestForm has already been run. The GETFILTER function returns any filters on the field, which are passed as an argument, as a text string.

See Also

Tasks

Walkthrough: Designing a Simple Report
Walkthrough: Designing a More Advanced Report

Concepts

Designing Reports for the Classic Client

NAVISION - How To Display Time in Words.

Hi all,

This is nothing most of you would be interested in. This post only show steps about how to Display Time in words in Navision.

If you don't know this, then please go ahead.

The intended readers for this post are the Peoples who have recently joined and are still Learning NAV. Based on a Query from a Friend Via Contact Form.


This post list down the steps and objects that i have used to complete the requirement. I have done it for NAV 2009 Classic Client and As well as on NAV 2013.

Let's Start and see how we can achieve it.

Concept -
---------------------------
1. First thing that came to my mind that Navision Report 1401 Check contain a function which is able to convert Amount in Words. - I will be Referring That.

2. Second Thing that comes to my mind is date actually contain Decimal values separated with a date separator commonly ':'.
---------------------------

What i need to Do?
---------------------------
1. Separate the Date in three parts - Hours, Minutes & Seconds.

2. Create a copy of Standard Function in Report 1401 - FormatNoText, to convert Date in Words.

OR

2. Copying Related Function From Report 1401 Check to My Form & Page.

In Demo i will be Copying Function to My Form & Page but i would suggest to make a copy of the function in 1401 instead of make the form or page clumsy.
---------------------------

**I am not doing it in Report itself as the Report 1401 contain localization which may be be different in your case.

Step 1 - (Breaking Dates in Parts) -

- Created a Form / Page with a input field which will accept Time.

- Create a Button / Action, on click on which the date will be Break into Three parts.

- On Push Action
   1. Check the Input Value.
   2. Convert Time in Text.
   3. Break Time in Hours Minutes and Seconds.
   4. Convert Time from 12 Hours to 24 Hours.

CODE
----------------------------------------------------
IF InputTime = 0T THEN
  ERROR('Input Time Value');

TimeInText := FORMAT(InputTime);                  

EVALUATE(TimeParts[1],COPYSTR(TimeInText,1,2));      
EVALUATE(TimeParts[2],COPYSTR(TimeInText,4,2));      
EVALUATE(TimeParts[3],COPYSTR(TimeInText,7,2));      

IF COPYSTR(TimeInText,10,2) = 'PM' THEN
  TimeParts[1] += 12;
----------------------------------------------------
Where -
InputTime - Time.
TimeInText - Text (12).
TimeParts - Decimal (Array Dimension 3).

Step 2 - Copying the Related Function, Variables & Text Constants from 1401 to my form/Page.

I will copy following functions -

1. FormatNoText (Renamed the Function to - FormatNoTime)
2. AddToNoText
3. InitTextVariable

Copying Following Global Variables -
OnesText
TensText
ExponentText

Copying Text Constants -

1. Text026 TO Text028
2. Text032 TO Text058

Below are the Changes that i have done in the Standard Function Copied From Report 1401- 

**Only Changed Code Shown in Below Images

Function FormatNoText (Standard) VS FormatNoTime (Customized)

Removed Code in Customized Function where Currency Related Constants are added like Rupee for INR.

Other than Highlighted Part other Code Remain Same.

Function InitTextVariable -

Removed Code in Customized Function. Other than Highlighted Part other Code Remain Same.

4. Now i will append code in Push action of same button as shown below -

CODE
----------------------------------------------------
CLEAR(TimeinWords);
InitTextVariable();                    
FormatNoTime(Output,TimeParts[1],'');  
TimeinWords += Output[1] + ' HOURS ';
FormatNoTime(Output,TimeParts[2],'');
TimeinWords += Output[1] + ' MINUTES ';
FormatNoTime(Output,TimeParts[3],'');
TimeinWords += Output[1] + ' SECONDS ';
MESSAGE(TimeinWords);
----------------------------------------------------
Where - 
TimeinWords - Text (90)
Output - Text (80) - (Array Dimension 2).

What this code does?

 > Clear the Text Variable.
 > Initialize the Text Variables.
 > Do the Conversion for Each Part and Append that to a Text Variable.

Let's Try Executing the Form & Page. Below the screens running the Form & Page in 2009 & 2013.

Input Window -

Output Window -

Objects can be Downloaded in Text & FOB From My Skydrive.
File Name - Convert Time in Words.zip

I hope you find the post useful and let me know the feedback of such basic navision post on the Contact form or as a comment to post.

Stay Tuned for More.

Regards,
Saurav Dhyani
saurav-nav.blogspot.com

Navision - How to Format Dates for Cheque Reports?

Hi all,

This is nothing most of you would be interested in. This post only show steps about how to Format Cheque Date specific to Actual Cheque.

If you don't know this, then please go ahead.

The intended readers for this post are the Peoples who have recently joined and are still Learning NAV. Based on Request received via Contact Form..


So in Cheque Report we have date like 22/10/2014 i.e DD/MM/YYYY which may not what Customer is looking for in actual cheque. Customer Normally look for format as per country regulation. In India current Cheque Date format is as shown below -

How we can change that in Navision Report 1401 ?

Design the Report 1401 you will see something like shown below -

Go to the Code of Second Data Item i.e GenJnlLine and on Trigger onAfterGetRecord you need to search for CheckDateText.

This is the text constant that is used to display Date in Cheque Reports.

So how can i change that to my desired format?

I will create three global variables as listed below -

CheckDateText1 - Data Type (Text) - Length (30)
ChrB                    - Data Type (Text) - Length (1)
Chr1                     - Data Type (Text) - Length (1)  & Dimension (8)

Now i will change code as below -

Standard -
-------------------------------------------------------------------------
  IF "Cheque Date" <> 0D THEN
    CheckDateText := FORMAT("Cheque Date",0,4);
ELSE
    CheckDateText := FORMAT("Posting Date",0,4);
-------------------------------------------------------------------------
Modified -
-------------------------------------------------------------------------
  IF "Cheque Date" <> 0D THEN
    CheckDateText := FORMAT("Cheque Date",0,'<Day,2><Month,2><Year4>')
  ELSE
    CheckDateText := FORMAT("Posting Date",0,'<Day,2><Month,2><Year4>')

  Chr1[1] := FORMAT(CheckDateText[1]);
  Chr1[2] := FORMAT(CheckDateText[2]);
  Chr1[3] := FORMAT(CheckDateText[3]);
  Chr1[4] := FORMAT(CheckDateText[4]);
  Chr1[5] := FORMAT(CheckDateText[5]);
  Chr1[6] := FORMAT(CheckDateText[6]);
  Chr1[7] := FORMAT(CheckDateText[7]);
  Chr1[8] := FORMAT(CheckDateText[8]);
  ChrB := ' ';

 CheckDateText1 := Chr1[1] + ChrB + Chr1[2] + ChrB + Chr1[3] +  ChrB + Chr1[4] + ChrB + Chr1[5] + ChrB + Chr1[6] +  ChrB+ Chr1[7] + ChrB + Chr1[8];
-------------------------------------------------------------------------

I will replace the source expression of Cheque Date From CheckDateText to CheckDateText1.

What Exactly the Code DO?

1. The Code First Convert the Date as Format (DDMMYYYY).

2. Put Each part of the Text i.e Date into a text constant of Length 1 (8 Dimension for 8 Values).

3. ChrB is used to put Spaces in the Middle of Date Parts. (NEED TO BE CHANGED AS PER CHEQUE)

The No. of occurrence of ChrB changes as per cheque that you want to print.

I hope you find the post useful and let me know the feedback of such basic navision post on the Contact form or as a comment to post.

Stay tuned for More.

Regards,
Saurav Dhyani
saurav-nav.blogspot.com

NAV 2009 Reports - Common Issue & Resolutions.. (Part 2)

Hi all,

As promised in the blog entry i will list down some common known things that we need to take care while estimating & developing RTC Reports in 2009 Versions.

* These are my faced issues and their resolutions, you may not agree with all these. If that will be case i would suggest you to share same with others.

Lets begin with RTC Report Header -
During development you might have seen that in standard reports there are basically two ways to display report Headers.

1) Using Page Header.
2) Using Header in Body.

When to use which?


Use Page Header when -
1) No conditional Data is a part of Header. (workaround exist from keeping the field as blank from classic).
2) Header is Required on all pages. ( there is not option to print page header in First Page Only).
3) Page x of y is required. (can't set this property in Body as total pages is a global variable).

Use Header in Body when-

1) conditional Data is a part of Header.
2) Header is Required on First  page only.
* IN Table Header there is a property to repeat on new page.

If you have a picture in the Page Header remember these things -

- You don't need to enlarge / size it to the size you want because the property is set to auto size.
- If you try to have a image box with the height that you want in report you will find a space between page header and body. (TESTED)
- If you have the above listed issue then reduce the height of picture box and see the difference.

Let's see RTC Report Footer -


During development you might have seen that in standard reports there are basically two ways to display report Headers.

1) Using Page Footer.
2) Using Footer in Body as Table Footer.

Use Page Footer when -
1) No conditional Data is a part of Footer. (workaround exist from keeping the field as blank from classic).
2) Footer is Required on all pages.
3) Footer Contain Less Data.

Use Footer in Body when-

1) conditional Data is a part of Footer.
2) Header is Required on Last  page only.

A common condition that occur Mostly "FOOTER ON BOTTOM OF LAST PAGE ONLY". 

This is a real business requirement. Suppose you have a sales invoice report which ends data in 2 pages but the last row of Sales Invoice Line comes in the second page 2'nd line.After that line your footer begins.

Will that invoice look likes a professional Invoice? 

No, then how can we achieve that? Have a look on the Report 1401, Cheque Report. As we remember the standard check also print the check information at the bottom of the last page.

If you see the footer is a part of Page Footer inside a rectangle (as we cannot use list / Table in Page Header and Footer) with a hidden condition =iif(CBool(ReportItems!IsLastRec.Value),False,True)

Where IsLastRec is a field in the Body of the Report and its value is set to 

=iif(CInt(CountRows()) = CInt(RowNumber("Table1_Group1")),True,False)

* I was unable to understand the condition so what i did is -

- Create a Boolean variable in classic with include in dataset to TRUE.

- Set the Boolean to True when my last record was printing. IF NEXT = 0 Then "X" = TRUE.

- Set the Boolean as visibility of the Rectangle in Report Footer.

* Remember by using the above listed method if the report is printing in more than 1 page, there will be a blank space in each page until last page. That is the space for the Page footer.

Dynamics NAV withholding tax double entry

Withholding Tax

May 24, 2014

The payer is required to:

• Withhold tax at 15% of the gross income payable to the non-resident professional; OR
• the non-resident rate of 20% if the non-resident professional elects to be taxed on net income;

Source of withholding Tax from the IRAS website: http://www.iras.gov.sg/irashome/page01.aspx?id=758

Assuming the bill from the non-resident professional is $10,000, the amount payable to the non-resident professional will be $8,500 and the withholding tax amount will be at $1,500.

In the Payment transaction window, the amount paid to the non-resident professional will be $8,500; check the W/O (Write Off) checkbox and click the OK button to complete the payment (The balance $1,500 will be written off).

The double entry for this transaction will be:

Debit Accounts Payable account $8,500

Credit Cash at Bank account $8,500

On the following screen, MoneyWorks will prompt you to select a relevant “write off” account, in this case you select the Withholding Tax Payable account (Current Liability account type).

MoneyWorks will automatically create a new Purchase Invoice (debit balance) transaction for the $1,500 write off. The double entry will be:

Debit Accounts Payable account $1,500

Credit Withholding Tax Payable account $1,500

On the 15th of the following month, when you need to make payment to the IRAS for the $1,500 withholding tax due, you create a Payment transaction with Withholding Tax Payable account associated. This will:

Debit Withholding Tax Payable account $1,500

Credit Cash at Bank account $1,500

posting PV to gl

IF NOT CONFIRM ('Are you sure you want to post this payment?') THEN
 EXIT;

IF Status <> Status::Released THEN
 ERROR('This document need to be approved');

DefaultCtl.GET();

//>Start create pv journal batch
IF NOT GenBatch.GET('PAYMENTS','PAYMENTS') THEN
BEGIN
GenBatch.INIT;
GenBatch."Journal Template Name":='PAYMENTS';
GenBatch.Name:='PAYMENTS';
GenBatch.INSERT;
END;
//<Stop

//>Start clear journal line
GenjLine.RESET;
GenjLine.SETRANGE(GenjLine."Journal Template Name",'PAYMENTS');
GenjLine.SETRANGE(GenjLine."Journal Batch Name",'PAYMENTS');
GenjLine.DELETEALL;
//<Stop

PaymentLine.RESET;
PaymentLine.SETRANGE(PaymentLine."No.","No.");
IF PaymentLine.FIND('-') THEN BEGIN
 REPEAT
  ExtDocNo:=PaymentLine."Applies-to Doc. No.";
//>Start expense line
  LineNo:=LineNo+1000;
  GenjLine.INIT;
  GenjLine."Journal Template Name":= 'PAYMENTS';
  GenjLine."Journal Batch Name":= 'PAYMENTS';
  GenjLine."Source Code":='PAYMENTJNL' ;
  GenjLine."Line No.":= LineNo;
  GenjLine."Document Type":= GenjLine."Document Type"::Payment;
  GenjLine."Document Date":= "Document Date";
  GenjLine."Posting Date":= "Posting Date";
  GenjLine."Document No.":= "No.";
  GenjLine."Account Type":= PaymentLine."Account Type";
  GenjLine.VALIDATE(GenjLine."Account No.",PaymentLine."Account No.");
  GenjLine.Narration:=PaymentLine.Narration;
  GenjLine.VALIDATE(GenjLine."Currency Code","Currency Code");
  GenjLine.VALIDATE(GenjLine.Amount,PaymentLine."Net Amount");
  GenjLine.VALIDATE(GenjLine."Fund No.","Fund No.");
 //**GenjLine."Applies-to Doc. Type":=PaymentLine."Applies-to Doc. Type";
 //**GenjLine."Applies-to Doc. No.":=PaymentLine."Applies-to Doc. No.";
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 1 Code",PaymentLine."Shortcut Dimension 1 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 2 Code",PaymentLine."Shortcut Dimension 2 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 3 Code",PaymentLine."Shortcut Dimension 3 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 4 Code",PaymentLine."Shortcut Dimension 4 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 5 Code",PaymentLine."Shortcut Dimension 5 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 6 Code",PaymentLine."Shortcut Dimension 6 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 7 Code",PaymentLine."Shortcut Dimension 7 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 8 Code",PaymentLine."Shortcut Dimension 8 Code");
  GenjLine."Bal. Account Type":=GenjLine."Bal. Account Type"::"Bank Account";
  GenjLine.VALIDATE(GenjLine."Bal. Account No.","Bank Account");
  PayDetails;
  GenjLine."Purch Order Ref":=  PaymentLine."Applies-to Doc. No.";
  GenjLine."External Document No.":= "Document No.";


  IF GenjLine.Amount<>0 THEN
  GenjLine.INSERT;
//<Stop

//>Start W/Tax
  LineNo:=LineNo+1000;
  GenjLine.INIT;
  GenjLine."Journal Template Name":= 'PAYMENTS';
  GenjLine."Journal Batch Name":= 'PAYMENTS';
  GenjLine."Source Code":='PAYMENTJNL' ;
  GenjLine."Line No.":= LineNo;
  GenjLine."Document Type":= GenjLine."Document Type"::Payment;
  GenjLine."Document Date":= "Document Date";
  GenjLine."Posting Date":= "Posting Date";
  GenjLine."Document No.":= "No.";
  GenjLine."Account Type":= PaymentLine."Account Type";
  GenjLine.VALIDATE(GenjLine."Account No.",PaymentLine."Account No.");
  GenjLine.VALIDATE(GenjLine."Currency Code","Currency Code");
  GenjLine.Narration:=PaymentLine.Narration;
  GenjLine.VALIDATE(GenjLine.Amount,PaymentLine."W/Tax Amount");
  GenjLine.VALIDATE(GenjLine."Fund No.","Fund No.");
  //**GenjLine."Applies-to Doc. Type":=PaymentLine."Applies-to Doc. Type";
  //**GenjLine."Applies-to Doc. No.":=PaymentLine."Applies-to Doc. No.";
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 1 Code",PaymentLine."Shortcut Dimension 1 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 2 Code",PaymentLine."Shortcut Dimension 2 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 3 Code",PaymentLine."Shortcut Dimension 3 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 4 Code",PaymentLine."Shortcut Dimension 4 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 5 Code",PaymentLine."Shortcut Dimension 5 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 6 Code",PaymentLine."Shortcut Dimension 6 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 7 Code",PaymentLine."Shortcut Dimension 7 Code");
  GenjLine.VALIDATE(GenjLine."Shortcut Dimension 8 Code",PaymentLine."Shortcut Dimension 8 Code");
  GenjLine."Bal. Account Type":=GenjLine."Bal. Account Type"::"G/L Account";
  GenjLine.Description:= 'W/Tax on'+'-' +PaymentHeader."Payee Name";
  GenjLine."Purch Order Ref":=  PaymentLine."Applies-to Doc. No.";
  //>Start get W/Tax account
  VatPost.SETRANGE(VatPost."VAT Prod. Posting Group",PaymentLine."W/Tax");
  VatPost.SETFILTER(VatPost."VAT Bus. Posting Group",'<>%1','');
  IF VatPost.FIND('-')THEN BEGIN
  GenjLine.VALIDATE(GenjLine."Bal. Account No.",VatPost."Purchase VAT Account");
  END;
  //<Stop
  PayDetails;
  GenjLine."External Document No.":="Document No.";
  GenjLine."Purch Order Ref":=  PaymentLine."Applies-to Doc. No.";

  IF GenjLine.Amount<>0 THEN
  GenjLine.INSERT;
//<Stop
 UNTIL PaymentLine.NEXT=0;
END;

//Allow Automatic Submission since entries were Approved on petty cash Doc.
 GLTranManagement.GenJournalSubmit(GenjLine);
 GenjLine.RESET;
 GenjLine.SETRANGE(GenjLine."Journal Template Name",'PAYMENTS');
 GenjLine.SETRANGE(GenjLine."Journal Batch Name",'PAYMENTS');
 CODEUNIT.RUN(CODEUNIT::"Gen. Jnl.-Post",GenjLine);
 Status:=Status::Posted;
 Posted:=TRUE;
 MODIFY;

Post to GL via Report

//AFK
BudgetName  := "G/L Budget Entry".GETFILTER("G/L Budget Entry"."Budget Name");
PostingDate := "G/L Budget Entry".GETRANGEMIN("G/L Budget Entry".Date);
//Message('%1',PostingDate);

IF DATE2DMY(PostingDate,2) > 6  THEN BEGIN
   StartDate := DMY2DATE(1,7,DATE2DMY(PostingDate,3));
   EndDate := DMY2DATE(30,6,DATE2DMY(PostingDate,3)+1);
END ELSE BEGIN
   StartDate := DMY2DATE(1,7,DATE2DMY(PostingDate,3)-1);
   EndDate := DMY2DATE(30,6,DATE2DMY(PostingDate,3));
END;

BudgetAmount:= 0;
ActualAmount:= 0;
GLBudgetName.RESET;
GLBudgetName.SETRANGE(GLBudgetName.Name,BudgetName);
GLBudgetName.SETRANGE(GLBudgetName."Budget Type",GLBudgetName."Budget Type"::Ceiling);

IF GLBudgetName.FINDFIRST THEN BEGIN
   GLBudgetEntry.RESET;
   GLBudgetEntry.SETCURRENTKEY("Budget Name",Date,"Posting Status");
   GLBudgetEntry.SETRANGE("Budget Name",GLBudgetName.Name);
   GLBudgetEntry.SETRANGE(Date,PostingDate);
   GLBudgetEntry.SETRANGE(GLBudgetEntry."Posting Status",GLBudgetEntry."Posting Status"::Unposted);
   GLBudgetEntry.SETFILTER(GLBudgetEntry."G/L Account No.",'%1..%2','1001','5899');
   IF GLBudgetEntry.FINDFIRST THEN BEGIN
      GLBudgetEntry.CALCSUMS(GLBudgetEntry.Amount);
      BudgetAmount := GLBudgetEntry.Amount;
      IF (GLBudgetEntry."Fund No." = '01A') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
         GLAcc.RESET;
         GLAcc.SETRANGE(GLAcc."No.",'6001');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END ELSE
      IF (GLBudgetEntry."Fund No." = '01B') THEN BEGIN
         GLBudgetEntry2.RESET;
         GLBudgetEntry2.SETRANGE("Budget Name",GLBudgetName.Name);
         GLBudgetEntry2.SETRANGE(Date,PostingDate);
         GLBudgetEntry2.SETRANGE(GLBudgetEntry2."Posting Status",GLBudgetEntry2."Posting Status"::Unposted);
         GLBudgetEntry2.SETRANGE(GLBudgetEntry2."Fund No.",'01B');
         GLBudgetEntry2.SETFILTER(GLBudgetEntry2."Global Dimension 2 Code",'%1..%2','002','025');
         IF GLBudgetEntry2.FINDFIRST THEN BEGIN
            GLAcc.RESET;
            GLAcc.SETRANGE(GLAcc."No.",'6150');
            GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry2."Fund No.");
            GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry2."Global Dimension 2 Code");
            //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
            //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
            GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
            IF GLAcc.FINDFIRST THEN BEGIN
               GLAcc.CALCFIELDS(GLAcc.Actual);
               ActualAmount := GLAcc.Actual;
              // MESSAGE(FORMAT(ActualAmount));
            END;
         END;
      END ELSE
      IF (GLBudgetEntry."Fund No." = '01D') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
         GLAcc.RESET;
         GLAcc.SETRANGE(GLAcc."No.",'6401');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END ELSE
      IF (GLBudgetEntry."Fund No." = '01C') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
         GLAcc.RESET;
         GLAcc.SETRANGE(GLAcc."No.",'6201');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END ELSE
      IF (GLBudgetEntry."Fund No." = '01E') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
         GLAcc.RESET;
         GLAcc.SETRANGE(GLAcc."No.",'6501');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END ELSE
      IF (GLBudgetEntry."Fund No." = '01F') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
         GLAcc.RESET;
         GLAcc.SETRANGE(GLAcc."No.",'6601');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END ELSE
      IF (GLBudgetEntry."Fund No." = '80') AND (GLBudgetEntry."Global Dimension 2 Code" = '001') THEN BEGIN
         GLAcc.RESET;
         GLAcc.SETFILTER(GLAcc."No.",'%1..%2','6800','7299');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END ELSE BEGIN
         GLAcc.RESET;
         GLAcc.SETRANGE(GLAcc."No.",'7301');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Actual);
            ActualAmount := GLAcc.Actual;
         END;
      END;
      IF ABS(BudgetAmount) > ABS(ActualAmount) THEN
        ERROR(BTText0002,ABS(ABS(ActualAmount)-ABS(BudgetAmount)))
     //  MESSAGE(BTText0002,ABS(ABS(ActualAmount)-ABS(BudgetAmount)))
       ELSE
//added code chacha- compares the total amount row in the imported excel
  GLBudgetName.SETRANGE(GLBudgetName."Budget Type",GLBudgetName."Budget Type"::Ceiling);
  IF GLBudgetName.FINDFIRST THEN BEGIN
     CLEAR(GLBudgetEntry);
     //GLBudgetEntry.RESET;
     GLBudgetEntry.SETCURRENTKEY("Budget Name",Date,"Posting Status");
     GLBudgetEntry.SETRANGE("Budget Name",GLBudgetName.Name);

     GLBudgetEntry.SETRANGE(Date,PostingDate);
     GLBudgetEntry.SETRANGE(GLBudgetEntry."Posting Status",GLBudgetEntry."Posting Status"::Unposted);
     GLBudgetEntry.SETFILTER(GLBudgetEntry."G/L Account No.",'%1','6150');
     IF GLBudgetEntry.FINDFIRST THEN BEGIN
     //calcsums chacha
        GLBudgetEntry.CALCSUMS(GLBudgetEntry.Amount);
        BudgetTotal:= GLBudgetEntry.Amount;

        IF ABS(BudgetTotal) <> ABS(ActualAmount) THEN
          ERROR('The Excel total is wrong by %1 ',ABS(ABS(ActualAmount)-ABS(BudgetTotal)));


        END;
        END;
//chacha , check current actual amount  vs allocation
        IF GLAcc.FINDFIRST THEN BEGIN
         CLEAR(GLAcc);
         GLAcc.SETCURRENTKEY(GLAcc."No.");
         GLAcc.SETFILTER(GLAcc."No.",'%1..%2','1001','5899');
         GLAcc.SETRANGE(GLAcc."Fund Filter",GLBudgetEntry."Fund No.");
         GLAcc.SETRANGE(GLAcc."Global Dimension 2 Filter",GLBudgetEntry."Global Dimension 2 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 3 Filter",GLBudgetEntry."Global Dimension 3 Code");
         //GLAcc.SETRANGE(GLAcc."Global Dimension 6 Filter",GLBudgetEntry."Global Dimension 6 Code");
         GLAcc.SETRANGE(GLAcc."Date Filter",StartDate,EndDate);
         IF GLAcc.FINDFIRST THEN BEGIN
            GLAcc.CALCFIELDS(GLAcc.Allocation);
            GLAcc.CALCFIELDS(GLAcc.Actual);
           // GLAcc.CALCSUMS(GLAcc.Actual);
           // GLAcc.CALCSUMS(GLAcc.Allocation);

            CurrentActualTotal+= GLAcc.Actual;
            CurrentAllocationTotal+= GLAcc.Allocation;
            RemainingAmt:=ABS(GLAcc.Actual)-ABS( CurrentAllocationTotal);
              IF RemainingAmt < ABS(ActualAmount) THEN
              ERROR('You dont have enough funds');

         END;
         END;



   END;
END;


SETRANGE("Posting Status","Posting Status"::Unposted);

Window.OPEN(
  SNText007 +
  SNText008);

Window.UPDATE(1,SNText005);

G/L Budget Entry - OnAfterGetRecord()

IF Amount = 0 THEN
  CurrReport.SKIP;

GLAcc.GET("G/L Account No.");
IF GLAcc."Account Type" = GLAcc."Account Type"::Posting THEN BEGIN
  GenJnlLine.INIT;
  GenJnlLine."Source Code" := GenJnlTemplate."Source Code";
  GenJnlLine."System-Created Entry" := TRUE;
  GenJnlLine."Journal Template Name" := LedgerBudget."Journal Template Name";
  GenJnlLine."Journal Batch Name" := LedgerBudget."Journal Batch Name";
  GenJnlLine."Line No." := "Entry No.";
  GenJnlLine."Budget Name Code" := LedgerBudgetCode;
  GenJnlLine.VALIDATE("Account Type",GenJnlLine."Account Type"::"G/L Account");
  GenJnlLine.VALIDATE("Account No.","G/L Account No.");
  GenJnlLine.VALIDATE("Posting Date",Date);
  GenJnlLine."Document No." := DocNo;
  IF Description = '' THEN
    GenJnlLine.Description := COPYSTR(LedgerBudget.Name,1,MAXSTRLEN(GenJnlLine.Description))
  ELSE
    GenJnlLine.Description := COPYSTR(Description,1,MAXSTRLEN(GenJnlLine.Description));
  GenJnlLine.VALIDATE(GenJnlLine."Bal. Account No.",GenJnlBatch."Bal. Account No.");
  GenJnlLine."Gen. Posting Type" := GenJnlLine."Gen. Posting Type"::" ";
  GenJnlLine."Gen. Bus. Posting Group" := '';
  GenJnlLine."Gen. Prod. Posting Group" := '';
  GenJnlLine."VAT Posting" := GenJnlLine."VAT Posting"::"Manual VAT Entry";
  GenJnlLine."VAT %" := 0;
  GenJnlLine."VAT Amount" := 0;
  GenJnlLine."VAT Base Amount" := GenJnlLine.Amount;
  GenJnlLine."VAT Bus. Posting Group" := '';
  GenJnlLine."VAT Prod. Posting Group" := '';
  GenJnlLine.VALIDATE(Amount,Amount);
  GenJnlLine."Ctl. Fund No." := "Fund No.";
  GenJnlLine.VALIDATE("Fund No.","Fund No.");
  DimensionManagement.DIMGLBudgetTOGenJnlLine("G/L Budget Entry",GenJnlLine);
  GenJnlLine."Transaction Type" := GenJnlLine."Transaction Type"::Budget;
  GenJnlLine.Status := GenJnlLine.Status::Approved;
  GenJnlLine."Internal Control No." := "Internal Control No.";
  GenJnlLine."Document Line No." := "Entry No.";
  GenJnlLine."Budget Type" :=LedgerBudget."Budget Type"; // TBL Added
  GenJnlLine.Quantity := Quantity;
  GenJnlLine."Unit of Measure Code" := "Unit of Measure Code";
  Window.UPDATE(2,GenJnlLine."Account No.");

// Check if a Normal budget needs to be reversed and create reverse line then allow for posting to proceed. == TBL LGM
IF LedgerBudget."Budget Type" =LedgerBudget."Budget Type"::Virement THEN
  TestBudgetReverse("G/L Account No.","G/L Budget Entry".Date);


 // GenJnlLine.INSERT;
  GenJnlPostLine.RUN(GenJnlLine);

  "Posting Status" := "Posting Status"::Posted;
  MODIFY;
  TempGLBudEntry.TRANSFERFIELDS("G/L Budget Entry");
  TempGLBudEntry.INSERT;
  LinesPosted += 1;
END;

G/L Budget Entry - OnPostDataItem()
IF TempGLBudEntry.FINDFIRST THEN
  REPEAT
   GLTranManagement.DeleteLineDistributionBuffer(TempGLBudEntry."Internal Control No.");
  UNTIL TempGLBudEntry.NEXT = 0;

Window.CLOSE;
MESSAGE(SNText006,LinesPosted);

TestBudgetReverse(BudgetAc : Code[20];PostDate : Date)
GLEntry.SETCURRENTKEY("Transaction Type","G/L Account No.","Fund No.","Global Dimension 1 Code",
"Global Dimension 2 Code","Global Dimension 3 Code","Global Dimension 4 Code",
"Global Dimension 5 Code","Global Dimension 6 Code","Global Dimension 7 Code","Global Dimension 8 Code",
"Posting Date","Budget Name Code","Budget Type");
GLEntry.SETRANGE("Transaction Type",GLEntry."Transaction Type"::Budget);
GLEntry.SETRANGE(GLEntry."G/L Account No.",BudgetAc);
GLEntry.SETRANGE("Fund No.","G/L Budget Entry"."Fund No.");
GLEntry.SETRANGE("Global Dimension 1 Code","G/L Budget Entry"."Global Dimension 1 Code");
GLEntry.SETRANGE("Global Dimension 2 Code","G/L Budget Entry"."Global Dimension 2 Code");
GLEntry.SETRANGE("Global Dimension 3 Code","G/L Budget Entry"."Global Dimension 3 Code");
GLEntry.SETRANGE("Global Dimension 4 Code","G/L Budget Entry"."Global Dimension 4 Code");
GLEntry.SETRANGE("Global Dimension 5 Code","G/L Budget Entry"."Global Dimension 5 Code");
GLEntry.SETRANGE("Global Dimension 6 Code","G/L Budget Entry"."Global Dimension 6 Code");
GLEntry.SETRANGE("Global Dimension 7 Code","G/L Budget Entry"."Global Dimension 7 Code");
GLEntry.SETRANGE("Global Dimension 8 Code","G/L Budget Entry"."Global Dimension 8 Code");
GLEntry.SETRANGE(GLEntry."Posting Date",PostDate);
GLEntry.SETRANGE("Budget Type",GLEntry."Budget Type"::Normal);
GLEntry.CALCSUMS(Amount);
AmtToReverse := GLEntry.Amount;

IF AmtToReverse > 0 THEN
  GLLine.INIT;
  GLLine."Source Code" := GenJnlTemplate."Source Code";
  GLLine."System-Created Entry" := TRUE;
  GLLine."Journal Template Name" := 'BUDGET';
  GLLine."Journal Batch Name" := 'REVERSE';
  GLLine."Line No." := 10000; //"G/L Budget Entry"."Entry No.";
  GLLine."Budget Name Code" := LedgerBudgetCode;
  GLLine.VALIDATE("Account Type",GLLine."Account Type"::"G/L Account");
  GLLine.VALIDATE("Account No.",BudgetAc);
  GLLine.VALIDATE("Posting Date",PostDate);
  GLLine."Document No." := DocNo;
  GLLine.Description := 'Reversal for normal budget line';
  GLLine.VALIDATE(GLLine."Bal. Account No.",GenJnlBatch."Bal. Account No.");
  GLLine."Gen. Posting Type" := GLLine."Gen. Posting Type"::" ";
  GLLine."Gen. Bus. Posting Group" := '';
  GLLine."Gen. Prod. Posting Group" := '';
  GLLine."VAT Posting" := GLLine."VAT Posting"::"Manual VAT Entry";
  GLLine."VAT %" := 0;
  GLLine."VAT Amount" := 0;
  GLLine."VAT Base Amount" := GLLine.Amount;
  GLLine."VAT Bus. Posting Group" := '';
  GLLine."VAT Prod. Posting Group" := '';
  GLLine.VALIDATE(Amount,-AmtToReverse);
  GLLine."Ctl. Fund No." := "G/L Budget Entry"."Fund No.";
  GLLine.VALIDATE("Fund No.","G/L Budget Entry"."Fund No.");
  //DimensionManagement.DIMGLBudgetTOGenJnlLine("G/L Budget Entry",GLLIne);
  GLLine."Transaction Type" := GLLine."Transaction Type"::Budget;
  GLLine.Status := GLLine.Status::Approved;
  GLLine."Internal Control No." := "G/L Budget Entry"."Internal Control No.";
  GLLine."Document Line No." := "G/L Budget Entry"."Entry No.";

GLPost.RUN(GLLine);

Restrict Posting to current Month

WITH Rec DO BEGIN
  GLTranManagement.ValidatePostingDate("Posting Date");
END;


ValidatePostingDate:Date(par)
{
MonthPosting := DATE2DMY(PostingDate,2);
CurrentMonth := DATE2DMY(TODAY,2);        // commented by amos chibwana to allow carol to post for earlier dates..
 IF MonthPosting <> CurrentMonth THEN     // the comments should be removed during deployment
  ERROR(Text50000);
}

Show Hide Fields based on condition

the visibility property will work on Groups.

As per your requirement, set 1-8 fields in Group1 and 9-15 in Group2.

Create a Boolean(IsgrpVisible) variable and set IncludeInDataset to Yes

Write IsgrpVisible = (your condition) in OnOpenPage() and OnAfterGetRecord()

Set Group Visible = IsgrpVisible

It will work while moving through the records by clicking the "next record" action from the action menu.

If you want it in field wise in same group, you can assign boolean variable to visible property of field but
 it will work only you open the Record not while moving through the records by clicking the "next record" action
 from the action menu

OBJECT Page 50000 Resource Card 2
{
  OBJECT-PROPERTIES
  {
    Date=04/28/10;
    Time=10:01:15 AM;
    Modified=Yes;
    Version List=CUS01;
  }
  PROPERTIES
  {
    SourceTable=Table156;
    PageType=Card;
    OnAfterGetRecord=BEGIN
                       IsCityVisible := (Type = Type::Person);
                     END;

  }
  CONTROLS
  {
    { 1100495000;0;Container;
                ContainerType=ContentArea }

    { 1100495001;1;Group  ;
                Name=General;
                GroupType=Group }

    { 1100495002;2;Field  ;
                SourceExpr="No." }

    { 1100495003;2;Field  ;
                SourceExpr=Type;
                OnValidate=BEGIN
                             IsCityVisible := (Type = Type::Person);
                           END;
                            }

    { 1100495004;2;Field  ;
                SourceExpr=Name }

    { 1100495005;2;Field  ;
                SourceExpr="Search Name" }

    { 1100495008;2;Field  ;
                SourceExpr=City;
                Visible=IsCityVisible }

    { 1100495007;1;Group  ;
                CaptionML=ENU=City;
                Visible=IsCityVisible;
                GroupType=Group }

    { 1100495006;2;Field  ;
                SourceExpr=City }

  }
  CODE
  {
    VAR
      IsCityVisible@1100495000 : Boolean INDATASET;

    BEGIN
    END.
  }
}

Loop Numbering

GeneralLedgerEntry.VALIDATE("G/L AccountNo", '100');
// This corresponds to:
GeneralLedgerEntry."G/L AccountNo" := '100';
GeneralLedgerEntry.VALIDATE("G/L AccountNo");

Record.FIELDERROR(Field, [Text])

Name := Record.FIELDNAME(Field)

//loop numbering

WITH SquashJnlLine DO BEGIN
  IF EmptyLine THEN
    EXIT;

  SquashJnlCheckLine.RunCheck(SquashJnlLine,TempJnlLineDim);

  IF NextEntryNo = 0 THEN BEGIN
    SquashLedgEntry.LOCKTABLE;
    IF SquashLedgEntry.FIND('+') THEN
      NextEntryNo := SquashLedgEntry."Entry No.";
      NextEntryNo := NextEntryNo + 1;
    END;

    IF SquashReg."No." = 0 THEN BEGIN
      SquashReg.LOCKTABLE;
      IF (NOT SquashReg.FIND('+')) OR ... THEN BEGIN
        SquashReg.INIT;
        SquashReg."No." := SquashReg."No." + 1;
        ...
        SquashReg.INSERT;
      END;
    END;
    SquashReg."To Entry No." := NextEntryNo;
    SquashReg.MODIFY;

How To create a report which is based on a temporary table?

How To create a report which is based on a temporary table?

Author: Luc Van Dyck

When you normally create a report, it is based on one or more tables.

Sometimes you need to create a temporary table (eg. to combine or sort data) and you want that data to appear in a report. The problem is that you can't use a temporary table directly in the DataItem-property of the report. It only accepts "real" tables.

The solution for this is to use the virtual table Integer instead, and write code to simulate the OnAfterGetRecord-trigger.

OnPreDataItem()

FOR i := 1 TO 80 DO BEGIN

  tmpItem.INIT;

  tmpItem."No." := FORMAT(i);

  tmpItem.Description := 'Item description ' + FORMAT(i);

  tmpItem."Unit Price" := i * 1000;

  tmpItem.INSERT;

END;

tmpItem.RESET;

SETRANGE(Number,1,tmpItem.COUNT);                                            

                                               

OnAfterGetRecord()

IF Number = 1 THEN

  tmpItem.FIND('-')

ELSE

  tmpItem.NEXT;

When defining the layout of your report, you use this Integer-dataitem to place your headers and body's.

You can't use the Field Menu to place fields on your report: you have to type the name of your tmp-variable together with the fieldname (eg. tmpItem.Description).

The result is a normally looking report: