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:
Enabled | Field No. | Field Name | Data Type | Length | Description |
Yes | 26 | Net Invoicing this Week | Decimal | Chris | |
Yes | 27 | Net Invoicing MTD | Decimal | Chris | |
Yes | 28 | Net Invoicing YTD | Decimal | Chris | |
Yes | 6 | Payment Recieved this week | Decimal | Chris | |
Yes | 7 | Payment Received this month | Decimal | chris | |
Yes | 37 | Payment received YTD | Decimal | Chris |
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