Using calc for 'tunneling'

[GrantSymon]

Hi,

I don't know if this is the correct thing to do or not. It appears to work, but I'm wondering if it is a fragile technique or not.

In one of my files I want to access data from table (Forms_Submissions), whilst in another table (Submissions_Release). The data is 2 tables away.

I've made a calc field in the 'middle' table (Releases) which is simply = the field I want to access in the 1st table (Forms_Submissions). I then made a third relationship table and put the calc field from the second table, directly onto the layout.

The data shows up as desired, but it seems an odd workaround. Is it a good way to do this?

Grant

[Genx]

Hi Grant,

We can't really tell you unless we know your structure in the first place (the image is kind of bad quality and can't be read). The technique itself sounds fine to some extent if it works but I don't see why (and i'm sure theres a good reason) you couldn't just add another table occurance and another relationship directly to the main table -- again, this is more likely not possible than it is... possible.

Just get back to us on structure so we can advise you.. i.e. specific purpose of those three tables, and specific criteria by which they are related.

[Søren Dyhr]

I jump on Alex' bandwaggon as well, tunneling via calc'fields ought to be a bygone!

http://www.newcenturydata.com/downloads/gtrr_multi_hop.zip

Since I consider you pretty accomplished in dealings with filemaker must there be something I ignore - please enlighten me!

--sd

[Genx]

All i have to say is... yay i have a bandwagon

But we'll just have to wait and see as to what the structure is...

[comment]

I can't follow this either (the table names are rather cryptic), but what happens when you place a field from Forms_Submissions directly onto a layout of Submissions_Release?

[GrantSymon]

Soren, Genx,

thanks for the replies.

I rather thought that this was an 'iffy' technique. I don't do much FM these days, so I'm not really up on how this should be done. Also ... I thought the screen-grab would have been useful. Ho hum.

I have several tables in this file, but there are 3 in particular which are involved here.

1/ Forms, which feeds PDFs in container fields to both Submissions and Releases.

2/ Submissions, which displays the pdfs from Forms and info from Releases

3/ Releases, which I want to display pdfs as displayed in Submissions *and* others fed from Forms.

My problem is, that in Releases, I can display the pdfs fed directly from Forms but the pdfs fed from Forms and then *via* Submissions don't show up. (So ... I found that making a calc in Submissions and then displaying that in Releases, works.)

I need forms to be fed via Submissions so that they relate to the appropriate records.

Grant

[Søren Dyhr]

so I'm not really up on how this should be done.

Comment and I have known you from way back in Compuserve, so we thought you knew that the RG not is an ERD, so what's next is some Anchor Bouy'ing or similar to your graph and perhaps a read of this:

http://www.digfm.org/ref/FM7_key_concepts.pdf

But indeed thumps up to your website!!

--sd

[GrantSymon]

Hi Soren,

yes I remember you well from the CIS days. Wasn't that just a great forum. I loved it, but spent waaaayyyy to much time on it, with the happy result that my FM skills weren't too bad in those days for a fumbling amateur. Today though, with so much changed in FM, I'm a little lost ... hence the lingering use of old techniques. (I don't remember anyone called 'comment' though! )

I shall try and read through the pdf. Thanks for the link.

However, I have *no idea* what "RG" and "ERD" are???

Thanks for the kind words about my *real* work. :)

Grant

[Genx]

ERD - Entity Relationship Diagram, RG - Relationships Graph.

[GrantSymon]

Hi Genx,

thanks for the illumination.

Okay ... now that I know that an ERD is an " Entity Relationship Diagram" ... what exactly is an Entity Relationship Diagram??

Grant

[GrantSymon]

Okay ... found the ERD answer in the PDF Soren linked to. (Not sure I understand it ... but at least I've found it )

Grant

[Genx]

An Entity relationship diagram, is essentially a diagram of the way processes relate to each other in the real world, what information needs to be stored and so on. The relationships graph... is the way you attempt to "fit" your database to that structure..

[Søren Dyhr]

I don't remember anyone called 'comment' though!

I can't remember his CIS id, but this is him:

http://www.fmforums.com/forum/showuser.php?uid/21152/

--sd

[GrantSymon]

Problem solved. A few more TOGs did the trick for an increasing number of distant cousins. Trouble is, the whole thing is becoming rather confusing. Ho hum. Anyway ...thanks for the pointers.

Grant

[Søren Dyhr]

Google your way to some "Anchor Bouy'ing!" - which provides the developer with some rules to organize the TOG's in a flow'ish way where things gets more "digestive".

--sd

[xochi]

Regarding your original question -- using a calc field in the 2nd table, to get a value in the 3rd table, may in some cases work differently than referencing the field in the third table directly. I'm thinking in particular of Nulls / missing values. For example, doing a find for a blank value using the calc in table 2 may not give the same result as doing a find for blank value in table 3.

If you use this technique, make sure you are paying attention to the "...even if all values are missing..." calc options.

[Søren Dyhr]

I'm thinking in particular of Nulls / missing values.

Excellent point you make here, urging to make the validation by the commitment of a record tighter!

--sd