How to create Cue tiles for Role center in Dynamics NAV 2017

Introduction:

Cues are designed to give users with a quick status of their daily activities, which acts as a prompt them to take action. A Cue is a tile on a page in the Dynamics NAV client that provides a visual representation of business data.

This blog explains how to create cue tiles to get visual representation of Net Invoices and and payment received for current week, month and year.

Pre-requisites:

Microsoft Dynamics NAV 2017

Steps:

1. Create fields in a table as below:

2. Create a Query. select DataItem as Cust. Ledger Entry and enter the fields as Filter and add a column as Amount and use the method type as Totals and Method as Sum

3. For Invoices Received this week:

Create a global function in the table Calculate Invoice Received this week week with return type as Decimal. For this requirement, the week start day is Sunday and end day is Saturday but by default in Dynamics NAV, the week start day is Monday and end day is Sunday. So in NAV 1 = Monday and 7 = Sunday.  So I’ve written the below code to get the weeks start date and end date.

TodayDateNo:=DATE2DWY(TODAY,1); //this code fetches which day of the week is today e.g its Friday it returns 5.

I’ve declared two global  variables StartDateofWeek1 and EndDateofWeek1 and local record variable  and added the below code:

CalculateInvoicetReceivedThisWeek() AmtWeek : Decimal

TodayDateNo:=DATE2DWY(TODAY,1);
IF TodayDateNo =1 THEN BEGIN //Monday
StartDateOfWeek1:=CALCDATE('< -1D >',TODAY); //Sunday
EndDateOfWeek1 := CALCDATE('< +5D >',TODAY); //Saturday
END;
IF TodayDateNo =2 THEN BEGIN //Tuesday
StartDateOfWeek1:=CALCDATE('< -2D >',TODAY);
EndDateOfWeek1 := CALCDATE('<+4D>',TODAY);
END;
IF TodayDateNo =3 THEN BEGIN  //Wednesday
StartDateOfWeek1:=CALCDATE('< -3D >',TODAY);
EndDateOfWeek1 := CALCDATE('<+3D>',TODAY);
END;
IF TodayDateNo =4 THEN BEGIN  //Thursday
StartDateOfWeek1:=CALCDATE('< -4D >',TODAY);
EndDateOfWeek1 := CALCDATE('<+2D>',TODAY);
END;
IF TodayDateNo =5 THEN BEGIN  //Friday
StartDateOfWeek1:=CALCDATE('< -5D >',TODAY);
EndDateOfWeek1 := CALCDATE('<+1D>',TODAY);
END;
IF TodayDateNo =6 THEN BEGIN //Saturday
StartDateOfWeek1:=CALCDATE('< -6D >',TODAY);
EndDateOfWeek1 := TODAY;
END;
IF TodayDateNo =7 THEN BEGIN  //Sunday
StartDateOfWeek1:=TODAY;
EndDateOfWeek1 := CALCDATE('< +6D >',TODAY);
END;

I’m using a Query to get the sum of invoices for the current week

CF_Query.SETRANGE(Document_Type,CustLedgerEntry."Document Type"::Invoice,CustLedgerEntry."Document Type"::"Credit Memo");
CF_Query.SETRANGE(Posting_Date,CALCDATE('<CW>',StartDateOfWeek1),EndDateOfWeek1);
CF_Query.OPEN;

IF CF_Query.READ THEN
AmtWeek:=CF_Query.Sum_Payment;

4. For Invoice received this Month:

Create a global function as below with return type as decimal and insert the below code

CalculateInvoiceReceivedThisMonth() AmtMonth : Decimal

//This query fetches data from 1st date of current month till today
CF_Query.SETRANGE(Document_Type,CustLedgerEntry."Document Type"::Invoice,CustLedgerEntry."Document Type"::"Credit Memo");
CF_Query.SETRANGE(Posting_Date,CALCDATE('<-CM>',TODAY),TODAY);
CF_Query.OPEN;

IF CF_Query.READ THEN
AmtMonth:=CF_Query.Sum_Payment;

5. For Invoices received this Year:

Create a global function as below with return type as decimal and insert the below code:

CalculateInvoiceReceivedThisYear() AmtYear : Decimal

//This query fetches data from start date of the year i.e 1 Jan till today
CF_Query1.SETRANGE(Document_Type,CustLedgerEntry."Document Type"::Invoice,CustLedgerEntry."Document Type"::"Credit Memo");
CF_Query1.SETRANGE(Posting_Date,CALCDATE('<-CY>',TODAY),TODAY);
CF_Query1.OPEN;

IF CF_Query1.READ THEN
AmtYear:=CF_Query1.Sum_Payment;

6. For Payments received same code is used as above only in the query the setfilter condition is changed to Payments e.g for Payments received this year the code is as below:

CalculatePaymnetReceivedThisYear() AmtYr1 : Decimal
CF_Query.SETFILTER(Document_Type,'%1|%2',CustLedgerEntry."Document Type"::Payment,CustLedgerEntry."Document Type"::Refund);
CF_Query.SETRANGE(Posting_Date,CALCDATE('<-CY>',TODAY),TODAY);
CF_Query.OPEN;

IF CF_Query.READ THEN
AmtYr1:=CF_Query.Sum_Payment;

7. Now you need to call these functions, we will call them from the Cue page. create a page and create a group with subtype as CueGroup. Add your fields below the group

8. On OnOpenPage of the Cue page write the below code

OnOpenPage()
RESET;
IF NOT GET THEN BEGIN
INIT;
INSERT;
END;

OnAfterGetRecord()
CalculateCueFields;

9. On the trigger OnAfterGetRecord, a local function is called CalculateCueFields.In this function, call is made to the functions for invoices and payments.

LOCAL CalculateCueFields()

IF FIELDACTIVE("Net Invoicing this Week") THEN
"Net Invoicing this Week":=CalculateInvoicetReceivedThisWeek;
IF FIELDACTIVE("Net Invoicing MTD") THEN
"Net Invoicing MTD":=CalculateInvoiceReceivedThisMonth;
IF FIELDACTIVE("Net Invoicing YTD") THEN
"Net Invoicing YTD":=CalculateInvoiceReceivedThisYear;
IF FIELDACTIVE("Payment Recieved this week") THEN
"Payment Recieved this week":=CalculatePaymnetReceivedThisWeek;
IF FIELDACTIVE("Payment Received this month") THEN
"Payment Received this month":=CalculatePaymnetReceivedThisMonth;
IF FIELDACTIVE("Payment received YTD") THEN
"Payment received YTD":=CalculatePaymnetReceivedThisYear;

Run the page :

10. Drilldown: Suppose the user wants to check for the entries which account for Net Invoicing MTD to 718.00 then create a global function in the table DrillDownInvoiceThisMonth , create a record variable CustLedgerEntry and write the below code:

DrillDownInvoiceThisMonth()
CustLedgerEntry.SETRANGE("Document Type",CustLedgerEntry."Document Type"::Invoice,CustLedgerEntry."Document Type"::"Credit Memo");
CustLedgerEntry.SETRANGE("Posting Date",CALCDATE('<-CM>',TODAY),TODAY);
PAGE.RUN(PAGE::"Customerer Entries",CustLedgerEntry);

Call this function in the page, under the field Net Invoicing MTD – OnDrillDown()

Net Invoicing MTD - OnDrillDown()
DrillDownInvoiceThisMonth