Skip to main content

myEvolv Tips: Custom Sort on User-Defined Fields

It is possible to display your user-defined picklist options in a custom order in myEvolv. This comes in handy when you are using a user-defined table for things like scales where the description would not naturally be in alphabetical order.

This recently came up for a form I was working on where the user needed to determine if a score on a test was

  • None
  • Low
  • Moderate/Low
  • Moderate
  • Moderate/High
  • High
  • Very High

When I created the User-Defined Table, the picklist displayed the options in alphabetical order, which is not an intuitive order for the user to see this kind of list displayed.

How can I get the picklist to display in the logical order? The answer lies in the user-defined table setup. In the table definition section, there is a field called “Order Expression”. It will take any SQL code that would follow ORDER BY in an order by clause.

The default value is description which means it will sort the description column in ascending order.

You could change the value to description desc to change the order to reverse-alphabetical.

You can also change which column the sort is applied to. With user-defined fields that means you can use the Shortcut Code, Standard Code or Generic Code columns. The trick is that you have to know the actual column name in the table. Here they are:

Shortcut Code = sc_code
Standard Code = std_code
Generic Code = gen_code

Keep in mind that the shortcut code is usually also displayed in the picklist and/or used on the form as a way of typing in the selection so you may or may not want to use that column as your sort column.

In my case, I went with using the Standard Code column. I simply put a number for each row in the order I wanted them to display and changed the Order Expression to std_code.

Setup like this, my picklist is now sorted in a logical fashion:

myEvolv Tips: Reusable User-Defined Picklist Fields

When you are building forms and have to create new database fields, best practice is to give the columns generic names so that you can use them again on other forms in the same form family. For example, if you need to link a tenth diagnosis (yes, the event_log has 9 diagnosis columns!) on a People Activities form, you should name it something like udf_diagnosis10_id rather than for the specific form like udf_extradiagnosisforclinicprogressnote. This will allow you to keep your database neat and help prevent running out of columns in your _x tables.

You may have run into a situation where you setup a user defined foreign key field on one form and had it working beautifully. However, when you went and tried to re-use it on a second form, you found that you could not select any look-up tables to use with it. What happened there?

You missed a crucial step in creating a user-defined database field. Here’s how to avoid making that same mistake going forward.

When you got to create a user defined foreign key field, use the [Data] Foreign Key Column attribute. Like the column name and data type, this attribute is only available when you first create the new column. If you forget this step, you cannot go back, and it is the reason that you cannot link a look-up table on subsequent uses of this column on other forms.

What to choose?

This depends on what picklist(s) you want to use with this column.

For example, if we go with the initial scenario, the picklist I want to use will consist of diagnoses so the foreign key column I want to select is diagnosis_id. This will allow me to reuse the column on additional forms as long as I use a picklist that is diagnosis-based.

User defined fields are commonly paired with a user defined look-up table. In order to re-use these, you should select the user_defined_lut_id foreign key column. Since all of your user defined look up tables use the user_defined_lut table, they all use the same column as their primary key.

If you need help figuring out which column to select, first select the look-up table you intend to use. In the picklist for look up tables, there is a column called “Table From”. Many of these will be views, but you should be able to figure out what the underlying table is in most cases. In our example, I want to use a look-up table from the diagnoses_view and so I know that the diagnosis table and therefore the diagnosis_id column is what I should select.

myEvolv Tips: Subform Field Manipulation

Credit for figuring these out/discovering them goes to Perry. I find myself referring to them in a training binder I got from last year’s NY Training Summit and figured it would be easier to just get them up on the web so I don’t have to go hunting for that binder every time. The following code works for Classic.

Subform Considerations

Subforms have to be handled differently than forms when it comes to JavaScript because while in the form designer, the two things look identical, the way that myEvolv renders a subform in the browser is very different from how it renders a form. But it isn’t radically different and the main changes account for the fact that a subform can have one or more rows and so you need to be more specific about which field you are trying to manipulate so that you don’t change every line simultaneously.

Scenario 1: Get the Value of a Subform Field

This code is for use within the subform, e.g. if you want to default the value of one field based on the value of another on the same subform.

self.getElementFromXML(currentRowXML, 'column_name');

Note the self object is being used here. This is the subform object as distinguished from the parent form object. The parameter currentRowXML then further narrows it down to the current record/row on the subform that you are concerned with.

Scenario 2: Set the Value of a Subform Field

Again, for use within a subform, this code can be used to set the value of a field in the same subform, e.g. when you want the On Change event to auto-populate a field.

this.form.'column_name'.value;

If you are checking a checkbox, use this code:

this.form.'column_name'.checked = true;

Note that in this code, you do keep the single quotes in the code for it to work properly. All you change is the column_name

