Skip to main content

Idea pipeline (top 25)

Filter by idea status

Filter by product

479 Ideas

K.Bakkenes
Captain
K.BakkenesCaptain

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

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.

Multiple instances of the TSF databaseCompleted

We are running a development platform with about 30 developers and thus we are often bothered with a slow working SF, which primarily happens when someone is either generating definitions,validating definitions or executing a merge. But it also can happen sometimes just by trying to save a template or a new table column.The root cause of this, in my opinion, is the fact that all of our transactions within the SF are executed on one single SF database. Especially during generation or merging a lot of data is being read, deleted or created in that single database, which often results in a slow performance or even processes being assigned as deadlock victim.I believe the performance of the SF could largely be improved if we were able to run multiple instances of the SF database, maybe even across multiple database servers, and be able to merge branches from different databases. A couple of great benefits are:Whenever I work in a branch I am only bothering people that work in the same branch as I do or at least people that work in the same SF database that I do. It greatly improves scalability, especially when it supports multiple database serversEspecially the first benefit is great for a performance improvement.With about 30 people developing there are several merges happening in a week. Consider the trunk to have it’s own SF-database and executing a merge to the trunk. This merge will then primarily lock access to the SF-database that belongs to the trunk and not slow down performance of the other 29 developers still developing in their own branches. 

tsf_user inlineableCompleted

As also discussed in the following link, since SQL Server 2019 inlineable scalar functions are an option. This can make sure a query can run parallel. The tsf_user function is not inlineable at this moment. When using this function in a heavy query, it will not be executed parallel as you can see in the images below. Not inlineable not inlineable Query with tsf_user not being executed parallelWhen removing the use of variables in the function, the function is inlineable and the queries will run parallel when needed:Inlineable = trueMy heavy query with tsf_user goes parallelThe script I have used is:SET QUOTED_IDENTIFIER ON;SET ANSI_NULLS ON;GOCREATE OR ALTER FUNCTION tsf_user()RETURNS tsf_userASBEGIN --control_proc_id: sql_tsf_user --template_id: sql_tsf_user --prog_object_item_id: sql_tsf_user --template_description: Returns context_info or system_user RETURN CASE WHEN CONTEXT_INFO() IS NOT NULL THEN CASE WHEN LEN(REPLACE( CONVERT( VARCHAR(128), CONTEXT_INFO() )COLLATE Latin1_General_BIN, CHAR(0), '' ) ) <> 36 THEN REPLACE( CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN, CHAR(0), '' ) WHEN SERVERPROPERTY('EngineEdition') <> 5 THEN REPLACE( CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN, CHAR(0), '' ) WHEN REPLACE( CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN, CHAR(0), '' )NOT LIKE '________-____-4___-____-____________' THEN REPLACE( CONVERT(VARCHAR(128), CONTEXT_INFO())COLLATE Latin1_General_BIN, CHAR(0), '' ) END ELSE SYSTEM_USER END;END;GOPerhaps this is something you can use in a new version. 

Robert Jan de Nie
Thinkwise blogger
Robert Jan de NieThinkwise blogger

Filtered indexesCompleted

Since a recent version of the Software Factory it is possible to create unique indexes that exclude NULL values. This is a useful feature, however, I think it would be great if we can take it one step further.I’d like to propose the possibility to create filtered indexes, where the developer him/herself can supply a where clause that will be applied to the index.For myself, I have this working using some dynamic model fiddling, but I think it would be pretty straight-forward to implement as a default feature.This is an example from the index script that is being created using my adjustment:/* Create index 'ix_un_def_dimensions_len' on table 'article_default_dimension'. */*snip*create unique nonclustered index ix_un_def_dimensions_len on "article_default_dimension"( article_id, default_length)where default_width is nullgo/* Create index 'ix_un_def_dimensions_len_width' on table 'article_default_dimension'. */*snip*create unique nonclustered index ix_un_def_dimensions_len_width on "article_default_dimension"( article_id, default_length, default_width)where default_width is not nullgoThese indexes ensure that I can have unique records for either article_id / length where width has not been provided and have unique records for article_id / length / width where width has been provided.The only thing the developer has to do, in my example, is provide the where clause, i.e.where default_width is nullAt the moment, I use a tag on the indexes to do this, but this could just as well be a field on the index-table/screen.