Building relationships - orders and payments via customerID

[exposbabe]

Filemaker newbie here.
I've got the basics down, but here's what I need to do, and I'm afraid I just don't quite grab the hang of the relationships issue (which is clearly key).

Any conceptual help will be appreciated.

The business has a list of customers.
The customers made orders (for which an invoice is printed, etc).
The customers make payments.

Mostly it's a one-on-one relationship, although there are exceptions.

I currently have the customers and their orders in the same table in my working design, which was probably a mistake, but I can fix that later.

What I want to do is configure a table in which I can enter payments: customer #, payment date, cheque #, and amount.

What I would like to have is a portal on each customer's file that will have the orders, the payments, and a balance owing. Each entry, once done in the payment, would appear in that portal automatically. I'd like to have both types of transactions in the portal even though the order billing is currently already part of the customer table.

What do I do? How do I create this?

I'm currently experimenting with various scenarios, trying to learn this by doing. But that's clearly not the most effective route.


Thanks for being lifesavers.

 

[JeanW]

You can have all your tables in one FILE. No problem.

In this file you need at least your 3 tables.
Customer
Order
Payment

Your customer table will hold all the info related to customers.
The Order table will hold all the details of orders, regardless which customer is placing the order.
The payment table will hold all the details of the payments.

Basic idea is that you will have no orders without customers and no payments without orders, hence you need customers as start point.
Make sure you have a customer ID field that uniquely identify the customer.
Same goes for the Orders and the payments.
In your customer table you will have 1 and only one record for each customer.
Make a button with script on this layout to make a new record in the order file, where you make it possible to push the customerID from the customer table into the order table.
The customerdetails in the ordertable are lookup fields based upon a relationship between the two tables customerID = customerID
At the same time do the same in the payment table, where you push the orderID from the order table into the payment table, together with the customerID.

Back in the customertable, you can make a portal, based upon the relationship between the tables and the customerID.
In this portal you can show the orders and the payments for each order on customer level.
By adding calculation fields in the paymenttable you can calculate the balance and show the result in the portal as well.

One missing item is the ‘line items’ for your orders.

It-s in a nutshell, but it will get you on your way.

 

[exposbabe]

OK, that makes sense. And it's quite simple.

My issue is in the technical details of how to actually go about it.


To create those relationships, do I have to create a "customer ID" files in each of the three tables, then relate them?

Or do I create it in only one place (the customer info table).

That's where I get stuck.


And, No. 2, when I solve that problem, here's what I want to do considering the data I've already put in.

The main file I have has both the customer info and the orders. Clearly, I want to separate the two.


Assuming I turn that able into my "orders table" (because I've already scripted some reports, and have entered about 50 orders for experimenting), I need to move the customer info for each file into a "customer table".

Can I write a script transferring the customer info (field by field) into new files, which are contained in the new table? Then make those fields the main fields from which the other subsidiary tables get their customer info?

Y'know, I probably should have asked a question here before I got started. But since I had no idea what I was doing then, I wouldn't have been able to ask a proper question!

 

[JeanW]

>>To create those relationships, do I have to create a "customer ID" files in each of the three tables, then relate them?

Not a 'file', but a 'field'. And yes a customerID in each table. And a relationship between the customer table and the order table based upon customerID(customerTable) = customerID(orderTable) and a relatinship between customer table and payment table based upon customerID(customerTable) = customerID(paymentTable)

>>Assuming I turn that able into my "orders table"
Make a new table for the customer.
In your actual table, isolate all the 'customer' records.
Import the found set into your new customer table.
Check for possible errors and delete the foundset in the now orderTable.

That is if your customer info is in a separate record...if not, it will be harder.
Then I suggest a creation of equal customerID in the new customer table, a relatinship between the 2 tables (which you need anyway) and all the customer related fields as temporary lookup from the table into the new table.
After that remove the lookup from the fields, making them regular input fields.

 

[exposbabe]

Beautiful. It's working like a charm so far. You're the best.