To confirm, your value list lists values from the Specialties table, use only related values starting from Patients?
Things to check:
- Make sure that all of these fields are of type text.
- Make sure that the fields in the specialties table are indexed.
Yes to To confirm, "your value list lists values from the Specialties table, use only related values starting from Patients".
I have checked and confirmed that all the fields are text. Also the fields in the specialities field are indexed.
I see why your frustrated, everything seems correct, but it doesn't work.
One more thing I can think of:
When you select a "table" from the drop down before specifying the field and when you select a "table" in the "starting from" table, you aren't really selecting the name of a table, but rather the name of a table occurrence--the name of one of the boxes in your relationships graph. Since you can have more than one table occurrence for the same data source table--each for a different relationship, you must be careful to specify the names of the two table occurrences used in the relationship between Patients and Specialties--not some other table occurrence that happens to have the same data source table.
For the same reason, Show Records From in Layout Setup.... should list the same Table Occurrence as the "starting from" drop down.
At the moment there are no duplicate tables. It is a very simple database - which makes it all the more annoying !!!
can I email the file to you ?
Easiest is to post it to a file share site then post the download link here. If it's too sensitive for that send me a private message and I'll respond with an email address. Please do not include 1000's of records in any case. Use a clone or just a few sample records for demo purposes.
Ok, the relationships you described do not match the file. I figure you were trying to simplify your descriptions, but that's dangerous when trying to get help with a problem.
The Table Occurrence (TO) Care_Episodes was linked to Specialty like this:
Care_Episodes::Specialty = Specialty::Specialty.
At first I thought that the fact that the table was not directly linked to Patients like you described in your original post was the problem--especially when the fields involved were also not defined in Patients--they're related fields in Care_Episodes. I eventually determined that this was not a factor as you also had specifyed "starting from Care_Episodes".
I then looked at your layout again and determined that Care_Episodes::Specialty is not on this layout anywhere. Instead, the pop up menu for selecting a specialty was a field named Care_Episodes::Episode_Specialty. Thus, there was no value being entered into Care_Episodes::Specialty on which to relate to records in specialty table. You can either do what I did and change the relationship to match to Care_Episodes::Episodes_Specialty or you can replace that field on the layout with Care_Episodes::Specialty and leave the relationship unchanged.
Thanks-sorry about the confusion. One more question - any idea why my portal is only allowing one row - when I have asked for 5 ? thanks
Which portal? you have more than one.
Assuming you mean the portal on the Activity layout, you do not have allow creation of records via this relationship enabled for the Clinical Activity table occurrence. Without that option enabled, you can't create new records in this portal. (This relationship traces through an intermediary, "join" table, so "allow creation" must be enabled from Patients to the Join table (which you have done) but also from the Join table to Clinical Activity.)