5 Replies Latest reply on Jan 14, 2016 8:31 AM by Razor

    Unexpected Results from Relationship


      I'm having unexpected results from using a calculated field to define a relationship.  I have a list of ~ 2,600 part masters but not all of them are active parts, and while I don't want to delete these records I don't want them to appear anywhere in my forms.  I thought the right way of handling this was to create a calculated field defined as such;

      Screen Shot 2016-01-13 at 12.46.40 PM.png

      Then in my relationships I created the following;

      Screen Shot 2016-01-13 at 12.47.34 PM.png

      When I created my layout I set it up as follows;

      Screen Shot 2016-01-13 at 12.48.12 PM.png

      I expected to see approximately 200 records however I see all 2,600.  I don't know what I'm doing wrong but I can almost guarantee its something stupid.  Any suggestions what to check?  I even made sure my calculation was returning a text calculation.



        • 1. Re: Unexpected Results from Relationship

          The layout isn't restricted by the relationship.

          A layout shows records from the perspective of the assigned TO. If you were to show a portal of related partmaster_ACTIVEPARTMASTER records from a layout based on PARTMASTER, I believe you'd see the records you expect.

          For a similar need, we use an active checkbox that is checked when the record is created. You could do the same with a calculated checkbox. Then, when entering find mode, we check the box, so the user will find only active records unless they explicitly uncheck the box (to indicate they want to find regardless of whether the box is checked).

          Chris Cain


          • 2. Re: Unexpected Results from Relationship

            Creating a portal I would need to change something else from what I'm currently doing wouldn't I?  From my understanding the portal is going to show records related to whatever the parent record is in the layout.  So lets say for example my 5th record is my first active record, I would need to advance 5 records to see it in my portal right?

            • 3. Re: Unexpected Results from Relationship

              I assume the OP is using, perhaps, a list view. An alternative to a portal might be to create a script which finds only the active records, and attaching this script to an OnLayoutEnter script trigger.

              • 4. Re: Unexpected Results from Relationship

                You could do an OLE script trigger, but you'd still need to script find, find all, etc.


                Since there's generally no actual harm in users seeing inactive records if they want to (and potentially some usefulness) we use the method I described. I did forget to mention that we use our find routine for navigating to the layout in the first place, but without a pause the user doesn't get a chance to uncheck the box.


                The point, still, is that the relationships don't affect what records from the TO are available on a layout. The layout'll show all the records in the base table.


                Chris Cain


                • 5. Re: Unexpected Results from Relationship

                  So I've been thinking about this, and what I came up with was to create a table with one record in it that is "Active".  I have it linked to a new calculated field in my parts table which has the following;

                  Screen Shot 2016-01-14 at 11.10.50 AM.pngScreen Shot 2016-01-14 at 11.06.02 AM.png

                  My portal is then built off of the partstatus_PARTMASTER and I'm getting the look and feel of what I wanted.  With a minimal amount of scripting (no need to do any additional find's / "filters") this seems to be working great so far.  Any comments on possible performance issues doing it this way?