ThinkStore

Share reusable code and model examples with fellow developers

65 Topics

Mark JongelingCommunity admin

posted in Functionality

Autonomously generate report and send it by e-mail

GoalBased on a customer story.Every day at 06:45 a procedure runs for the customer and during that procedure, errors can occur. These errors are recorded inside the application and viewed by one particular user. This user only opens the application to look at these errors. If there are no errors at all, the user still has to look inside the application to find out. It would be more handy if the user gets an e-mail with a Report of all the errors. SolutionBy using the System Flow functionality, we can autonomously let Indicium take care of generating a Report and sending it to the user by e-mail.In this example I will show you what is needed to achieve this.Prerequisites:Indicium (Universal) - hereafter called Indicium Application in IAM SMTP server for sending the e-mail - this example uses a free SMTP server by sendinblueChaptersCreate a View in the Software Factory Create a DevExpress Report with the DevExpress Report Designer available in TCP Create a Report in the Software Factory

Robert Jan de NieThinkwise blogger

posted in Functionality

Add a where clause to your index

Goal There are some cases, where you would like to add a where clause to an index. For example, you want to have unique article numbers in your article table, but also have the option to de-activate articles and in that special case, re-use the article number. You could write some trigger code to make sure this is the case, but there is an easier solution by creating a unique index, combined with a where clause. The index would look something like this: CREATE UNIQUE NONCLUSTERED INDEX ix_article_number ON article( article_number ASC)WHERE active = 1 There is only one problem, you cannot configure this in the Software Factory by default. Solution There is a way around this problem, making use of tags, combined with some code in the dynamic model that injects the where clause in the index creation scripts. There are two steps involved, first you have to add a control procedure in dynamic model, containing the following code: declare @sql varchar(2000)--The bit of code that needs

Moller TomaCaptain

posted in User Interface

Thinkstore has been integrated into the SF development environment

From version 2022.1, the Thinkwise Thinkstore has been integrated into the Software Factory development environment. The demos and code samples shown here are still available for older versions for now but are not actively maintained. We recommend upgrading to the latest version of the Thinkwise platform to benefit from the latest features and the integrated Thinkstore.Do you have any new ideas? For example, which solutions would you like to see in the Thinkstore or which function will enrich our Thinkstore, then we would like to see them in the Ideas module. This way, other members of the community can also see your idea and vote on it.

posted in User Interface

Enrich your application with Workflow

GoalWe are often asked by companies whether we offer functionality for Workflow and/or Case Management. At the moment, there is no standard functionality for this in the Thinkwise Platform. In this topic you will find a Workflow Engine which can be used in (existing) Thinkwise applications.SolutionIn the video you will find all the steps needed to start using this module. The steps are Import the workflow in the Software Factory Upgrading an application to add workflow The architecture of the workflow Defining workflows Executing workflow tasks Monitoring workflows Configure notifications Technical side notes This workflow module automatically generates process flows which might be used in the workflow execution tasks. Such a process flow might end with starting a certain task in your application. When in your application this task is defined as the start of an other process flow, this flow would not be started, because the Thinkwise Platform does not support linked proces

FreddyThinkwise Local Partner Brasil

posted in User Interface

Indicium marks dates as UTC mark, a helpful function to convert you locale dates.

ProblemThe ones using Indicium probably encountered this problem. Indicium marks all date(time) as UTC. Which can cause a problem when a third party GUI interprets this date with its timezone marking. Although SQL server has domains for date/time that can hold timezone information, a lot of times it lacks this info.Solution The best solution is to use a date/time domain like datetimeoffset that also holds timezone information, however if that is not available, or not really necessary if you work in just one timezone. In all cases you could create a function that takes a datetime and converts it to UTC in this case. create function get_datetime_in_UTC ( @date_time date_time ) returns date_time as begin-- Datum/tijd in UTC teruggeven omdat indicium elke tijd aanmerkt als UTC  declare @TimeZone varchar(50)  exec master.dbo.xp_regread 'HKEY_LOCAL_MACHINE'  ,'SYSTEM\CurrentControlSet\Control\TimeZoneInformation' 

