Validate field input value without using a default/layout/trigger

Related products: Software Factory Windows GUI Universal GUI Indicium Service Tier

If we want to check a field on a valid input value (and prevent saving) we need to create a default, a layout and a trigger procedure.

We regularly use a combination of a default + trigger because the user will see an error message as early as possible and the trigger would prevent the insert the record.

  • The default will validate the field after leaving it (@cursor_from_col_id) and return an error message.
  • The trigger will also validate the field with the same code (but with the inserted of course).

There’s a problem with this combination when a user fills in the field and saves the form without leaving the field first. In this case the error message (see screenshot below) will be shown twice. Once for the default and once for the trigger.

 

A solution could be to add a layout procedure that also validates the field with (again) the same code. The layout procedure could disable the save button when it determines that the field value is incorrect. But this result in a default, layout and a trigger with the same validation check. Not exactly low code.


What if the TSF offers a field validation option? A screen to create one or multiple validations per field. It could range from simple checks like: field is empty, field value must be unequal to 0 or the option the add a query. We could also add a desired result to a configured field validation like showing an error message or placing a conditional layout.

These new field validations need to be executed in the GUI before saving/inserting a record and could act as a default because the validation is linked to a field.  They can also be used for dynamically creating the validation within triggers.

I'm currently looking into this myself and am wondering whether all code could be placed inside a layout procedure?

Looking at the debugging-screen I get the impression that after each default procedure also a layout procedure is executed. And beside disabling the save button you can also place other code, such as the EXEC tsf_send_message.

Unfortunately you still need a Conditional layout to emphasize the erroneous column. And this currently implementation of conditional layout requires a calculated column that indicates whether the value is correct or not.
And although I would love to have this centered around constraints in the data-model, you only get a standard, non-specific message database message when the constraint is not met.

So I agree that there must be a way to simplify this. With various constraints on fields with the option to select a specific message and layout (other than only background or font, maybe also a border-layout).


I recal there’s new functionality in an upcoming release, that allows for SQL code as a default value (as opposed to static defaults).

Depending on the trigger for that functionality, it enables the behaviour requested here, would it not?

 

As an aside, I'm all for allowing SQL-based entries for what is now controlled by the ‘regular/read-only/invisible’-combobox for the field type.


Not sure if I understand you correctly @Boudewijn. I get the impression that the new functionality that you describe is for dynamically setting a default value. If that is correct then I believe that is something different than the subject of validating if an entered value complies to certain conditions (and if not signal the user to improve this).

A dynamic default value, such as the current date that could be changed by the user, could remove the necessity of using a default procedure.

The introduction of more SQL-based expressions/verifications is also something I would welcome.

 


This relates closely to my idea, I think these could be combined:

Since the general problem is, you have to validate something in a lot of places, leading to not-so-low-code applications.


...you have to validate something in a lot of places,

 

That implies using domains. Domains already have entry validation of course, via domain elements, but those are hardcoded and it is difficult to foresee all possible combinations needed.

You'd need ‘domain validations’ besides ‘domain elements’. I imagine it would take an SQL-snippet, and perhaps a set of predefined rules, like ‘mask aa-nnnn’, ‘only Dutch postal codes’, ‘valid IBAN’, and such.

Applying the domain would implement the entry validation every time, and makes central management and maintenance possible.

 


Note that the comments below the idea that Robert Jan has linked also apply to this idea and what was said there has already been implemented in Indicium Universal. I think that both of these ideas describe a nice addition to the SF, but I would also like to give some context on how a large part of the mentioned issues have already been solved and will no longer exist once we fully migrate to only having user interfaces that operate on top of Indicium Universal (which is a goal that we are working towards).

Given the situation where you don’t have any non-Thinkwise software accessing the database, you will typically only need either a layout procedure or a default procedure to both ensure the integrity of your data and give a message to the user.​​​​

If the field is mandatory, you can simply clear it in the default procedure, set the cursor to that field and return the error message. Even if it’s a column that’s not mandatory on the database and was made mandatory by a layout procedure, Indicium Universal will ensure that the record cannot be saved until it has a value for as long as it is mandatory.

If the field is not mandatory and you’re okay with not moving the cursor to the field that is incorrect, you could simply use a layout procedure, set the confirm_button_type to readonly or hidden and return the error message. Indicium Universal will ensure that the record cannot be saved if the confirm button is disabled.

