Accessing data from a fourth file

[Barry Joyce]

Using version 5 I'm trying to access data that is four files deep in a relational database. Both the manual and the online help have a section that says that it is possible to create a calculation field in the master file and use the relationship to refer to the data from the related file. It implies that it is possible to repeat this to further depths, but when I try to create another calculation field and refer this to the previous calculation field there are no error messages, but I just get a blank on the layout.

I'm sure this must be an issue that others have met before but as I'm new to filemaker (well after a gap of about 8 years) I just can't see a way round it, so would be very appreciative of any help

Thanks

Barry

[Vaughan]

Use a calculation in each related file to "carry" the field data across. Think of it being like a bucket brigade. The calculations are really simple:

field = relationship::field

You'll need one calculation field for each field you want to carry across in each related file in-between.

[Edited to add the second colon in the calculation.]

[ February 14, 2002, 08:41 PM: Message edited by: Vaughan ]

[Barry Joyce]

Thanks Vaughan, but this is exactly what I've done (though on the PC two ":"s are shown between the relationship and the field). I have the fields as "unstored", but it doesn't seem to be possible to change this so I guess that can't be the reason it doesn't work.

Anybody got any other ideas?

Barry

[kraftyman]

Do you need to work with the data or just view it?

If it is the latter, then you should be able to create some sort of relationship between the "first" and the "fourth" files. Then you could use a portal, or just drag a related field onto a layout. You may have to duplicate some data, but if this is done rignt, the performance hit is negligible.

Alternatively, you could use some scripting to run some background finds on the "fourth" file and then flick across to either that file directly, or another layout.

It may be necessary to bend the normalizatioin rules for the sake of convenience and logic. With the size of today's disks, and the speed of processors, I think we often strive for the purist "relational model" and shoot ourselves in the foot unnecessarily. Why not let the megahertz do the searching?

[Vaughan]

Kraftyman: If a direct relationship was possible between the first and fourth files, there'd be no need for the intermediate databases: they are join files.

Barry: the calculation fields *will* be unstored -- that's expected and OK. Let me illustrate the process...

dbA -- surnameA (original field)

dbB -- surnameB = B2A::surnameA

dbC -- surnameC = C2B::surnameB

dbD -- surnameD = D2C::surnameC

The surnameD field will have the same data as surnameA (but of course it cannot be editied -- gotta go back to dbA to do that). Oh a thought: make sure the data type for all calculations is the same as the data type of the original field!

Note carefully: there has to be a chain of valid relationships between dbA and dbD for the data to carry across.

To ensure that there is, start at a particular record in dbA and make a related record in dbB. In dbB, go to that related record and make a related record in dbC. In dbC go to that record and make a related record in dbD. Go to that record in dbD and you'll see the surname value orignally entered in in dbA in the originally selected record. You won't see any other record's values because there is no direct relationship chain to the record in dbD and any other records in any of the other databases!

[ February 14, 2002, 08:47 PM: Message edited by: Vaughan ]

[kraftyman]

Originally posted by Vaughan:

[QB]Kraftyman: If a direct relationship was possible between the first and fourth files, there'd be no need for the intermediate databases: they are join files.

Spot on Vaughan!

I got a bit muddled up,only realizing it afterwards. D'oh! I suppose my point was that often there is an alternaltive way of doing things, and that if editing data was the goal rather than just viewing it, then the clculated field approach will not work.

[Barry Joyce]

Vaughan Thanks I've got it now. The trouble with knowing other DMS very well is that we tend not to spent the time over the trivial things and so often it's these that trip us up. It was your aside that did it, I'd got an intermediate calculation set to field type "calculation", a change to text and voila!

Thanks again for your help.

Barry