Skip to main content

How To: Setup Multiple Subforms from the Same Form Family on a Single Parent Form

Dean 1 Comment

When you put a Non Event Based Subform on Form, the database link between the parent form and the subform occurs on event_log_id. For example, if you have a Materials Provided subform on your event form, each row in that subform will create a row in the materials_provided table and the materials_provided table will have the event_log_id of the event it was added from. myEvolv uses that link to know which of the materials_provided rows to display on the parent form – only those that were entered on that specific event with the matching event_log_id.

When you use two or more subforms from the same form family on the same parent form, a complication arises.  If you do not change the default behavior of the subforms, all of the subforms from the same form family show all of their rows from the form family table regardless of which specific subform was used to enter the information.

Here is an example.  This form has two subforms on it – the top one is used to track the notifications that were made once per event.  The bottom one is used to track notifications that must be made per child who were involved in the incident.  Both of these subforms are created in the Materials Provided form family.

This is the form filled in but not saved. Each type of notification is in its rightful subform and there is one row per subform. After Save, the problem arises:

Both subforms now have both rows listed in them, each showing only the fields that are defined in their respective subform design (see the lack of Child column in the first subform). So what’s happening here?

“Out of the box”, a Materials Provided subform will pull all of the materials_provided database table rows that belong to this event – and both rows do because both subform rows were added on this parent event.  But we want them to stay in their own subforms so that a user looking at this event after save can see the data organized in the intended manner. 

Thankfully, myEvolv has a way to do this and it involved using the List Condition attribute for the form header used on these subforms.

The List Condition attribute allows you to put further filtering on the query that determines which rows will pull in on the subforms.   It is literally the conditions for when to list a row in the subform and it acts like everything after “WHERE” in a SQL where clause.  This means that there are a lot of possibilities for filtering – you can use any of the columns in the form family table to filter on and combinations of them.

For our example, I want to keep the event-level notifications in one subform and the child-specific notifications in the other subform.

To do this, I create a user-defined field of Regular String type in the Materials Provided form family and add it to both of my subforms.  It can be left not visible on subform so that users don’t see it.

My custom database column is called udf_list_cond_filter

For the event-level notification subform, I give this column a default value of ‘event’ (see image below)

And for the child-specific notification subform, I use the same field setup the same way, but I give this column a default value of ‘child’

Now when a new row is added in one of the subforms, this column is getting the value of ‘child’ or ‘event’, depending on which subform is used.  We will use that fact to filter using the subform’s list condition attribute.NOTE:  you can use whatever terms you want for your default value and list condition logic, these are just what I chose for my example.  The important thing is that it will match between the forms list condition and the default value.

On the event-level notification subform, my List Condition value will be

udf_list_cond_filter = 'event'

and on the child-specific notification subdorm, my List Condition value will be

udf_list_cond_filter = 'child'

This is telling the subform to only list a row from the materials_provided table if the event_log_id matches the parent event (already happening automatically) AND if the udf_list_cond_filter column has the matching value in it.

With this all setup, when I enter a new event with the same values as before, it will look the same after saving as it did before saving.

To make it clear what is at work here, the image below shows the udf_list_cond_filter fields visible on both subforms so you can see how they look behind the scenes.

This setup is extensible, allowing you to setup as many subforms from the same form family as you need to achieve your desired effect.

NOTE: This setup will not work retroactively on already-saved events because the new udf_list_cond_filter value on all rows that exist until you do this setup will not have a value set in them to filter on.

How To: Default Value for Picklist Field

Dean 1 Comment

You may find that a myEvolv form that you wish to use contains a picklist field that cannot be done away with, even though the user is always going to be selecting the same item.  For instance, you may have a treatment plan for a program where there is only one category of goals, but you have to select that category on each and every component anyway.  Just like other fields in myEvolv, you have the option of setting a default value for a picklist field, but it is a little trickier to setup than it is for text and date fields.  Defaulting values for picklist fields where there are no real options will save your users time when completing their work in myEvolv.

For my example, I have a service event for Attendance Documentation that requires a Service Location field. My user-defined look-up table for Location has 9 options to select, but for my Day Care program, only one location should ever be used for this service event: Day Care. My Day Care staff are sometimes erroneously selecting a different location and are complaining about having to do the extra clicking required to fill in a field that is always the same value.