If it’s not a mandatory field and you must move the cursor to it, then you will have to use both a default procedure and a layout procedure and the solution mentioned in this idea would be nice to have. A trigger will no longer be necessary to ensure integrity of a record unless you need to cover inserts/updates made by non-Thinkwise applications as well. Note that end users won’t require any access to the database when Indicium Universal is used, so they won’t be able to bypass it.

Note that this applies to context procedures as well. If you have a table task that can only be executed through Indicium Universal, then you can put your validations in the context procedure and rest assured that the task cannot be executed without checking the context procedure first.

I hope this illustrates how we have indirectly already been working towards solving this issue in our efforts to make authorization easier at every level.


Thanks for clarifying!

A trigger will no longer be necessary to ensure integrity of a record unless you need to cover inserts/updates made by non-Thinkwise applications as well. Note that end users won’t require any access to the database when Indicium Universal is used, so they won’t be able to bypass it.

Even better, because you did not yet state so explicitly: as long as third party applications access the database via the Indicium API, triggers are not needed.

Validations can be reused of course by maintaining granular templates, so that each template implements a field validation.

A slight hurdle I foresee there is having the same validation applying to columns with different names. ‘Sending IBAN’ and ‘Receiving IBAN’ are both IBAN. Using templates means using template parameters, which does not enhance readability and testability of templates. If the validation is at domain level, both fields will have IBAN as domain, and the validation comes through no matter the column name.


 

(Yes, answering myself… I always have trouble finding the ‘edit’ button on the forum...)

 

Would it be possible for the code being prepared for sequences in the SF to be reused at domain level? That would be sweat!

 

 


Hope that a solution to this problem is released quickly as it currently requires a lot of coding to have an easily maintainable solution.


The following idea has been merged into this idea:

All the votes have been transferred into this idea.
The following idea has been merged into this idea:

All the votes have been transferred into this idea.
Updated idea status OpenOn the backlog
On the backlogWorking on it!

@Jasper Will this make the 2023.1 Release? I am also very curious what exact functionality is to be expected here!


Hello Arie,

I am happy to announce that this will be included in the 2023.1 release. I will add an excerpt from the release notes below to show how this feature will function.

 

 

 
A new tab has been added under domain for the input constraints.
 
There are several settings that need to be configured to use input constraints:
  • Constraint type:
    • SQL expression: Evaluated the entered data with an SQL expression.
    • Regular expression: Evaluate the entered data with a regular expression.
  • Constraint timing:
    • Immediate: The data will be evaluated as it is being entered into the field. (Note that because SQL expressions require a database transaction, this timing is only available for regular expressions).
    • On change: Data will be evaluated when the cursor leaves the field.
    • On commit: Data will be evaluated when the record is saved.
  • Sequence: If multiple input constraints are used for the same domain, the sequence will determine the order in which they are evaluated.
  • Expression: The expression that will be used to evaluate the data (Note that the expression must match the syntax of the chosen constraint type).

Also note that the input will only be evaluated for editable columns, task parameters, report parameters etc. Read-only and hidden columns will be excluded from input constraints.


Working on it!Next release

@Peter Verwijs Great, sounds like very promising and valuable functionality!


A quick update on this feature. Through internal testing we discovered that the On commit timing wasn’t functioning as intended and implementing a correct solution would drastically increase the complexity of the development. Due to how close we are to the next release and the effort required to implement it, we made the decision to ship this feature without the On commit timing for now.


A quick update on this feature. Through internal testing we discovered that the On commit timing wasn’t functioning as intended and implementing a correct solution would drastically increase the complexity of the development. Due to how close we are to the next release and the effort required to implement it, we made the decision to ship this feature without the On commit timing for now.

@Peter Verwijs That’s too bad! Basically the new functionality without On commit will mainly replace current messages from Default procedures.

With the On commit we hoped to be able to remove validation triggers that we currently build.

By the way: the functionality I am really expecting with this solution is to be able to throw a validation both Immediate/On change AND On commit, which will allow us to replace Default message and Validation triggers with a single check (for which the message is being shown in-line instead of as pop-up or snackbar).

Long story short: could you replace the Domain with Elements for Immediate/On change/On commit with Checkboxes so the constraint could be assigned both as validation in the GUI and on the Database?


Hi Arie,

Thanks for your feedback. While the On commit option will not be shipping with the next release, including it in a future release is certainly still possible. For now we would like to consider the initial implementation of this idea as closed. Would you mind creating a new idea with any feedback you have so we can keep track of progress there separately, as well as use it as a place to gather further feedback.


Next releaseCompleted