Layout Based on what table/relationship
Please help! I'm going crazy trying to figure out what I'm doing wrong.
I have the following tables:
ClientInfo:: CIDPK, Name, Address, Etc
Referrals::RIDPK, CIDFK, ReferralDate, Referral Source, Etc.
Service::ServicePK, RIDFK, ServiceDate, ReviewDate, Etc.
Clients_Reports::CRIDPK, CIDFK, RIDFK, ServiceFK
Relationships as Follows:
ClientInfo::CIDPK = Referrals::CIDFK
Referrals::RIDPK = Service::RIDFK
Client_Reports = each of the PK in the three other tables.
Each client can have multiple Referrals and each referral can have multiple services.
On a layout based on the Service table (which shows fields about the client and the Referral the service is a part of) I have an image that I want the user to click and using a script it will open another layout (which I had based on Client_Reports). It has merge fields showing the clients name, address, referral date, referral source and the service info. The script at the moment sets variable for each of the PK's, opens the layout based on the related record, creates a new record and sets the appropriate fields to the variable created. This does not bring up the correct referral/service info onto the layout. If I tried changing table the layout was based on I get that they are not part of a related table.
So I'm not sure, are my relationships wrong, is the layout based on wrong table or is the script wrong? Or all of it? Am I making this more complicated than it needs to be?
Please help, ready to pull my hair out.