The simplest way to handle this is to use the default value on my service location field in order to make sure the value we want to be there is always selected, but when I enter ‘Day Care’ in the default value for the picklist field, it doesn’t work. What gives?

Simplest Method: Hard-coded GUID

When you use Picklist fields on your form, the underlying data type is a Foreign Key ID, which means the data value actually being stored in the database is the GUID for the picklist item that you are selecting, NOT the description, shortcut code or any other column from the table being referenced. What we need to do here is provide the GUID for the picklist item that we want selected.

There are a few different ways to find the GUID for your intended picklist item. See Tips and Tricks: Two Methods for Finding a GUID for two methods.

Assuming you have the GUID, then all you need to do in the default value field of the service_location is enter the GUID surrounded by quotes.

Now the form defaults with “Day Care” selected for this field.

NOTE: When hard-coding with GUIDs, myEvolv Classic requires all upper-case letters in the GUID and myEvolv NX requires all lower-case letters in the GUID. If you are still using classic and intend to use the method below.

More Robust Method: getDataValue

This method is more robust because it works in both Classic and NX, though the over-use of getDataValue can cause system performance to slow.

Instead of hard-coding the GUID, we will use a getDataValue() call to lookup the correct GUID using the description of the item we wish to default.

For this method, you need to know some things about the lookup table you need to fetch the GUID from. In my example, I know that the LUT is a user-defined LUT, so the table is going to be user_defined_lut. I know that the primary key column for that table, the column I want the GUID from, is therefore user_defined_lut_id. And I also know that the column that contains the name of the item in that table is called description. With those bits of information, I can put together a getDataValue() call that will return the correct GUID and I can put that call into the Default Value attribute for my Service Location field.

getDataValue('user_defined_lut', 'description', 'Day Care', 'user_defined_lut_id');

The result is the same as above, with “Day Care” defaulting into the field on the form.

Other Possibilities

The possibilities are endless when it comes to default value logic, since the Default Value attribute accepts any JavaScript code that you might concoct. Whatever code you come up with, the end result must be a single GUID that is a primary key value from a record in the table being used as your picklist field’s LUT.

How To: Create Multi-Select Form Controls using Driving Form Line

Dean 1 Comment

As good as drop-down pick lists are, there are times when you might want the ability to addset of multi-select checkboxes to one of your forms. A set of multiselect checkboxes can make for much faster service entry for your users and is useful in form design to show views of the form what options were available so that they can see what was selected as well as what was not selected.

Creating a multiselect checkbox is not as easy as adding other types of control to your form in the form designer. You will need to create a subform for your main form and perhaps a user-defined look-up table.

The instructions below are going to go through the entire process of setting a fully user-defined multiselect form control but you can use the driving form line in other scenarios if it suits your purpose.

Step 1 – User Defined Lookup Table

For my scenario, I need to create a multi-select checkbox list of types of restraints that my agency uses to support clients. This type of control is a good candidate in this situation because I cannot predict how many of the types of restraints may be used in a single incident. I do not want to have 2,3, 4 or more(?) picklist fields on my form just in case there is a situation where more than one restraint is used.

The first step is to create the list of restraints as a user-defined table.

I simply set mine up like this:

Note that I changed the Order Expression to std_code. This changes the sorting on the lookup table to use the standard code field. I then just simply numbered my options in the order I wanted them to appear on the form. The sorting is alphabetical, not numerical, which means you need leading 0’s or 17 will render before 7.

Step 2 – Subform Design

In order to create something like a multi-select checkbox control, we need to leverage a subform. Subforms have the ability to add new rows as additional selection are needed. For example, here is a subform for one or more attached documents that I have added to forms where we cannot predict how many separate document may end up being attached:

If I add an attached document, a new row is added to the subform for me to add an additional scanned document.

Instead of a document link form element on my subform, I could use a foreign key field that uses my new user-defined table and then I could add as many rows as needed to capture the multiple types of restraints that may have been employed in the incident.

