7 Replies Latest reply on Feb 23, 2012 2:21 PM by philmodjunk

    Best way to solve a looping problem



      Best way to solve a looping problem



      I have the following tables/relationships:



      Site::__kp_SiteID = Messages::_kf_SiteID

      The messages table also has a field called "Read" which is automatically set to "No".

      Here's what I want to do...I want to create a conditional button that is active only if there is a message for the specific site. Pressing the button will bring up a dialog window and change the color of the font from red to green in the button.

      I've figure out the conditional formatting but run into a problem when there is more than one message that is related to the site. Theoretically, after reading the first message, the button should still be red since there is still a message in the table related to that record.

      Now I originally thought that the program would automatically pick up the extra message but that's not the case (it looks like FM stops at the first entry that is related to the site). Adding a script with with Refresh Window() and Commit Records doesn't do anything either.

      Part of me thinks that I need to implement a Loop that goes through the entire table to see if there is another related record but before I go through the process of figuring out the logic, is there another solution to this problem?


        • 1. Re: Best way to solve a looping problem

          Direct references to a related table will refer to the 'first' related record only as you have found out. That's why we have portals and list functions to use so that we can work with multiple related records as is typical of a one to many relationship.

          You could define an additional relationship that matches by the value of the "read" field as well as the ID fields, but I'd probably just use the list function instead to get a list of the values of Read from the related messages table.

          IsEmpty ( Filter values ( List ( Messages::Read  ) ; "No" ) )

          will be true only if there are no related records in Messages with the value "No" in the Read field.

          • 2. Re: Best way to solve a looping problem

            Ok. I'm trying to figure out how to implement this. The isEmpty statement above will allow me to check the entire message table for messages that aren't read (Based on the relationships).

            From the messages table, I can filter out all those messages that haven't been read. How do I go about changing each Messages::read to "Yes" in that filtered list?

            The results from the List() function comes from a calculation whereas the lopping functionality is a scripting function. How do I get around that?

            • 3. Re: Best way to solve a looping problem

              It was my understanding that you wanted a conditional format to change a layout object's appearance if any one of the related message records was not "read". The list function example would work for that conditional format expression.

              If you want to modify data in multiple related records then you must either loop through the records or pull up a found set of them and use Replace Field Contents to update them in one batch. With either method, care must be taken if it is possible that another user is editing the record you want to modify with this loop or replace field contents operation. If a user is editing one of the records, this will "lock" the record and the script will fail to change that record.

              • 4. Re: Best way to solve a looping problem

                Trying the looping method...here's my script:


                Go to Record/Request/Page[ First ]


                If [ Messages::Read="No" ]

                Show Custom Dialog [ Message: Messages::Message; Buttons: “OK”, “Cancel” ]

                Set Field [ Messages::Read; "Yes" ]

                End If

                Go to Record/Request/Page[ Next; Exit after last ]

                End Loop 


                Now from my understanding, this script should go through all the records in the table but it only shows the first entry in the table. What am I missing?

                • 5. Re: Best way to solve a looping problem

                  What takes place before the first line of this script?

                  Do you pull up a found set of mesages and a layout based on messages, then run this script?

                  The looping script, as written, must be performed on a layout based on Messages after performing a find or go to related records to pull up the correct set of records to loop through.

                  • 6. Re: Best way to solve a looping problem

                    So everything I'm trying to do here is part of a larger database I'm trying to create. Its an upgraded version of the database from this Creating new entries based on a portal. I've attached a screenshot of the relationships/layouts.

                    The script is run on a layout based on the Ice Inspection table. This layout has a portal pointing to Job List. The top part of the screenshot shows the layout of the portal. The exclamation marked buttons is what starts the script. I do not perform any found sets for the messages, everything has direct relationships.

                    So it sounds like what I'm trying to do with my current layout setup is not possible since the Find function requires me to be performed on a layout based on messages.

                    • 7. Re: Best way to solve a looping problem

                      It's a matter of correct "table context". Go to Next record tells filemaker to go to the next record in the current layout's found set. Unless that record is a record in the message table it won't work. The other option is to use go to portal row to loop through the rows in your portal that will work, but only if the portal displays the records you need to display like this. In your case the portal to Job List_Messages_..., if that's the portal present on your layout, could be used with such a looping script.

                      There are several caveats to looping through a portal:

                      Go to Portal Row does not include any reference to a specific portal. It acts on either the portal that currently has the focus or the "first" portal. If you have more than one portal on your layout or later add a second portal to it, the script may end up looping through the wrong portal. To avoid this with multiple portal layouts and to protect yourself from future changes that might add that second portal on single portal layouts, give the portal na object name and use Go to Object [ "portalObjectNameHere"] to set the focus on the desired portal before starting up your loop.

                      If you have enabled "allow creation..." for the portal, go to portal row [last] puts you one row lower than you may expect as it puts you on the blank 'add' row of the portal. This can even trigger creation of new related records and an endless loop if you are using the exit after last parameter in some cases. Thus, you may need to use count ( portaltable::ForeignKey ) = Get ( ActivePortalRowNumber ) in an Exit If step to exit your loop at the right time.