Routine to filter dates, Help Please.

[Keith Hubert]

My customer wants to write to his customers that he has done work before, but not to write to them if he has done work for them since a variable date.

eg. Work was done for Mrs Smith in 2009 and 2010, therefore, if the variable date is 31/12/2009 Mrs Smith will NOT be included in the list of customers to be written to.

My question is, what is the best method required to capture the required data to print from?

[Tom Cone Jr]

Re: Routine to filter dates, Help Please.

Keith, there's probably a more efficient method, but you could "mark" or "tag" customer records that should NOT get a letter (i.e. those for whom work has been done recently), and then send the letter to customers who haven't been marked or tagged.

This problem comes up now and then. You need a list of records where something you are tracking has NOT happened. It's hard to build a list of what you can't see, so to speak. Annual membership renewals are another good example. Members renew. Renewals are tracked. Non-renewals are not recorded. Now give me a list of members who didn't renew.

[Keith Hubert]

Re: Routine to filter dates, Help Please.

Hi Tom,

Thanks for getting back to me on this.

This seems an interesting suggestion to mark unwanted records, which might be better than my idea of creating a temp table and not using records if the Customer_Id is in both tables.

Your comments are appreciated.

[DVSOL005]

Re: Routine to filter dates, Help Please.

Hi Keith,

Wouldn't you want to register which customers have received a letter so that you avoid sending more than one letter?

I would focus on the customers that meet this variable date rather than marking the ones that don't.

As for Tom's example, isn't it true that once you have a renewal date for customers that it's rather simple to select the ones that haven't renewed?

Regards,

[Mike Wilson]

Re: Routine to filter dates, Help Please.

I guess I'd get 2 lists of customers. First list is ALL CUSTOMERS. Second list is customers with work done from starting date tp present, and then subtract the second list from the ALL list to get the list of customers to petition.

[Keith Hubert]

Re: Routine to filter dates, Help Please.

Hi Mike,

Thanks for your suggestion.

then subtract the second list from the ALL list to get the list of customers to petition

What process should I use to accomplish that task?

[Tom Cone Jr]

Re: Routine to filter dates, Help Please.

Jo, thanks for your comments. I suppose whether its easy to list non-renewing members or not depends on the particulars of each application. In the case I had in mind the organization tracked the receipt of dues payments. Since multiple types of dues might be owed by any particular member the organization did not record the expiry date for any particular dues payment. They did not track renewal dates. Only the receipt of dues payments. Building a list of members who did not pay dues is how they sent out "renewal notices". So you see the problem. Since they tracked only the receipt of dues payments, they didn't have an easy way to build a list of non-payers. I thought this was similar to Keith's scenario. Is it not? I assume keith's client does not track, in the customer record, the date of the last job or sale. I think that's a valid assumption since if that information were available in the customer record he wouldn't be asking the question in the first place, right?

[DVSOL005]

Re: Routine to filter dates, Help Please.

Tom,
Thanks for your extensive explanation.
I'm beginning to see the issue as you described....

Reading through Keith's post however, I have to assume that job dates are recorded and that there is a relation between a customer table and a job/order table. So he can cycle through the customer table and check for work done. If yes, check if all jobs for that customer have a date older than 01/01/2010. If yes, than that customer should receive a letter.

Regards,

[G Gabriel]

Re: Routine to filter dates, Help Please.

So many questions along this line and for some reason the easy solution never seem to come up at first!

All you need is a summary operation and a query on the summary table.

Need further elaboration?

[G Gabriel]

Re: Routine to filter dates, Help Please.

Given that the previous question seems rhetorical, I might as well answer it now. Here you go:
Just go to the Letter tab and print the letter "test".

[Keith Hubert]

Re: Routine to filter dates, Help Please.

Gabe,

Thanks for you input.

What you have failed to appreciate is that the dates are in the child table of Jobs, with parent table as the Customer. ie. A Customer can have many Jobs over a period of time.

The object of the exercise is not to write to the customer if they have had work done after an input date.

[Tom Cone Jr]

Re: Routine to filter dates, Help Please.

G, thanks. That's very helpful. I forgot that the Summary Operation can do more than total. Here, your example, uses a summary operation to find the "Max" work date value... i.e. the most recent work date. This is dumped to a new table which can be used to do the report or letter with a simple filter excluding the more recent work dates customers. Very nice.

-- tom

[G Gabriel]

Re: Routine to filter dates, Help Please.

ie. A Customer can have many Jobs over a period of time.

I figured that already, but how would I have known about the child table? When there was no mention of such? In fact there was no mention of multiple dates per customers, I assumed that to be the case and hence the reason of the difficulty.
Regardless, the same idea applies.
1-Summarize the child as I did
2-Have a set with the parent 1:1 sum_child
3-Print the letter based on that set.
Same vernacular.

[MikeC]

Re: Routine to filter dates, Help Please.

More mud to put into the water maybe.
I have used the Summary method, the two table method using a Subtract operation, and also creating two lists which then uses the word_subtract() function. All a matter of what someone is most comfortable with as they all work.

[G Gabriel]

Re: Routine to filter dates, Help Please.

2 tables method?
with subtract operation?
2 lists?
with word_subtract?

Sorry, but this sounds to me like a two-headed monster that doesn't know which way to look, much less which way to go!

Sorry. No such a thing will work here.

What will work though is an array (or a list) in a UDF picking up the record with the most recent date for each customer, i.e. home-made summarize operation as opposed to alpha's built-in.
Which is better?
Test both and report.

No, don't think of dbmax() or anything similar unless you want to spend a lot of time at the beach. I personally like the home-made one and was going to write a UDF but didn't want to spend the time, specially when you never hear a word of thanks from Jordon Brown.