In order to create my sub form, I need to select the right form family. There are a few options for this. You can see them in the form designer when you add a Sub Form to a form (NOT an Event Based Subform) As of this post, there are 26 form families at your disposal, although some may not actually be useful for this:

I usually choose Materials Provided because my agency does not use the materials provided functionality that myEvolv intended so there’s little danger of obtaining weird results somewhere down stream from adding materials provided to random events.

Copy the default form from the top of the list and then on your new form, you can remove all of the fields except the Event Log Id and Agency variable.

Then add a new database field to the form like this:

Note that I am entering user_defined_lut: user_defined_lut_id as the Foreign Key Column — this will allow me to re-use this user-defined column again on other forms. This new field is now linked to my user defined table Restraint Type.

After saving, make sure that only this field is visible on subform. The other two may be hidden or at least hidden on the subform. This is what you should be left with, if using Materials Provided:

Add this subform to your main form. It should look something like this now:

This setup could technically get the job done. Users can select the gray ellipses button and add a row for every type of restraint used. However, there’s a lot of clicking involved and nothing prevents them from selecting the same one twice. Furthermore, we don’t have a sense of what all of the options are to know what was not used, only what was used.

Step 3 – Form Driving Line

There is a setting that has been available for a while but seldom used called Driving Form Line available in the form header properties.

What is does is allows you to select a foreign key field that is on your form and use it to “drive” the creation of subform rows. It will pre-populate your subform with one row per active item in the lookup table associated with the foreign key field you selected and render each with a checkbox next to it. In the database, only those items with a checkbox checked in it will be considered selected.

In my scenario, we will have a row per restraint type in our subform when it renders. Note that you will likely want to increase the size of the subform on your main form to accommodate all of the rows that will be rendered so that users do not need to scroll. My list has 11 items and I have found that you need 25px per item so I will use a height of 275:

Beware: because of this height aspect, you may want to limit your driving form lines to lookup tables with manageable number of options.

P.S. — Other Considerations

The scenario above is the most basic but keep in mind that you can include other columns in the subform. For example, I have another subform that gets the classification of an incident and also asks the use to select if the classification is primary or secondary.

I have a third subform using driving form line that handles information about notifications that have been made.

When you use this method with subforms that contain additional rows, keep in mind that ONLY those rows where the driving form line checkbox is checked will save to the database. This presents a risk for data loss on save that can drive users crazy. They fill in all of the columns but forget to check the box and now they have to re-enter everything.

It is therefore recommended that you use the following JavaScript in the subform design to disable all of the other columns unless the driving line checkbox is checked.

This code should be on the disable rule property for every column that is not the driving form line column. You must provide the correct column name for the code, which is the column name for the driving form line column.

!SubDisableRule('udf_restraint_type', 'checked')

How To: Get and Set Form Field Values

Dean 4 Comments

One of the challenges of working with myEvolv and myEvolv NX is that the Document Object Model (DOM) is different between the two views. That means that if you are using JavaScript, or even jQuery, to manipulate form fields, you code for one version will not necessarily work in the other. myEvolv includes JavaScript libraries that are used to facilitate some common form manipulations and that are safe to use in either version.

A couple of the most common things you might want to do on you forms is get the value of a field or set the value of a field. Getting the value that a user has entered allows you to validate the form or drive form field behavior with disable rules. Being able to set form values can help make the user experience faster and more enjoyable. You can do a lot with the simple ability to get and set form field values. The following post will get into the myEvolv functions getFormElement and setFormElement.

getFormElement()

The getFormElement function returns the value that is entered in a form field. The function takes a single argument– elementName

getFormElement(elementName);

The elementName is the name of the column used for the form field, NOT the caption. So for example, if I want to get the value of the Actual Date/Time field on the form, I would use

getFormElement('actual_date');

Keep in mind that when you get the value of a foreign key id field, you will be retrieving the GUID and not the caption of the picklist item. For dates, you will get a date string– in order to do date manipulation, you will need to convert it into a JavaScript Date object (I covered this topic in this post).

setFormElement()

The setFormElement function sets a form field’s value. The function takes two arguments — elementName and elementValue

setFormElement(elementName, elementValue);

The elementName is the name of the column used for the form field, just as with getFormElement. The elementValue is the value you want to set the form field to. If I wanted to set the remarks field to “Approved!”, I would use

