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.
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?
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.
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" ]
Go to Record/Request/Page[ Next; Exit after last ]
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?
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.
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.
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.