4 Replies Latest reply on Feb 2, 2009 12:14 PM by TSGal

    Temporary "ad-hoc" relationships



      Temporary "ad-hoc" relationships


      Hi TSGal


      A question about relationships.


      As mentioned in an earlier post, I am an experienced DataEase (DOS) developer, but new to Filemaker.


      In DataEase, there are two possible types of relationship:

      • "Normal" where there is a permanent relationship established beween two tables
      • "Ad-hoc" where a temporary relationship is established as a step within a script
      • The first "Normal" type is essentially the same as those established in the Filemaker Relationship graph. They can be used in much the same way, either within forms & tables (for lookups and portals etc), or within scripts for any relevant purpose.


        However there are some differences, in that Filemaker allows some greater control in terms of how the tables are matched (unequal, greater than, smaller than, etc) and also allows the developer to hide relationshipships from the user, so preventing "drill-downs" when this is undesirable. By contrast, Dataease "normal" relationships are inherently bi-directional and are always visible to the user (although control can be exerted by security settings, and by controlling how one table is updated via the other).


        The second "Ad-Hoc" type exist only within scripts, where they are "declared" as a script step in much the same way as a temporary variable would be declared. From the users point of view, this type of relationship only functions while the script is running, and is otherwise unavailable.


        There are three main reasons why a DataEase developer might use the "Ad-hoc" type:

        • Where the pathway created beween the two related tables should not be readily accessible to the users, so the secondary table is effectively "hidden"
        • Where a more complex relationship can be created than is possible with the "Permanent" relationship type (largely replicating Filemaker's richer matching possibilities, but can go a beyond this)
        • To avoid the possible performance hit of excessive numbers of permanently established relationships in complex & large applications.
        • It seems that with Filemaker, the first two scenarios are pretty much covered by Filemaker's inherent functionality. However, the last one concerns me somewhat.


          With DataEase, best practice would be to keep the number of permanent relationships to a bare minimum, using ad-hocs with scripts to supplement when needed. 


          I appreciate that Filemaker is very different from DataEase under the skin, so having a large number of permanent relationships between heavily populated tables may not have the same overhead as it might in DataEase, but the point still concerns me.


          Does Filemaker have any means of establishing temporary relationships within scripts?


          Many thanks for your help.


        • 1. Re: Temporary "ad-hoc" relationships



          No, FileMaker does not have any means of establishing temporary relationships within scripts.  The relationship needs to be set up beforehand.


          You can have multiple relationships set up into the same table.  For example, you may want the Customer table hooked up to the Invoicing table by a Customer number.  You can then go into "Manage -> Database...", click on Relationships, click the bottom left icon to add a new table, and select the Invoicing table again.  You can link this up to the Customer table again, but also add the condition of Invoice > 1000.  That is, you have a second table occurrence of Invoicing, and on your Customer layout, you can view all invoices in a portal, plus you can view all invoices greater than $1000 in a second portal.


          I know this doesn't answer your question, but it may give you some more ideas.



          FileMaker, Inc. 

          • 2. Re: Temporary "ad-hoc" relationships

            Hi TSGal


            Thanks once again for your very prompt & helpful reply.


            You are confirming what I suspected. I am reasonably up-to-speed on the flexibility of Filemaker's Relationship Graph and the concept of multiple relationships being set up on any given table (I understand the distinction between TOs & Tables). My intention is to adopt the 'Anchor/Buoy' method of using the Graph.


            My main concern was the need to set up absolutely all the relationships needed within a given application on a permanent basis. I am not used to doing this, in part because of reservations about the potential overhead that large numbers of complex relationships could impose upon the database processing engine. However, I appreciate that FileMaker's engine may handle its relationships in such a way that this is not an issue in practice.


            I'll go away and play some more with my new toy :smileyhappy:


            Best regards

            • 3. Re: Temporary "ad-hoc" relationships

              Hi TSGal,


              I'm missing something here. Are you saying the condition "Invoice>100" can be defined in the relationship graph? I don't see how that can be done.



              • 4. Re: Temporary "ad-hoc" relationships



                Sorry for the confusion.  You cannot have a static value; you can only compare fields in the Relationships graph.  You would need to put the static "100" into a global field.  Then you could have "Invoice > global field"


                Does that make sense?



                FileMaker, Inc.