Skip to main content

How To: Create an Events to Complete Subreport

EDIT 3/16/2018: Reader Jen G. pointed out that a staff member’s staff_id != people_id in all cases and therefore my approach would not always work for all staff. She proposed a different form to copy for this setup that will work 100% of the time so I have updated the post to reflect her proposed changes and fix my approach. Thank you, Jen!

This comes from a reader request. The goal was to create a subreport in the Agency > Staff & Security > Staff Information formset that would list the service events that a selected staff member had not fully completed. The subreport could be useful for supervisors to follow up on events that their supervisees needed to e-sign or otherwise complete, especially in the event where a staff member was transitioning out of the agency. The following is my proposed solution to that problem:

Step One: Create a User Sub Report

Navigate to Setup > User Tools > Sub Reports – User > User Sub Reports and create a new User Sub Report. Give the sub report whatever name and code you wish. For System Sub Report, select any report that has event_view as a Data Source Name. In my case, I chose the “Compass (Initial) Peer Delivered Services” report.

When you are selecting from this list, the only critical thing is the data source name. Everything else only speaks to how that particular report had been configured with the data source for that particular system report. It will tell you which columns were used to join and the remarks column will give you and idea of how it might be used “out of the box” but you can change all of those properties once you have the data source linked to your user sub report.

On your new sub report, change the following properties:

SQL Code: is_service_event = 1 AND is_locked = 0

This will filter the event log to only show events that are service event and only those that are not locked. The event_view has many columns in it that will allow you to customize this filtering to get exactly what you want to see. Some other options are: completed, is_amended, is_billed, is_deleted, is_e_signed, is_final

Report Fields: Pick any that you want to display. For my subreport, I wanted to show the client (full_name), the event (event_name), and the date of the event (actual_date).

The one that you need to have for sure is staff_id, which we will mark as a join column. In the Overwrite Form Field to Join, put some value (I used “staff”) and for the operator, choose “Equal”. Remember the Form Field to Join that you used for later. Below is a screenshot of my setup:

Step Two: Create Form

You don’t have to create a whole new form to house this subreport. You might choose to add the subreport to one of the already existing forms in the Staff Information formset. The important thing for this step is that the form you end up putting this subreport on MUST have the staff’s people_id staff_id on it. Maybe the form already has it on there or maybe you need to add it in the form of a variable. I will show you how to add it if it doesn’t already exist.

In my case, I am adding the subform to a custom version of the Personal Info formset member. So in form designer, under the Personal Information form family, I copied the Staff Personal Information Form. On my custom form, I hid all of the fields that I did not want users to see and I added the subreport that I created in part one. Now I need to make sure there is a staff_id named “staff” for the subreport to join on.

To do this, add a regular string variable to the form. Caption it whatever you want but give the variable name the same name as your used for your Overwrite Form Field to Join on the subreport created above. We need this variable to default in with the staff member you select’s staff_id so in the default value field, we will put the following code:

keyValue

Edit: Because I used a form from the Personal Information form family, the keyValue in this case will be a people_id and not a staff_id. Therefore it will only work right on those staff who have the same value for their staff_id as they do for their people_id and this is not always the case. If you copy a form from the Staff form family, you can simply use keyValue because it is the staff_id. But if you are copying a Personal Information form, use the following code to get the staff_id:

getDataValue('staff', 'people_id', keyValue, 'staff_id');

Here is a screenshot of my variable’s configuration:

Once you have confirmed that this field is pulling in a GUID, you can make this field invisible on the form.

Step Three: Create a New Formset Member

With your new form created, you can now associate it with a new formset member so that you can display it for users.

Navigate to Setup > User Tools > Formset Maintenance > FormSet Members and select the Staff Information Formset from the Agency module.

Create a new formset member, name it what you’d like and then select the Personal Information form Family and your new form as the Default Form. Make sure Is Active is checked and then save. Remember to go through the Navigation scheme setup and turn on the new formset for the users who will need access.

The Final Product

With everything setup above, you can navigate to your new formset member and select a staff member. Your form variable should get a default value of the staff you selected’s staff_id and your sub report should be joining on the that staff_id, thereby filtering the event_view to that staff member’s events. Your SQL Code statement on the user sub report will further filter the event_view to just those you are interested in seeing. In our case, the service events that are not locked.

How To: Create a Custom Formset Member that Shows Only Active Diagnoses

