Example: Update Sport Rating in an Athletics Portal

In this example, an athletics portal is used by coaches to set a "rating" value for an athletic recruit. The Slate standard athletics portal comes with the pop-up view with the update functionality built in.

This rating is associated with a sport on the standard Sports section of the person record.

Athletics portal users can update the rating if desired. However, in order for an update to be applied correctly to existing sport data, it must be able to specify that particular sport record's GUID. Slate forms do not have access to individual sport record GUIDs, and so a POST method running a SQL query must be used.

The Athlete Details pop-up in this portal is comprised of a form HTML element, which can be seen in the Source of the view. The form element has an action as well as a method attribute. This action must match the cmd input value.

<form action="?cmd=saveDetails" method="post" style="width: 500px;">
<input name="prospectid" type="hidden" value="{{prospectid}}" />
<input name="sportid" type="hidden" value="{{sportid}}" />
<input name="cmd" type="hidden" value="saveDetails" />
...
</form>

The cmd input value saveDetails corresponds to the Action value (saveDetails) of the POST method:

Effectively, when the HTML form is submitted (by clicking the Submit button in the pop-up), the POST method with the matching Action value is called.

The form element in the view also has hidden input fields, where Liquid markup is used to merge in values for the person record GUID (prospectid), as well as the individual sport record GUID (sportid). The other input field passes in a hardcoded string value of saveDetails.

The name attribute values for the inputs will later be referenced in the SQL query that updates the database.

The merge values for prospectid and sportid are derived from the Sports-based athlete details query that runs and populates data for the pop-up when it is opened (this query is run by a GET method, since it needs to pull information from the database, to be displayed in the view). Sport ID (i.e. the GUID of each individual sport on the person record) is a standard export part available in the query base:

Also nested within the form element is a select HTML element, which renders the dropdown list of sport rating options:

<td>{% assign curr_rating = {{sp_rating}} %}
<select id="rating" name="rating">
{% for rating in ratings %}
{% if {{rating.id}} == curr_rating %}
<option selected="selected" value="{{rating.id}}">{{rating.value}}</option>
{% else %}
<option value="{{rating.id}}">{{rating.value}}</option>
{% endif %}
{% endfor %}
</select>
</td>

The select list is derived from a Prompts-based query that returns all rating prompts, based on the prompt key of sport_rating:

The user sees the prompt value of the rating (i.e. rating.value), but behind the scenes, it is saving a GUID value (rating.id) corresponding to that prompt. 

To sum up, the following values are tracked "behind the scenes," and are passed into the SQL query via parameters:

• Person Record GUID

• Sport Record GUID

• Sport Rating Prompt GUID

Parameters in the POST SQL Query

POST methods currently rely on running SQL queries to carry out the desired update. The input values (from the HTML form that was submitted via the pop-up) are passed into the query via parameters:

The parameter tags have id attributes whose values correspond with the input name values in the Source of the pop-up.

These parameters can then be used in the SQL query, providing the passed-in input values and allowing the update to be applied to the sport record as desired. In this example, a SQL update statement will set the particular sport record's rating (with the GUID of the rating prompt) based on what was selected in the dropdown:

update [sport]
set [rating] = @rating
where ([id] = @sportid)
and ([record] = @prospectid)

Note that in this custom SQL query, we are updating the [rating] column on the [sport] table. In some other cases, the custom SQL query may require a delete and an insert statement—for example, when changing a field value for a custom field.

If the portal was to update a custom sport scoped field, the query should simply insert a new row into the [field] table, if the sport record did not already have a value for that field. If the sport record previously had a value and now the user wants to remove that value and replace it with a different value, the query should delete the existing row on the [field] table and then insert a new one. If the portal user wanted to remove the value but not replace it, the custom SQL query should just delete the row in the [field] table. 

Use Slate Forms When Possible

To reiterate, POST methods are meant to handle a few certain scenarios where a Slate form isn't able to reach the specific record that needs to be updated. Using Slate forms (either by adding a Form widget in a view, or taking a form's embed code and adding it to a Static Content widget's source code) should cover the majority of use cases in which a record in the Slate database should be updated by a portal. 

Slate forms, using the form's scope setting, are able to directly update the desired type of record; these updates will be tracked as form submissions associated with that form, as well as on the record's Timeline, for forms that update person/application/dataset records.

Using Slate forms obviates the need for custom SQL, helping to make your portal more sustainable and easier to maintain.

Use Extreme Caution (With Great Power Comes Great Responsibility)

POST methods directly impact database tables, and therefore must be used with extreme caution. For this reason, some custom SQL POST queries cannot be saved and activated without prior approval from Technolutions staff. An alert will be displayed with the following message: "This statement contains SQL that should be first sent to Technolutions staff to review." Please copy and paste your SQL into a Service Desk request, and we'll be happy to take a look.