Scenario 3: Get the Value of a Parent Form Field

With this code, you can get the value of a field in the parent form based on an action in the subform.

window.parent.getFormElement('column_name');

Note the window.parent is the only difference from the code you would use on the parent form. This is what allows your code to ‘jump up out of the subform’.

Scenario 4: Set the Value of a Parent Form Field

Maybe you figured it out by now but you can use the same small change to set values on the parent form from the subform.

window.parent.setFormElement('column_name', value);

Scenario 5: Trigger an Alert from the Subform

Alerts are useful in guiding user activity and you can trigger them from subforms. Similar to the last two, the trick is moving back up the DOM to the parent form to trigger it.

window.parent.window.alert('Alert Message');

Other Possibilities

It is possible to go the other way and get and set values on subform records from the parent form, however, it becomes a much more complex problem that requires very specific solutions for very specific challenges. That is because of the One-to-Many relationship that the subforms have with the parent form. For these types of situations, you will probably be targeting the subform itself and then looping through each record to get or set values.

How To: Create a Better Treatment Link Subform

One of the most powerful aspects of an electronic health record is the ability to link service documentation to a client’s goals and objectives and to ensure that the services provided are related to the clients current goals and objectives.

myEvolv provides a simple toggle setting to add a Treatment Plan Link on service documentation in the event setup.

This setting adds a system subform to the bottom of your service documentation forms that includes a Treatment Link column for selecting a goal from the client’s treatment plan, an Additional Treatment Detail column for selecting a child objective or method from that goal and a Notation column for capturing a note.

After attempting to use the system subform for several months, we noticed a couple of things about it that we didn’t like. The main issue is that the subform does not filter to list only goals from the most recent approved plan. It lists all of the goals that ever existed for the client. In our programs, plans must be reviewed as often as monthly so the list begins to grow very quickly and we were finding that users were often linking old goals to their current documentation. Also, you are stuck with the subform being way at the bottom of your form, which isn’t always ideal.

I used the following method to create a better subform for our direct care workers to link their service documentation to treatment goals without having to sort through a huge list of goals and ensure they always picked goals that were currently in place.

Step One: Get the service_plan_header_id of the most recent completed treatment plan onto the service documentation form.

For this task, we can use a variable since we only need to filter at the point where the service documentation is being added. If someone opens last year’s event and the variable (which will not be visible anyway) has the current treatment plan’s service_plan_header_id in it, that doesn’t affect anything other than the subform’s Goal picklist, which will be locked since the event is complete.

I added a Custom String variable to my form with the name current_plan

In order to get the service_plan_header_id of this client’s most recent treatment plan, I used the following JavaScript code for the Default value property:

var conditions = 'program_providing_service=\'\''+programPS+'\'\' AND actual_date = (SELECT MAX(actual_date) AS most_recent FROM service_plan_event_view WHERE people_id = \'\''+parentValue+'\'\' AND program_providing_service=\'\''+programPS+'\'\' AND approved_date IS NOT NULL)';
var plan_id = getDataValue('service_plan_event_view','people_id', parentValue, 'service_plan_header_id', conditions);
if (plan_id == null) {{
plan_id = 'NONE ON FILE'
}}
plan_id;

The first line of this code is setting up the SQL conditions that will be passed along in a WHERE clause query executed by the getDataValue() function. In my case, I wanted to be sure I got the most recent service plan event that matches the client for whom this service documentation is to be entered and matches the program providing service of the service documentation. The latter is necessary in case the client has more than one treatment plan at a time in different programs. The approved_date IS NOT NULL clause ensures that if there is a treatment planner working on a draft, the draft goals do not list for the direct support staff doing service documentation.

In the second line, we use this condition statement as the fifth argument in the getDataValue call. Here we are looking for a treatment plan in the service_plan_event_view that has the matching values from above and returns the service_plan_header_id

Then I checked to make sure a service_plan_header_id is returned. If one is not returned, I set the variable plan_id to be ‘NONE ON FILE’ mostly as a way to make sure my code was executing. This will be the value that fills in if someone goes to add service documentation for someone who has no plans on file for that program providing service.

Finally, I echo the value of plan_id so that the value will populate the form field.

Step 2: Create a subform similar to the system version to add to your service documentation form.

For my purposes, the subform didn’t need to be much different than the system subform. We wanted the direct support staff to select the Goal and Objective that was worked on and then enter a note related to the specifics of the objective selected.

I copied the default form in the Treatment Plan 2 Event Link – People B2E form family and made modifications.

This is what the overall form looked like in Form Designer when I was done:

Event Log Id and Additional Treatment Detail were from the original form. I left them on but pushed them to the top and made them not visible. Notation is also the same field as from the original form but we decided to make it required.

