Incorrect Data Returned for one field in Portal

[rlemenchick]

First off, I'd like to give a big thank you to all of the contributors here on the board. I decided to try my hand at creating a database recently and many of the issues I've run into have been already solved for other users by the combined knowledge of this board and others...

 

... but now I'm stuck with an issue that I'm not even sure how to phrase the search for a solution to.

 

The purpose of the file I'm creating is to audit employees in our company on various policies, procedures, and guidelines in the field to ensure they are compliant. The file contains tables for (relationship diagram attached);

• Employees
  • Business units (employees are assigned ot various Business Units)
  • Job Positions (assigned to the business unit that they apply to)
• Safety Test Occurences (Details where test occured, how/when/who performed test)
  • Safety Test Line Items (child to Occurence, type of test, result of test, comments, follow-up action, etc.)
    • Test Type Groups (grouping of tests)
    • Safety Tests (individual tests with related info on how to perform, assigned to a Test Type Group)

 

I have an employee information page (based on employee table). There are two portals; the first portal is based on the Safety Test Occurences table and returns the information without issue.

 

The second portal is based on the Safety Test Line Item table, is filtered by the selected entry from the portal using a variable. This portal layout has two field from child tables Test_Type::test_group_name and Safety_Tests::safety_test_name. From what I have observed, all data displayed in the portal for field Test_Type::test_group_name is correct, however the data for Safety_Tests::safety_test_name seems to only be returning the first record associated with the Test Group that has been selected, and does not match the data from the primary key data for that record. (not sure if I explained this part correctly).

 

At first I assumed that this had to do with the layout being based off the Employee table, and the safety tests being too many relationship steps down through the portal. I figured I could get around this by creating a text calculation field in in table Safety_Test_LineItems which stored the Test ID and Test name from the Safety_Tests table associated with the record. To my surprise I found that the calculation was returning the same data as the portal, the data from the first record which matched the relationship between Test Types, and Safety Tests, not the data from the record that was selected.

 

So now I think that I either have a relationship issue, or an issue with the way the data is stored / indexed.

 

I hope I've provided enough info. The only other thing that I could mention is that I have a value list that that only returns safety tests that match the test group that is used. I'm pretty sure that's not the culprit though.

 

Thank you in advance.

 

Ryan

 

 

[Matthew F]

Is the relationships between Test_Type::Safety_Tests  a 1:1 relationship?  If there's more than one Safety_Tests that match the Test_Type, then the Line Item portal will just display the first match.  

 

Its a little odd to be putting fields from related tables in a portal because of this issue.  Its hard to predict which value is going to match if you're reaching through multiple levels of relationships. 

[rlemenchick]

Matthew - thanks for the reply. The relationship is many-to-one.

 

I actually figured out the issue this morning by starting at the beginning, and working my way through it. The issue actually resulted from referencing a the field in the the wrong table occurance of the Safety_Tests table. I'm not sure why this causes an issue, but I suspect that it's due to the other T.O. being filtered with the Test_Type.

 

By using the field from the T.O. Safety_Tests 2, the data in the portal on the Layout based on the Employee table returns the proper data, and the calculation also returns the proper data.