Help with a Find

[Ron Cates]

Hi all. Looking for a little help on this one. We are a calendar company that sells advertising on calendars we produce. So for each calendar a customer will only have a purchase once per year. What I am trying to do is create a found set of customers who bought an ad on a particular clendar last year but didn't buy one this year.

The tables involved are:

Jobs ( The calendar in question )

Customers

Tickets ( Our equivalent of an invoice )

So I have started by doing a find for all tickets ( invoices ) for Job A for both this year and last year. Now I need to figure out how to get to the customers within that found set that only have Tickets for last year.

Any Ideas?

Thanks in Advance

Ron

[bcooney]

How about if you find all tickets for JobA (this year's calendar). Go to related Customers. Now you've got all of this year's calendar's Customers. Then, show omitted. These should represent all customers that didn't buy this year's calendar.

Oops. That's not exactly what you're asking. They need to have bought last year, but not this year.

How about a calc in Customers? If there is a relationship to JobA but not JobB, then 1. Then find on 1.

Edited August 19, 2009 by Guest

[Ron Cates]

Hmm. I am not sure I quite follow yet. But to clairify further, there is no direct relationship between the customer and the job because a customer may buy an ad on more than one calendar each year. The relationship to job is from the ticket. Hope that makes sence.

[bcooney]

What is the line item table on the Ticket? Does that contain a JobID? Actually, can you please detail the relationships?

[Ron Cates]

Ok, I created a graphic to display the relationships in question. So we have Customers, Tickets and Lines like most invoicing solutions. On the other side we have departments which reffers to the Sheriff / Police Departments that we do the calendars for. A Job would be one calendar for one department. In this case let's just say it's the Hazzard County Sheriff's Calendar. In the Job records we have two records for Hazzard County. One for the 2009 calendar and one for 2010. The reason that the Job is related to the ticket is because each calendar is a fund raising project and has to be invoiced seperately for fund allocation purposes. So for example if a customer were to buy an ad on two different calendars this year, they would recieve two different invoices. One for each calendar. If on the other hand they were to buy two ads one the same calendar, that would be one invoice with two line items.

[bcooney]

Thanks for the diagram. I'm going go with my suggestion of a calc in Customers. Use List ( ) to build a list of JobIDs related to customer, then use filtervalues by the two JobIDs you are interested in. You'll need some logic in the calc, like if JobIDA is in the list, but JobIDB is not, then 1.

[Ron Cates]

Thank you for the help. It looks like I'm going to have to put this one on hold for the rest of the week. But that should give me a place to start when I get back to it hopefuly next week. I'm sure I'll need some more help to put it all together so I'll keep you posted.

Thanks Again,

Ron

[comment]

I believe a find in the Customers table, with two requests, should do it: the first request searches the related field in Jobs (or Tickets) for the last year's calendar; the second request searches the same field for this year's calendar and omits.

[Ron Cates]

Ok. I am finally able to get back to this project and I have been thinking about it here and there to see if I could come up with an approach. For the sake of simplification, lets disreguard the Job aspect for now. What I am truely after is a found set of customers who bought from us last year but not this year. So in the traditional Invoices - Customers relationship, how would I do that?

My thoughts (and I could be way off here)

1. Find all invoices for 2009

2. store those Customer IDs in a variable

3. Find all invoices for 2010

4. store those Customer IDs in another variable.

5. Filter out the IDs in the 2010 variable from the 2009 variable.

Your thoughts?

Thanks

Ron

[Ron Cates]

I have been moving forward along the lines I laid out above. I have a script that now finds all invoices from last year and collects all Customer IDs, then does the same for this year. So now I have two variable. $thisyear and $lastyear. Now how do I filter $thisyear out of $lastyear?

[comment]

What I am truely after is a found set of customers who bought from us last year but not this year.

Isn't this practically the same thing as before? If you do the find from a layout of Customers, searching in the related date field in Invoices, you can do this with a single find with two requests.

[Ron Cates]

I am not sure why I had such a hard time grasping the concept, but yes, it is that simple. Did the find from the customers table with two requests as you suggested and got the found set I was looking for.

Thanks comment