FD Tutorial: Dashboard Monthly Sales

Using the Form Designer, you can create a dashboard to show graphs and list key figures for your company. This tutorial requires the AR and IN Modules.

This tutorial will show you how to display Monthly Sales on a form.

1. Go to Navigator > Setup > Form Designer.
2. Click the Memory Table button, then click inside the form on the right. A Memory Table object will be added to the form. The memory table object will not be displayed when the form runs, so it doesn't matter where you put it.
3. Click the memory table icon on the form. The properties will be listed on the left.
4. Change the Name of your table to tblGrpSales, by typing this beside the Name property, over tblMemoryTable1. The table is going to contain the monthly sales data, so it is helpful to give it a meaningful name, indicating the table contents.
5. Double-click the Memory Table icon on your form. The FD Memory Table Designer will open.
6. Add the following fields to the table:

   No.	Name	Display Label	Type	Domain	Size	Width	Req	Visible
   1	PeriodID	PeriodID	Byte			10		Clear
   2	ExclusiveAmount	Amount	Float	Amount		10		Selected
   3	PeriodName	Period	String		16	15		Selected

7. Go to the FD Memory Table Designer - Keys tab.
8. Add the following key to the table.

   No.	Name	Unique	Primary	Parts
   1	Period		Selected	+PeriodID

9. Click Save. The FD Memory Table Designer will close.
10. Set the Active property of the Memory Table to True.
11. Set the IndexName property of the Memory Table to Period.
12. Click the Graph button, then click inside the form on the right. A Graph object will be added to the form.
13. Click the Graph on the form, to show the properties on the left.
14. Change the Name of the graph to graphSales.
15. To change the display of the graph, set the following properties:

    Property	Value	Description
    Anchors	[akLeft]	Click the Expand [x] button next to Anchors, and set akTop to False. This will bind the graph to the left.
    BackWallColor	clWhite	Click the [...] button in BackWallColor, and click white, to set the graph back wall to white.
    Height	258	Sets the graph height. You can also drag to change the height.
    Left	0	Moves the graph to the left of the form.
    ShowGridLines	False	Hides the grid lines.
    ShowLegend	False	Hides the legend.
    Top	52	Sets the graph position from the top. You can also drag to change the position.
    Width	640	Sets the graph width. You can also drag to change the width.

16. Set the Table to tblGroupSales. This links the Graph to the memory table created previously. Set the XLabelFieldName to PeriodName.
17. Double-click the graph in the form. The FD Graph Editor opens.
18. Add the following field to the Graph Editor:

    Field Name	Show Key Border	Pen Style	Pen Width
    ExclusiveAmount	Selected	psClear	2

19. Click Save. The FD Graph Editor will close.

    You can now write the MaxBasic code to populate the Memory Table and graph.

20. 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.

21. Enter the following code, to extract the sales information and populate the memory table, and display it in the graph:

    Sub RefreshSales
      Dim tblSales as Object 'Will be used to store the query data
      Dim TotalSales as Number
      tblGrpSales.empty 'Clear the memory table
      'Enter the SQL Query to extract the sales amounts per period
      SQLSales ="SELECT INHEAD.PeriodID as PeriodID " & _
      " ,Sum(INHEAD.ExclusiveAmountBs) as ExclusiveAmount " & _
      "FROM INHEAD " & _
      "WHERE INHEAD.PeriodID Between :LYStart AND :TYEnd " & _
      " AND INHEAD.PostStatus = 'P' " & _
      " AND INHEAD.DocumentClass IN ('I', 'C') " & _
      "GROUP BY INHEAD.PeriodID " & _
      "ORDER BY INHEAD.PeriodID "
      tblSales = ExecuteSQL(SQLSales, StartPeriod, EndPeriod)
      tblGrpSales.AllowInsertDelete = True
      tblGrpSales.IndexName = "Period"
      tblSales.First
      Do Until tblSales.EOF
        tblGrpSales.Append
        CopyFieldsByName(tblSales, tblGrpSales)
        tblGrpSales.PeriodName = PeriodName(tblSales.PeriodID)
        tblGrpSales.Save
        tblSales.Next
      Loop
      tblGrpSales.AllowInsertDelete = False
      tblGrpSales.IndexName = "Period"
      graphSales.RefreshData
    End Sub

22. 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
      RefreshSales
    End Sub

23. Click the Layout tab. Click on the form, or select the frmFDForm component.
24. Go to the Events tab. Set OnCreate to OnCreate.
25. You can add a Link label as a title to the graph. Click the Link Label button, then click on the form.
26. Change the Link Label Caption to Monthly Sales for Last 12 Months.
27. You can change label size, position, font, colour or other properties.
28. Add code to go to the IN Invoice Report when the Link Label is clicked. Go to the Code tab, and enter the following code:

    Sub OnLinkSales

    Dim Report as Object

    Report = CreateObject("Accredo.INInvoiceReport")

    Report.Layout = "Invoice"

    Report.Destination = "Screen"

    Report.Type.Invoice = True

    Report.Type.Credit = True

    Report.Type.Quote = False

    Report.PostStatus.Posted = True

    Report.PostStatus.Unposted = False

    Report.PostStatus.Open = False

    Report.PostStatus.Deleted = False

    Report.CurrentHistory = "Both Current and History"

    Report.Invoices = "Range of Periods"

    Report.PeriodFrom = StartPeriod

    Report.PeriodTo = EndPeriod

    Report.Preferences.IncludeRecordsWithEmptyDetail = False

    End Sub

29. Go to the layout tab. Select the Link Label component. Go to the Events tab. Set OnClick to OnLinkSales.
30. Click Save Form (Ctrl+S).
31. Click Run (Ctrl+R) to test the form.