Yes, we need to see your file. Can you upload it somewhere (even one of the free sites such as 4shared.com) and provide a link to it here? Otherwise, I have sent you a private message with my email if you wish to send it only to one person for review. It is best to keep the discussions here so others, who may run into similar situations, can benefit from it.
I have reviewed your file. You are looking up Meds into Client Notes AND Encounter Notes? I am unclear on these two tables - I would combine them into one called Encounters. There are only one or two fields which are different and the remaining 11 fields are the same. When the record is created, you can specify a Type to distinguish them if need be. But I will respond based upon your file which has form called Encounter Form which is based upon Clinical Notes.
If you want the 'list of medications at the time of the encounter' to remain static then you will need to plant that data into the Medication field when the Encounter record is first created. You cannot use a *lookup because you need to gather values from multiple related records. Try this:
In your Clinical Notes table on your Medication field, change from Lookup to Auto-Enter by calculation:
Substitute ( List ( trialB medication::medication ) ; ¶ ; ", " )
This writes (at the time the Encounter/Clinical Note record is created) all related medication records to the Medication field. The result will look like this (on record 4 in your sample file): Toprol 50 daily, Lisinopril 20 daily
Two additional notes:
1) This will not work with existing data. If you have existing data then do this ... back up first then change the new auto-enter calculation from standard text field to type calculation. Be sure the result type in the calculation dialog (at bottom) says text. Then exit clear out of field definitions and go back in. Now change it back to auto-enter text.
This takes the calculation (which is indexed) and plants the values in all the records in your table (using each record's related meds). Of course you cannot capture backwards in this situation ... if you have older records from last year, system will list all CURRENT meds on the record but there is nothing you can do about that type of backward consideration.
You could also run a loop or Replace Field Contents (to update older records) but both take more time and also require you be on a layout based on the table and that you also show all records. By changing the value via Field Definitions, it works for all records no matter what. Whenever making changes to data, be sure other Users aren't in the system (if hosted).
2) This type of auto-enter works when an encounter record is created. If you later, during this same encounter record, add a med which should be included in this record, your Notes table can't *see* that addition. You can place a button next to the Medication field called UpDate Meds with:
Set Field [ trialB medication::medication ; Substitute ( List ( trialB medication::medication ) ; ¶ ; ", " ) ]
* You could use a lookup by defining a self-join from the notes table to itself and creating the calculation instead in notes then use Lookup to grab the calculation but that requires more resources. I also highly suggest that your PatientID be an FM-generated, auto-enter serial number.
Please clarify if I'm off base or missed something. :^)
ADDED: If you want to keep the list in multi-line instead of using commas then use only this: List ( trialB medication::medication )
thank you so much for your suggestions.
Let me give this a try and repost if I get stuck
Worked great, thanks for your help