Sort order of column on EntitySchemaQuery

please provide your code

Tyler Rainey,

Hi, here goes, the comments are in portuguese, sorry:

namespace Terrasoft.Configuration 
{
	using System;
	using System.Collections.Generic;
	using System.Collections.ObjectModel;
	using System.Data;
	using Terrasoft;
	using Terrasoft.Common;
	using Terrasoft.Core;
	using Terrasoft.Core.Entities;
	using Terrasoft.Core.DB;
	using Newtonsoft.Json;
	public class imdRCCStatusServiceMacro : IMacrosInvokable
	{
		public UserConnection UserConnection {
			get;
			set;
		}
		//a estrutura geral de como construir EntitySchemaQuery está aqui https://academy.bpmonline.com/documents/technic-sdk/7-14/introduction-10
		public string GetMacrosValue(object arguments) {
			// 1 - IR BUSCAR CONTEXTO a Activity ID,
			// //estou a seguir isto https://community.bpmonline.com/questions/get-object-id, estou dentro da activity, qual a oportunidade que está relacionada com a activity
			var sjson = JsonConvert.SerializeObject(arguments);
			var templ = new {Key = String.Empty, Value = String.Empty};
			var args = JsonConvert.DeserializeAnonymousType(sjson, templ);
			var ActivityId = args.Value;
			// 2 - IR BUSCAR A OpportunityId o Eugene fez uma coisa parecida aqui https://community.bpmonline.com/questions/error-value-id-was-not-found
			// 2.1 - CRIO QUERY NA ACTIVITY
			var ActivityOportunityQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "Activity");
			// 2.2 - ADICIONO TODAS AS COLUNAS AO ESQ
			ActivityOportunityQuery.AddAllSchemaColumns();
			// 2.3 - CRIO FILTRO EM QUE O ID DA ACTIVIDADE DO FILTRO É IGUAL AO ACTIVITYID QUE VEM DO EMAIL ONDE ESTOU A CHAMAR A MACRO
			var ActivityFilter = ActivityOportunityQuery.CreateFilterWithParameters( FilterComparisonType.Equal, "Id", ActivityId);
			ActivityOportunityQuery.Filters.Add(ActivityFilter);
			// 2.4 - VOU BUSCAR TODOS OS DADOS DO FILTRO QUE SERÁ A INSTANCIA DA ACTIVIDADE
			var ActivityOportunityEntities = ActivityOportunityQuery.GetEntityCollection(UserConnection);
			// 2.5 - INICIO A VARIAVEL ONDE VOU GUARDAR O OpportunityId E ATRIBUO-LHE O VALOR QUE VEM DO QUERY
			var OppId = "";
			foreach (var ActivityOportunityEntity in ActivityOportunityEntities){
				OppId = ActivityOportunityEntity.GetColumnValue("OpportunityId").ToString();
			};
			// 3 CONTRUIR EntitySchemaQuery -  qual o objecto e colunas que queremos ir buscar
			var StatusServiceQuery = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "imdStatusService");
			// 4 - ADICIONA COLUNAS - se fôr para adicionar todas posso fazer simplesment: StatusServiceQuery.AddAllSchemaColumns();
			var col1 = StatusServiceQuery.AddColumn("imdMatricula");
			var col2 = StatusServiceQuery.AddColumn("imdDataStatusService");
			var col3 = StatusServiceQuery.AddColumn("imdLocalizacao");
			var col4 = StatusServiceQuery.AddColumn("imdkmFalta");
			var col5 = StatusServiceQuery.AddColumn("imdMoradaDestino");
			var col6 = StatusServiceQuery.AddColumn("imdETA");
			var col7 = StatusServiceQuery.AddColumn("imdNotas");
			// 5 - CRIAR FILTROS  - cria os r filtros para construirmos a tabela. O filtro tem de ser uma das coluna da tabela para se conseguir ligar
			var OpportunityFilter = StatusServiceQuery.CreateFilterWithParameters( FilterComparisonType.Equal, "imdOportunidade", OppId);
			// 6 -  APLICAR FILTROS faz as operações com os filtros
			StatusServiceQuery.Filters.Add(OpportunityFilter);
			// 7 - FAZ O SORT DAS COLUNAS
			col1.orderDirection = Terrasoft.OrderDirection.DESC;
			col2.orderDirection = Terrasoft.OrderDirection.DESC;
			// 8 - VAI BUSCAR DADOS FILTRADOS Vai buscar toda a informação filtrada
			EntityCollection entities = StatusServiceQuery.GetEntityCollection(UserConnection);
			// 9 - CONSTROI CABEÇALHOS DA TABELA
			var html = "<table><tr><td>1 - License Plate </td><td>2 - Status Service date (PT time) </td><td>3 - Location </td><td>4 - Km's to delivery point </td><td> 5 - Delivery point </td><td>6 - ETA (local Time) </td><td>7 - Comments </td></tr>";
			// 10 - CONSTROI CONTEUDO DA TABELA NA VARIÁVEL HTML ferramenta para gerar tabela: https://www.tablesgenerator.com/html_tables
			foreach (Entity entity in entities) {
				html += "<tr>";
				html += "<td>" + entity.GetColumnValue(col1.Name).ToString() + "</td>";
				html += "<td>" + entity.GetColumnValue(col2.Name).ToString() + "</td>";
				html += "<td>" + entity.GetColumnValue(col3.Name).ToString() + "</td>";
				html += "<td>" + entity.GetColumnValue(col4.Name).ToString() + "</td>";
				html += "<td>" + entity.GetColumnValue(col5.Name).ToString() + "</td>";
				html += "<td>" + entity.GetColumnValue(col6.Name).ToString() + "</td>";
				html += "<td>" + entity.GetColumnValue(col7.Name).ToString() + "</td>";
				html += "</tr>";
			}
			html += "</table>";
			//  11 - DEVOLVE A TABELA
			return html;
		}
	}
}

Dear Luis,

The article that was mentioned above is related to client esq. The issue happens because you try to use the client esq code inside the server esq code.

 

Please find an example about how to sort in both server and client esq in the article by the link below:

https://community.bpmonline.com/articles/how-sort-records-date-modifica…

Additionally, please find more information in the article by the link below:

https://academy.bpmonline.com/api/netcoreapi/7.13.0/index.html#Terrasof…

Best regards,

Norton

Norton Lingard,

Hi Norton,

thanks a lot, it was a stupid mistake. I tried OrderByAsc and it is working to sort one column.

I realized I do need help with one other point:

the output of the sorted table should sort by 2 columns, 1st by "License Plate" and then by "status service date".

Is there a way to apply an equivalent to the ThenBy class from c#?

something like "StatusServiceQuery.OrderBy(c => c.LastName).ThenBy(c => c.FirstName)

like described here: https://stackoverflow.com/questions/2779375/order-a-list-c-by-many-fiel…

Thanks in advance,

Luis

Hi Norton/Tyler,

I ran some tests and if I apply the sort OrderByAsc 2 times for the diferent columns it has the ThenBy behaviour, so please forget my last comment and thaks for all the help!

Luis

col1.OrderByAsc(0);
col2.OrderByAsc(0);