Perform Find: Customers who are due to be called

[drumorgan]

I have Customer table with a field for "frequency", number format with "12" or "6" or "3" etc. for how often they want me to come back.

And, I have a Job table with a "date" field of every job.

I want to pull up a list of customers who the last job I did was over a year ago and they want me to call them every 12 months, along with customers who I worked on 6 months ago and they want me back every 6 months, etc. (then, I'll put them into a form letter to print and mail).

I am having trouble going through the "perform find" step of making a script.

First of all, I am thinking logically, I need to "omit" any customer who has a job that is less than the frequency. While I might have multiple jobs for one customer, say, every 6 months, I don't want a job that I did two years ago triggering the customer as due. So, I think if I omit all customers that have a recent job (within their specified frequency) I can get the found set I desire.

So, in the "perform find" step, I am allowed to pick a field and set it to an operator (less than) a value. But, I can't set it to less than another field.

As I am typing this, I am thinking that I might need to make a "calculation" field that will give me a simple boolean value.

Hmmm. Any thoughts?

[comment]

I am thinking that I might need to make a "calculation" field that will give me a simple boolean value.

That would be one way - since Filemaker will not perform calculations during Find. Alternatively, you could submit 3 find requests, one for each frequency.

[drumorgan]

OK, I am doing this in a couple steps.

I have created a field "months since last job". Since it comes up with negative numbers, I am adding 12 to some results...

If ( (Month (Get ( CurrentTimeStamp ) ) - (Month ( Jobs::Date ))) ≤ 0 ;

(Month (Get ( CurrentTimeStamp ) ) - (Month ( Jobs::Date ))) + 12 ;

Month (Get ( CurrentTimeStamp ) ) - (Month ( Jobs::Date )) )

Though, depending on if I use ≤ or < I either get jobs from a year ago looking like they just happened, or jobs I did this month looking like they happened a year ago. I need help around this conundrum

THEN, I have a field "Customer Due" where I simply compare this previous field to a job for a simple boolean result that I can now use in a found set.

If ( Frequency ≤ Months since last job ;

1 ;

0 )

I thought I would have trouble with it comparing randomly from all the jobs that a particular customer might have, but since I sort the jobs by date (descending) it seems to be only comparing to the most recent job.

Any advice on how to solve the first problem? I basically want a whole number corresponding to the month of last job from the current month.

[drumorgan]

Ah, I think I got it.

I needed to compare years, not months.

If ( (Year (Get ( CurrentTimeStamp ) ) > (Year ( Jobs::Date ))) ;

(Month (Get ( CurrentTimeStamp ) ) - (Month ( Jobs::Date ))) + 12 ;

Month (Get ( CurrentTimeStamp ) ) - (Month ( Jobs::Date )) )

Though, I can see an issue when I run into customers that are farther out than just one year since their last job.

[comment]

See:

http://fmforums.com/forum/topic/71509-count-no-of-month/page__p__338359#entry338359

[drumorgan]

Thanks for the link... I aligned the text (and substituted my real variable) to look at it. You sure this is right???

12 * ( Year ( Get(CurrentDate) ) - Year ( Jobs::Date ) )

+ Month ( Get(CurrentDate) ) - Month ( Jobs::Date )

- ( Day ( Get(CurrentDate) ) < Day ( Jobs::Date ) )

It sure seems to produce the right number, but I can't wrap my head around how.

AND, very cool, it seems to pick the 12 month from the actual DAY of the last job. Nice. Now I don't have to wait till the 1st of the month to see my list populated. Theoretically, I could have new ones popping up every day!

[comment]

Well, there are 12 months in a year, so the difference between two dates can be expressed in months as =

12 * endYear + endMonth - 12 * startYear - startMonth

Then you subtract 1 if the current day-of-month has not yet reached the original one.

[drumorgan]

Well, this totally works. But, could you step me through each line of this code? I think the "<" is throwing me. Is that sort of an "IF" operator?

[comment]

The expression:

Day ( Get(CurrentDate) ) < Day ( Jobs::Date )

returns 1 when true, 0 otherwise. This applies to all Boolean statements, so whenever you see something like:

If ( a = b ; 1 ; 0 )

you can safely shorten it to:

a = b

[drumorgan]

Wow. Very cool. Thanks. Please don't tell my wife that I didn't already know that. ;)