8 Replies Latest reply on Aug 11, 2015 3:08 PM by philmodjunk

    Need Help with Script



      Need Help with Script


      Hey guys, I already made one post about this, but I feel as though I did not explain my problem correctly. 

      This is my project: I work for a company with nearly a million patient records, which is stored in a database in Filemaker. Due to periodic imports, we need a script that will auto-update the main database with the updates given in a second layout. I have attached below a picture showing an example of what the two layouts might look like. The "Test Database 1" represents the imports of changes that need to be made, and "Test Database 2" represents the main database with patient records. I have also included at the bottom of the first picture the "Test Database 2" if the script were to work correctly. As you see, it changed the first patient's insurance name and the 2nd patients name. I tried scripting it, and here is my current script (with different layout names because I can't show real patient info!) http://i.imgur.com/yNWBkZo.png 

      **Note** "Changes to Make" in the script would be "Test Database 1" and "BTWeeklyReport" would be "Test Database 2

      The hard part about this is that because the target fields to change are variable and I need to find and match the patient records, that I cannot use many of the commands in the scripting of filemaker. The problems I am running into at the moment are: the script does not loop through properly, no changes are being made, and the variability in what fields need to be changed mess me up.

      Also, would there be a more preferable way using finds to script this?

      Any advice would be greatly appreciated.


        • 1. Re: Need Help with Script

          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.

          • 2. Re: Need Help with Script

            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. 

            • 3. Re: Need Help with Script

              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.

              • 4. Re: Need Help with Script

                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.

                • 5. Re: Need Help with 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.
                  • 6. Re: Need Help with Script

                    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.

                    • 7. Re: Need Help with Script

                      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.

                      • 8. Re: Need Help with Script

                        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.