1 2 3 4 Previous Next 49 Replies Latest reply on Dec 21, 2011 10:19 AM by philmodjunk

    Track Status Changes



      Track Status Changes




      At our hospital, I've created a Filemaker database that has all our department's patients who are being considered - or once were considered, for a transplant. When a new patient is referred to us, they get entered into the database, and their status is selected from a dropdown value list, as either 'considered for transplant,' 'unconsidered for transplant,' 'transplant complete,' or 'rejected for transplant.'


      I have set up creation timestamps and modification timestamps for each patient record. However I would like to create a report or perhaps a portal, where it displays when a patient's status changed, i.e. what date did a patient go from being 'considered for transplant,' to 'rejected for transplant.' And then being able to find how many patients in the last year for example went from 'transplant considered' to 'rejected.'


      Thank you for your help!!


        • 1. Re: Track Status Changes

          A portal would make sense for this and you'll need to create a new related record for your portal.

          Define a new table and add fields so that you can use this portal for your relationship:

          CurrentTable::PatientID = StatusLog::PatientID

          Move the status field from your current table into the new StatusLog table. If you have existing records with real data in them, you can use a looping script to create a matching record in StatusLog and copy the status data into this new related record.

          If you double click the relationship line for the above relationship, you can select "allow creation of records via this relationship" for the StatusLog table if you want to log status changes simply by scrolling to the bottom blank portal row and selecting the new status. You can also add a button that uses a script to create the new records if you prefer.

          • 2. Re: Track Status Changes

            If possible, I would like only the most current status to display on the CurrentTable (Patient Info), because we use the layout for meetings and it would be confusing to the doctors to have a portal on the layout with several different statuses. Is there a way to keep the current status on the original layout and only show the most recent portal record for that patient?




            • 3. Re: Track Status Changes

              To show just the current status, use:

              A one row portal and specify that the portal be sorted in descending order by a date or serial ID field so that the most recent record is listed in this single row.

              To show the entire history, you would show more than one row and include a scrol bar. It can be sorted in the same order as the first portal or sorted so that the most recent status is listed last.

              • 4. Re: Track Status Changes

                Thank you for your help. I've set up the following table and relationship:



                Patient Info: Bio info (name, DOB, etc); Pre-TransplantID

                Transplant Status Log: Pre-TransplantID; Transplant Status; Date Status Entered



                Patient Info::Pre-TransplantID = Transplant Status Log::Pre-TransplantID


                Currently I have a field in Patient Info table that has the patient's transplant status. I do not want to lose the current data and I'm not sure I quite understand how to go about the Looping script so I do not lose the current data.


                Also, for the layout  - Do I want to insert the portal on the Patient Info layout, and then only display one row in date order?


                Thanks again,


                • 5. Re: Track Status Changes

                  I'm not sure I quite understand how to go about the Looping script so I do not lose the current data.

                  #Run this on the patient Info layout
                  Show all records
                  Go to record/request/page [first]
                    Set variable [$ID ; value: Pre-TransplantID ] //see note at end about the Pre-TransplantID field name
                    Set Variable [$Status ; value: Patient Info::Transplant Status ] //guessing the field name here use the name in your table.
                    Go to layout [Transplant status Log]
                    New record/request
                    Set Field [Transplant status Log::Transplant Status ; $Status]
                    Set Field [Transplant status Log::Pre-TransplantID ; $ID]
                    Go to Layout [original layout]
                    Go to record/request/page [next ; exit after last]
                  End Loop

                  Once you've run this script and confirmed that the data was all successfully moved from Patient Info in to Transplant Status Log, you can delete the status field from patient info.

                  Also, for the layout  - Do I want to insert the portal on the Patient Info layout, and then only display one row in date order?

                  That's certainly a good option though not your only one. As long as you start with a layout based on Patient Info, you can add a portal to Transplant Status Log. As I suggested earlier, you can even put a tab control on this layout and put the same portal on both panels of the tab control--one as a single row portal showing only the latest status log entery and one showing all the entries in a scrolling version of the portal. Just make sure that your one row portal has the correct sort order specified so that the one record shown is for the most recent status.

                  Note: I suggest changing the field name: Pre-TransplantID to PreTransplantID. Unless this it is a table view layout, the text you use to label such a field, can be edited as needed so the text on the layout can still read Pre-TransplantID when the field name in Manage | database | Fields reades PreTransplantID.

                  Inclucing math operators such as the - in a field name results in calculation expressions that are more cryptic when they need to be when FileMaker automatically includes a $ and brackets {} to show that this is the name of a field and not a two fields, one subtracted from the other. It can also affect your ability to indirectly reference this field in certain script steps.

                  • 6. Re: Track Status Changes

                    I've created the script and tried it out, however no PreTransplantID or statuses transferred over.


                    For some reason on the part of the script:

                    Set Field [Transplant status Log::Transplant Status ; $Status]

                    it will not allow me to write in $Status into the script - do I enter in $Status as the repitition? But then it does not look like the script that you wrote. And if I specify the target field, it does not give me the option to enter in $Status


                    The same issue happens for the PreTransplantID and $ID


                    Any ideas?




                    • 7. Re: Track Status Changes

                      Without the set field steps correctly specified, the script won't work.

                      When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

                      • 8. Re: Track Status Changes

                        Great - the loop worked!


                        So to have the timestamp be entered on the TransplantStatusLog, I just make a timestamp to auto-enter on creation?


                        Thanks for all your help!

                        • 9. Re: Track Status Changes

                          On creation or On modify are both possible. You'll need to think about that and decide on best option for this. On creation makes most sense if you are sorting your portal on that field to put most recent entry at the top. On modification may be useful if you need to know when the entry was last changed by someone.

                          • 10. Re: Track Status Changes

                            The portal works great and it creates the appropriate time stamp when a new status is entered.


                            I am trying to think about if a button would work best for my scenario: 

                            the problem is we use this database during a meeting where if someone is being unconsidered or rejected for transplant, a drop down list is available on the transplant status field to change the status. However, now we need to keep track of the dates/status changes (hence the need for the portal). The problem is, if I display the portal on the layout, it would display only the most recent change, but if i need to add a row to the portal, then I would have to display more than one row and the previous status would also show.


                            I was looking through the scripts and wondering if it was possible to somehow have a button that would enter in the date of status change into the newly created portal, while also changing the portal row that is currently displayed on the layout.


                            If you haven't noticed, I'm fairly new at scripting and trying to figure out the best way to do this.


                            Thank you for any ideas!


                            • 11. Re: Track Status Changes

                              Add a button to your layout near your portal. Set it to run this script:

                              Freeze Window
                              Set variable [$ID ; value: PreTransplantID ]
                              Go to layout [Transplant status Log]
                              New record/request
                              Set Field [Transplant status Log::PreTransplantID ; $ID]
                              Go to Layout [original layout]

                              You should see that this is very similar to the looping script I posted earlier. In fact, I copied a section of it down here and then made the needed changes.

                              • 12. Re: Track Status Changes

                                How do i enter in the new status? will I need to keep more than one row on the portal that is displayed on the layout? Or does this button take me to the TransplantStatusLog layout and I enter in status on that table?

                                • 13. Re: Track Status Changes

                                  Try it out and see what happens.

                                  What you should see is that the transplant status shown in your one row portal goes blank, then you can select the status for this new portal record in your drop down list of status options.

                                  • 14. Re: Track Status Changes

                                    Hmmm, just had an interesting idea. If you want to ramp up your scripting skills, it's possible to design the layout so that selecting a different status in the status drop down generates a new record in the log table while keeping the previous portal record unchanged.

                                    It'll look to the user like all they did was change the status, but when you look at the history portal where you can see multiple log entries, you'll see that a new log record is added each time. It takes working with script triggers and global variables so let me know if you want to try out this more sophisticated approach.

                                    1 2 3 4 Previous Next