I'm looking for some help here, but feel the need to explain this solution in detail for some context.
I have a solution that allows a user to enter a score for a test item and see an associated converted score pulled from a related table. I have a parent table "Evaluations" and child table "Test_Scores." I have a layout based on Evaluations with a portal showing records from Test_Scores. Test_Scores has a Raw_Score number field where scores for most test items are entered by the user (e.g., score = 1, 2, 3, etc.), an Item_Name text field for the name of the test item (e.g., Question_1, Question_2, Index_Score), and a Converted_Score field that is an unstored calculation that shows a lookup value (a converted score) from various related tables based on the entered Raw_Score and Item_Name (latter part based on Case conditions) of the record. Most of the records in Test_Scores are just individual test items, and the unstored calculation Converted_Score calculates correctly and immediately updates on the portal.
Here is where things get a little more complicated, but I still have this part configured correctly. Occasionally, I want a record in Test_Scores to be an index score (if Item_Name = Index_1) that returns a value from a related table Index_Scores representing values at the intersection of the previously-entered Raw_Score for Question_1 and Question_2. One problem that I had to handle to achieve this was that a record in Test_Scores with value of Item_Name = Index_1 needs to know what Raw_Score value was entered in other records with Item_Name = Question_1 and Item_Name = Question_2. I have solved this problem using conditional summary fields in Test_Scores that calculates the Max Raw_Score of Question_1 and Question_2 matching the parent record (Test_Scores fields named: Max_Score_1, Max_Score_2). This index table where I am matching the intersection of these scores has a Raw_Score field (whose records have all possible values of Question_1) and several other fields named with a preceding underscore and all possible Raw_scores values for Question_2. So, fields in this index table are named: _0, _1, _2 … _10. Test_Scores is related to Index_Scores on Test_Scores::Max_Score_1 = Index_Scores::Raw_Score. The result is that if a record in the Test_Scores portal has Item_Name = "Index_1" and two other records with Question_1 / Raw_Score = 5 and Question_2 / Raw_Score = 8 , then Converted_Score should calculate the related value from Index_Scores::_8. This is all working correctly and returning the correct values in Test_Scores::Converted_Score by way of an indirect reference using GetField() in a case condition of the Converted_Score calculation.
So, my problem is this. I have three records in the Test_Scores portal with Item_Name = Question_1, Question_2, and Index_Score, respectively. If I change Raw_Score for Question_2, then the Converted_Score for the Index_Score record immediately calculates and changes to the new value in that record's portal row. However, if I change Raw_Score for Question_1, then the Converted_Score for the Index_Score record calculates but does not change to the new value in that record's portal row. The value does show if I refresh the window.
Does anyone know why the Converted_Score for Index_Score is immediately updated in the portal when Question_2 score is edited but not when Question_1 is edited? I think the issue has something to do with my Max summary fields not updating in the Index_Score record. I have tried various methods of calculating this Max field (summary fields, ExecuteSQL, parent table Sum() calculation field with mirrored calculation field in the child). None of these methods fixed the issue of Index_Score not updating after editing Question_1. I'd prefer not to use a Refresh Window script trigger because of the interface lag it causes.