Method Community

This is regarding the Customer / Contact list.

On QuickBooks, I have a list of clients, and multiple sub-clients under each client. On Method, by default, it shows as follows:
 

In this photo, "Matthew" is the client on QuickBooks and the sub-clients of Matthew are "#2585-1" and "#2585-2". Each sub-client will have a balance of it's own, but the client won't have any balance directly inputted into it. Is there a way to have the Client show the total balance of all it's respective sub-clients? For example, in the photo shown, the main client "Matthew" will have a balance of 65.00. This question also relates to showing all transactions of all sub-clients when I click on the main client on method and go to the "Transactions" table.

Hi Jason,

In Method for QBD, the TotalBalance field can be used to display the balance of the customer plus the balance of all sub-customers. This field is already linked to your Contacts table so all you need to do is add it to the grid on your Customers and Contacts List screen.

QBO doesn’t use the TotalBalance field, so you’ll have to calculate this on your own. I’ve had success using the following SQL override script, but I’ve done very minimal testing so your mileage may vary.

CAST((SELECT SUM(viewaccCustomer.Balance) FROM viewaccCustomer WHERE viewaccCustomer.FullName LIKE (viewContacts.FullName + '%')) AS decimal(12,2))

Alternatively, you could create a button with an action set that would calculate this value for you and store it in a custom field. The value wouldn’t be dynamic, as you would need to run the action set to calculate it, but you would be able to include very specific filtering for making sure that you calculate the correct value. The action set may take a long time to run depending on the number of customers you have.

As for displaying the transactions for all sub-customers in the grid, you’ll need to re-build the transactions grid as a detached grid. This will allow you to include transactions that are not directly related to the current customer. You can mirror the logic on the original grid with the addition of a filter for only including transactions related to the current customer and all sub-customers. The following should work for this.

I hope this helps.

Audisho

Both of these solutions were EXACTLY what I was looking for! Thank you for your prompt and detailed responses.

Regarding the SQL Override for the Total Balance, I will continue to monitor this column to see if any bugs occur and will modify it accordingly. But as of now, it is working as needed!