JasperAdministrator

published in Functionality

Translating model objects in business logic

In some cases it may be desirable to know the translation of a model object, such as a table, column or domain element, inside your business logic. See for example this idea: Translating domain elements | Thinkwise Community (thinkwisesoftware.com)Starting with the last releases of Indicium and soon available in the Windows and Web GUIs, a session variable is added that provides the language of the current user. This makes it possible to automatically generate a function that returns the translation of model objects in the user’s language. FunctionTo add this dynamic function to your Thinkwise application, first create a function subroutine named tsf_translate with the following properties:Return value: scalar Return scalar domain: transl - nvarchar(200) Input parameters: type_of_object - int transl_object_id - varchar(100) Control procedureNext, add a control procedure in the Functionality modeler with assignment type SQL to create the body of the function:Code:-- Determine of whic

JasperAdministrator

posted in Integrations

Microsoft Graph authentication

This post decribes the required steps to connect to Microsoft Graph webservices (Azure, Office 365, Microsoft 365). Detailed information about Microsoft Graph can be found here: https://docs.microsoft.com/en-us/graph/.This guide uses Application permissions to get access to the API without a user's credentials. The different types of permissions for Microsoft Graph are explained here.To access Microsoft Graph using Application permissions, first follow the instructions on this page to configure the app registration, which is required to retrieve an authorization token. Process flowOnce you have setup your app registration in the Azure portal, you can create a process flow to authenticate and call the required services.In this example we will be using the following process flow, which is triggered by the execution of a task, then uses an HTTP connector to authenticate and get an authorization token, calls a Graph web service using that token, and shows the response of that call. Only th

posted in User Interface

Make your logging data available in the GUI

GoalWhen you have system versioned tables in your database, SQL Server logs all changes made to the data in those tables. This solution makes this logging data available for the user. This can look like this: SolutionThe solution in this topic creates views on all system-versioned tables. These views give the user access to the logging data. Also, all of these views get conditional layouts in the Software Factory. These conditional layouts give fields with changed values a color, which makes it easier to analyze the data.Dynamic model meta_view_historyThis code create a view definition for every system versioned table, along with conditional layouts to point out changed data.When you add this code to the dynamic model, you have to change a few values first:@screen_type_id Your desired screen type: You can keep this null, or change the value.@original_date_column Name of an existing date column with settings that can be copied.@valid_from Name of columns that

posted in Functionality

Do periods in time overlap?

