1 of 1 people found this helpful
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.)
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.