Script loses related records

[Asu]

Hi FM Mavens, this will be long, I am sorry. The following is the setup:

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 15.0.4.400

Task: (a) selecting an arbitrary number of records in a portal within Pts that looks at Rxs by setting the match field to a certain value (b) going to the records that become related via this act and (c) doing something with those records (duplicate them). (c) is not problematic. (a) and (b) are.  

Relationships: Pts - Encs via _PtID, Pts - Rxs also via _PtID, Encs - Rxs via _RxID and Pts - RxDup via _RxDupID.

_PtID is a 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.

_RxID is 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 _PtID,  all prescriptions are visible from table "Pts".  This also works as it should. 

All these are static values. I mention these only for the sake of completeness, there are no problems here. 

_RxDupID is the value of _PtID combined with the term "dup" (a text field) e.g. "25dup". It is the match field between tables "Pts" and "RxDup".

In table "Pts" _RxDupID is a static calculation text field.  In tables "Rxs/RxDup" it is a text field that can be set to the match value or to "". The setting occurs via clicking a checkbox on or off in the portal looking at "Rxs" from "Pts" via the _PtID relationship.  The value list attached to the checkbox is the value of _RxDupID in the active "Pts" record, obtained via a Pts-to-Pts self-relationship using PtID.  This also works, i.e. the value of the match field in the Rxs records seen in the portal is correctly set to the single value that shows up in the value list. I assume that this occurs in RxDup as well necessarily and simultaneously. (Am I correct?)

The following happens:

I open the database, go to today's set of patients. I go to one of the 5 patients whose records are in today's found set. Let's say it is Pt #25. I tab to the portal showing Rxs, let's say there are 30 previous rx orders showing. I click the checkbox in 3 records. I see it confirmed that the match fields 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 ]

Do (c)

It does what it is supposed to do. It goes to the 3 related records in the correct layout and does (c) 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 can go wrong here:

- The script takes me to the 3 related records of Pt #25 and does (c) 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 really interesting. 

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 runs (c) on the 5 records there. 

This repeats reliably. On first run, 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 the indexing but I could be wrong of course. Any help would be much appreciated. 

Thanks, Asu

[Lee Smith]

Hi Asu,

A picture is worth a thousand words, or in this case a copy of the file, or a mockup of it would help.

It would be better if we could see the schema of the file, script, relationships, etc.

If it is an Index problem, you can turn the Index off for the field, Go into Browse Mode, and then return and reselect the indexing options. Storage Options.png

https://fmhelp.filemaker.com/help/16/fmp/en/#page/FMP_Help%2Frecovery-options.html

HTH

Lee

Edited February 12, 2018 by Lee Smith
The exhibits didn't post like expected

[Agnes Riley]

HI Asu,

Have you run your script in the debugger?

As a general rule of thumb I stay away from Go To Related Record in FileMaker. It can be missing a layout (or field) and you won't know until you open the script step and see what's wrong. I prefer to search and find the records I need. It's also a way that can be easily debugged. And it can't get me in trouble. :-)

I know a lot of people love it but I've seen it fail on me several times, so I thought I'd let you know.

Agi

[Asu]

No, I have not run it in the debugger, as it is a single step (action c is irrelevant to the script, as it can be any other action that could be done on a set of related records. ) and the naked eye can see what happens. 

My guess was that something was not working in FM and Agi's post supports my suspicion. And thank you  for the advice, I will return to FMPro v5 and do a search and find.

As for indexing, I have tried every variation and their combinations on both ends but to no avail. 

Thanks for all yall's help