Skip to main content

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

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

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.

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.