FD Tutorial: Filter Top Customers

You can filter the Top Customers dashboard form created in the FD Tutorial: Dashboard Top Customers. This tutorial will show you how to limit the number of customers shown.

1. Go to Navigator > Setup > Form Designer. Click Open (Ctrl+O) to open your Top Customers form.
2. Click Spin Edit and add a spin edit component to the form. You will use this to select the number of customers to show.
3. Change the spin edit LabelCaption to Limit. Change the Name to eSPLimit.
4. Click the Code tab. Change the code as follows:

   Sub RefreshTopCustomers
     tblTopCustomers.Empty
     if eSPLimit.value < 1 then eSPLimit.value = 5 ' < ADDED
     NumTopCustomers = eSPLimit.Value ' < ADDED
     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
     Count = 1 ' < ADDED
     Do Until tblTemp.EOF or (Count > NumTopCustomers) ' < ADDED
       tblTopCustomers.Append
       tblTopCustomers.CustomerCode = tblTemp.CustomerCode
       tblTopCustomers.CustomerName = tblTemp.CustomerName
       tblTopCustomers.SalesValue = tblTopCustomers.SalesValue + tblTemp.Sales
       tblTopCustomers.Save
       Count = Count + 1 ' < ADDED
       tblTemp.Next
     Loop
     graphTopCust.RefreshData
   End Sub

   If the Spin Edit is 0, we set it to 5 as a default. We have added a field called NumTopCustomers that takes the Limit from the spin edit component. We have also added a Count, to count how many records we have added. The Count gets incremented each time a record is added. We have changed our loop condition to check whether Count is still below the Limit.

5. Click the Layout tab. Select the spin edit component. Click the Events tab. Set OnAfterChange to RefreshTopCustomers.
6. You can now select the limit of Top Customers you want to see in your form. You have now created a form to display your top customers. Click Save Form (Ctrl+S).
7. Click Run (Ctrl+R) to test the form.