7 Replies Latest reply on Feb 15, 2012 5:39 PM by SKY

    Duplicating a record with portal information

    SKY

      Title

      Duplicating a record with portal information

      Post

      I know that this has been posted before, especially here:

      http://forums.filemaker.com/posts/fe15111e7e/comments/180368/edit

      and here:

      http://forums.filemaker.com/posts/ac6afe2913

      I tried to adapt the scripts to do a the same for my database.  I've been working on it for a few days now and can't get it to work.  I was hoping that someone on here might be able to help?  The part of my database that I need help with is the encounter.  When a patient returns, I would like to not have to punch in all the same diagnosis codes (ICD_9_ID).  For each encounter, there are ICD_9_IDs and CPT_IDs.  Often times the CPT codes change, so I'm not interested in duplicating these.  I would just like the new encounter to copy all the ICD_9_IDs over to the new encounter.  The encounter table is related to the ICD9 and CPT code tables via a join table.

       

      My encounter table is set to automatically create and delete records from the join table, so I don't have foreign and primary keys (I'm currently thinking this is my main problem trying to accomplish my goal).  I followed the links above, but am still having problems.  I assume that my join table is like the line items table from that script and that my ICD_9_ID is similar to the partsid, but haven't been able to get it to work.

      Any experienced FM users willing to help me out a little?  I'd really appreciate it!

       

      Relationships

      Encounter

      case join

        • 1. Re: Duplicating a record with portal information
          philmodjunk

          My encounter table is set to automatically create and delete records from the join table, so I don't have foreign and primary keys

          You have one primary and one foreign key in this table: EncounterID (primary) and PatientID (Foreign) and this is what you should have here.

          The structure looks correct and the scripted method I posted in Duplicating Bill Of Materials (duplicating portal line items) should work for you.

          I suggest that you post your script and describe what happens when you run your script.

          To post a script to the forum:

          1. You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
          2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
          3. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
          4. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format. (Use the HTML option on the database tab panel and paste the text into the forum's HTML editor.)
          • 2. Re: Duplicating a record with portal information
            SKY

            Phil, thanks for the response!  I'm not at work currently and don't have direct access to the database, but I'll copy my script.  Maybe I don't understand correctly or maybe I didn't do a good job of taking a screeshot.  Aren't the only keys I need, "EncounterID" and "ICD9_ID" ?

             

            Here is what I have been trying (Doesn't work):

             

            Freeze Window

            Set Variable [$RecordNumber; Value:Get(RecordNumber)]

            Duplicate Record/Request

            Set Variable[$ICD_9_ID; Value:ICD_9_Codes::ICD_9_ID]

            Got to Record/Request/Page[No dialog; $RecordNumber]

            If [not isempty(Case_Join_Encounter::ICD_9_ID)]

               Go to Related Record [Show only related records; Match found set' From table: "Case_Join_Encounter"; Using Layout: "Case_Join_Encounter"     (Case_Join_Encounter)]

               Go to Record/Request/Page [First]

               Loop

                  Duplicate Record/Request

                  Set Field[Case_Join_Encounter::ICD_9_ID; $ICD_9_ID]

                  Omit Record

                  Go to Record/Request/Page[First]

                  Omit Record

                  Exit Loop If [Get (FoundCount) = 0]

               End Loop

               Go to Layout [original layout]

               Got to Record/Request/Page [last]

            End If

            • 3. Re: Duplicating a record with portal information
              philmodjunk

              "doesn't work" doesn't tell me what happens when you run this script. HOW doesn't it work? That info can provide key clues as to how/why it didn't work. In this case, though, I can spot the error.

              Looking at your script, you are capturing the wrong key at the beginning.

              Set Variable[$ICD_9_ID; Value:ICD_9_Codes::ICD_9_ID]

              Should be changed to:

              Set Variable[$EncounterID ; value: Encounter::EncounterID]

              Then the set field step in the loop becomes:

              Set Field[Case_Join_Encounter::EncounterID; $EncounterID]

              This is needed to link each newly created record in the Join table to the newly created Encounter record.

              Note that this duplicates all data in the join table records. If you don't want the CPT_ID values from the original record to appear in the duplicate, you'll need to add a step after duplicating the record to set the field to "" to clear it.

              • 4. Re: Duplicating a record with portal information
                SKY

                Phil, ahhh, thanks so much!  Sorry I wasn't giving you all the information that could have been helpful.  You must have the patience of a Buddist monk to help out clueless beginners like me.  Works perfect now!  I haven't implemented setting the CPT_ID to "" yet, but will work on that later today when I have some time.  Thanks again!!!

                • 5. Re: Duplicating a record with portal information
                  SKY

                  I spoke a little too fast.  On patients that there have been multiple encounters, it is creating a new encounter and then inserting all the ICD_9_IDs from all of the previous encounters, not just the previous one.  Any idea how I can get it to just duplicate the previous encounter?

                  • 6. Re: Duplicating a record with portal information
                    philmodjunk

                    Sounds like you are performing the script from a layout based on patients instead of a layout based on Encounters.

                    • 7. Re: Duplicating a record with portal information
                      SKY

                      Phil, thanks again.  Somehow, I had chosen "match found set" instead of "match current record only" in my goto related record step.  After I changed that, everything worked great.  I was able to stop the new record from recording the CPT codes as well as some other codes.  I also had a few values that were automatically set to a specific value when records were created that I made sure were duplicated accordingly.  Since there may be others with a similar problem to mine, I have attached a copy of my final script.

                      final script