Description of work in SQL
To work with Bitrix24 and amoCRM, Nemind uses an intermediate SQLite database to store data on the hard disk of a user's computer.
You can directly open this database, for example, in the free DB Browser for SQLite application.
Thanks to the SQL language, you can configure almost any logic of data queries.
Where to debug SQL?
You can use the built-in SQL editor from Nemind (see the "Enable SQL Editor" section), DB Browser for SQLite or other means of working with SQL. When working with any editors except Nemind, you should pay attention to the peculiarities of storing SQL in .npt files:
Nemind SQL Features, Built-In Functions and Attributes
Using special characters more> and less <
Since these characters are reserved for the XML language in which .npt projects are stored, these characters in them are replaced according to xml standards:
<changes to & lt;
> changes to & gt;
example:
Tasks.closedDate & gt; Tasks.DEADLINE
This in SQL will be Tasks.closedDate> Tasks.DEADLINE
In this case, when debugging SQL and replacing it in an npt file, it is necessary to write special characters & lt; or & gt; , and in SQL debuggers, <or>.
Nemind Built-in Functions
For a special tasks, we developed internal functions that are not directly supported by external SQL editors, such as DB Browser for SQLite, but you can use them inside Nemind in .npt files.
DurationWorkTime
function for calculating the period taking into account working hours.
Important: if the second parameter is empty, then the time until the function is called, that is, until now, will be considered.
Example:
CASE WHEN Tasks.DEADLINE is not null
THEN
DurationWorkTime(DateTime(Tasks.createdDate), DateTime(Tasks.DEADLINE))
END
AS [Planned working hours #ftDurationHms],
The example shows how to return a void, if the second parameter is empty, through the CASE construct.
Translate
Data translation function through Nemind translation methods.
example:
Translate(Statuses.Phase) AS [Stage group]
StripTags
Cuts html codes, for example
<div><b>text</b> </div>
will turn into
text
The function is usually used for descriptions of cases in Bitrix24, since they are stored there with html-formatting, for example, from e-mail.
example
StripTags(Activities.DESCRIPTION) AS [Description],
Attributes in Nemind Header Names
Attributes are inserted at the end of the header, separated by spaces, for example:
[Duration #ftDuration]
the title will be Duration (more precisely, “Duration” if Russian is selected), and the Nemind type #ftDuration will be applied. We list the attributes
#ftDuration -
the result in seconds will be converted to a common duration format: years - months - days - hours - minutes - seconds. Often used with the built-in DurationWorkTime function.
Examples in SQL:
CAST(CASE WHEN Leads.DATE_CLOSED is not null
THEN
strftime('%s', Leads.DATE_CLOSED) - strftime('%s', Leads.DATE_CREATE)
ELSE
strftime('%s', 'now') - strftime('%s', Leads.DATE_CREATE)
END AS integer) AS [Duration #ftDuration],
Here, lead time is considered either from the beginning to the end, or until the moment of calculation.
DurationWorkTime(DateTime(Activities.START_TIME), DateTime(Activities.END_TIME)) AS [Working hours #ftDuration]
It counts business hours based on business hours.
#ftDurationHms
DurationWorkTime(DateTime(Activities.START_TIME), DateTime(Activities.END_TIME)) AS [Working hours #ftDurationHms]
The #ftDurationHms attribute translates seconds into hours - minutes - seconds format. Unlike #ftDuration, the program will not show days and months, it will complete rounding to hours. This is convenient, for example, if you need to calculate the number of man-hours spent, where there may be thousands.
#ftBoolean
Yes or no, the variable will be displayed as a checkmark.
example:
CAST(Tasks.parentId IS NOT NULL as boolean) AS [Subtask #ftBoolean],
Here we check if the task has a link to the parent task and displays it as a boolean value with check marks.
#ftStages
The status or stage will be shown taking into account their sorting and completeness (in amocrm and taking into account color). Example:
Statuses.NAME AS [Status #ftStages],
How to calculate the duration?
This is one of the most common tasks for which SQL is used.
Take a look at the examples:
strftime('%s', Leads.DATE_CLOSED) - strftime('%s', Leads.DATE_CREATE)
this is a calculation of the time between the creation and completion of the lead (pay attention to the features of the field names in your CRM).
Create a field for it:
CAST(CASE WHEN Leads.DATE_CLOSED is not null
THEN
strftime('%s', Leads.DATE_CLOSED) - strftime('%s', Leads.DATE_CREATE)
ELSE
strftime('%s', 'now') - strftime('%s', Leads.DATE_CREATE)
END AS integer) AS [Duration #ftDuration],
Above, we made checks for the completeness of the field, two types of calculations and pointed to the end of the attribute #ftDuration
Time between lead creation and current moment:
strftime('%s', 'now') - strftime('%s', Leads.DATE_CREATE)
