FD Tutorial: Dashboard Top Customers

This tutorial will show you how to create a form that will display your top customers in a pie graph in order of sales. This tutorial requires the AR and IN Modules.

First you will need to write a SQL Query to extract the top customers. Go to Navigator > Setup > SQL Query Builder.

1. From the list of table on the right, find the INHEAD table, and drag it into the centre of the form. This table contains Invoicing information.
2. Find the ARCUST table, and drag this into the form. This table contains customer information. You will see that a link is automatically created, linking the tables on the CustomerCode field. The link shows 1 at the ARCUST end, and at the INHEAD end. This shows that each 1 customer can have multiple invoices.
3. Select the INHEAD CustomerCode field. (You could select the ARCUST CustomerCode field instead.) The field is added to the grid at the bottom. This shows the CustomerCode field will be include in your query.
4. Select the ARCUST CustomerName field.
5. Select the INHEAD ExclusiveAmount field.
6. If you Execute the query now, you will get a list of all invoices, showing the Customer Code, Customer Name and Exclusive Amount of each Invoice. To go back to the editing the Query Layout, click Clear Results.
7. We want to see all the invoices for each customer, not each invoice. In the grid, beside INHEAD.CustomerCode and ARCUST.CustomerName, double-click the Group column to show a ü. Now the results will be grouped by these fields.
8. If you Execute the query now, you will see there is one exclusive amount for each customer. The Exclusive Amount results column shows the Minimum Exclusive Amount. We don't want to see the minimum Exclusive Amount, we want to see a sum of all the exclusive amounts for each customer. Click Clear Results, and go to the Query Layout tab.
9. In the grid, for the INHEAD.ExclusiveAmount row, set the Function column to Sum.
10. If you Execute your query now, you will see the total of Exclusive Amounts for each customer.
11. To see the results in order of Top sales, in the Query Layout, for the INHEAD.ExclusiveAmount, set the Sort to Descending. Change the Alias for INHEAD.ExclusiveAmount to Sales.
12. We want to make sure we are only looking at Invoices or Credits, not Quotes or Standing Orders. In the Query Layout tab, select the field INHEAD.DocumentClass. In the Criteria field, enter IN('I','C'). This ensures we are only looking at Invoices and Credits. Click the Show column to unselect it, as we don't want to see the Document Class.
13. We want to make sure we are only looking at Unposted and Posted Invoices, not Open or Deleted Invoices. Select the field INHEAD.PostStatus. In the Criteria field, enter IN('P','U'). Again, clear the Show column.
14. We now have our Query showing our customers in sales order. On the SQL tab, you can see the SQL Statement that has been generated from the Query Layout tab. You will use this in your form showing your top customers. Save your query as topcustomers.pfq. Your SQL should look like this:

    SELECT INHEAD.CustomerCode
            ,ARCUST.CustomerName
            ,Sum(INHEAD.ExclusiveAmount) as Sales
    FROM ARCUST
            INNER JOIN INHEAD ON ARCUST.CustomerCode=INHEAD.CustomerCode
    WHERE INHEAD.DocumentClass IN ('I', 'C')
        AND INHEAD.PostStatus IN ('P', 'U')
    GROUP BY INHEAD.CustomerCode
            ,ARCUST.CustomerName
    ORDER BY 3 DESC

    Next you will create your form to show the top customers. Go to Navigator > Setup > Form Designer.

15. Form Designer will open with a blank form.
16. Add a Label component. You can do this by selecting Label from the Component menu, then clicking the form, or by clicking the Label component icon, then clicking the form.
17. The Label properties are displayed. Change the Caption to Top Five Customers. Click the Font property to change the font, font colour and font size. The default font is Segoe UI. You may need to resize or move the label component.
18. Add a Graph component and a Memory Table component. We are going to write code to load our SQL Results into the Memory Table, and then have the Graph display the information. Resize the Graph component. You won't see the Memory Table component on the form, so it doesn't matter where you place it.
19. Double-click the Memory Table, or right-click the Memory Table and select Memory Table Designer. Add the following fields to the Memory Table:

    No.	Name	Display Label	Domain	Width	Visible
    1	CustomerCode	Code	AR Customer Code	14	Selected
    2	CustomerName	Name	Name	30	Selected
    3	SalesValue	SalesValue	Amount	10	Selected

