Question

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

Like

7 comments

The table for service agreements is called ServicePact

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?

Mitch Kaschub,

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

  1. Open the configuration by going to https://mybpmnonlineurl/0/dev
  2. 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".
  3. You'll see the object that returns in the results is named ServicePact that has a title of "Service agreement"
  4. 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

Thank you Ryan!

This is going to be very helpful!

 

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. 

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

Oscar Dylan,

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

Log in or register to comment
Question

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.

Like

1 comments

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. 

Log in or register to comment