setFormElement('generic_remarks', 'Approved!');

Some additional considerations are again needed with dates and times – the elementValue must be a formatted date string. And for a lookup table, you must provide the GUID of the item you want to be select.

Where to use?

You can execute JavaScript in several places on a form and on form fields. Where you use the JavaScript depends on what you are trying to do and the code properties of forms and fields are pretty self descriptive.

Form-Level

Before Save Code

This code executes right after the user clicks the Save button on the form but before the Save function executes. This makes it a good trigger for code that you want to run one time after the user has entered values in all of the fields. This might be a good candidate for code that will calculate a value based on the values entered in several fields on the form. You might not be able to trust the order that the users will fill in the values so triggering them at the field level might be tricky.

Note that at the point this code executes, you can no longer manipulate the DOM of the form and have those values become part of the Save function so you have to instead use the setElementFromXML() function (I will cover in another post) to get the calculated value into the save.

After Save Code

This code executes just after the Save function executes in the system. Because of this it is mostly used for popping up alerts or keeping windows open after saves.

After Delete

This code will execute after the Delete function executes. I have not used this one yet. It could be useful for popping up alerts.

Before Load Code

This code will execute before the form XML is rendered into HTML and displayed on the screen. There is a note that this code does not run on forms when they are used as subforms.

After Load Code

This code will execute after the DOM is fully loaded and the form is displayed on the screen. This trigger would be good for popping up alerts when the form is first opened.

Field-Level

On Load Script

This code will trigger when the form field loads in the browser — the browser will load the form fields in the order they appear. If I recall correctly, you cannot target DOM elements until the full form is rendered, but you can set JavaScript timers that will delay execution of code until the DOM elements you wish to target are loaded.

On Click Script

This code triggers when the form element is clicked. It works best for checkboxes but you can get it to fire for other form fields as well.

On Change Script

This code triggers when the form element’s value is changed. This one does not work great with checkboxes but is perfect for date/times, strings, foreign key id fields.

Default Value

You can use JavaScript in the default value property in order to generate your default value. The code in this property executes before the HTML DOM loads and the calculated default values exist in the formXML layer.

Disable Rule

You can use getFormElement() in the disable rule property to determine if a field should be disabled or not disabled based on the values retrieved. You return true if you want the field to be disabled and false if you want it to be enabled.

What about Memo fields?

Memo fields are a little more complicated than the other form fields in how they generate in the DOM and so setFormElement() does not work on them. Instead, you must use the setMemoField() function.

setMemoField(formLinesId, updateText);

The difference between this function and setFormElement is that the first argument here is for the GUID of the Memo field’s form field id and not the column name. So to make this usable, you should pair it with a getDataValue() call to grab that form_lines_id.

The following code will get the form_lines_id of a memo field on the form (form_code = “MY_TEST_FORM”) that is captioned ‘My Memo’. It then uses that GUID value in the setMemoField() function to set MyMemo to ‘Updated text value.’

var formLineId = getDataValue('form_view', 'caption', 'My Memo', 'form_lines_id', 'form_code=\'\'MY_TEST_FORM\'\'');
setMemoField(formLineId, 'Updated text value.');

When reusing this code on your form, you just need to change the caption (‘My Memo’) to be the caption of your memo field, the form_code (‘MY_TEST_FORM’) to be the form code of the form you are putting this code on and then your updateText (‘Updated text value.’). You must be sure that your Memo field has a unique caption on the form you are using– you cannot return more than one value with getDataValue().

JavaScript Fundamentals: String Concatenation

Dean Leave a comment

In my CONNECTIONS2019 presentation, I showed how I use SSRS to create “Pretty Print” versions of myEvolv Treatment Plan components to use as a handout for treatment plan meetings that can be accessed by clicking a URL variable on the treatment plan itself. Several people asked me to share the method and specifically how to do the JavaScript string concatenation, which can be used in many other places.

JavaScript Strings

In JavaScript, strings are any text characters inside of a single or double quote.

var myString = "Hello world.";

You can use concatenation to “glue” strings together. In JavaScript, the concatenation operator is + , just like adding numbers together.

