FD Tutorial: Dashboard Top Five Customers

Using the Form Designer, you can create a dashboard to show the top five customers for the last 12 months, and their sales totals, with links to lists of the invoices. This tutorial requires the IN Module.

Go to Navigator > Setup > Form Designer.
Add five Labels to the form. Click the Label button, then click on the form, five times.
Set the Width of each Label to around 125, and the Height to around 40. To set properties for multiple components, hold down the Shift key and click each component. You can then set a property for all selected components. You can also drag to resize the components.
Set the Caption property of each label to be blank.
Set the Name properties of the five labels to: lblCust1; lblCust2; lblCust3; lblCust4; lblCust5.
Move the labels next to each other.
Change the colours of the labels, by setting the Color property, to distinguish between them. You could use alternating colours, such as yellow, blue, yellow, blue.
Click the Link Label button, then click to add a link label in each label.
Set the Caption properties of each link label to be blank.
Set the Name properties of the three link labels to: linkTopCust1; linkTopCust2; linkTopCust3; linkTopCust4; linkTopCust5.
Position one of the Link Labels at the top of a label. Set the Top property of the other Link Labels to be the same.
Set the following properties on the Link Labels:

Property

Value

Alignment

taCentre

ParentColor

True

Transparent

True
Add a Label as a heading, above the existing labels. Click the Label button, then click on the form. Set the label Caption to Top Five Customers for last 12 Months.
You can change label size, position, font, colour or other properties.
Next, you can write MaxBasic code to add the figures to the labels.
Initialise variables to be used in the dashboard. Click on the Code tab. Enter the following:
Dim PeriodToUse as Number, StartPeriod as Number, EndPeriod as Number
PeriodToUse = CurrentPeriod("AR")
StartPeriod = AddPeriod(PeriodToUse, -12,True)
EndPeriod = AddPeriod(PeriodToUse, -1,True)

This sets the PeriodToUse to the current AR Period, the StartPeriod to 12 periods prior to the PeriodToUse, and EndPeriod to 1 period before PeriodToUse.
Click on the Code tab. Enter the following:
Sub RefreshTopCustomers

SQLCUST = "SELECT INHEAD.CustomerCode " & _

" ,Sum(Case When INHEAD.PeriodID Between :StartPeriod AND :EndPeriod Then INHEAD.ExclusiveAmount Else 0 End) as Sales " & _

"FROM INHEAD INNER JOIN ARCUST ON ARCUST.CustomerCode=INHEAD.CustomerCode " & _

"WHERE (INHEAD.PeriodID Between :StartPeriod and :EndPeriod " & _

" AND INHEAD.DocumentClass IN ( 'I' , 'C' ) " & _

" AND INHEAD.PostStatus IN ( 'U' , 'P' )) " & _

"GROUP BY INHEAD.CustomerCode " & _

"ORDER BY Sales DESC "

tblCustomers = ExecuteSQL(SQLCUST, StartPeriod, EndPeriod)

Counter = 0

tblCustomers.First

Do Until tblCustomers.EOF or (Counter >= 5)

If Counter = 0 Then

LinkTopCust1.caption = tblCustomers.CustomerCode

lblCust1.caption = CHR(10) & " $" & FormatNumber(tblCustomers.Sales, ",0.00")

End If

If Counter = 1 Then

LinkTopCust2.caption = tblCustomers.CustomerCode

lblCust2.caption = CHR(10) & " $" & FormatNumber(tblCustomers.Sales, ",0.00")

End If

If Counter = 2 Then

LinkTopCust3.caption = tblCustomers.CustomerCode

lblCust3.caption = CHR(10) & " $" & FormatNumber(tblCustomers.Sales, ",0.00")

End If

If Counter = 3 Then

LinkTopCust4.caption = tblCustomers.CustomerCode

lblCust4.caption = CHR(10) & " $" & FormatNumber(tblCustomers.Sales, ",0.00")

End If

If Counter = 4 Then

LinkTopCust5.caption = tblCustomers.CustomerCode

lblCust5.caption = CHR(10) & " $" & FormatNumber(tblCustomers.Sales, ",0.00")

End If

Counter = Counter + 1

tblCustomers.Next

Loop

End Sub
Create a routine called OnCreate to run when the form is created. This will call the RefreshSales sub-routine. Enter the following code:
Sub OnCreate
RefreshTopCustomers
End Sub
Click the Layout tab. Click on the form, or select the frmFDForm component.
Go to the Events tab. Set OnCreate to OnCreate.
Click Save Form (Ctrl+S).
Click Run (Ctrl+R) to test the form.
Next, you can link the Link Labels to lists of invoices.
Click on the Code tab. Enter the following code to display all current open invoices for the Customer:
Sub ONLinkTopCust1

SQLTopCUST = "SELECT INHEAD.CustomerCode as Customer " & _

" ,INHead.DocumentID as DocID " & _

" ,INHead.DocumentNo as DocNo " & _

" ,INHead.DocumentType as DocType " & _

" ,INHead.DocumentClass as DocClass " & _

" ,INHead.DocumentDate as DocDate " & _

" ,INHead.ExclusiveAmount " & _

" ,INHead.ExclusiveCost " & _

"FROM INHEAD INNER JOIN ARCUST ON ARCUST.CustomerCode=INHEAD.CustomerCode " & _

"WHERE (INHEAD.PeriodID Between :StartPeriod and :EndPeriod " & _

" AND INHEAD.DocumentClass IN ( 'I' , 'C' ) " & _

" AND INHEAD.PostStatus IN ( 'U' , 'P' ) " & _

" AND INHEAD.CustomerCode = :CUST ) " & _

"ORDER BY DocID DESC "

tblTopCust = ExecuteSQL(SQLTopCUST, StartPeriod, EndPeriod, LINKTOPCUST1.caption)

tblTopCust.Fields.Item("Customer").Visible = False

tblTopCust.Fields.Item(6).Total = True

tblTopCust.Fields.Item(7).Total= True

Report = CreateReport(tblTopCust, "Invoices and Credits For " & tblTopCust.Customer & CHR(10) & " From " & (PeriodName(StartPeriod) & " To " & (PeriodName(EndPeriod))))

Report.Destination = "Screen"

Report.Run

Handled = True

End Sub
Enter the code again, changing OnLinkTopCust1 in Line 1 to OnLinkTopCust2, and changing LINKTOPCUST1.Caption to LINKTOPCUST2.Caption on Line 16.
Repeat the above step 3 more times, changing the numbers to 3, 4, and 5 respectively.
Link the code to the link labels. Click the LinkTopCust1 link label, then go to the Events tab. Set OnClick to ONLinkTopCust1. Repeat for the other four link labels and events.
Click Save Form (Ctrl+S).
Click Run (Ctrl+R) to test the form. When you click on the Link Labels you will see the relevant list of Invoices.

Property	Value
Alignment	taCentre
ParentColor	True
Transparent	True