My agency has a program that requires that clients have a script for service on file in order for services to be billable.  Since a script for service requires a diagnosis, we configured the program’s services to require an active diagnosis in order to generate a claim.  As a result, we have client diagnoses expiring on the same date as the script for service.  When a client gets a new script for service, the same diagnosis is re-entered with a new start and end date.  After a few years enrolled in this program, a client’s diagnosis screen begins to get cluttered with inactive diagnoses so our clinicians asked for a way to just see the current diagnoses that were on file rather than all of the diagnoses that had been entered for the client over the years.

myEvolv includes as “Diagnosis – Active” formset member that looks like it was created to serve exactly this function but it would not list any diagnoses that have end dates on them (even future end dates).   Our program’s diagnoses are entered with end dates since we need them to expire at the same time as the script for service.  We were able to get what we wanted by creating a custom formset that uses a custom default form with a modified list condition.  I have outlined the steps we took to accomplish this for our specific scenario but you could apply this in other situations where you want to customize the listing in a formset member.

Step 1: Create a New Default Form for Active Diagnoses

myEvolv’s Active Diagnosis formset member used the “Diagnosis form for listing – active only” form as its default form so we used it as the basis for our new form.

In the form designer, open the “Diagnosis Information” form family.

Right-click to copy the “Diagnosis form for listing – active only”.  Give the new form a name and code.  Ours is called “Active Dx for MyEvolv Launcher”

 

Step 2:  Modify the List Condition

In the new form’s header properties, edit the “List Condition Property”

list-condition-edit

Change the list condition code as shown below

list-condition-code

Explanation of Code and the List Condition Property

myEvolv’s code: event_log.end_date is null and event_log.is_deleted = 0

The List Condition property allows you to use SQL statements to manipulate the data that will be listed on the formset member.  Anything you put in the list condition property will be executed as though it is part of the “WHERE” clause on a SQL query.  In the code above, you can see that myEvolv’s list condition was designed to only list diagnoses that have no end_date and have not been deleted.

We adjusted the list condition to also show us diagnoses with end_dates in the future so I modified the code to:

(event_log.end_date IS NULL OR event_log.end_date > GETDATE()) AND event_log.is_deleted = 0

I used SQL’s GETDATE() function to get today’s date so now in order for the diagnosis to list, it must either have no end_date or an end_date that is greater than today’s date and not have been deleted.

Step 3:  Create a Custom Formset Member

We now need a place to apply the new form we created in myEvolv so that it can be used.  We could just swap put the form for the myEvolv-supplied active diagnosis formset member but since other programs might be using it, we decided to create a custom formset member that was only visible to the clinicians in this program.   Our clinicians use myEvolv Launcher as their home pages in myEvolv.  They are used to clicking on their clients from the My Client’s widget, which launches the My Client Information standalone formset so that is where we wanted to add our custom formset.

Go to Setup > User Tools > Formset Maintenance > FormSet Members and select the ‘My Client Information’  FormSet (it is in the “My Evolv” module)

Create a New “Formset Member – User Defined”.

list-condition-formset-member-setup

You can give the tab any caption or description you would like.  Because we are working with the Diagnoses, we have to pick “Diagnosis” for the Event Category that will allow us to pick the “Diagnosis Information” form family and our default form, “Active Dx for MyEvolv Launcher”.

NB: Once this is setup, remember to give users access to the new form in the Navigation Access Setup.  In this example, the new formset member is listed in the My Evolv Module -> My Client Information section.

Results

The screenshots below were taken on 7/11/2016 so active diagnoses would be those that have no end date or an and date after 7/11/2016.

diagnoses-all

This is the generic “Diagnosis Information” formset member.  It is showing all of the diagnoses (except those that have been deleted) for the client.  You can see that the first two would be active by our program’s definition since we have one with a future end date and one with no end date.  The other three are expired diagnoses and those are the ones that we do not wish to display in our custom formset member.

 

 

diagnosis-filtered-poorly

This is myEvolv’s “Diagnosis – Active” formset member.  It is only listing the active diagnosis that has no end date.

 

diagnoses-active

This is our “Script for Service – Active Only” formset member.  Both active diagnoses are listed.  Success!

 

List Condition Overwrite on Upgrade?

We have noticed that the list condition on this formset member has been overwritten after upgrading our system.  We have a support case about the phenomenon and hopefully it will be resolved in a future release but until then, beware and be sure to save a backup of your custom list condition code to re-apply after upgrades.

We are using cookies on our website

Please confirm, if you accept our tracking cookies. You can also decline the tracking, so you can continue to visit our website without any data sent to third party services.