var string1 = "Hello world.";
var string2 = "How are you?";
var newString = string1 + string2;

The value of newString would be Hello world.How are you? Notice that There are no spaces between the sentences because I didn’t include them in the strings.

If you have added a URL variable to a form, you have already used a JavaScript string because you probably entered a default value like this:

'http://www.example.com'

This created a static URL that will always point to example.com every time the form is opened in myEvolv. Using JavaScript variables and string concatenation, you can create dynamic URLs that will be unique to a client, event, staff, or any other things you can come up with and these can be very useful for making myEvolv more user-friendly and effective.

“Pretty Print” Reports

At my agency, we find that the default printouts for things like Treatment Plans are long and difficult to read, especially for people who are not using myEvolv day-to-day. When treatment teams get together to meet about the treatment plan with the client and family members, we were printing copies of the treatment plan to share at the meeting.

Through meeting with the staff in those meetings, we determined that the main focus was to use these printouts to review the component pieces of the treatment plans, so why not come up with a way to just generate a one-page print-friendly list of Goals, Objectives and Methods from the current plan?

I was able to accomplish this using our SSRS Report Server by creating a report that would pull in all of the service_details for a specific plan and displaying them neatly.

Example of “Pretty Print” report

The SSRS Web Portal would allow staff to access the report where I could have added parameters that would allow the staff to lookup a client and select the plan they were looking to print this report for. But it would be easier if they could just click a link and have the report generate for the plan they clicked the link from automatically.

Dynamic URL to SSRS Report

Query Strings

SSRS allows you to pass report parameters through query strings. You may have seen these in web URLs that you have browsed:

http://www.example.com/search?search_term=balloon%20animals&limit=20

The first section of the URL directs you to a search endpoint

http://www.example.com/search

The ? starts the query string and then the parameters and their values are listed. In this case, the search_term is “balloon animals” and we only want it to return (limit) 20 results.

We can do the same thing with SSRS. In the case of this Pretty Print example, I only need one parameter, event_log_id. My query includes a WHERE clause

WHERE event_log.event_log_id = @event_log_id

This creates a parameter called event_log_id that the report is expecting in order to run.

The event_log_id of the Treatment Plan is on the treatment plan so we can use string concatenation to glue the report endpoint and the parameter name to a variable on the plan that holds the value of the event_log_id.

URL Variables

The URL form field type is used to create a clickable button on a form that will launch a new web browser window pointed to the URL value of the field.

You could add the field as a user-defined field and that might make sense if you are doing something like collecting a URL from someone. For example, if you wanted to get the website of an organization. In that case, you want to store the URL in the database.

In this situation, however, we don’t need to store anything in the database. We just want to generate the URL and create the button every time an event is opened, so a variable is perfect in this case. I added mine like this:

The URL field displays the URL in a ext box and then has a clickable globe icon to the right of it. I don’t like to display the url itself because they can sometimes be very long and look terrible so I put 1 in display size. That still shows the first few characters of the URL but that’s the smallest you can get it. As of this writing, the display size does not seem to effect how it looks in NX. I also make my URL variables not modifiable.

Default Value

Now for the JavaScript. We have our SSRS Web portal setup to work on the agency Network only so the url is

http://ssrs:8080/reports

The specific report I have created is located in the Pretty Print Reports directory and is called Plan Components:

http://ssrs:8080/reports/report/Pretty%20Print%20Reports/Plan%20Components

The %20 all stand for spaces

I also know that I have one parameter, event_log_id, so that is going to be a static part of the url:

http://ssrs:8080/reports/report/Pretty%20Print%20Reports/Plan%20Components?event_log_id=

And now all I have to do is concatenate the event_log_id to the end of the url and I will have my link. myEvolv stores the event_log_id of any given event in the variable keyValue, so I can just use that. So in the default value for the URL variable, I will use the following code:

var url = 'http://ssrs:8080/reports/report/Pretty%20Print%20Reports/Plan%20Components?event_log_id=';
url += keyValue;
url;

Explanation: I created a variable called url and gave it the string value of the static portion of my report URL. Then I concatenated the event_log_id of whatever event the form opens with to the end of the string using the myEvolv variable keyValue.