Goal Sometimes you have a table which contains records with a period in it. For example: A product has a price which is valid between a from date and an until date. In such a case, it is important that this product only has one price per date. This means periods may not overlap per product. This topic discusses a solution for this. Solution In this piece of code the price-example is elaborated. Important here is to realize that periods can overlap in several ways: From date period A is between From date period B and Until date period B End date period A is between From date period B and Until date period B From date period A is before From date period B and Until date period A is after Until date period B ----------------------------------------------------------- This example code retrieves all periods which overlap---------------------------------------------------------declare @price table ( product_id varchar(100) ,from_date da

posted in User Interface

Trace your changes by adding trace fields

GoalThe goal of this solution is to automatically add trace fields to all tables so the user can see who added the record or who made the last change. This solution adds these fields to a separate tab page on a form. This looks like this in the GUI: You can choose at what moment you want the fields to be filled: During editing or after saving the record.SolutionThe solution consists of code for Dynamic Model (for adding the fields) and two Control Procedures (for filling the fields). Depending on the moment you want the fields to be filled (during editing or after saving), you can choose either for a control procedure for defaults or for a control procedure for triggers.Dynamic model add_trace_columnsThis code adds trace columns to every table.The trace fields are: update_user update_date_time insert_user insert_date_time If you add the tag 'no_trace' to a table, no trace columns will be added to that table. The trace column are automatically translated in ENG and NL.Important:

posted in User Interface

Multi language data in your application

In applications built with the Thinkwise Platform, the GUI supports multi language translations of objects like menu’s, entities, columns, tasks, and so on. However, the data is not multi language (yet). For example, when you have a table Products, the name of the product can be “Car”. This is fine when you’re English speaking, but when you’re French, it would be nice if you would see ‘Voiture’.The data to be translated resides in your application, not in the Thinkwise Platform. Therefore it is hard (and not desirable) to solve this challenge in the Thinkwise Platform itself.GoalIn this solution you will find functionality to translate one or more values per record. This functionality automatically generates:Tables in which you can translate one or more values of records. Lookup queries which retrieve lookup values in the language of the user.SolutionThe solution for multi-language data consists of one Dynamic Model script. This script generates translation tables for every table which

Andre te RaaCaptain

posted in User Interface

Default tooltip for tasks, reports, prefilters and cube views

GoalWhen you are using icons for tasks, reports, prefilters and cube views, it is not always clear what the icons represent. Is it a task or is it a prefilter?You can use the ribbon or the context menu to know what it is, but you can also add this information to the tooltip (e.g. Task: …., Prefilter: ….). Now you only have to hover the mouse over the icon. SolutionYou can do this with a simple dynamic model procedure. Based on the type of object the tooltip is default filled when the object is translated and the tooltip is empty. update t set tooltip_text = case when type_of_object = 11 then case when appl_lang_id = 'NL' then 'Taak: ' when appl_lang_id = 'ENG' then 'Task: ' end when type_of_object = 10 then case when appl_lang_id = 'NL' then 'Rapport: ' when appl_lang_id = 'ENG' then 'Report: ' end

Mark JongelingCommunity admin

posted in User Interface

Automatically create a function for system versioned tables

GoalWhen working with the Software Factory, it is possible to make tables System versioned, or another name is temporal tables. The SF will create a history table when the the setting System versioning is set to true. However, to make use of this history data we recommend using a function to obtain records from the history table. SolutionHere is a Dynamic model control procedure that automatically creates a 'get_[table]_at_point_in_time’ function. And below this code, find more code - this is the Control procedure that is needed to create the function.(This script works for the 2021.2 version of the Software Factory. Compatibility may change over time.)Dynamic model (meta_function_point_in_time):select @project_id as project_id, @project_vrs_id as project_vrs_id, 'get_' + tab_id + '_at_point_in_time' as subroutine_id, 'Function' as subroutine_type_id, 2

Arjan SollieThinkwise blogger

posted in Functionality

Hide empty fields when not editing

GoalSome table might contain allot of fields. A good way to show the user the fields that contain important information is to hide all empty fields. By saving all the space that the empty fields normally would use, more (important) fields that contain data will be instantly visible to the user.SolutionYou could manually set up a layout procedure to hide all these empty fields. But since we are using the Software Factory, why bother with setting up manually assigned templates. Instead it would be great to have a dynamic control procedure that automatically sets up such a layout procedure. That is exactly what this topic contains.All you have to do is create the tag HIDE_EMPTY_FIELD and assign this tag to the specific table. The layout procedure will automatically hide all fields when they are empty or contain a empty string. Fields that are already set to hidden on column level are excluded.------------------------------------------------------------- link program object items----------

Arjan SollieThinkwise blogger

posted in User Interface

Active prefilter

GoalApplications often use a checkbox to determine whether a record is active or inactive. In most tables you would like to display the records which are marked as active and hide the records marked as inactive. This means you will have to set up a prefilter to hide the inactive records. Image how great it would be if you could generate those prefilters automatically. This topic will teach you how to configure the automatic generation of prefilters.SolutionThis topic will provide you with a dynamic model procedure which generated prefilters on tables that contain a column named inactive. It also provides this prefilter with an image and sets the prefilter to enabled and visible on the main, detail and popup screens. The icon and the column which triggers this script, can be ajusted to match your own application.Create a dynamic procedure and submit the following control procedure code:--------------------------------------------------------------- create prefilter active where not exis

posted in Functionality

Making sure your changes won't be overwritten by someone else

Goal Sometimes different users change the same record at the same time. The last one who saves the records, overwrites the earlier changes by the other user. And it is likely both users won't even notice! If this scenario is a daily practice, you probably need an optimistic locking mechanism in your application. Optimistic locking is when you check if the record was updated by someone else before you commit the transaction. (Pessimistic locking is when you take an exclusive lock so that no one else can start modifying the record) In this topic a solution for optimistic locking is provided. Solution First of all, by dynamic model code, every table gets an extra column, update_counter. This column is update after the commit of an update, or, when in the meantime the counter is incremented by another process, it will throw a message. Dynamic model create_column_update_counter This dynamic model code adds column to the tables. With the tag NO_UPDATE_COUNTER you can exclude a table f

Moller TomaCaptain

posted in User Interface

ThinkStore topic structure

Topic type: conversationTitle: <Explain the purpose of your topic>Description: Start with the header 'Goal’<Explain the purpose of your functionality or screenThen continue with the header 'Solution’<Share the solution here.>Example PS: Don’t forget to subscribe for the ThinkStore!

Mark JongelingCommunity admin

posted in User Interface

Drag and drop between subjects example

GoalCreating a drag and drop task for the first time can be challenging. This example will tell how to create a simple drag-drop task that will enable you to drag products into a shopping cart.SolutionThere are three components, namely the Source tab, the Target tab and of course the Drag-drop task. Assuming the tabs are already present, we will focus on making the task and setting it correctly.Create a task that has the Primary Key of the source tab and the target tab. Connect it to the Target tab on the Primary key columns. In Subjects, navigate to the Source tab and create an entry in Links → Drag-drop. Enter for the Drag-drop parameters (at least) the Primary key columns of the Source tab. These are the parameters that get filled with values from the SOurce tab. Set up the drag-drop interaction and you are good to go. Enable def. drag-drop enabled if needed at the Settings tab in Subjects.Result looks like this: Code used for this example:--Product not in shopping cart yetif not ex

Mark JongelingCommunity admin

posted in User Interface

Reorder Drag and drop example

GoalSome lists have an order number that functions as a standard sorting, or as a sequence of actions, or for other reasons. The Drag and drop functionality the GUI offers is a great tool to use for this but it needs some coding.SolutionThe following code is a good start for sequential lists (1,2,3,4,etc.) and work with multi-selection.Requirements: Task with PK parameters, a from_order_no and a to_order_no parameter. Connect to table with only to_order_no = table.order_no Create drag-drop at desired subject and connect PK parameters and from_order_no = table.order_noUse following code in Functionality > Template.--Continue if differentif @from_order_no <> @to_order_nobegin --Copy all records into #temp select * into #temp from [table] t --Make space for reorder update t set t.order_no = order_no * 10 from #temp t --Alter order no of dragged row (+1/-1 depending higher/lower dragging) update t set t.order_no = @to_order_no * 10 + ii

Arjan SollieThinkwise blogger

posted in User Interface

Automatically refresh activated documents

GoalAfter opening a screen the dataset is loaded within this screen. When you open a different screen and afterwards reactivate the screen, which you had opened previously, the dataset is still the same. You have to manually click on refresh to activate a refresh action within this screen. You could also set an automatic refresh based on a specified schedule, but this is often not the most suitable option.Image being able to automatically refresh data after reactivating a screen, that is exactly what this topic allows you to configure.SolutionThis topic will guide you through setting up a dynamic Software Factory model template. It will create the following objects:Processflow Per table/variant, determined by which table/variant is accessible through a menu type or table/variant tag Processactions Start Stop Activate document Refresh table Processteps in this order Start process Activate document Refresh table Implementing it requires you to set a control procedure in the “Dynam

Kevin HorstModerator

posted in User Interface

Multi level UI

GoalWorking with a new application can get quite overwhelming for end users. Not only is there a whole new UI for them to figure out, but they could also get overwhelmed with information, options, and functionality that is not yet relevant for their day-to-day tasks. This makes them take longer to get used to the whole UI. Someone who just started at sales and uses the application for the first time, might not immediately need historical data or Business Intelligence to target their first prospects. It is important that these ‘beginner’ employees smooth into using the application with a simplified version of the UI. In a later stadium, these users can then be scaled up to an ‘advanced’ or ‘expert’ level, revealing new objects (e.g., menu items, details, tasks, etc.). SolutionTo achieve this at this moment we need to define user groups in the Intelligent Application Manager, for each difficulty level (e.g., ‘sales_beginner’, ‘sales_advanced & ‘sales_expert’). For each difficulty le

Arjan SollieThinkwise blogger

posted in Functionality

Email queue with email attachments

GoalSending emails from within your application is a common feature and can be implemented in many ways. However the traceability of sending emails from your application is often difficult. Therefor it is very usefully to gain some insights into which emails have been sent to whom and whether the email has exactly been sent. This topic demonstrates a basic setup for gaining insight into your email traffic. SolutionFirst of you will have to set up some tables. This example will also contain tables used by the Thinkwise Reporting Service. This topic will allow you to email attachments created by the Thinkwise Reporting Service. This topic will not explain how the configure sending emails with a template. If you would to know how to do that, look at this topic: Example email module. Create the following tables (if you do not use the Thinkwise Reporting Service, ignore the reporting_service_request tables and columns):Make sure that you use the following domain elements for the column emai

posted in Functionality

Find illegal XML characters in your database

Goal Many application have functionality for importing or exporting data in XML format. Sometimes you will get an error about an illegal XML character: XML parsing: line 1, character 2967663, illegal xml character And then you will have to try to find that character, which is very hard. This solution can help you out. Solution This solution creates a stored procedure search_illegal_xml_characters which searches your entire database for illegal XML characters. It is also possible to search in one specific table. The procedures uses a function, remove_illegal_characters_for_xml, which is also created in this solution. Important: this function does NOT remove illegal characters from your database. It only removes those characters from a string. Dynamic model add_search_illegal_xml_characters This dynamic model code creates stored procedure search_illegal_xml_characters and function remove_illegal_characters_for_xml. If you want to use this procedure as task, create your own task

Andre te RaaCaptain

posted in Functionality

Handling errors in procedures and triggers on SQL Server

Goal This is a solution for default error handling on SQL Server. And how to add this solution in all your triggers and SQL procedures (tasks and subroutines) using dynamic control procedures. The solution is based on the article “Error and Transaction Handling in SQL Server” (Erland Sommarskog, 2015). When I started developing software on SQL Server, I missed an easy way to find the place where an error occurred. I was used to DB2 where you could always find the sending program in the job log. It was easy to show the job log using a view and a table valued function. On SQL Server this is a little different. When an error occurs you have some functions to get information about the error like ERROR_MESSAGE() and ERROR_PROCEDURE(). But if you don’t use a mechanism to handle the error, you don’t know where the error occurs. It could be in a procedure, in an instead of trigger or in one of the triggers down the stack. Solution When I started looking for a solution, I found the articl

Arjan SollieThinkwise blogger

posted in Functionality

Example email module

Goal Most application constructed will include some sort of email module. In most cases those email modules share a lot of similarities. Therefor it is better to use a proven concept instead of designing one yourself. This topic will help you set up the basics regarding most email modules. Solution This example is based on setting up an email module while using the Databasemail option available within MS SQL Server. The first step is setting the email template configuration. Note that my example contains 2 views: email_profile email_profile_account These views allow a user to select the email configuration available within the MS SQL Server database mail. First create these tables and views: The email profile view requires the follow SQL code: ------------------------------------------------------ select all email profils----------------------------------------------------select sp.profile_id ,sp.name ,sp.descriptionfrom msdb..sysmail_profile sp The email profil

1
2
3

Page 1 / 3