5 Replies Latest reply on Aug 15, 2017 2:00 AM by mactabby

    Showing if a customer has ever bought a given product

    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.

        • 1. Re: Showing if a customer has ever bought a given product
          jbrown

          Hello mactabby. I have read thru your post and would be glad to provide some advice. Let me study it and get back to you. I just wanted to make sure you knew someone saw it.

          Thanks

          Jeremy

          • 2. Re: Showing if a customer has ever bought a given product
            DanielShanahan

            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?

            • 3. Re: Showing if a customer has ever bought a given product
              jbrown

              Here are my thoughts:

              Essentially you need to find the list of products that are in the campaign list but are not something the customer has ordered. Sort of like what you see here: Screen Shot 2017-08-08 at 11.57.09 AM.png

               

              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

              1 of 1 people found this helpful
              • 4. Re: Showing if a customer has ever bought a given product
                mactabby

                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.

                • 5. Re: Showing if a customer has ever bought a given product
                  mactabby

                  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.