Relationships across different files

[RSpisketts]

Hi,

I have a clients table (and layout) in one file and a contacts table (and layout) in another.

On both layouts, I want a button to ADD various details of the record to the other table, so that name/address details will be copied across (or updated).

I tried relating the tables through ID and using a lookup for the fields to be copied, but get the error 414 (layout cannot display the result) if I try to "Go To Related Record" in the other file (wrong use of the command !).

Intuitively, the tables should be related to make copying easy, but I can't see how best to achieve this between the 2 files.

I suppose the problem can be summarised thus:

Q. How do I best determine if another table in another file has an existing

record, based on a unique ID field (say)

Q. How do I then copy the relevant fields over to that record (new or pre-

existing)

I suspect this is pretty basic stuff, but I'm baffled at the apparent difficulty in achieving it using FileMaker

Any help would be most appreciated.

[Balder]

RSpisketts,

When i understand you correctly, you created a relation from clients to contacts, via ID. Why don't you create a relation backwards (from contact to clients using the same ID field) so you have the name and adress details available (from clients) in contacts. If you want you can allow your users to change the data in clients from a layout in contacts...

This may be a better solution compared to copying data, no redundancy, always the correct and uptodate data... and easily to implement...

Your other question:

check whether in the related file the field 'ID' is empty....

I assume there is a relation from table A to table B using fields ID (or whatever...)

Do the following:

Create in table A a calculated field called 'FLAG'.

calculation: Case(IsEmpty(B::ID) ; "NO"; "YES")

If FLAG contains 'NO', there are no related records in table B

I hope that helps...

[RSpisketts]

Thanks for your suggestions.

I spent part of yesterday destroying my contacts table - I decided to use one "clients" table and "find" either clients or contacts depending on which layout I was in.

I didn't want to do this originally, because the "Show All Records" command causes problems for the find, but...

Roger.