20. Click Save. The FD Memory Table Designer will close.
21. Click on the Memory Table. The properties are displayed. Change the Name to tblTopCustomers.
22. Click the Graph. Change the following properties:

    Property	Value	Description
    Style	csPie	Display top customers in a pie chart.
    Name	graphTopCust	Name to refer to the graph component.
    Table	tblTopCustomers	This links the graph to your memory table.
    ShowLegend	False	Hide the legend.
    XDataFieldName	CustomerCode	Populate the X-axis with the Customer Code.
    XLabelFieldName	CustomerCode	Display the Customer Code as a label on the X-axis.

23. Double-click the graph, or right-click and select Graph Editor.
24. Add the following field:

    Field Name	Display Label	Show Key
    SalesValue	Sales	Selected

25. Click Save. The FD Graph Editor will close.
26. Rearrange the size of your form and the components on the form to display the way you want.

    Now we will enter code to execute the SQL Query, and use it to populate the Memory Table and the Graph.

27. Click on the Code tab at the lower left of the Form Designer.
28. We are going to create a sub-routine called RefreshTopCustomers.
29. Enter the following code:

    Sub RefreshTopCustomers
      tblTopCustomers.Empty
      SQL = "SELECT INHEAD.CustomerCode " & _
      " ,ARCUST.CustomerName " & _
      " ,Sum(INHEAD.ExclusiveAmount) as Sales " & _
      "FROM ARCUST " & _
      " INNER JOIN INHEAD ON ARCUST.CustomerCode=INHEAD.CustomerCode " & _
      "WHERE (INHEAD.DocumentClass in ( 'I' , 'C' ) " & _
      " AND INHEAD.PostStatus in ( 'U' , 'P' )) " & _
      "GROUP BY INHEAD.CustomerCode " & _
      " ,ARCUST.CustomerName " & _
      "ORDER BY 3 DESC"
      Dim tblTemp as Object
      tblTemp = ExecuteSQL(SQL)
      tblTemp.First
      Do Until tblTemp.EOF
        tblTopCustomers.Append
        tblTopCustomers.CustomerCode = tblTemp.CustomerCode
        tblTopCustomers.CustomerName = tblTemp.CustomerName
        tblTopCustomers.SalesValue = tblTopCustomers.SalesValue + tblTemp.Sales
        tblTopCustomers.Save
        tblTemp.Next
      Loop
      graphTopCust.RefreshData
    End Sub

    This code does the following:

    • Clears the tblTopCustomers memory table.
    • Sets SQL to the Sql query we created earlier.
    • Sets a temporary object called tblTemp.
    • Executes the SQL Query and loads the results into tblTemp.
    • Goes to the first record in tblTemp.
    • Adds the Customer Code, Customer Name and Sales to the tblTopCustomers memory table and saves the table.
    • Goes to the next record in tblTemp, and repeat the previous step, until it reaches the end of tblTemp.
    • Refreshes the GraphTopCust graph with information from tblTopCustomers.
30. Go back to the Layout tab.
31. Click the Form or select the Form from the Component drop-down selection above the list of properties and events. The Form properties are shown.
32. Click the Events tab.
33. Set OnCreate to RefreshTopCustomers.
34. You have now created a form to display your top customers. Click Save Form (Ctrl+S).
35. Click Run (Ctrl+R) to test the form.
36. You can create a shortcut for the form. Click Add Shortcut. The Script Shortcut Editor will open with your form.
37. Click Available on Toolbar. The Toolbar Name defaults to the Global toolbar. Set the button order to a large number (such as 200) to show the shortcut at the end, after other shortcuts. Set the Button Hint to Top Customers Form.
38. Close Form Designer.

You can run your form from Accredo. Your shortcut will appear as a button on the Toolbar, and you can also access if from the Shortcut Menu. The graph will display the total sales for your customers.

You can further filter your Top customers Dashboard, to show a selected number of Top Customers. See FD Tutorial: Filter Top Customers Dashboard.