You do not need additional TO's.
You do not even need to look up or copy the data from one table to the other though there are times when you need that option. You should be able to add fields from Requesters to a layout based on Requests and then those fields will display data from that matching Requester record. This works when you have just one Requester for a given Request record. If you have many, you'll need to use a portal or other approach.
First I would recommend against using the RequesterFullName and a key field in the relationship. It is more reliable to use a serial number.
Once you have a relationship between the two tables, you should be able to display any of the Requesters fields on a layout based on the Requests table, as long as the primary and foriegn keys match.
OK, then I'm not sure why I'm having the issue that I am having.
I have a number of tables (the image is large, so I'll try to describe this here).
Department --> Requester --> Requests --> SampleDataEntry --> Analysis --> Analyst
I also have Analysis --> InstrumentType --> Instrument of the end of the same chain.
The relationships here are as follows (most being set by a value list from filed with both the name and ID, and only the name being shown):
Department::DeptID = Requesters::DeptID
Requesters::RequeterID = Requests::RequesterID
Requests::RequestID = SampleDataEntry::RequestID
Samples::SampleID = Analysis::SampleID
Analysts::AnalystID = Analysis::AnalystID
Instrument::InstrumentID = InstrumentType::InstrumentID
InstrumentType::InstrumentTypeID = Analysis::InstrumentTypeID
I have run into a couple of issues. I have a layout called "Sample Labels" that is based off of SampleDataEntry. It has a field SampleDataEntry::SampleDispositon, which is defined as a lookup from Requests::SampleDisposition. The Requests::SampleDisposition is also defined as a look up from Requesters::SampleDisposition. It is done this way because the disposition is not always the same, but we do have a "default" value (for example, samples from R & D are normally disposed of upon completion, but sometimes they are returned). So, we want the "default" value to be put in at the request level, but we need to be able to change it and not have that reflected in the Requester record. We also need that information to flow from the Request screen (which we use to generate "invoice" type acknowledgements of work in the lab) to the sample labels, which are based on the SampleDataEntry table. I think all the look ups are done right (they are all set up the same way), but the disposition does not appear on the SampleDataEntry table, or on the labels. Further, the email and name should flow in the same way, and they do not appear on documents created off the SampleDataEntry table.
The other issue I'm having is in reporting. I want to set up a report based on the analyst, and show only what they are assigned to do. I can get all the requests/analysis they are assigned to do to show up, but cannot figure out how to remove analyses they are not assigned to do for the same sample.
I cannot upload files from here, but can upload them from home if need be.
I appreciate any help I can get on this. I recognize that probably all the problems I am having are coming from the same place, but can't figure out where that is. I thought that perhaps I needed more TO's to fix it, but that is probably not the problem.
All the basic design aspects look correct. I was all set to tell you toss the sample Disposition fields and just use the field from Requests until I read far enough to see why you use the look up. Somehow that chain of look ups is being broken. I'd trace your work flow from Request to SampleDataEntry checking to see if the disposition fields are each correctly looking up values to see where this chain breaks and why. If I found an empty disposition field in SampleData Entry, I'd first confirm that I have a related record in requests, then examine it's disposition record to see if it has a value and on back up the chain.
I'd also check for Requests or Requester records with duplicate ID's. If you have two with the same ID and once has a disposition specified when the other does not the look up may be attempting to look up a disposition from a blank field. Also, the order in which records are created can break the chain. This seems unlikely here, but if you create a SampleDateEntry record before you create a Request record, the value would not be able to be looked up.
Your report problems sound like a separate issue. I suspect that you can use a constrain found set action to filter down your record set to just those Analysis records assigned to a given analyst after pulling up all analysis records for a given requester, request, or SampleData record.
Thanks! I will try working through that first thing tomorrow. I appreciate all the hellp on this!