A SearchEngine question`!

Hi forum.

I'm having trouble writing a certain CRM-script.

I want all persons who DON'T have an appointment of a certain type in the future.

One solution could be to loop through each person en a SearchEngine, and then check count future appointments for each. But the customer is huge, so this would take too long for the crm-script to run.

Can this be done in a single searchengine?

RE: A SearchEngine question`!

Hi,

Sorry, but this would require a subquery which is not supported. Best solution is normally to load all future appointments of given type and store the customerId's in a map. Then query all customers and in the loop you filter out the ones who have their id in the map. Only requires two queries, but depending on your database size you could run into problems with time or memory.

Alternatively, Saint?

Sverre

Av: Sverre Hjelm 18. jan 2019

RE: A SearchEngine question`!

Hi Sverre. Thank you for the reply.

I've thought about Saint.

But this is just a small part of the final query I need. Another criteria is, that the persons shouldn't be part of an active sale, among several other criterias.

Anyway, I don't need to wrap my head around a solution requiring searcengine anymore - thanks :).

 

Av: Kasper Rosenlund 18. jan 2019

RE: A SearchEngine question`!

Elaborating on what Sverre said you can use the dataset returned by this SearchEngine to fill up your Map, but you probably already knew that :)

SearchEngine se;
se.addField("person.person_id");
se.addCriteria("person.(appointment->person_id).do_by","gte",getCurrentDateTime().toString(),"and",0);
se.addCriteria("person.(appointment->person_id).task_idx","equals","<ID of task>","and",0);
se.setDistinct("person.person_id");
se.execute();
Av: Hans Wilhelmsen 18. jan 2019

RE: A SearchEngine question`!

Hi Hans.

Thank you, this part I already figured out. But nice with other inputs :).

Av: Kasper Rosenlund 18. jan 2019

RE: A SearchEngine question`!

Hi Sverre. Thank you for the reply.

I've thought about Saint.

But this is just a small part of the final query I need. Another criteria is, that the persons shouldn't be part of an active sale, among several other criterias.

Anyway, I don't need to wrap my head around a solution requiring searcengine anymore - thanks :).

 

Av: Kasper Rosenlund 18. jan 2019

RE: A SearchEngine question`!

Hi guys, is this Online or Onsite? Because if it's Onsite then there is the seldomly used StatLib class's .setSql method.

This will get all the person_id's for persons who do not have an appointment of a given type. SQL query could be anything.

#setLanguageLevel 3;

String[] getPersonIds() {
  
  Integer[] result;

  String query = "
  select 
    p.person_id 
  from CRM8.PERSON p
  left join CRM8.APPOINTMENT a ON (a.person_id = p.person_id and a.task_idx = 18 and a.document_id = 0)
  where a.appointment_id IS NULL";

  Parser p;

  StatLib sl;
  sl.setSql(query);
  sl.execute(p);

  String parsValues;
  parsValues= p.parseString("%PARSER_TREE%");
  parsValues = parsValues.stripLeading("ejSStat0: ");
  String[] getAll = parsValues.split("\n");
  String[] personIds = getAll[0].split(",");
  return personIds;
}

String[] personIds = getPersonIds();
for (Integer i = 0; i < personIds.length(); i++)
  printLine("person_id: " + personIds[i]);

Credit goes to Anders and Margrethe who came up with the idea five years ago.

Could that work for you?

Av: Frode Lillerud 18. jan 2019

RE: A SearchEngine question`!

Hi Frode.

Thank you for the response - I'll save this answer for later use :).

Unfortunately the Customer is Online, thus not able to use this class.

Av: Kasper Rosenlund 21. jan 2019

RE: A SearchEngine question`!

Just for the record: We do not recommend using this SQL function. It is not supported in Online, we might end up removing it from OnSite as well. Being able to run queries directly towards the database is not a good idea from a security perspective, and the only reason this function is there is legacy.

The way this should be supported is allowing subqueries (strictly selects) as Where-clauses. Unfortunately, we don't support this, and due to our database-abstraction-layer, I don't think it will be supported any time soon. So my best suggestion is the double-query and Map solution.

Sverre

Av: Sverre Hjelm 21. jan 2019