7 Replies Latest reply on Sep 18, 2013 2:23 PM by philmodjunk

    Script problem

    EP

      Title

      Script problem

      Post

           I'm working on a solution that allows a healthcare intake department to create new cases in the database.  They track "NEW" referrals and want new referrals tagged in the system. Basically, I created a field (OfficePatients::CaseCount) to track how many cases the patient has attached to their record.  I'm attaching the script that I created to tag "new" referrals.  I imported old referrals, so part of my IF statement accounts for that by specifying that the _pkClientID must be a number lager than 244 (serial ID).  The next part of my statement specifies that OfficePatients::CaseCount field has to equal 1.  If it is equal to 1, then the OfficeCases::NewPatient field is set to "NEW", which is later viewed on a cases referral report of "NEW" patients.  I think the problem lies in having the script identify the OfficePatients::CaseCount field as 1, as it has been working reliably for a few months, but every so often a patient gets tagged incorrectly as "new" (maybe a connection issue, or the order in which the end user is entering info?).  This has me puzzled and I am wondering how I can make this script foolproof.  

           Thanks

      Edit_Script__OFFICECreateNewCaseConfirmation___Score_crm_.jpg

        • 1. Re: Script problem
          philmodjunk

               is _pkClientID a field of type number or text? If it's text, your expression can fail. With text values, "3" is greater than "244" so that can be a source of your trouble.

               How does CaseCount get a value? Is it a calculation field? Does it count related records in OfficeCases? What expression do you use?

               When the script step executes, are you on a layout based on OfficePatients or OfficeCases?

               Instead of a script, I'd set up a calculation field in OfficePatients, not OfficeCases, that returns "New" if the ID is greater than 244 and the count of OfficeCases is less than 2.

          • 2. Re: Script problem
            EP

                  

                 is _pkClientID a field of type number or text? If it's text, your expression can fail. With text values, "3" is greater than "244" so that can be a source of your trouble.

                 -It is a number field

                 How does CaseCount get a value? Is it a calculation field? Does it count related records in OfficeCases? What expression do you use?

                 Calc field:   Count (OfficeCases::_fkClientID)

                 When the script step executes, are you on a layout based on OfficePatients or OfficeCases?

                 OfficeCases

                 Instead of a script, I'd set up a calculation field in OfficePatients, not OfficeCases, that returns "New" if the ID is greater than 244 and the count of OfficeCases is less than 2.

            • 3. Re: Script problem
              philmodjunk

                   You may need to add a commit records script step to your current script before the count function accurately counts related records all the time. That will force ;your newly created OfficeCases record to be included in the count computed from the context of OfficePatients.

                   But as I have already noted, there's another way to do this that seems a lot simpler...

              • 4. Re: Script problem
                EP

                     I'm going to add a commit records command right before the set field commands that I highlighted and see how it goes.  

                     I'm not sure if using a calc field in officePatients would work here because  OfficeCases is used to track referrals (orders) and I need to show which referrals in intake are "new" to the company.  I need to take a break soon, so it is possible that I am just missing your point.

                • 5. Re: Script problem
                  philmodjunk

                       Any layout based on OfficeCases can show data from any field in OfficePatients via the relationship that links the two table occurrences. You can simply add any field from OfficePatients to your OfficeCases layout and it will show that data--such as a calculation field that shows "New".

                  • 6. Re: Script problem
                    EP

                         Thanks Phil.  That makes sense.  But what happens when the same customer makes another "order"?  They are no longer "new" and they will be lost from the new patient reports history. This is why I baked the functionality into officeCases, because the "new" tag would live forever in that case (or "order") and when a report is generated if a patient appears 3x, only once will they have been "new."

                    • 7. Re: Script problem
                      philmodjunk

                           Hmm good point. Having been to the doctor this week, I was picturing a case where you needed to flag new patients only during data entry as a way to make sure that staff got all the need info such as your health insurance and address.

                           See if you can set up an auto-enter calc in OfficeCases that enters "new" for new clients. You can set up a self join between OfficeCases and a new Tutorial: What are Table Occurrences? of OfficeCases linked by client ID and then count the number of records. New clients will only have 1 matching record as the record will match to itself. Records for repeat clients will match to more than one record of the same client id.