FD Tutorial: Dashboard Analysis of Expenses

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

This tutorial will show you how to display an analysis of expenses for a range of periods.

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 tblGraphExp.
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	Visible
   1	AnalysisCode	Expenses	String	Analysis Code	8	15	Selected
   2	AnalysisName	Analysis Name	String	Analysis Name	60	25	Selected
   3	PeriodBalance	Period Balance	Float	Amount		15	Selected
   4	YrToDateBalance	YTD Balance	Float	Amount		15	Selected
   5	LastYRBalance	LY Balance	Float	Amount		10	Selected
   6	GLAccountCode	Account Code	String	GL Account Code	8	15	Clear
   7	AccountClass	Account Class	String	Description	60	28	Clear

7. Click Save. The FD Memory Table Designer will close.
8. Set the Active property of the Memory Table to True.
9. Click the List Grid button, then click inside the form on the right. A List Grid will be added to the form.
10. Click the List Grid on the form, to show the properties on the left.
11. Change the Name of the grid to gridExp.
12. To change the display of the grid, set the following properties:

    Property	Value	Description
    Height	270	Sets the grid height. You can also drag to change the height.
    Left	25	Moves the grid to the left of the form.
    Top	30	Sets the grid position from the top. You can also drag to change the position.
    Width	550	Sets the grid width. You can also drag to change the width.

13. Set the graph Table property to tblGraphExp. This links the grid to the memory table created previously.
14. Click the Grid Navigator button. Click beside the List Grid to add a navigator next to the grid.
15. Change the Name of the navigator to navExp.
16. Change the following properties of the grid navigator.

    Property	Value	Description
    Height	270	Sets the navigator height. You can also drag to change the height.
    Left	0	Moves the navigator to the left of the form.
    Top	30	Sets the grid position from the top. You can also drag to change the position.
    Visible Buttons	ebOpen ebPrint ebFilterSort ebCustomise	Select the buttons to show on the grid navigator. Set the buttons listed to True, all others to False.
    Width	25	Sets the grid width. You can also drag to change the width.

17. Set the Table to tblGraphExp. This links the grid to the memory table created previously. Set the Hook Control to gridExp, to link the navigator to the grid.

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

18. Enter the following code, to extract the expense information and populate the memory table, and display it in the grid:

    Sub RefreshExpenses
      Dim tblExpenses as Object
      SQLExpenses = "SELECT APANL.AnalysisCode as AnalysisCode" & _
      " ,APANL.AnalysisName as AnalysisName " & _
      " ,APANL.CurrentPeriodBalance as PeriodBalance " & _
      " ,APANL.LastYearBalance as LastYRBalance " & _
      " ,APANL.YearToDateBalance as YRToDateBalance " & _
      " ,APANL.GLAccountCode as AccountCode " & _
      " ,APANL.AccountClass as Class " & _
      "FROM APANL " & _
      "Order By AnalysisCode"
      tblExpenses = ExecuteSQL(SQLExpenses)
      tblGraphExp.empty
      tblGraphExp.AllowInsertDelete = True
      tblExpenses.First
      Do Until tblExpenses.EOF
        tblGraphExp.Append
        tblGraphExp.AnalysisCode = tblExpenses.AnalysisCode
        tblGraphExp.AnalysisName = tblExpenses.AnalysisName
        tblGraphExp.PeriodBalance = tblExpenses.PeriodBalance
        tblGraphExp.LastYRBalance = tblExpenses.LastYRBalance
        tblGraphExp.YRToDateBalance = tblExpenses.YRToDateBalance
        tblGraphExp.GLAccountCode = tblExpenses.AccountCode
        If tblExpenses.Class = "A" Then
          tblGraphExp.AccountClass = "Asset"
        ElseIf tblExpenses.Class = "C" Then
          tblGraphExp.AccountClass = "Capital"
        ElseIf tblExpenses.Class = "E" Then
          tblGraphExp.AccountClass = "Expenses"
        ElseIf tblExpenses.Class = "I" Then
          tblGraphExp.AccountClass = "Income"
        ElseIf tblExpenses.Class = "L" Then
          tblGraphExp.AccountClass = "Liability"
        End If
        tblGraphExp.Save
        tblExpenses.Next
      Loop
      tblGraphExp.AllowInsertDelete = False
      tblGraphExp.First
    End Sub

19. Create a routine called OnCreate to run when the form is created. This will call the RefreshExpenses sub-routine. Enter the following code:

    Sub OnCreate
      RefreshExpenses
    End Sub

20. Click the Layout tab. Click on the form, or select the frmFDForm component.
21. Go to the Events tab. Set OnCreate to OnCreate.
22. Add the following code to enable the Open and Print buttons:

    Sub OnNavExp(Sender as Object, Button as Number, ByRef Handled as Boolean)

    Dim Report as Object

    If Button = ebOpen Then

    Dim Form1 as Object

    Form1 = CreateObject("Accredo.APExpenseCodeForm")

    Form1.Find(tblGraphExp.AnalysisCode)

    Form1.GraphSelection = "Range of Periods"

    Form1.GraphPeriodFrom = StartPeriod

    Form1.GraphPeriodTo = EndPeriod

    Form1.GraphField2 = ""

    Form1.GraphField2 = ""

    Form1.GraphField = "PeriodActivity"

    End If

    If Button = ebPrint Then

    Report = CreateReport(tblGraphExp, " Expenses For Last 12 Months ")

    Report.Destination = "Screen"

    Report.Run

    Handled = True

    End If

    End Sub

23. Go to the layout tab. Select the navExp component. Go to the Events tab. Set OnClick to OnNavExp.
24. You can add a Link label as a title to the graph. Click the Link Label button, then click on the form.
25. Change the Caption to Analysis of Expenses.
26. You can change label size, position, font, colour or other properties.
27. 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 OnLinkExpenses

    Dim Form1 as Object

    Form1 = CreateObject("Accredo.APExpenseDetailedReport")

    Form1.Layout = "Expense Analysis Detail"

    End Sub

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