The url variable now holds a value like:

https://ssrs:8080/reports/report/Pretty%20Print%20Reports/Plan%20Components?event_log_id=2542C1F3-2D25-4840-97B3-A17C86652E9F

In the last line, I simply output the value of url, which becomes the default value of the URL variable.

One More Brief Example

To give an idea of a slightly more complicated string concatenation for a URL variable, another place where I use this is on our Monthly Summary events. I have created a dynamic URL variable that will launch an SSRS report where the parameters are the client’s people_id and then a date range. The SSRS Report then pulls in the specified daily notes for that client between those dates so that the summary writers can easily review the months’ activities.

Here’s the code for the default value:

var url = 'http://ssrs:8080/reports/report/Raise%20the%20Age%20Reports/Action%20Step%20Specific%20Notes?people_id=';
url += getElementFromXML(formXML,'people_id');
url += '&start_date=';
url += getElementFromXML(formXML,'udf_summary_start');
url += '&end_date=';
url += getElementFromXML(formXML,'udf_summary_end');
url;

This is pretty similar to the first example but a few difference. One, instead of using a myEvolv variable, I am just concatenating values from the form directly to the string: people_id and two user-defined date fields, udf_summary_start and udf_summary_end.

I am using getElementFromXML() to get the value from the form’s definition rather than the rendered form elements. This works well when you have default values in the form. If you do not, then those values may be null until filled in on the form.

In that case, you might want to go a different route and use similar code to the On Change field of every form element that is used in the URL with t

var url = 'http://ssrs:8080/reports/report/Raise%20the%20Age%20Reports/Action%20Step%20Specific%20Notes?people_id=';
url += getFormElement('people_id');
url += '&start_date=';
url += getFormElement('udf_summary_start');
url += '&end_date=';
url += getFormElement('udf_summary_end');
setFormElement('monthly_summary_report_url', url);

***In this example, my URL’s variable name is monthly_summary_report_url

What this does is any time the client is switched on the form or the start and end dates are changed, a new URL is generated and entered into the URL variable as the value.

In either case, you can see that I am alternating between concatenating static parts of the query string with they dynamic parts to make a more complicated query string.

How To: Create a Data Insight Report to Get Form Information

Dean Leave a comment

I was asked by a reader about how I figure out the form_line_id assigned to elements on a form. There are a few ways, but this is probably the easiest and it provides a tool that can be used over and over again. However, since it does require writing a custom report, I figured I would use the opportunity to walk through designing a Data Insight report using a custom virtual view.

Step 1: Writing the SQL Query

Before we get to Data Insight, we need a query to use for our virtual view. The report that I want to create is going to need to include the name of the form field (caption) so that I know I am getting the right GUID. I want to filter on the form name so that I can more quickly find the right form and form field. And if your system is anything like mine, you have multiple forms with the same or similar names, possibly even across various form families. So I am also going to want to bring in the form codes and the form family information.

All of these items are stored in just 3 tables: form_family, form_header and form_lines.

UML Diagram

The form_header table holds information about each form. Each form_line belongs to a single form_header and gets a unique form_line_id per form that it is on. In other words, even if you have actual_date on all of your forms, each actual_date element has a different form_line_id for each form it is on. Also be aware that if you remove a form element form a form and re-add it to the same form, it will be assigned a new form_line_id when it is re-added.

Forms (form_headers) belong to a single form_family and so they have a form_family_id. Similar to the above statements about form_lines, if you delete a form in the form designer and then recreate it, it will have a new form_header_id. If you import a form from development into your production system, myEvolv is going to give your form and all of the form_lines (elements) therein a new id as part of the import process. Once your production is later copied over to development, that imported form’s elements and header will have matching ids.

So here’s the SQL query we will use for this report:

SELECT
form_family.form_family_name,
form_family.form_family_code,
form_header.form_name,
form_header.form_code,
form_lines.caption,
form_lines.form_lines_id AS nx_friendly_form_lines_id,
UPPER(form_lines.form_lines_id) AS classic_friendly_form_lines_id
FROM form_family
JOIN form_header ON form_header.form_family_id = form_family.form_family_id
JOIN form_lines ON form_lines.form_header_id = form_header.form_header_id

