Method Community

Is there a way to retrieve and view the total amount invoiced to date, per job/customer, on the customer grid?

Please provide the steps to customize this, if it is possible.

Thank you.

Hey Afrodite,

You can retrieve that information but it would require a SQL Override. 

First start off by creating a new field to your Contacts table and set it as a Money field.

Second add that field to your Contacts grid.

Now Go into the Advanced properties for the field and add the following SQL Code:

select Cast(ROUND(Sum(viewaccInvoice.Amount),2) as decimal(9,2))
FROM viewaccInvoice
WHERE viewaccInvoice.Customer = viewContacts.Entity

What is proccessing on the backend: Retrieve the Sum of the Amount field for each Customer in the Invoice table.

By default Subtotals: calculate Subtotals for this column is checked. You can remove that check if you don't want it displayed on the bottom of your grid.

- Mortaza

It worked perfectly.

Thank you.

Hi Mortanza,

Can you please show me how to also add the following colums for the amount billed :

Year to Date

Month to Date

Last Month

Last Year

Thank you,

Afrodite

HI Afrodite,

You are asking good questions.  With all of the dates you want, I would generate a "pivot grid" report using the report designer and then display that report on your screen. 

The pivot grid tool  gives you all sorts of date options.   Just a thought.  Also keep in mind how far back you have asked Method to bring in your transactions. 

Audisho may have other SQL code for you, but I just thought I would offer this suggestion.

Thank you for the input Fran. I will also look into this very helpful info. Im sure there is a lot I can do with those reports and my next step is to work on them. J

In the meantime , I am being asked to have this info available for the other time frames right on the Contact grid screen,  just like I did with the previous sql overide for total billed to date.

It is needed for an upcoming meeting, please sadvise if the sql overrides are available for these time frames.

Thank you,

Afrodite

Hey Afrodite,

Here is the SQL Override for Year to Date Amount:

SELECT Cast(ROUND(Sum(Amount),2) as decimal(9,2)) FROM viewaccInvoice WHERE viewaccInvoice.Customer = FullName AND viewaccInvoice.TxnDate > cast(year(getdate()) -1 as varchar(4)) + '/12/31' and viewaccInvoice.TxnDate < cast(year(getdate()) + 1 as varchar(4)) + '/01/01'

Using the same code you can follow the pattern to adjust it to any date you would like. Reference THIS article from MSDN which gets into detail on how to use the strings char and varchar to adjust lengths.

- Mortaza

Hi Mortaza,

I have a request that is very close to this solution.  Could you help me create a field for my contacts grid that will show me the total number of invoices.  I intend to use this to be an easy way to see customers that have re-ordered.

Thank you in advance,

Ben

Hey Ben,

Welcome to the Forums!

There are a few ways to retrieve that data into a Grid, here is one way:

SELECT Count(viewaccInvoice.RecordID)
FROM viewaccInvoice
WHERE viewaccInvoice.Customer = viewContacts.Entity

Using SQL Count function we retrieve the total number of unique Invoice RecordIDs related to Customer.

-- Mortaza

Hey Mortaza,

Thank you!!!  What a wonderful welcome to the Forums.  Your code worked like a charm.

A quick follow-up question:   How would you use SQL to retrieve "Number of days since most recent invoice"," if that is even possible?

Thanks a again!

Ben

Hey Ben,

Great question. Using documentation I was able to put it together correctly.

Firstly the functions:

DATEDIFF()

GETUTCDATE()

And the code:

SELECT DATEDIFF(day, Max(viewaccInvoice.TxnDate), GETUTCDATE())
FROM viewaccInvoice
WHERE viewaccInvoice.Customer = viewContacts.Entity

Layman's terms: take the difference in dates between the transaction date and the current date ~> display it in days.

-- Mortaza

Hi Mortaza,

Thank you again.  It worked wonderfully.  However, I am unable to use the 'greater than' or 'less than' filters on the customer list for the custom columns.  Any idea what I could do to get them working?

Thanks, 

Ben

Hey Ben,

I don't believe this is an option at the moment. When taking advantage of SQL Grid Overrides you have to keep in mind that the values you see are 'display data' only. The filters work by comparing the actual data in the fields to your query.

-- Mortaza

Hey Mortaza,

Thanks for the quick response.  Maybe there is another way we could approach this request.  I think it is a pretty good idea and that other users may find it useful as well.  Here is the objective:  To categorize our customers into groups based on their order history.  Example: Category 1 Customers order frequently and have higher dollar orders. 

To achieve this, we could apply logic to a combination of the code you already presented to create a new field:

Category 1 = (Number of Invoices > 2) and (Days since last order < 200) and (Sales Volume > $1,000)

Category 2 = (Number of Invoices > 1) and (Days since last order < 300) and (Sales Volume > $1,000)

Category 3 = (Number of Invoices > 1) and (Days since last order < 600) and (Sales Volume > $750)

Category 4 = (Number of Invoices > 1) and (Sales Volume > $500)

Then we could simply sort the Category column ascending or descending.  This gives our sales person a wonderfully prioritized current list.

What do you think?

Thanks again, Ben

Hey Ben,

I believe as Fran pointed earlier it will be a lot cleaner to bring this outside the grid and use a Pivot Grids and/or Charts. You can have 4 of each for each category or you can put all 4 categories into one Pivot/Chart. Using arguments you can filter each for how you want it set per category.

Please take a look at our help center articles on both (the videos will give you a visual of how they will look):

Pivot grids

Charts & Graphs

Once ready you can have these accessible on screens for your Sales team to look at, a lot easier on the eye. 

Take a look and let me know your thoughts on this route.

-- Mortaza