Linked relational databases

[kenny]

Hi,

 

I'm a real newbie to databases, but am a computer veteran of some 22 years.

 

I'm using Filemaker Pro to create a really simple customer tracking database, where I just log their details into a single page.

 

Now I want to add some extra functionality: I need to make a kind of sub database that can be attached to the main customer record where I can make lists of what each customer brings in to me to be sold. I initially didn't think I'd get that much repeat work with what I do, but it's growing and my database is inadequate.

 

Any suggestions on how I can make a sub or linked database that has each customers visit and item brought in for sale, attached to their main 'name card'.

 

Go easy on the DB jargon, as mentioned I'm new to all this!!

 

Thanks in Advance.

 

Ken - London UK.

 

PS. If someone wants to do this littel job for me I can pay by Pay Pal or NoChex. I just want to get on with my life!! I have the databases made, just need to link them.

[Ender]

The 'link' between databases is called a relationship. In FM7, to create a relationship between two separate files, you will need to do several things. For this example, lets assume we're talking about a Customer file and an Order file.

 

1. Make sure the Customer file has a CustomerID field defined as an Auto-Entered serial number.

2. Make sure the Order file has a CustomerID field defined as a number.

3. In the Customer file, go into Define->File References, and add a file reference to the Order file.

4. In the Customer file, go into Define->Databases, into the Relationships tab. You should see a Table Occurance (a box) for Customer and a Table Occurance for Order. Drag a line between the CustomerID fields in the two boxes (if you make a mistake, double click the line to bring up the Define Relationship dialog and change your fields.)

5. Repeat steps 3 and 4 in the Order file, adding a file reference and a relationship to Customer.

6. Now that the tables are related, you can use the relationships to access related data in field definitions, scripts, or layouts. It sounds like you want to add a listing of related orders to the main layout in your Customer file. In layout mode, use the portal tool (it looks like the corners of a box.) Click on the tool, then click and drag a box on the layout where you want the list of orders to go.

7. FileMaker will ask you which Table Occurance and which fields to use for this portal. Choose the Order Table Occurance and whichever Order fields you wich to use.

8. If you want to create new Orders through this portal just by typing into the last line of the portal, you will need to (within the Customer file,) go back into Define->Databases, in the Relationships tab, double click the relationship line between the Table Occurances, and select the option to allow creation of related records on the Order side.

 

If you want a developer to get things working for you (and possibly improve it,) there are plenty of FileMaker developers that work long distance, and there are also some developers in the UK. There are also some prebuilt solutions out there that may fit your needs.

[kenny]

If you fancy a long distance job, let me know and i'll send over what I have and what I want it to do, it's not complicated but it's done my head in (and 2 friends!)

 

Let me know if you're interested.

 

Kenny.

[kenny]

Oh and thanks for a helpful detailed posting!