Method Community

In 2010, I had a custom screen built out that exports our customer data. One of the fields is ‘YTD Sales’ that has the following SQL override code to pull each customer’s YTD sales:

SELECT Cast(ROUND(Sum(Amount),2) as decimal(9,2))

FROM viewaccInvoice WHERE viewaccInvoice.Customer = viewaccEntity.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'

I have never had a problem with this grid before, but today this field is not calculating YTD sales. Every Tuesday I export this grid and send the data to our IT company; last week I didn’t have any problems and I haven’t changed anything on the screen. Only a few customers show sales in this field, all of them have a ‘last invoice date’ of 8/26/11-8/30/11 and their YTD sales are not ‘YTD’, it is just the total of their order that was invoiced in the past week. Is there an explanation for why this field would start to calculate 'week to date' sales? I need to send this file before the end of the day, so any help/suggestions is appreciated.

Thanks, BIrch

Hi Birch,

Under QuickBooks > Synchronize what is the Transaction History set to? If you click on Show options per transaction type what's set for invoices?

-Michael

Hi Michael - Transaction history is set to 31 days and invoices are set to 'all days'. These settings haven't been changed since we started exporting our customer data from this grid over a year ago, which is why it's so difficult to figure out why this field isn't calculating as it should.

Thanks, Birch

Birch,

Just to confirm if you take a look at your Invoice list, you do see invoices older then 31 days? I did a copy and paste of this code into one of my screens and it seems to work fine. 

-Michael

Yes, I just checked and all of our invoices are in Method (dating back to 2/22/10). If the code works, then could there be an action on the screen that is altering the data in the YTD sales field?

Thanks for your help,

Birch

Birch,

I don't think any actions on the screen would affect the SQL Override. I tested it again just to be sure and it works. In my test a took a copy of the Customer List screen(From the old crm) and entered your SQL override into the balance field and it seems to be working. 

The next step would be to do paid consulting, with paid consulting you would pay up front but if it turns out to be a Method Platform bug(and not an issue with the customization) then you will get the time credited back.

-Michael

Michael -

Apparently my daily full-sync that is scheduled for 7pm did not happen on Sunday night, 8/29. Yesterday I did a manual full-sync and the YTD sales field populated this morning! I'm not sure exactly how that would cause the problem, but it is fixed. Thanks for your help!

Birch

Michael,

I was trying to get the YTD Sales into a grid for a client...  So I copied this SQL override into the customer list grid balance field but I'm getting an "error generating the grid."  I'm doing something wrong, I am sure.  Can you make a screen shot of the advanced screen for the balance field in the grid design and let me see what you're doing?  I suspect it's that I've selected the wrong table...

BTW, I am using the Old customer center because it is based on the customer table and not the contact table.  :)

Best regards,

Carol

Hi Carol-

The above example posted by Birch also gives me a grid error when using it on a customer based grid, however this slightly modified version of it should work.

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'

The difference in this version is that I removed viewaccEntity from FullName.

-Michael