8 Replies Latest reply on Mar 1, 2017 4:18 PM by Stephen Huston

    I "moved" a field from one table to another. Need to copy values

    bryanade

      In my DB, I had patient weight in the Patient table, but we are actually interested in the weight of the patient on their visit dates (so there's possibly more than one weight per patient). I've created the new variable in the Visit table, but now need to copy the already entered weights from the Patient table to the Visit table. The two tables are linked in Relationships, but also there are two unique identifying numbers that can be used to verify patients. Would I do this through a script that I run just once?

       

      Thanks!

        • 1. Re: I "moved" a field from one table to another. Need to copy values
          siplus

          did you enter the patient weight as a list in a text field, or dou you just have the last measured weight ?

           

          Bad data is worse than no data, especially when we talk about health.

           

          On new visits you can display the related weight from "Patients" and decide if you want to copy the existing value or weigh the patient and insert the current, verified value into the weight field. That field from Patients should only be visible if sum(visits::weight) = 0, otherwise I'd hide it.

           

          I would not automatically copy data, let the doctor decide.

          • 2. Re: I "moved" a field from one table to another. Need to copy values
            bryanade

            Currently, every patient only has one weight. We're not that far into data entry/implementation where multiple weights are yet a problem; hence why I'd like to fix it now.

             

            Secondly, I am the doctor. So this is what I'm deciding

            • 3. Re: I "moved" a field from one table to another. Need to copy values
              philmodjunk

              From s layout based on visits, you could show all records and use replace field contents with the calculation option to copy over the weights from the related patient table.

               

              I'd try this first on a copy of the file as this cannot be reverted. Also, this can copy the same weight into multiple visit records for the same patient.

              1 of 1 people found this helpful
              • 4. Re: I "moved" a field from one table to another. Need to copy values
                siplus

                Ok, and I've been programming medical software for the last 20 years, we have hundreds of doctors as clients and my wife is a MD too . And yes, in our software weight, height, BMI etc are fields in the Visit, not in the Patient.

                 

                Now, back to the problem.

                 

                right now, in this transition phase, you have at least 4 possibilities:

                 

                1- patient is new (no data)

                2- patient is getting visited (has old data)

                3- patient has old visits and will come soon and you are studying the case

                4- patient won't come soon but you are studying the case

                 

                now what do we have to do in each of these cases ?

                 

                 

                1- no brainer. Weight will be inserted in his first visit, no data in Patient.

                2- IMHO show the weight from patient and decide if you want to copy it over in the current just created visit or weigh him.

                3- and 4- same situation, have a small portal with just one line and 1 field showing the weight from patient.

                 

                Now, the day after tomorrow you will have a mixed situation, i.e. patients having a verified or accepted weight in a previous visit and patients that don't have it. Therefore I suggested to show the portal with one value from patient as long as there's no visit holding a value for weight and hide it when this is not true anymore. It's de facto a transition move, covering your current situation. But it can be extended to other fields and values, too.

                 

                Another approach would be having the weight in visits as an autoenter of the existing value in patients, when the visitCount is 1 (just the one created right now). Or autoentering it from the last visit. But it complicates things, and as I said before, wrong data is worse than no data.

                • 5. Re: I "moved" a field from one table to another. Need to copy values
                  bryanade

                  Sorry, I don't think I was being clear: I'm just looking to batch copy all of the existing weights from the Patient table to the Visits table. Currently, every patient in the DB has only one visit, so I don't need to create a "transition phase", since weights will no longer be collected in the Patient table at all going forward from today.

                   

                  I think philmodjunk's suggestion will likely do the job though.

                  • 6. Re: I "moved" a field from one table to another. Need to copy values
                    siplus

                    So you created an empty visit (visit zero) for all patients, even if the patient did not come ?

                     

                    In that case yes, you can do a replace manually, Visit::Weight with Visit_Patient::Weight.

                    • 7. Re: I "moved" a field from one table to another. Need to copy values
                      philmodjunk

                      Speaking theoretically now, If you did find that you had multiple visits for the same patient, it would not be impossible to set up a found set where you have omitted all but the most recent visit. Then your replace operation would pull the data only into the most recent visit and leave the field blank for previous visits. This would then pretty accurately put the weights into the correct visit records.

                      • 8. Re: I "moved" a field from one table to another. Need to copy values
                        Stephen Huston

                        Use the 3 fields you will need in the new weights table: patientsPrimaryKey, weight, dateWeighed. Export them to a new FM dtatabase file. Check these records to remove blanks/missing data records.

                        Use that new FM file as your one-time source to import into the weights table.

                        Discard the FM temp file and, after you've checked the weight values match, delete the old weight field from the patient table.