2 Replies Latest reply on Jul 9, 2016 10:51 AM by aramandhammer

    Customer Order Counter | Show All Customer Orders | Prevent Duplicate Customers


      Hello Everyone,


           Let me start off by saying that I'm completely new to FIleMaker Pro. I currently have FileMaker Pro Advanced Version 14.X.

      I have been through the FileMaker Pro tutorials from Lynda.com (FileMaker Pro 15).

      The guy who explained everything did a great job of teaching simple stuff and not stuff that can be used in everyday application. Well, I guess in my case this is probably not something everyone does everyday.


      So anyway, let me get straight to the point.

      My questions are as follows:


      Customer Order Counter:

      Is there a script that can basically show me how many times 1 customer has ordered from me??...I have searched all throughout google in many different queries and nothing turns up with any useful information.


      Show All Customer Orders

      So following the Customer Order Counter, I want to create a button that runs a script and finds all the Orders from that specific customer. Or maybe a portal...if that's possible.


      Prevent Duplicate Customers

      So every time I fill out a order, its usually from a new customer...I may have hundreds upon hundreds of customers and obviously I cant keep track of all of them and unknowingly I might add a customer that already exists in the database.


      Question: Is there a script where before adding the new customer it performs a search to see if the customer already exists?


      I hope my explanation is understandable.

      Let me just say thank you ahead of time to everyone who contributes to the discussion.

      If you guys need further explanation please let me know and I will do my best to elaborate.




        • 1. Re: Customer Order Counter | Show All Customer Orders | Relationships | Prevent Duplicate Customers

          I'm, imagining that you already have a relationship between your Customers and your Orders tables. I'm not sure what naming convention you use or how you group your table occurrences, so just for clarity I'm going to say that you have "CUS_ORD" as a table occurrence based on Orders, and linked to Customers, where the primary key in Customers matches the foriegn Customers key in the Orders table.


          To get, from Customers, a count of CUS_ORD, there are two common methods: One is to create a calculated field in Customers that = Count ( CUS_ORD::primaryKey ). Another is to create a summary field in Orders (let's call it "recordCount") that is a summary field, count of primary key. If you show that field from CUS_ORD, on a Customers layout, it'll show the count of related orders.


          To show all customer orders on a Customer record, you can show a portal based on CUS_ORD. To go to those records, you would use the script step Go To Related Records, which you could use as a single step on a button, or in a script (with other steps), also from a button.


          As for preventing duplicates, there are many tools and methods at your disposal. The simplest is probably just ensuring that some field, such as customer name, is validated to allow only unique records. In Manage Database, go to that field in Customers, double-click it, and go to the validation tab. This and many other validation options are laid out there. Another tool in preventing duplicates is to make sure the user, from the Orders screen, can easily select from existing customers, via drop-down, type-ahead, and/or other methods, so that it's easier to select an existing record than it is to accidentally create a duplicate.


          You might want to check out the recording of our April '16 fmpug meeting here: FMPUG | Extensitech . In it, Jason discusses the common scenario where you need to link a record in one table (like Orders) to a record or records in another table (like Customers). Although linking is the "jumping off point" for the discussion, part of that is what to do if the related record doesn't exist yet, or if there are multiple possible records (dupes or similar records), etc. It may not seem like it at first, but this is also a common way that duplicates end up getting added to your db in the first place. Controlling this process and making it easier to select existing records than to create duplicates is key to duplicate prevention. (By the way, our fmpug is online and open to the public. Contact me directly if you, or anyone else reading this, wants to be on our mailing list to be reminded of upcoming events.)



          Chris Cain


          1 of 1 people found this helpful
          • 2. Re: Customer Order Counter | Show All Customer Orders | Prevent Duplicate Customers

            Hello Mr. Cain


            Thank you so much for your quick response.

            I think I understand everything your saying but just to clarify, allow me to regurgitate what you said in your post:


            *Note*: Customers Have a One to Many Relationship because 1 customer can have multiple orders. This also applies to Products because we create a new invoice for every product purchased. so Invoice to Products is also a One to Many Relationship...just thought I would add that to my post if it helps you understand what I'm trying to achieve.


            Customer Order Counter:

            So I want the counter to show up on the customers table but I will also put copy the field into the Invoice table so its view-able from both tables.


            So in order to do this I have to make a calculation in regards to the Relationship between Customers and Invoices.

            With that being said, i name (and related the tables) by connecting CustomerID(pk) [in the customers table] to the CustomerID(fk) [in the Invoices table].


            With this connection i make a calculation in the Customers Table with the field name "TotalOrders".

            The Calculation is as follows:



            is that correct?


            If it is correct, i can add the customer table fields into the Invoice layout for me to view which customer this invoice is assigned to. So then, how can I see all the related orders?


            Also, will I have to make an additional Invoice Layout in List or Table view to compact all the orders? because Form view would be way to huge for a small portal.


            Show All Customer Orders

            To show all orders in a Portal I put the Portal in the "Customer Layout" and then "Show Related Records From: Invoices".

            Then i Tick the box that says "Filter Portal Records" Click "Specify" then the calculation dialog box open, and then finally select "CustomerID" from the "Invoices" layout.


            But here is the funny part that I don't understand...as soon as I do the steps stated above and click OK...another box opens up and asks me to "Add Fields to Portal" again...why?

            So then if i click Cancel...it goes away and doesn't show up again...and the portal looks empty.

            However, if i select "CustomerID" the portal populates.



            The Validation options don't really cut it because when you have hundreds of customers, you can't consider "Name" as a "Unique Value" because there could be different people but named the same...for example John Smith.

            Address wouldn't really help either because what if its a different user from the same household but a different name...

            Can you see the problem that I'm having here?

            If you could possibly give me an alternate way to validate my Customer Entry I would really appreciate it.


            Would you mind helping me understand this as well.

            Thank you in advance again for all of your help, I greatly appreciate it.