Method:Classic General Q&A

Re: Overdue Invoices

Pioneer_Feed — Wed, 28 Oct 2015 15:44:49 GMT

Well that cleared the error, and populated the TxnDate field in the grid. New Problem - every date is the same - 10/26/2015 12:00:00pm.

Also, when you say have the 'Customer' Field on the grid, there is no field called 'customer' in the listbuilder table?

Am I missing something here?

Ted

Re: Overdue Invoices

Method_Morty — Wed, 28 Oct 2015 14:58:42 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Wed, 28 Oct 2015 10:24:16 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Mon, 28 Sep 2015 11:22:39 GMT

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!

Re: Overdue Invoices

Method_Morty — Fri, 25 Sep 2015 15:06:34 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Fri, 25 Sep 2015 14:48:44 GMT

We could, if I could do SQL!

Re: Overdue Invoices

Method_Morty — Fri, 25 Sep 2015 14:34:29 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Fri, 25 Sep 2015 12:19:04 GMT

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

Re: Overdue Invoices

Method_Morty — Thu, 17 Sep 2015 20:13:14 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Thu, 17 Sep 2015 10:51:31 GMT

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

Re: Overdue Invoices

Method_Morty — Wed, 09 Sep 2015 15:15:09 GMT

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

Re: Overdue Invoices

John M — Mon, 07 Sep 2015 15:50:51 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Mon, 07 Sep 2015 14:57:02 GMT

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

Re: Overdue Invoices

Pioneer_Feed — Sat, 05 Sep 2015 15:37:35 GMT

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?

Re: Overdue Invoices

fran — Thu, 03 Sep 2015 19:21:06 GMT

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.