Method Community

Hey Ted,

That may relate to what you have the Transaction history set to.

You can find it under the Tab: QuickBooks > Synchronize.

Please verify what you have that set to. If you want to retrieve data before those days, change it to a larger range then execute a Full Sync.

-- Mortaza

HI Pioneer_Feed and Mortaza,

I was reading this thread and a few thoughts came to mind.  It seems like you may need to consider what if a customer you tagged as a wormer customer purchases something other than wormer on a Sales Receipt?  You don't want those Sales Receipts.  You may have to find Wormer Items from Sales Receipt lines.  and another thought, in QB (or in Method) add the  Due Date field to the Sales Receipt.  Fill it in for wormer expirations. Then it wouldn't matter is customer purchased other stuff, you know you have a wormer expiration. Then you can use Mortaza's SQL and check for Due Date instead of Last Invoice Date.

If your Sales Receipts were generated in method, you could even add the expiration in months to wormer item and have it automatically calc a due date on the Sales Receipt.

Just some thoughts.  I have dogs so the thread caught my attention.

Hi Mortaza - Fran!

Mortaza, I like the sound of what Fran is sugesting, so I created a field in contacts called DueDate, and added  the following version of your SQL to the advanced section,

select Max(viewaccDueDate.TxnDate)
FROM viewaccDueDate
WHERE viewaccDueDate.Customer = viewContacts.Entity

But unforntunately, when I view the screen I get this message:-

The table this grid is based on does not exist. Go to Customize > Table & Fields, edit the table and click 'Finished Editing Fields' to regenerate the table.

This only occurs when the SQL is present, otherwise the field displays Ok. I am assuming I have slipped up with the SQL.

Also, I changed the Transaction History setting to 360 days, and it has brought in a few more results, but still not all. Could this be caused by duplicate entries?

Hi Mortaza,

Fran's comments got me wondering last night, and maybe I am overthinking this. Looking at the Listbuilder screen, it will do half of what I am trying to achieve. So with a few small adjustments, maybe this would give me a solution.

On Step one, I only need the lower half of the screen ( fill from invoices ), and I don't need the Balance filter -I would replace it with a filter for a wormer tag. I have looked and can't spot where the actions are taking the balance figure into the loop through grid. to remove it.

I don't need step three - Actions

Can I process the list in mailchimp? Not a major part, but would be a bit more professional if the format of the mail matched the newsletter we send via MailChimp.

Thoughts?

Ted

Greetings.

I was looking at your post and i thought yea i have done this before.  In my business landscaping i was having a issue trying to group plants, sprnkler parts, sprinkler repair parts, pesticides, fertlizers, lighting, rock, mulch, etc.

For you, you have products like tick, wormer, eye, food, health related, play related, last hair cut, last vet visit.  You see the grouping I did?

What I did was i added a new field in inventory. I called the field type.  Its a drop down list and it contains this.  P for plants, SP for sperinkler parts, SRP for sprinkler repair parts etc....

For you I would put T for tick, W for wormer, E for eye, F for foood etc. 

Now you have a way to group all your wormer products in a grid. You base a grid on your sales order table.  When you open up your new grid you insert the month and year on the sale date grid to filter down three months ago. Now you have a grid of customers 3 months ago that have a wormer product.  

If you like this idea there is more that needs to be done to make this work.

John

Hey Ted,

Instead of going through the trouble of customizing the screen, you can:

- Click on Fill list with Contacts

In there you have under Tags: "Include contacts" in your case you would choose the Tag 'wormer.'

That will take you right into Step 2 which will show you the list of Customers that fit the conditions in the Grid.

You cannot process this list and send out the emails directly from MailChimp out of Method. On Step 2 you do have an option to Export the Grid to Excel.

You can then import that list into MailChimp I found an article on the MailChimp knowledge base HERE. You can now send those emails out MailChimp using the list you gathered from Method.

Step 3 & Step 4 are Optional you can either go ahead and move to Step 5 or remove the Tabs completely customizing the screen.

-- Mortaza

Hi Mortaza,

Thats ok to a point. Unfortunately the filters are either/or, I can filter on tags or by date range but not both. I can't see how to get it to do both. If we can get this to work, then that would be ideal!

I can live with the import part!

Cheers

Ted

Hey Ted,

Another route you can take is choose Wormer from the 'Insert contacts into List Builder' and after you have the Contacts with that Tag you can then filter the Grid by Date Range.

On Step 2, customize the screen and add two DatePicker objects to the screen. 

DatePicker1 & DatePicker2

Now in the ListBuilder Grid let's add a new Filter View. Using the TxtDate field filter with 'Within Date Range' and choose the two on screen DatePickers.

Now add a new button object on screen: 'Export Grid to Excel by Date'

First action - Grid: Set Filter View (this action will change the ListBuilder Grid to the new Filter we created)

Second action - Grid: Refresh Grid

Last action - Grid: Export Grid to Excel 

What this will do will change the Filter View of the Grid to the new Filter we created, refresh, lastly export the Grid to Excel.

Try this route, and let me know of the outcome.

-- Mortaza

Hi Mortaza,

The customizations all work, only problem is the TxnDate and TxnType fields ( I added the latter for reference ) do not appear to be populated for any of the tagged customers. I have set the synchonisation  to show all records going back 365 days. Having checked QB, there should be several that would come into the bracket.

Thoughts?

Ted

Hey Ted,

Very odd. Can we try overriding the TxtDate field on Grid with SQL? 

Give that a shot and let me know if it works for you.

-- Mortaza

We could, if I could do SQL!

T

Sorry Ted,

Thought we had the code posted earlier in the thread:

SELECT Max(viewaccInvoice.TxnDate)
FROM viewaccInvoice
WHERE viewaccInvoice.Customer = viewContacts.Entity

try that and follow up.

-- Mortaza

Hi Mortaza,

No appology needed, Just me not realising you meant the earlier code!

I took the SQL from your last post and put it into the advanced section for the TxnDate field. As soon as the screen opens I get the following message:

Once again I suspect it is me!

Hi Mortaza,

Any thoughts on the above? I got rid of the error by removing a Get Sum commmand from the advanced screen properties. I still don't get any results in my grid, so I replaced it for now.

I put the SQL in the advanced section for the TxnDate field.

Ted

Hey Ted,

I modified the code and this should load for you, give it a try and let me know if it does.

SELECT Max(viewaccInvoice.TxnDate)
FROM viewaccInvoice
WHERE viewaccInvoice.Customer = Customer

*note make sure you have 'Customer' field on your Grid listed.

-- Mortaza