FD Tutorial: Dashboard Current Period Invoices

Using the Form Designer, you can create a dashboard to show current invoice totals, with links to lists of the invoices. This tutorial requires the IN Module.

1. Go to Navigator > Setup > Form Designer.
2. Add four Labels to the form. Click the Label button, then click on the form, four times.
3. Set the Width of each Label to around 180, 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.
4. Set the Caption property of each label to be blank.
5. Set the Name properties of the four labels to: lblInvOpen; lblInvUnposted; lblInvPosted; lblInvTotal.
6. Move the labels next to each other.
7. 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.
8. Click the Link Label button, then click to add a link label in each of the first three labels.
9. Set the Caption properties of the three link labels to: OPEN; UNPOSTED; POSTED.
10. Set the Name properties of the three link labels to: linkInvOpen; linkInvUnposted; linkInvPosted.
11. 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.
12. Set the following properties on the Link Labels:

    Property	Value
    Alignment	taCentre
    ParentColor	True
    Transparent	True

13. Add a Label as a heading, above the existing labels. Click the Label button, then click on the form. Set the label Caption to Current Period Invoices - Exclusive Amounts.
14. You can change label size, position, font, colour or other properties.

    Next, you can write MaxBasic code to add the figures to the labels.

15. Click on the Code tab. Enter the following:

    Sub RefreshCurrentPeriodInvoices
      Dim PeriodToUse as Number
      Dim TotalSales as Number
      PeriodToUse = CurrentPeriod("AR")
      TotalSales = 0
      'Enter the SQL Query to extract the current Open Invoices
      strSQLInvoices ="SELECT INHEAD.PeriodID as PeriodID " & _
      " ,Sum(INHEAD.ExclusiveAmountBs) as ExclusiveAmount " & _
      "FROM INHEAD " & _
      "WHERE INHEAD.PeriodID = :PeriodToUse " & _
      " AND INHEAD.PostStatus = 'O' " & _
      " AND INHEAD.DocumentClass IN ('I', 'C') " & _
      "GROUP BY INHEAD.PeriodID " & _
      "ORDER BY INHEAD.PeriodID "
      tblInvoices = ExecuteSQL(strSQLInvoices, PeriodTouse)
      lblInvOpen.caption = " " & CHR(10) & " $" & FormatNumber(tblInvoices.ExclusiveAmount, ",0.00")
      TotalSales = TotalSales + tblInvoices.ExclusiveAmount
      'Enter the SQL Query to extract the current Posted Invoices
      SQLInvoices ="SELECT INHEAD.PeriodID as PeriodID " & _
      " ,Sum(INHEAD.ExclusiveAmountBs) as ExclusiveAmount " & _
      "FROM INHEAD " & _
      "WHERE INHEAD.PeriodID = :PeriodToUse " & _
      " AND INHEAD.PostStatus = 'P' " & _
      " AND INHEAD.DocumentClass IN ('I', 'C') " & _
      "GROUP BY INHEAD.PeriodID " & _
      "ORDER BY INHEAD.PeriodID "
      tblInvoices = ExecuteSQL(SQLInvoices, PeriodTouse)
      lblInvPosted.caption = " " & CHR(10) & " $" & FormatNumber(tblInvoices.ExclusiveAmount, ",0.00")
      TotalSales = tblInvoices.ExclusiveAmount
      'Enter the SQL Query to extract the current Unposted Invoices
      strSQLInvoices ="SELECT INHEAD.PeriodID as PeriodID " & _
      " ,Sum(INHEAD.ExclusiveAmountBs) as ExclusiveAmount " & _
      "FROM INHEAD " & _
      "WHERE INHEAD.PeriodID = :PeriodToUse " & _
      " AND INHEAD.PostStatus = 'U' " & _
      " AND INHEAD.DocumentClass IN ('I', 'C') " & _
      "GROUP BY INHEAD.PeriodID " & _
      "ORDER BY INHEAD.PeriodID "
      tblInvoices = ExecuteSQL(strSQLInvoices, PeriodTouse)
      lblInvUnPosted.caption = " " & CHR(10) & " $" & FormatNumber(tblInvoices.ExclusiveAmount, ",0.00")
      TotalSales = TotalSales + tblInvoices.ExclusiveAmount
      lblInvTotal.caption = " TOTAL " & CHR(10) & " $" & FormatNumber(TotalSales, ",0.00")
    End Sub

16. 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
      RefreshCurrentPeriodInvoices
    End Sub

17. Click the Layout tab. Click on the form, or select the frmFDForm component.
18. Go to the Events tab. Set OnCreate to OnCreate.
19. Click Save Form (Ctrl+S).
20. Click Run (Ctrl+R) to test the form.

    Next, you can link the Link Labels to lists of invoices.

21. Click on the Code tab. Enter the following code to display all current open invoices:

    Sub ONLinkInvOpen
      Dim Form1 as Object
      Form1 = CreateObject("Accredo.INInvoiceListForm")
      Form1.AutoSelect = True
      Form1.ToggleShowAll
      Form1.Selection = "All Documents"
      Form1.PeriodSelection = "Period"
      Form1.Invoice.FilterSort.Filter = "(DocumentClass=""C"" Or DocumentClass=""I"") And (PostStatus=""O"")"
    End Sub

22. Enter the following code to display all current posted invoices:

    Sub ONLinkInvPosted
      Dim PeriodToUse as Number
      PeriodToUse = CurrentPeriod("AR")
      SQLInvoices ="SELECT INHEAD.DocumentID as DocumentID " & _
      " ,INHEAD.CustomerCode as Customer " & _
      " ,INHEAD.DocumentDate as Date" & _
      " ,INHEAD.DocumentNo as InvoiceNo " & _
      " ,INHEAD.DocumentClass as Type " & _
      " ,INHEAD.GrossAmountBs as GrossAmount" & _
      " ,INHEAD.PeriodID as PeriodID " & _
      " ,INHEAD.ExclusiveAmountBs as ExclusiveAmount " & _
      "FROM INHEAD " & _
      "WHERE INHEAD.PeriodID = :PeriodToUse " & _
      " AND INHEAD.PostStatus = 'P' " & _
      " AND INHEAD.DocumentClass IN ('I', 'C') " & _
      " ORDER BY INHEAD.DocumentID "
      tblPostedInvoices = ExecuteSQL(SQLInvoices, PeriodTouse)
      tblPostedInvoices.Fields.Item(5).Total=True
      tblPostedInvoices.Fields.Item(7).Total=True
      Report = CreateReport(tblPostedInvoices, " Posted Invoices For Period " & PeriodName(PeriodToUse))
      Report.Destination = "Screen"
      Report.Run
    End Sub

23. Enter the following code to display all unposted invoices:

    Sub ONLinkInvUnposted
      Dim Form1 as Object
      Form1 = CreateObject("Accredo.INInvoiceListForm")
      Form1.AutoSelect = True
      Form1.ToggleShowAll
      Form1.Selection = "All Documents"
      Form1.PeriodSelection = "Period"
      Form1.Invoice.FilterSort.Filter = "(DocumentClass=""C"" Or DocumentClass=""I"") And (PostStatus=""U"")"
    End Sub

24. Link the code to the link labels. Click the linkInvOpen link label, then go to the Events tab. Set OnClick to ONLinkInvOpen. Set the OnClick event for linkInvUnposted to ONLinkInvUnposted. Set the OnClick event for linkInvPosted to ONLinkInvPosted.
25. Click Save Form (Ctrl+S).
26. Click Run (Ctrl+R) to test the form. When you click on the Link Labels you will see the relevant list of Invoices.