Linked databases

[Kenevans]

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 repaired. 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. Ideally I click a button and it takes me into a sub menu or database with all that customers repaired items.

Any suggestions on how I can make a sub or linked database that has each customers visit and item repaired, 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.

[RalphL]

To start make sure thaat each customer has an unique ID. I suggest using the auto-entered serial number. Make a second table in you existing file, let's call it Detail. The fields for this table would be CustomerID same type, i.e., number or text, as the CustomerID in the first table. Now add other fields that you need such ad Date, Item, Notes, etc.

Now go to the Relationships graph. Select CustomerID in the first Table Occurance (TO) and drag it to CustomerID in the Detail TO. Double click on the = to get Edit Relationship dialog box. Under the part for Detail click on the Allow creation of related records, click on Delete records..., You may also want to click on Sort Records. Sort by date in Desending order. This way the new data will appear at the top of the portal you are going to make in the first file.

Go to the layout for the first table and enter layout mode. Use the Portal tool to make a portal. Add the fields you want to show in the portal.

[Kenevans]

Thanks very much, that makes perfect sense!

Greatly appreciated

[Kenevans]

OK It made sense, but I'm not sure what purpose the portal serves. Can I not just have a button that opens up that persons repairs detailed?

Also having done what you suggested, the customer ID field remains blank in the other table (repairs table), it's not updating as per the main record even though they are linked in the relationship graph. It says 'indexed' in this view.

Could you provide any more help?

Regards,

Kenny.

[RalphL]

The portal displays the related data without haveing to go to another layout. But if you would rather have a button that opens up that persons repairs detailed, you can. You don't need a script but select show only related records.

In either case you will need to have the CusomerID in the related table. Verify that you have set the relationship to create related records. Go to the relationship graph, double click on the = in the box on the line joining the 2 related fields. In the dialog box that comes up make sure the Allow Creation of Related Records is checked for the new table. It should not be checked for the first table. This is near the bottom of the dialog box.

When this is tuned on the portal will display a blank row at the end of the portal. It is in this row thaat you enter a new job.

[RalphL]

The portal is also the way you create new related records. You didn't say how you created the related records when the CustomerID remained blank.

[bruceR]

Could you provide any more help?

I suggest you look at the example files and tutorial that came with your installation of Filemaker.