Question

Hi community!

Contact has a colmn called "ContactPhoto" which returns System.Byte[].
I need this to be a string for a view that I'm creating.

this is what I tried:
 

SELECT Id, ContactPhoto,
	cast('' as xml).value('xs:base64Binary(sql:column("ct.ContactPhoto"))', 
	'varchar(max)') AS UsrContactPhoto
	FROM contact ct

which returns me an empty string:

https://prnt.sc/po1qe8

 

Any idea how I can convert it?

Thanks in advance!

Like

0 comments
Log in or register to comment
Question

Hi,

I'm currently trying to obtain the "Id" from one of the contacts in my "Contact" table but I'm getting the same error:

Terrasoft.Common.ItemNotFoundException: Value "Id" was not found.
   at Terrasoft.Core.Entities.EntityColumnValueCollection.GetByName(String name)
   at Terrasoft.Core.Entities.Entity.InternalGetColumnValue(String valueName)
   at Terrasoft.Core.Entities.Entity.GetColumnValue(String valueName)
   at Terrasoft.Core.Process.UsrProcess2MethodsWrapper.ScriptTask1Execute(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessScriptTask.InternalExecute(ProcessExecutingContext context)
   at Terrasoft.Core.Process.ProcessFlowElement.Execute(ProcessExecutingContext context)

 

This is the script task i'm using for it:

var result = "";
var userConnection = Get<UserConnection>("UserConnection");
    
EntitySchemaManager esqManager = userConnection.EntitySchemaManager;

var rootEntitySchema = esqManager.GetInstanceByName("Contact") as EntitySchema;

var esqResult = new EntitySchemaQuery(rootEntitySchema);

esqResult.AddColumn("Id");
esqResult.AddColumn("Name");

var entities = esqResult.GetEntityCollection(UserConnection);

result = entities[0].GetColumnValue("Id").ToString();

Set<string>("ProcessSchemaParameter1", result);

return true;

Note: If i try to get the "Name" instead i dont get any error.

Like

1 comments

Try this

var opportunityCarQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager,"OpportunityCar");
opportunityCarQuery.AddAllSchemaColumns();
 
var filter = opportunityCarQuery.CreateFilterWithParameters(FilterComparisonType.Equal, "Car", (Guid)carNodeId);
opportunityCarQuery.Filters.Add(filter);
 
var opportunityCarEntities = opportunityCarQuery.GetEntityCollection(UserConnection);
 
foreach (var opportunityCarEntity in opportunityCarEntities)
{
	var theId = opportunityCarEntity.GetTypedColumnValue&lt;Guid&gt;("Id");
}

 

Log in or register to comment
Question

Hello, we have about 55,000 cases and since go-live the "manage by records" default read rights has given the all portal users sys admin object access to read all new cases. 

We would like to remove all portal users read rights to all the current cases. What is the best way to do this? I've tried to use a business process, however it errors out after processing 2000 records. Is there a SQL query that can be used?

Like

3 comments

I was able to solve this with this SQL query in the SQL console marketplace app: delete from syscaseright where sysadminunitid = ‘(id of portal user)'

 

 

Hi Mitch, there was a BPM Online provided add on to handle such mass access control editing .. https://marketplace.bpmonline.com/app/access-rights-setup-wizard-bpmonl… i used this in once implementation, really a handy one..

KrishnaPrasad,

Thank you! sounds very helpful!

Log in or register to comment
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 &gt;= '2016-09-01' AND fd.departure_date &lt;= '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 &gt;= '2017-09-01' AND fd_ly.departure_date &lt;= '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 &gt;= '2016-09-01' AND fd.departure_date &lt;= '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
Question

Hi All!

We need to make the following query in OData using Http request.
 
SQL query:
 
select A.FieldZ, B.FieldX
from A inner join B on A.FieldA = B.FieldA

Basically the idea is to get data between relationships por example in one request to get an account including their contacts

if it is possible?
 
Thank you

Regards
 

Like

1 comments
Log in or register to comment