5 Replies Latest reply on Apr 13, 2012 9:01 AM by jhowlin

    Table Occurrences Driving Me Crazy!!!


      Table Occurrences Driving Me Crazy!!!


      Hi I have a database I use personally for marketing analysis. There are no other users and 100% data integrity and accuracy is not vital, but of course I want it to be as close to perfect as possible.

      Our business system records e-commerce orders and currently there are no customer records. So every ORDER comes in as a new record with customer data attached, such as shipping and e-mail and everything associated with it, and of course the product information for that specific order attached.

      I am trying to create another table occurrence of the ORDER table, look at just a found set of orders where I omit orders where the e-mail address and phone number are the same. This gives me a reasonable view of my UNIQUE CUSTOMERS. I'm calling this table occurrence of my ORDERS table "CUSTOMERS." 

      I then want to go back and take a given CUSTOMER and find all ORDERS where the E-MAIL address matches. This would give me a somewhat reasonable idea of orders of a given CUSTOMER. Currently I am trying to do this with a portal but am having no luck.

      I understand I have a self-referential join here and am just thoroughly confused. Am I approaching this correctly? Does anyone have any insight on how I might accomplish this?

      I frequently have to update this ORDERS table from an outside source, via import, so I want to avoid having to create a new CUSTOMERS table that is exported data from the ORDERS table. I tried that for some testing and was having problems: a CUSTOMERS layout I created that contained a portal into orders would only show ONE ORDER for each customer (when joined by the email field). When I tried another field, first name, it showed many different orders. I don't know if that was because of the @ symbol or something.


      Anyway, I'm uploading a diagram if that helps. DISREGARD THAT ORDERSCUSTOMERS TABLE IN THE CORNER - HE'S FOR SOMETHING ELSE.

      THANK YOU!








        • 1. Re: Table Occurrences Driving Me Crazy!!!

          I don't see how adding a new occurrence of Orders and changing the name to customers accomplishes this for you:

          gives me a reasonable view of my UNIQUE CUSTOMERS.

          It will contain the same data as your current orders table.

          Using your self join, you can set up a portal that will list all customers that have the same data in the BillEmail field.

          If your layout specifieds "Customers" in Layout setup | "Show records from", then the portal should list "Orders" in Portal Setup | "Show related records from". The reverse should also work.

          I think you should reconsider generating a customers table where you get one record for each email address. This table could be updated via an import each time you add more orders records.

          • 2. Re: Table Occurrences Driving Me Crazy!!!

            Is there a way to define that a table occurrence only contain data that meets a certain criteria, such as a find request?

            • 3. Re: Table Occurrences Driving Me Crazy!!!

              No. that's why I pointed that out. Table occurrences are simply a way to have multiple references to the same data table so that you can set up more than one relationship with the same table and to avoid a 'circle' in your relationships graph.

              You'd need to use a script that manipulates your found set--something that can be done with a layout based on either occurrence of your Orders table.

              Importing your data into a table where a unique values, validate always setting on your email address field to filter out duplicates may make more sense as a way to set up and maintain a table where you have just one record for each email address.

              • 4. Re: Table Occurrences Driving Me Crazy!!!

                I don't think you need a second table occurence here.

                I would make a new layout in a List view based on the "Orders" table.

                I would make a SubSummary part when sorted by "Emailadress"

                And I would sort that layout by "Emailadress". You won't need the body part.

                Then if you want you could make a new summary field in your orders table that counts the "Emailadress"

                Then put that summary field in the subsummary part and in a trailing total part on your layout and you get a nice view of the amount of orders you have pers customer and the total amount of orders.

                Then to calculate the amount of Unique visitors we need to use a little trickery as described here:

                I made you a really basic little example.


                Beware that the subsummary part only works when the records are sorted by email adress.


                EDIT: PhilmodJunks way of creating a seperate customers table is ofcourse the way to go.
                But in the mean time, my suggestion might be a solution for in the mean time :)

                • 5. Re: Table Occurrences Driving Me Crazy!!!

                  Thank you, guys. I actually used both of your responsed to get through this. I did end up creating a customers table, phil, and then ended up creating a report by title that counted and summarized by email address. So again, thanks for your help.