Hi Bob ! I had encountered this kind of issue many times with both 11 and 12 versions. Often, i found a conceptual error or "a better way to..." but not always. For instance, i often use a triggered refresh command (with full option checked) when i install a portal's filter. My english is bad and it is difficult to catch a particular error without an example database. So, i made one. I tried to replicate the rules of your report in a practical example. My little database have three tables : Departments, Employees and Calendar (that contain one record per day for one year). In the Department layout, you can define many employees and a month period. A large button perform THE test script. The script must retrieve all days of one month and apply the employee list on the found set. The loop proceed month by month, within the period defined. The Global field that permit the link between Employee and Calendar is a global field on the related Employee table and the script is executed on the Department parent layout. Normally i never would proceed like this but it is just for the test. In this example, the script always worked. I sent you a link to download this database by private message. Thus, you could compare the two structures and scripts and, maybe, find your better way. I hope... Bye, Fred
Posting a copy of the actual script may be helpful.
To post a script to the forum:
- You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
- You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
- If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
- If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
Bob is out of the office so I'm posting a screenshot of the problematic script on his behalf. The script in question is pretty long, so I've only included the problematic portion. Hopeully it's enough to understand the logic. The block of code with breakpoints is where we are pulling data through the relationship established by the global variable.
Well it's actually a global FIELD not a variable, but I'm nitpicking....
Have you tried recovering a copy of the file and then tested the copy to see if you still get the same behavior?
Recover rebuilds all your field indexes and I have seen cases where a messed up index kept a relationship from matching to the correct value so it's worth a shot. If the recoverd file works correctly, you can try using recover to only rebuild the indexes as a way to produce the safest possible recovered copy to use in place of your current copy.
If you have FileMaker 11 or newer, you can use Advanced Recovery options to rebuild your file's indexes:
- With the file closed, select Recover from the File Menu.
- Select "Use advanced Options"
- Select only: "Copy File Blocks as-is" and "Rebuild Field Indexes Now".
- The recovered copy of the file will be identical to the original copy except that it has completely rebuilt indexes.
If, after following Phil's instructions, the problem remains, here is my new contribution :
I red Bob's script and i have reviewed my example database to complicate the test. I sent him a second link to download it. I could not replicate the bug.
Bob : I really encourage you to check my little database. I would also send you these Questions / Suggestions (b2 is my favorite) :
A ) If the database is hosted :
a1 : What is the exact version of FileMaker Server 12 ?
a2 : Did you test the script with the file stored locally ?
B ) Did you double-check the related Field Options ?
b1 : Is there a calculation on one of the two related fields ?
b2 : If true, did you check if the context of this calculation is correct ?
b3 : On the child related field that is not Global, i hope, be shure that the option "Automatically create index as needed" is checked.
C ) Did you double-check the contexts Implied ?
c1 : Compare the context (O.T.) of the layout where the script is executed with the context of the fields implied in your script.
PS: Excuse me for my bad english
Just one more question that i thought about and that may be relevant : is your solution using multiple files ?
Thanks for the suggestion, and for clarifying my misstatement about the global field. I ran a recovery as you described, but unfortunately the script behavior did not change.
What IS the relationship exactly? Is it strictly a direct link between two tables are are there one or more table occurrences between the table with the global field and the table from which you are looking up values?
Thank you for your detailed questions! Bob sent me the link to the first version of your test database yesterday. I modified it to be more closely represent what our script does, and I also was unable to replicate the bug.
a1 : 18.104.22.1687
a2 : The script behavior is the same on the local copy.
b1 : No calculation fields are involved in the relationships used by this script.
b2 : Not applicable.
b3. It is. This table is heavily used throughout the system.
C) Yes. The context is definitely correct.
c1: Done. everything looks correct.
We are using a pretty standard approach to the "seperation model" with this solution, so there are multiple files involved. This script runs from the interface file, but all of the tables that it uses are stored in the data file.
We're confident that the script is written correctly. It performed without problems in FileMaker 11 for almost two years.
Hmm, when you tried recover, did you recover just one file or both for your test?
I've attached a screenshot of the relationship. The script runs from the context of a layout based on HS.d.home_site. The relationship between HS.d.home_site and HS.f.homesite_lines_SELECTED is one-to-one, and is established before the script is run. We are 100% sure that the relationship between those two tables is correct at the time the script runs.
All of the non-global fields that you see are indexed.
It seems like the "commit records" step is not behaving the same way it did in FM11. If I disable the "Commit Records" step in FM11 and run the script, I get the erroneous results that we're seeing in FM12. I'm assuming that this has something to do with the fact that we're setting the value of a global field in a child table, then attempting to commit it .
I recovered both files.
Thanks for your answers.
First, i please you to check my second version : because i even removed all the Commit Records script actions / steps and It is still working ! The reason why is that the Commit Records action / step is unnecessary as long as the parent key is a global stored field.But now, thanks for your details, i think i have a more precise opinion : this issue is probably due to your multiple files solution. Maybe it is managed differently between 11 et 12, but follow my reasoning Mark, please :All files are definitly executed in separated windows. It's a fact. Often, windows are hidden, but they are ! Your script is working perfectly. BUT, the only thing you are complaining is that you must use a Refresh window script action / step within and that was unnecessary with 11 version. Right ?If you want, i can confirm that surely : it is only to break out my exemple file in two linked files.Just tell me...Bye, Fred
Can you send me the link to the second version? I would also be curious to see if the problem can be replicated by using multiple files.