3 Replies Latest reply on Aug 16, 2011 5:03 PM by philmodjunk

    Filter relationship with a global- and a calc field?

    ferdinand

      Title

      Filter relationship with a global- and a calc field?

      Post

      I took me some time to find out that I can't filter a relationship with a global field and calc field.

      But my problem is:

      I have a self-join relationship which I need to filter data:

      Table A: payowner_OBJ / Table B: payowner_OBJ_selfjoin

      Filter: 
      _pkObject X _pkObject 
      xStatusNotifySaleOwner (=Open) = StatusNotifySaleOwner_c

      StatusNotifySaleOwner_c is a calc field which collects data from a related portal: 
      Case (Stat_NotifySaleOwner_lastrow_c = "Not Notified" OR Stat_NotifySaleOwner_lastrow_c = "Reported" OR Stat_NotifySaleOwner_lastrow_c = "Billed"; "Open")

      What is the best way to solve the problem (for a beginner in scripting) with the calc field? 

        • 1. Re: Filter relationship with a global- and a calc field?
          philmodjunk

          It can depend on how you intend to use this relationship. If this is for a portal and you are using Filemaker 11, you can use global and/or unstored calculations in the portal filter expression.

          Calculation fields can be used without any trouble in relationships if they are stored, indexed fields. Unstored calculations and global fields can be used in relationships on the "one" or "parent" side of a relationship to function as a filter so while StatusNotifySaleOwner_c cannot be unstored or global. xStatusNotifySaleOwner can be global or unstored.

          Another option for getting an actual relationship (rather than a filtered portal) to work requires using a data field to store the value from the related table and use script trigger controlled scripts that find this record and update this field whenever the value is changed in the related table. This can be easy or very slow/impossible to implement depending on the design of your database.

          You might also get this relationships to work.

          TableA------RelatedTable-------TableASelfJoin

          where you link to the field in the related table and from it back to a new occurrence of the original table. This assumes details about your data that may not be true...

          • 2. Re: Filter relationship with a global- and a calc field?
            ferdinand

            Hi Phil,

            thanks for your advice!

            I tried the calculated portal filter first, but it took ages to load the layout (the database contains 5.000 records with ca. 2.500 images -  I know that that's not the best solution, but this is a different matter. 

            I tried to add a trigger to the calc the field "StatusNotifySaleOwner_c" which should copy the data (either "Open" or "") to a text field, with both fields (calc plus text field) on the same layout. But I did not get this to work. Alternatively I tried script with a loop and "set field" on loading the layout, but it didn't work neither. I guess I need more training in FM, but this has to happen in my free time and it is already 2 am...

            BTW, TableA/LayoutA shows the basic records unfiltered, while TableASelfJoin shows a short version of the records in a Portal. 

            How could I get "TableA------RelatedTable-------TableASelfJoin" to work automatically? This sounds interesting! It is the first time that I hear of this. 

            Anyway, albeit the portal is important - it spares me hours of work to collect the data manually -, I won't use this layout daily, so it would be OK if the layout wouldn't load that fast, as long as it is not that slow as a calculated portal...

            • 3. Re: Filter relationship with a global- and a calc field?
              philmodjunk

              I tried to add a trigger to the calc the field "StatusNotifySaleOwner_c" which should copy the data (either "Open" or "") to a text field, with both fields (calc plus text field) on the same layout. But I did not get this to work.

              Triggers would be set up on data fields, not calc field. YOu need to set this on every data field reference by the calc field.

              I'd need to know how that other table is related to the table you have descirbed here. It may or may not be possible to get this to work.