You will notice that I selected the form_lines_id column twice and that in on one of them, I used the UPPER() SQL function. GUIDs are returned from myEvolv in lower case format like this:

494eed56-36fb-4fda-b54f-a86f1d150b38

These work fine in your JavaScript for NX, but if you are working in classic, your JavaScript requires the GUIDs to be in upper-case so the UPPER() function takes care of that for you and returns that same GUID like this:

494EED56-36FB-4FDA-B54F-A86F1D150B38

This query will provide you with both options and you can choose to use one or both on the final report.

Step 2: Create Data Insight Virtual View

Now to Data Insight to create the virtual view we will use to create the report. In order to add or manage a virtual view in Data Insight, you must be assigned to a role with “Access Configuration Area” in Data Insight. If you haven’t played with Data Insight roles at all, then this is the same as saying you must be an Admin in Data Insight. if you have the proper permissions, you will see the “Configuration” option in your Data Insight menu.

Click the “Configuration” option and then in the center column, under “Database Object Configuration”, click the hyperlink for “Create a new virtual view.”

Give your new virtual view a name and a friendly name. The name must be letters, numbers and underscores. The friendly name can have spaces.

Copy and paste your query into the Definition field and then click the “Test Definition” button. You can ignore the red underlining here since that is just Internet Explorer’s spell check at work. It does not indicate that the SQL is malformed.

If your query is valid SQL, you should see a list of the selected columns from your query appear. If not, you may have errors in your SQL to fix.

Once you have a query that passes the test, click “Save” to save that virtual view.

Step 3) Create Data Insight Report

Navigate to the Report Writing area of Data Insight by clicking the “Report Management” option in the top menu. Then click “Add” and select “Report” from the drop down menu.

Select a “Tabular Report with Header” from the Report Template options and click “OK”

When the Data Source pop-up window appears, make sure the “Data Objects in:” drop down menu has “(All)” selected. Then check the box next to your virtual view to select it and then click “OK”.

Since the data we are looking at includes 3 tiers of information– Form Families that contain Form Headers (forms) that contain Form Lines (elements), it makes sense to ad some groupings so we can see more easily what all belongs to what.

Click the “Grouping Tab”, select the “Grouped Flat-Table” radio button and then click the “Add Grouping Layer” button.

On your first layer, add the form_family_name and form_family_code field and click “OK”.

Click the “Add Grouping Layer” button again to create the next grouping. For this grouping add the form_name and form_code fields and then click “OK”. When this step is done, you should have a grouping screen that looks like this:

Go back to the “Table Columns” tab and add caption, nx_friendly_form_lines_id and classic_friendly_form_lines_id to the “Assigned Columns” list. It should look like this:

Your report preview pane at the bottom should spit out a report that includes every form line in every form in every form family in your system. It probably takes a while to load. We don’t want such an unwieldy report so we will add a filter on the form name by clicking “Modify Data Source” on the left side of the preview.

PRO TIP: Hiding the Live Preview is a good idea if you have a report that runs very slowly to avoid having to wait for the report to complete upon each change made to the report.

In the pop-up window, click to view the “Filter” tab and then click “Add a Parameter”. Select the column form_name. Change the operator value to “Contains” and check the “Ask in Report” checkbox. Make a caption for your parameter prompt and then click “OK” to close the parameter details popup and then “OK” again to close the data source details popup.

Your live preview pane should now be blank but prompting you to enter a form name (or part of one). Test your report by searching for one of your forms.

You should see something like this in the results:

The first result in my report searching for “Compass” is for form lines on the Compass Address Information form in the Address Info for People form family. If I look at the form in form designer, I will see all of the same elements listed. Keep in mind the groups are also form elements and therefore are include on the form_lines_id. That is what “Address Information” is on the Compass Address Information form.

At this point, you can make the report look and work to your liking by changing the column captions, adding sorting, removing pagination, etc. Be sure to save your report and move or copy it from your personal reports folder to the Shared Reports if you want to allow others to use the report too.

myEvolv Tips: Custom Sort on User-Defined Fields

Dean 2 Comments

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

Dean 1 Comment

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

Dean 7 Comments

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.

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.