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!

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");

    

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("ProcessSchemaParameter1", result);

return true;

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

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?

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

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.

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