Goal is the Treatment Link from the old form. I changed it to use the Treatment Plan Goals by Plan Look-up Table so that I could use the service_plan_header_id as a parameter.

For the Depends On Other property, I used the code

getParentFormElement('current_plan')

This gets the value of the variable we created in step one and uses it as that @param2 shown in the Look-up Table Picklist’s Condition column. This is the piece that will filter the Goals picklist on the subform to only list the Goals from the treatment plan with the service_plan_header_id we supply it.

Finally, I created a new database field to use for our Objective field. I set it up as a Foreign Key type of field and I used the Service Plan Details (child nodes) Look-up Table.

This look-up table uses the service_plan_details_id to filter for a list of objectives that belong to that service_plan_detail . In this case, we are selecting a service plan detail in the Goal field, so we can filter this LUT to only show the Objectives that belong to that goal. To do that, I just select the Goal field as the Depends On property.

***Note: You can go one level deeper and add a picklist field to the subform for the methods by repeating the steps for making the Objective field except selecting the Objective field for Depends On since Methods belong to Objectives the same way that Objectives belong to Goals.***

Once you have your subform ready (remember to check the box for Is Visible on Subform!), add it to your service documentation form. Assuming your variable from step one is pulling correct service_plan_header_id’s, you should see only the most recent active goals in your Goal field and then only the objectives for that goal in your Objective field.

Troubleshooting: Data Insight Reports and Views Don’t Update

If you are anything like me, you work with Data Insight by roughing out your SQL query, dropping it into a virtual view, and then starting a report to see if your query is pulling the data you were expecting. If you need to make changes to your virtual view, you return to the virtual view management area of Data Insight and make the changes and hen back to your report to check the results.

Once you have spent many hours working with Data Insight, you will see some pretty strange behavior but one of the most frustrating occurs when you spend a long time troubleshooting a query issue and no changes you make to the virtual view or report seem to make a difference. The report runs in the preview window exactly as it had before.

The Problem

Most of this is speculation but this is what my gut tells me is happening here. With other reporting tools like Crystal Reports or SSRS, you write your query and when it’s time to run the report, the query executes on the database. With Data Insight, you are creating a virtual view that runs when you save the virtual view definition and creates a data object that you then use to write a report.

This might explain why just saving a definition on some more complex SQL can take a while and also why you are limited to using filters on the data you retrieve AFTER retrieval rather than including parameters in your query statement.

The Solution

When you are in the Report Designer, changing the columns used in the report has the effect of refreshing the virtual view that is being used. So after you update your virtual view, reload your saved report, add a column to the report and then remove it before running the report to see your changes reflected in the data that pulls in. Keep in mind that this process will need to be done to all reports that use the virtual view if you want them to update.

myEvolv Tips: Third Party Software Installation Files

myEvolv uses some third-party plugins for things like scanning and printing. The installer for these are supposed to execute automatically for users when they reach a point where they need them. For example, the MeadCo’s ScriptX plugin installer will launch for users who try to print on a machine that does not have the plugin installed.

These installers are available to be downloaded on demand so that they can be pushed out with group policy or pre-installed on machine images. On the old forums site, I had found a listing of the location of each of the installers and grabbed a snapshot of it before it closed down. Recently our own IT people were wondering about how to get one of the plugins and it reminded me that I had the snapshot somewhere and also that I should post it here since it is something I’m sure others have needed.

The table below will lists the program and it’s location. The location is relative to your agency’s myEvolv login page. For example, if your agency’s myEvolv login page is https://myevolvagency.netsmartcloud.com and the location for the file you want is imageX.msi, then you will go to https://myevolvagency.netsmartcloud.com/imageX.msi

Third Party Software Installation Files

File Location Note
Spell Checker Wspell.cab Demo remarks field at /remarks.html ?
Printer Smsx.exe MeadCo ScriptX
Check Print Cp_setup.exe
Front Desk FrontDeskSetup.msi
Image Scanning imageX.msi No longer there?
Custom Report Designer RDSetup.msi
WebCam VideoCapX.msi This must be for direct capturing photos on photo link fields.

How To: Anchor Sub Report Data to the Parent Event

myEvolv sub reports can be very useful despite their limitations. Their most useful out-of-the-box application is in something like a face sheet, where you want to bring in the current information available, perhaps with some filtering.

In the first draft of some of our form designs, we used the current location sub report to display a client address. What we did not anticipate was that the address on previously entered notes would change when the individual’s address changed. We wanted that address to be locked on the form once it was saved. A few years later some of the Audit Snapshot functionality came out but that still didn’t work for us because of how audit snapshots worked. We needed a way to have the data on the sub report be based on the date of that event, not today’s date.

