The fact that you are importing the change data was a detail I did not spot in your original post. (You could have used Post a New Answer in that thread to post this follow up info.)
There are 3rd party "synch" tools for FileMaker by 360works and SeedCode that you may want to evaluate--especially for something as critical as patient records.
Do you have any control of the the way the imported data is organized? It appears that you get two changes to a single record and this is less than ideal for any system you use to apply these changes. This also explains why your had script checks checking the value of these "IF" fields.
I can suggest a script here, but I see a very dangerous deficiency in this data that could result in incorrect changes to your patient. I don't see any way to identify the correct patient record in your screen shots except by name and patient names are both non-unique and subject to change.
You need a patientID to import with the other data shown.
You have a ptkey in one record, but you must have this value for every record separate from the data that is logged as a change or you could get wrong data into a patient record with tragic results.
Your Set Field By Name script that you used in your original script is the right script step to use for this type of updating, but you have to be able to identify and link to the correct patient record first.
Thank you so much for answering. The real file includes over 100 fields, including unique identifiers. For sake of simplicity (as it is hard to explain my situation) I used only 3 fields. As of now, there is just 1 change to each record, although, I could see where things could go wrong easily.
As for your last line, I agree completely, and this is what I am having trouble with.
Thanks for answering my question, and if you have any other ideas, please pass them on.
Just to clarify, the problem we are having is not with matching the data to a single patient. We simply want to enter a constraint, or a few constraints to change any number of records fitting that/those constraints in a blanket-type change. Any ideas of why the current script doesn't work / any suggestions for a better script would be much appreciated.
From what I saw in your original script you DO have a problem matching up data to the correct patient record. The script you posted previously in the other thread made no effort at all, that I could see to match up the change records to specific patient records nor was their any code to avoid making the same changes more than once..
And a patient ID value should NEVER EVER be changed once assigned to a patient yet one of your example records shown in the initial post of this thread appears to do exactly that.
To describe a script for what you want, I must know exactly how you identify the patient record to be changed by each record imported into Test Database 1. Once I have that info, I can describe a script to do what you need, though the "two changes per record" format will complicate this script.
-We use a varying field to identify which records we want to change EX: Sometimes we want to find all records that have the Manufacturer Name of "Manu 1" and change the Manufacturer Name for all of them to "Manu 2" Ex2: Find any record that has Manufacturer Name of "Manu 1" AND Product Name of "Brace 1" and change the Product Name to "Brace 2" Ideally, we would like to use a find to create a found set and change all of the records in that found set, and then use a new find to find the next set of records and change those, etc... but we haven't yet found a way to search in only 1 field and cross match it to a 2nd field, and so we are just planning to run the script which tests every record in our patient records table against every record in the "If/Change to" table.
Sorry, but you have two types of data here:
Data that serves as search criteria to find the correct record to changs.
Data that documents what data to change and to what new value.
I do not see yet, how the first data is specified. I would have expected something like this:
PatientID Field To change New Value
123 Name Smith
456 age 45
With column 1 used to identify the record to change, a script can perform a find or use a relationship to access the correct record. It can then use Set field by Name to modify a field of that record.
But, so far, I do not see in your data what identifies the specific record to be changed. And if this data is in more than one table seeing as your first example was for patient records and now you are referring to a manufacturer (of a medical device perhaps?) then you would need one more column to identify the table in which this data to be changed is stored.
The criteria of change is itself variable depending on wha the change is. It is not just going to be a Patient ID. Sometimes a criteria may be a manufacturer, and other times, we may simply want the script to change someone's name.The reason that we had the "If field 1" in table 1, is so that we can match, or try to match up the correct record using that data. These items that we are searching through are invoice line items. They have both patient data and product data. This is why we can't use patient ID as a sole identifier.
Which does not change the fact that I do not see sufficient data in your table of imported data to successfully identify what record of what table needs to be updated with the imported information.
In your original example, the first row only specify a patient's first name. As I've previously stated, that won't work as you might have several dozen records with a first name of "David". You must specify some field in the table that always identifies one and only one record or this process could easily scramble data stored in that table.
And if this data is in more than one table seeing as your first example was for patient records and now you are referring to a manufacturer (of a medical device perhaps?) then you would need one more column to identify the table in which this data to be changed is stored.
I used a patient ID column in my example as it seemd consistent with your original description of the problem. But the data might look like this:
Table Primary Key Field Name Value
Patient 23456 Street 123 Main St.
Mfg 123 Description Heart Valve
but note how the first two columns tell you how to find the correct record in the correct table while the last two tell you what data within that record to modify.