query | Community Creatio

Question

Ahmad Bagus Ariyanto

06:35 Aug 10, 2023

It's possible to query TOP 10 to this code?

File attachments

carbon (1)_0.png

1 comments

Oleg Drobina

11:19 Aug 10, 2023

Hello,

You need to add esq.RowCount = 10; to select top 10 records only.

Show all comments

Question

Unable to update System values directly from database

Syed Ali Hassan Shah

06:27 Jul 24, 2023

I want to update MaxFileSize and ActiveFileContentStorage using a query to save time. But whenever I tried to run the query from SQL Console it successfully update the values in the database but they are not reflected in the Creatio Portal.

When I add the query in the SQL file in advanced settings and run it, it updates the data in the database and in the portal as well. The problem is that it's not dynamic which will kill the purpose of using the query.

In other words, if I have to go and change the value in that file and run it again then we can do the same for the system settings as well.

Can anyone help me with that?

Thanks in advance.

1 comments

Oleg Drobina

15:44 Jul 24, 2023

Hello Syed,

The issue here is that these values are also storred in cache and direct updating of these values are not reflected in the cache. That's why you have old values after updating values from the database directly.

What should be done after updating values in the database is Redis flush. But this will lead to all users to be logged out from the system and obviously cannot be performed during business hours for the application (or users should be notified that the Redis will be flushed soon). After Redis flush new system settings values will be used when working in the application.

Show all comments

Question

SQL Query for Removing and Re-Adding Service Agreements on Account Records

Mitch Kaschub

20:56 Mar 07, 2019

Hello, we've found when we open a case and set the account, the service agreement will default to which ever one has the oldest created on date. We want it to default to Non-SLA, however many accounts have that as the newest service agreement.

Is there a query that can be written and used in the SQL query console to remove the 5 older service agreements from an account and re-add them so they have a newer created on date?

In other words, I want to do what I am doing in this video for all accounts at once with a query instead of having to go through each account 1 by 1 and do this. Any ideas? Maybe someone could at least help me with what table I would find the accounts service agreements in?

http://recordit.co/RUW2oPrGwo

7 comments

Ryan Farley

01:53 Mar 08, 2019

The table for service agreements is called ServicePact

Mitch Kaschub

06:15 Mar 08, 2019

Thank you Ryan,

In the future, is there a recommended way for me to find which tables certain types of data live in? Instead of having to ask in these community articles?

Ryan Farley

20:52 Mar 08, 2019

Mitch Kaschub,

Yes. This is how you can find the name of any table.

Open the configuration by going to https://mybpmnonlineurl/0/dev
Search for the section or entity name, make sure you have "Title" checkbox checked. This would allow you to search for the name "service agreement".
You'll see the object that returns in the results is named ServicePact that has a title of "Service agreement"
The table name is always the same as the entity name, in this case "ServicePact"

Similarly, you can double-click on that object to open it, expand the Columns to see the names of the fields on that table as well, since they will be the same as the property names on that object (not to be confused with their titles).

Ryan

Mitch Kaschub

21:18 Mar 08, 2019

Thank you Ryan!

This is going to be very helpful!

Mitch Kaschub

23:13 Mar 08, 2019

Ryan is there a table that shows which service agreements are added to each account? If I select * from ServicePact it just shows the 7 service agreements but doesn't show which accounts are using them. I tried to run the Accounts table too and I don't see the service agreements in that table either.

Oleg Drobina

17:02 Mar 11, 2019

Mitch Kaschub,

The table that connects Service Pacts and Accounts is called ServiceObject. Connection happens using AccountId and ServicePactId columns. So if you need to delete service pacts for some particular account you can use this table and "CreatedOn" column, since records are added/deleted there once any record is added/deleted in "Service agreement" detail.

Best regards,

Oscar

Mitch Kaschub

20:14 Mar 11, 2019

Oscar Dylan,

Perfect, that should be all I need. Thanks Oscar!

Show all comments

Question

Transform raw query to JSON Format DataService

Romadan Saputra

07:39 Sep 27, 2018

Hi everyone,

I have query:

SELECT (
	SELECT
		count(DISTINCT(c.id))
	-- c.id, c.name
	FROM flight_detail fd
		JOIN contact c
			ON c.id = fd.contact_id
		WHERE
			-- get last year 
			(fd.departure_date >= '2016-09-01' AND fd.departure_date <= '2017-09-30')	
			AND
			-- get this year
			fd.contact_id IN (
				SELECT fd_ly.contact_id
				FROM flight_detail fd_ly
				WHERE 
					fd_ly.departure_date >= '2017-09-01' AND fd_ly.departure_date <= '2018-09-30'
			)
) AS 'Customer Last Year Who Still Buy Ticket This Year',
(
	SELECT
		count(DISTINCT(c.id))
		-- c.id, c.name
	FROM flight_detail fd
		JOIN contact c
			ON c.id = fd.contact_id
		WHERE
			-- get last year 
			(fd.departure_date >= '2016-09-01' AND fd.departure_date <= '2017-09-30')
) AS 'Customer Last Year';

How to transform query to JSON format for sending data paramater DataService.

This query for get data from bpm and I want show the result to my 3rd app.

Thanks.

1 comments

Peter Vdovukhin

14:58 Sep 28, 2018

Dear Romadan,

Not all queries can be easily translated in a single DataService request. In the most complex cases consider refactoring a query into few smaller queries and sending them one by one storing the result from first query and passing it as a parameter to the next query.

Show all comments

Subscribe to query