In subsequent years, I have figured out a few ways to accomplish this, depending on what columsn the sub report views provide and what we are trying to accomplish. I will try to lay out a few different methods below as I have time.

Method 1: Using a Join Column as a Parameter in the SQL Code

This method is good for when you want a sub report that will show you events within a time range from the parent event’s date. For example, you are creating a monthly report type event and need to list all of the progress notes from the 30 days prior to the monthly report date so that you can display them for the monthly report writers to summarize without running a separate report or opening them individually.

In this example, I will create a sub report using the physical_characteristics_view. I want to only show the physical characteristics from any and all events that occurred in the 30 days leading up to the event on which the sub report will display.

Configure Join Columns

When I first copy the system sub report to make my user sub report, the only join column is the people_id. If I display the sub report with this as the only join column, the subreport will show all of the physical characteristics in the system for all time for that person. If I save the event today, when I open it in a year, it will have an additional year’s worth of physical characteristics showing. The first thing we need to do is also use actual_date as a join field.

Check the box for Join Field? and select Less Than or Equal To for Operator.

With this step done, the subreport will now only ever show the physical characteristics for this client that have an actual_date prior to the actual date of the event it is being displayed on. Half of the problem is solved but this report does not cut off at 30 days prior– it would show all of the events prior, going back to the first one entered for this client.

Custom SQL Code

Values entered in the SQL Code field act as a WHERE clause in a SQL query. We already took care of filtering future events through a Less Than or Equal To join so now we just need to limit the past events in the WHERE clause.

I found that if you are using a field as a join field in the previous step, you can actually bring it in as a parameter value (though I have not used it enough to be sure this will hold for all cases, it seems to work here consistently). This allows us to use the same date value that we are using to filter on the join in our WHERE clause. We will combine it with the DATEADD function to go back 30 days and filter for those physical characteristics with an actual_date Greater Than or Equal To that date.

The first actual_date is the actual_date of events in the physical characteristics view. The @actual_date is a parameter that will get its value from the actual_date field on the parent form at the time the form is generated.

The DATEADD calculates thirty days prior to that parameter value and we are looking for anything that happened after that 30-days-ago-date and the parent event’s date.

actual_date >= DATEADD(day, -30, @actual_date)

More Methods to Come!

myEvolv Tips: Developer Tools in Modals

There are times when it is useful to analyze the Document Object Model of your myEvolv form, especially when you are trying to figure out ways of manipulating the form with JavaScript. If you are on a main screen in myEvolv, you can simply press the F12 key on your keyboard to bring up Internet Explorer’s developer tools. However, this does not work when you press it while focused on a modal pop-up in myEvolv.

In order to get the developer tools to work so that you can see the DOM for your event forms, follow the steps below.

1) Disable Setting that Allows Websites to Suppress Address Bar

Open Internet Explorer’s Internet Options and click on the “Security” tab. Make sure “Trusted Sites” is highlighted and click the “Custom level…” button.

In the Security Settings dialog, scroll down to the Miscellaneous settings. Select “Disable” for the setting “Allow websites to open windows without address or status bars”. Apply the settings and close the Internet Options dialog.

This setting will cause the modal popups that myEvolv uses to display event forms to display their url, which is required for developer tools to be used on a window.

2) Open Developer Tools and Un-Pin it From the Browser Window

At this point, some of you may be able to simply open an event form in myEvolv and press F12 and the developer tools open without problem. For others, nothing happens when F12 is pressed in a modal or you get a warning that says the main windows developer tools will need to close so that i can open a new one in the modal but then nothing happens.. The rest of the steps in this post will show the workaround for that.

The first thing you will need to do is un-pin the developer tools from the browser window so that they open in their own separate window. Close any modals you have open and press F12 so that the developer tools open in the browser. In the upper right hand corner of the development tools pane, there is an un-pin button you can click. The develop tools will then open in their own separate window.

3) Open The Event and Select the Right Form in the Debugger

With the Developer tools in their own window, you can open the event that you want to analyze with developer tools and still have access to the developer tools window. When you open the event, it will not automatically populate in the developer tools. You must select it in the “Debugger” tab.

Click the Debugger tab and note that the pane below the toolbar support multiple tabs. You will probably see main.aspx loaded in the pain and that represents the main screen of myEvolv. At the bottom of that pane is a debugger pane. Find an error or warning related to eventform.asp. It should be shown as a link and clicking that link will load the event_form DOM in the debugging pain next to main.aspx.

UPDATE: Easier Way

Instead of going through the last steps 2 & 3 above, you can just copy and paste the url for the modal into a new tab in Internet Explorer and then launch developer tools for that tab.

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.

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.