AnsweredAssumed Answered

Showing if a customer has ever bought a given product

Question asked by mactabby on Jul 21, 2017
Latest reply on Nov 23, 2017 by mactabby

Hi - new user with some limited past experience of relational databases (sql and access).


I am trying to create a simple (in theory) CRM system.  One of the things I want the system to do is show a flag on the customer record if they have never ordered the particular product which is the subject of the current campaign.   I want this to work automatically by adding products to a campaigns table.   I don't want to have to manually find and tag the customers, or create dedicated campaign customer lists, because of the number of products, customers and campaigns.


I currently have a customer table, an invoice table (joined to the customer table on customer number), an invoice line items table (joined to the invoice table by invoice number) and a campaigns table listing product codes that are the subject of each campaign.


By relating the campaigns table to the invoice line items table by product code I can get the campaign product to show in a portal on the customer layout when the customer has ordered that product.  But I can't work out how to do anything with it.  I either need to understand how to test the contents of that portal in order to display a notification somewhere, or else abandon this approach and use some sort of calculation somewhere else to sum the quantity field in the invoice items table for invoice items on invoices for this customer, where the invoice item matches any of the campaign products, and then use the result of that calculation to trigger a notification.


Have googled extensively and haven't found an answer which I have been able to understand.  Any help would be greatly appreciated.