Hi FM Mavens, this will be long, I am sorry.
Small medical office database I am writing for myself. 4 tables: "Pts", "Encs", "Rxs/RxDup" (patients, encounters, prescriptions - patients have several encounters, encounters have several prescriptions.) RxDup is a duplicate of Rxs but that is not why it is called RxDup, it is called that because it is used to duplicate individual medication orders.
Mac OS 10.13.2, FMPro Advanced 220.127.116.110. The overall setup presented in the following is only for context and may seem complicated but the question will be very simple.
Task: (1) in an arbitrary number of records, that are seen within a portal in Pts that looks at Rxs, set the match field to a certain value, then (2) go to the records that become related via this act. (3) Once there, duplicate the found records. (1) and (2) are problematic.
Relationships: Pts - Encs via _PtID, Pts - Rxs also via _PtID, Encs - Rxs via _RxID and Pts - RxDup via _RxDupID.
Match field _PtID contains the serial number of patients. It is added to every new encounter record for any patient in table "Encs". This is clear and it works as it should.
Match field _RxID contains a combination decimal number obtained as _PtID + encounter serial for that patient * 0.001. So for patient #25, prescriptions written on visit 1 will be matched between "Encs" and "Rxs" via 25.001, etc. This also works as it should.
As table "Rxs" also has a field to mach _PtID, all prescriptions are visible from table "Pts". This also works as it should.
I mention the foregoing only for context, there are no problems up to this point, they start in the next paragraph.
To create a match between tables "Pts" and "RxDup", the value of _PtID gets combined with the term "dup" (a text field; e.g. "25dup").
In table "Pts" this value is contained in _RxDupID which is a static calculation text field containing the calculation [_PtID&"dup"].
In tables "Rxs/RxDup" the corresponding field (_doDup) it is a text field whose default value is "". When I want to select records for duplication, I set _doDup to the match value. The setting occurs via clicking a checkbox on in the portal that is looking at "Rxs" from "Pts" via the _PtID relationship. Clicking on or off the checkbox sets _doDup to "" or [_PtID&"dup"], as needed. The value list of the checkbox contains a single value, which is the value of _RxDupID in the active "Pts" record. This value is defined via "Use values in field...", obtained via a Pts-to-Pts self-relationship using PtID (Pts selfRel::_RxDupID). This seemingly also works, i.e. the value of the match field in the portal is the correct single value, and clicking the checkbox on or off changes the content of the match field _doDup as it should. I assume that this occurs in table RxDup as well necessarily and simultaneously. (Am I correct?)
The following happens:
Either I just opened the database then I went to today's set of patients, OR I am already at the set of patientes but I just edited and saved the script. Let's say, there are 5 patients whose records are in today's found set. I go to one of them, it is Pt #25. I tab to the portal showing table Rxs, there are 30 previous rx orders showing. I click the checkbox in 3 records. I see it confirmed that the match fields _doDup are set to what they should be (PtID & "dup"). I run the script
Go to Related Record [ From table: “RxDup”; Using layout: “Rxs DUPLICATOR” (RxDup) ]
[ Show only related records ]
It does what it is supposed to do. It goes to the 3 related records in the correct layout and does (dup) on them.
Then I go to another one of the 5 patients in the found set. Let's say it is Pt #56. I tab to the portal showing Rxs, let's say there are 50 previous rx orders showing. I click the checkbox in 6 records. I see it confirmed that the match fields are set to what they should be (PtID & "dup"). I run the script again.
Here it becomes dicey. There are a few ways things go wrong here:
- The script takes me to the previously selected 3 related records of Pt #25 and does (dup) on them.
- The script takes me to the related records of Pt #56 but not all 6 show. If I go back to the portal in Pts and run the script again, this time it may show all 6 records.
- The script takes me to some or all of the related records of Pt #56 but in some records the match field is blank. (This really baffles me)
Then it becomes even more messed up.
I go to a third patient in today's found set, lets say it is Pt #77. I run the script again.
I get a 101 Error. The script does not go to the related records in table "RxDup", it stays in table "Pts" and duplicates the 5 records there.
This repeats reliably. On first run after a save, or after opening the file, and sometimes on second, the script works well. On runs 2 - 4 it makes an error in going to the correct (or correct number) of related records. Eventually it does not go to the related records at all.
The indexing of both match fields are set to "All". I tried "None" with "automatically create indexes" but the result is the same.
My guess is that something goes wrong with either the indexing or with the value list. Am I correct? If yes, that is the problem? If not, what else can it be? Any help would be much appreciated.