Method Community

Hi Method,

We are having a basic rules that each SalesRep can only have 200 Customer and 400 Leads. I haven't got a clue on how to show the Total count of Customer or Leads. Anyone have ideas?

Thank you

Hi Jonathan,

If you simply want to be able to evaluate and enforce this manually, the quickest way to obtain a count of how many customers and leads are assigned to a particular sales rep is through an SQL override.

Here is an example of a field I added to the QuickBooks_SalesRepList that uses an SQL override to count the number of customers assigned to each sales rep.

You can add any field, with the exception of RecordID fields, to the grid and override them with an SQL statement by selecting the advanced link.

You can do the same with an additional field for counting the number of leads instead.

If however, you want to be able to enforce these rules through actions on your screens, you will need to count and store these values in a field on a table. This is a process that will still require some manual input.

You will need to create a button that loops through each of your sales reps and counts the number of customers that are assigned to them, storing the result in a custom field on the table. This is not an efficient process and this action will likely take at least a couple of minutes to complete processing.

Once you have calculated this value, the actions on your screens can check agains it to evaluate if a sales rep can take on any additional customers / leads. Keep in mind that this value is only updated when you select the button you created.

The scope of this is quite large and the reliability isn’t great. Overall, my recommendation goes to the more simple SQL overrides and enforcing these rules manually.

I hope this helps.

-Audisho

Hi Jonathan,

I should add that SQL overrides can be tricky and if done incorrectly, your grid will fail to load.

If you want to forego all of this, you can customize the CRM_CustomerList and CRM_LeadList screens to include the SalesRep field on the grid. Then all you have to do is search the grid for “Contact” in the Rep / Type / Title heading and the sales rep’s initials in the SalesRep header. The results will include only a main contact for each customer that is assigned to the sales rep. The count will be displayed at the bottom of the grid as the number of records.

I hope this helps.

-Audisho

Hi Audisho,

OK this one is sure tricky and I couldn't get it running. There are no set of action that already build to count how many leads or customer that a SalesRep already have?

Thank you.

Hi Jonathan,

If you’re trying to retrieve the count to store it in a table, you will need to loop through your SalesRep table. Inside of that loop you will use a Retrieve Value From Table action for retrieving the RecordID (we’re not storing the RecordID value) FROM the Customer table WHERE the field SalesRep is Value From Row SalesRepInitial. Then set the If multiple results to Retrieve Count.

What this is doing is looping through your SalesRep table. Then for each sales rep, the retrieve action is counting the number of customers that are assigned to the current sales rep via the SalesRepInitial field.

In order to separate the count for your customers and your leads, you will need to use 2 of these retrieve actions inside of your loop. Each of these retrieve actions will have a second WHERE clause. 1 being IsLeadStatusOnly = No and the other being IsLeadStatusOnly = Yes. The resulting values can be placed in an action result to be entered into a custom field in your SalesRep table.

Let me know if this is more clear. If not can you tell me specifically what you are having trouble with?

-Audisho