3 Replies Latest reply on Aug 9, 2011 4:49 PM by philmodjunk

    Relationships and portals



      Relationships and portals


      Hello all... this is my first post - I'm a FileMaker Pro newbie, but have many years programming experience and various SQL experience (although not an expert).  I'm a bit stuck with this latest thing I'm attempting with FMP (I recently purchased FileMaker Pro 11), I suspect because I just don't understand enough about relationships and portals.  Any help appreciated!

      I'm trying to record the presence at each SITE of particular FEATUREs (eventually thousands of sites, but perhaps around 20 features).

      For example, SITE_ID 23 might have FEATURE_ID 5 (cafe) and FEATURE_ID 6 (toilet).

      I'd like this information to be accessible on the SITE layout. When that layout shows a particular site, I'd like it also to show a list of all possible features, with a checkbox by each one to show the presence or absence of that feature at that site.  If this were possible, I could then record the presence/absence of a feature with a single click.

      Below is what I've tried, and it feels like I'm close - but perhaps I'm actually miles away.  Is the above actually possible/sensible with FMP?  How would I go about it?

      I created a table for SITES, another for FEATURES, and a join table FEATURED (with columns SITE_ID and FEATURE_ID). (I thought it would be better to have a table listing possible features and use a join table rather than add a column to SITES for each feature, particularly as the list of features may well change as time goes on.)

      I created a second TO called ALL_FEATURES, with relationship SITE_ID x FEATURE_ID.  I added a portal to the SITE layout, showing related records from ALL_FEATURES.  Great - it shows a list of all possible features.

      But now I'm stuck at how to show the checkboxes. Each checkbox (on the SITE layout, within the ALL_FEATURES portal) should show whether a row (SITE_ID, FEATURE_ID) exists in FEATURED (SITE_ID from the SITE layout, FEATURE_ID from the portal row). But I can't see how to do this - in fact I suspect that it isn't possible to do this with a portal?

        • 1. Re: Relationships and portals

          With a bit of sneakiness, this can be done. The check box field in the portal can actually be a calculation field formatted to display it's value with a check box format. The field can then be set up as a button that performs a script to add/remove the linking record in Featured. This can also be done with conditional formatting on a layout object (such as the letter X) to make the text appear and disappear.

          Define a global field, gFeatureIDList. Your calculation field will return the value 1 if it's record's feature ID value is in this field and nothing if it is not.

          Use this script with an OnRecordLoad script trigger to update this global field with the current record's list of selected Feature ID's:

          Set Field [Globals::gFeatureIDList ; List ( Featured::FeatureID ) ]

          The calculation field, cSelected, can be: Not IsEmpty ( Filtervalues ( gFeatureIDList ; Feature_ID ) )

          Now format this field as a check box group with the number 1 as it's sole value in its value list.

          A script to add/remove selected feature records from Featured that you can set up this field to perform by using button setup might look like this:

          Set Variable [$SiteID ; value: Sites::Site_ID]
          Set Variable [$FeatureID ; value: All_Features::Feature_ID]
          Freeze window
          IF [All_Features::cSelected //remove the join record]
             Go To Layout [Featured (Featured)]
             Enter Find Mode[] //clear the pause check box
             Set Field [Featured::Site_ID ; $SiteID]
             Set Field [Featured::Feature_ID ; $FeatureID]
             Perform Find[]
             Delete Record [no dialog]
             #Add the needed join record
             Go To Layout [Featured (featured)]
             New Record/Request
             Set Field [Featured::Site_ID ; $SiteID]
             Set Field [Featured::Feature_ID ; $FeatureID]
          End If
          Set Field [Globals::gFeatureList ; List (Featured::Feature_ID ) ]

          • 2. Re: Relationships and portals

            Wow there is some seriously brilliant sneaky stuff in there, PhilModJunk!  Thank you very much indeed: I've just got this working here, and I've learnt a shedload along the way.

            Some points that might be useful to others who come across this later:

            • One problem that this solution solves is that calculations in the portal cannot directly access the FEATURED records.  (I did investigate whether it was possible to set up a relationship enabling this, but couldn't figure it out.)  So, this solution saves the relevant records from that table into a global variable which can then be accessed by calculations within the portal.
            • Having a checkbox set with a value list containing the single value "1" is a cunning trick semi-explained here.
            • In order to set the calculated field up as a button, when in layout mode, right-click the field and choose Button Setup.

            Just one follow-up question...

            Within the script, the implicit context is hugely important (eg at line 1, the value of Site_ID is taken from the Site layout context, at line 2, the value of Feature_ID is taken from within the clicked-on row in the All_Features portal row context, at line 4, similarly, cSelected is taken from the clicked-on row context).  This all seems very strange to me with my programming background, perhaps because none of this is explicitly stated.  Is there any decent reference that might help me to get more comfortable with this?

            PS I used bullet point formatting in the list above, but it doesn't seem to have come out right when it is published -- am I doing something wrong?

            • 3. Re: Relationships and portals

              It's a pleasure to read a response that so clearly spells out that you were not only able to follow the steps, but figured out how and why it works the way it does. Smile

              I don't have a recommendation for you, but will observe that all Filemaker scripts are very sensitive to the status of the current layout. What part of the layout currently has the focus can make a big difference. Here, it's rather elegant as the mouse click on the portal row button activates that portal row, putting the focus right where the script needs it to be. In other places, this can be an issue to be handled carefully in your script.

              We can and should try to generalize our scripts with variables, script parameters and indirect references to fields such as GetField and Set Field by Name, but even then, the current record of the current layout, its found set and sort order can all affect the way the script operates and/or how calculation fields, if they refer to other records, evaluate.