9 Replies Latest reply on Jan 21, 2015 8:36 PM by gilcano

    Problem with Table Relationships

    gilcano

      Hi all, In the design of my application I have a little bit of trouble or confusion should I said.  I created a TENANT Layout with 3 portals, One to see the Work Orders for that particular Tenant, the second portal is in a tab control with the third one, the second for FOLLOW UPS and the third one for NOTES.  I have no problem with that, the issue is that I would like to include "switch" for "OPEN" or "CLOSED" only.  At one point I would like to see the CLOSED Work Orders but when I load the Layout I would like to see the OPEN Work Orders only as well as their relative OPEN FOLLOW UP's and NOTES.  Here is a picture of my Layout and some schematic of the intended "relationships".  Thanks in advance.

      Tenants.PNG

      Relations.png

        • 1. Re: Problem with Table Relationships
          mtwalker

          Make your "Type of Work Order" a global field based on a custom value list with the values "Open, Closed, All"

           

          Set each portal to filter the records based on that global field:

          If ( GlobalField = "All" ; 1 ; statusField = GlobalField )


          Create an OnLayoutEnter script trigger to set the global field to "Open"


          You don't need to filter the portal by the relationship.

          • 2. Re: Problem with Table Relationships
            gilcano

            Thanks, actually "Type of Work Orders" Is not a field is just a "TEXT" before the Open/Closed radio buttons.  The field that hold the value list in the WORKORDER Table is STATUS  and the value list name is WO Status with the values (Open, On Hold, Parts Needed, Outside Contractor and Closed)

            • 3. Re: Problem with Table Relationships
              mtwalker

              I understand that. I was just being general in my explanation.

               

              The specific calculation for filtering the TENANTS portal would be:

              If ( WORKORDERS::STATUS = "All" ; 1 ; TENANTS::status = WORKORDERS::STATUS )


              The specific calculation for filtering the FOLLOW_UP portal would be:

              If ( WORKORDERS::STATUS = "All" ; 1 ; FOLLOW_UP::status = WORKORDERS::STATUS )


              And specific calculation for filtering the NOTES portal would be:

              If ( WORKORDERS::STATUS = "All" ; 1 ; NOTES::status = WORKORDERS::STATUS )


              Double click the portal in layout mode to get the portal option dialog box. Then click the Filtering button.


              You made need to add an OnModify field script trigger to the STATUS global field with a script that contains a Commit Record and a Refresh Window script step to get the portals to update. If the file is going to be hosted you could consider this technique from weetbicks to refresh the portals:

              Ditch those Flush Caches, Use Cartesian Join Instead!


              • 4. Re: Problem with Table Relationships
                DavidJondreau

                I would skip an extra table occurences and relationship and just use portal filtering. Show all Work Orders in the portal. Don't use "Status" in the relationship.

                 

                Portal filtering isn't a noticeable performance hit if you're showing less than a total of 100 related records and using indexable fields on the child side.

                 

                On the layout, you don't even need the extra "Status" field. You can use a global variable set by a button. Throw in some conditional formatting and you're good to go.

                • 5. Re: Problem with Table Relationships
                  gilcano

                   

                  Todd Walker wrote:

                   

                  The specific calculation for filtering the TENANTS portal would be:

                  If ( WORKORDERS::STATUS = "All" ; 1 ; TENANTS::status = WORKORDERS::STATUS )

                   

                  I don't have a STATUS field in the table TENANTS, the IF statement is working fine as a filter for the FOLLOW_UP and NOTES portals.  The Open and Closed radio button are showing values from the value list "Status Selector".  I was thinking about showing everything "NOT EQUAL" to Closed instead.  Everything NOT EQUAL to Closed, will be OPEN, ON-HOLD, PARTS NEEDED or OUTSIDE CONTRACTOR.

                  Tenants.PNG

                  • 6. Re: Problem with Table Relationships
                    mtwalker

                    Then the calculations would be:

                    If ( WORKORDERS::STATUS = "Open" ; FOLLOW_UP::status <> "Closed"; FOLLOW_UP::status = "Closed" )


                    If ( WORKORDERS::STATUS = "Open" ; NOTES::status <> "Closed" ; NOTES::status = "Closed" )


                    So when the Closed radio button is selected, the portal will just show the records with a status of "Closed" otherwise it will show records that don't have a Closed status.

                    • 7. Re: Problem with Table Relationships
                      gilcano

                      Todd, thanks for your help and pardon my ignorance.  I'm in the process of learning  FM13 PRO but perhaps I'm fixed in an old type of programming, and can't make WORKORDERS portal work.  Maybe I need to do some more reading.  I'm a bit confused about  "how and where" to utilize the scripts and variables specially Globals.

                      • 8. Re: Problem with Table Relationships
                        mtwalker

                        I have a attached a stripped down demo file of what I'm taking about. Look at the way the filtering is set up on the portal.

                        Also, there is a script trigger attached to the "Type of Work Order" radio button field (however, if this is going to be a hosted solution, you should look into the Cartesian Flush method I mentioned in an earlier post here).

                        • 9. Re: Problem with Table Relationships
                          gilcano

                          Thanks a million, Great .    Thanks for clarify the use of the GLOBAL field STATUS.FILTER.  Your help was of great value.

                          I did everything as you suggested and is working fine. 

                          Thanks agan