I currently have a customer table, an invoice table...
You didn't mention a Products table. I presume you have one; can you verify?
By relating the campaigns table to the invoice line items table by product code...
Can one campaign target multiple products?
1 of 1 people found this helpful
Here are my thoughts:
I'm using a custom function called AntiValuesList Custom Function that shows me from list A the values NOT in list B. Here I'm counting the Campaign Products as ListA. Have you used Custom Functions before? It does require FileMaker Pro Advanced.
So what you'd need to do is get a list of the products that the customer bought (Invoice line items) and a list of the products in the campaign and compare the two. If the returned result has a ValueCount () of greater than 0, the customer hasn't yet purchased everything in the campaign list. With this function you can display the products they've yet to buy.
I assume a customer is assigned to a campaign. Is there a separate field in the customer table that holds this value? If so, you can relate the campaign products table directly to the customer, pulling the list of campaign products, and doing the comparison.
The above example has a calculated field on the far right. I wouldn't do that in practice. Instead I'd use scripting to compare and then return the results of the custom function. This field gets updated each time the customer buys something. You can use it in a calculated manner, however.
I'll throw together a small demo and see if that helps you see how this can work.
EDIT: Your post mentions one campaign product, so you might not need to use that custom function.
I'll edit my demo to match this up
Thanks for answering. It doesn't currently have a products table and at the moment I'm not looking at multiple product campaigns, but it's not an issue for me at the moment as I'm not looking for an alternative solution to the question 'How do I set up product campaigns' I'm looking for an approach to the abstracted question 'When looking at a record in Table A, how do I show an alert/notification if items from Table D are not present in Table C, where Table C is related to Table B which is related to Table A'
In this case A is Customers, B is Invoices, C is Invoice Items and D is campaign items.
Thanks Jeremy - I'll have a look at this and let you know if it helped.
edit: and no, in this approach I'm trying to avoid adding customers to a campaign - I literally just want a flag to pop up automatically to the salesperson when they are looking at a customer record when one of the live campaign items is not present in their invoice items. I wouldn't have this challenge if I was adding customers to campaigns, but for business reasons I don't want to be doing that at this time.