how to check duplicated account

[Ellen]

Dear Tanya,
I have one issue:
I created one workflow to automatic create new opportunity one receiving specified email from customer.
In this case: if the account ( including name + email) is exist I will not create new account otherwise I will create new account.
But I do not know how to check this account ( name + email ) exist or not by writing fuction.Please check and advise
Thanks
Ellen

[roma]

Hi, Ellen
Maybe, this could help you

https://forum.espocrm.com/forum/general/569-duplicate-checking

[tanya]

Hello Ellen,
I think the only way to avoid duplicate creating in workflow is to use an action Run Service Action, where you need to check first, if account with this name and email exists. If not - create an Account

https://www.espocrm.com/features/add-custom-service-methods-for-workflow/

[Ellen]

Thanks roma
Thanks tanya

[Ellen]

Dear tanya ,
Now I want to create one function to get id of account.
Here is my code.in function:

namespace Espo\Core\Formula\Functions\StringGroup;

use \Espo\Core\Exceptions\Error;

class GetidType extends \Espo\Core\Formula\Functions\Base
{
public function process(\StdClass $item)
{
if (!property_exists($item, 'value')) {
return '';
}

if (!is_array($item->value)) {
throw new Error();
}

if (count($item->value) < 1) {
throw new Error();
}

$table = $this->evaluate($item->value[0]);
$name = $this ->evaluate($item->value[1]);
$email = $this ->evaluate($item->value[2]);
$sql = "SELECT distinct id FROM account WHERE name='$name' and emailtmp = '$email'";

return $result = query($sql);


But it showing error.
Please help to check and correct me.
Thanks Tanya.

[tanya]

Hi Ellen,
I also thought about this way,

Error is because of this row return $result = query($sql);
what is 'query' function and what it returns?
and do you really have emailtmp column in account table?
also this sql could be injected...

You need to use ORM, and here you can find, how to include it https://github.com/espocrm/espocrm/b...elatedType.php

https://www.espocrm.com/documentation/development/orm/

[Ellen]

tanya Hi,
It’s Ellen again.
Is there any example function which get data by sql query from database?
Thanks
Ellen

[Ellen]

tanya Hi,
Is there any example function which get data by sql query from database?
Thanks
Ellen

[tanya]

Hi Ellen,
I believe ORM is enough for this. Describe all needed filters and types and I will help you to write the code

[Ellen]

Hi Tanya,
Funtion result return custumer_id
variable input is
cutomer_name:varchar
email:varchar
I’m going query from table account
please help

[tanya]

based on your previous sql
try {
$result = $entityManager->getRepository($entityType)->where(['name' => $name, 'emailtmp' => $email])->findOne();
return (!empty($result)) ? $result->id : null;
} catch (\Throwable $e) {
return null;
}

don't forger to pass $entityType has to be 'Account'

[Ellen]

Thanks Tanya,
I put this function in Stringgroup, isn't it ok?
But it's still error.
Please check my file function and logs.

namespace Espo\Core\Formula\Functions\StringGroup;

use \Espo\Core\Exceptions\Error;


class GetidType extends \Espo\Core\Formula\Functions\Base
{
public function process(\StdClass $item)
{
if (!property_exists($item, 'value')) {
return '';
}

if (!is_array($item->value)) {
throw new Error();
}

if (count($item->value) < 1) {
throw new Error();
}

$table = $this->evaluate($item->value[0]);
$name = $this ->evaluate($item->value[1]);
$email = $this ->evaluate($item->value[2]);
$result = $entityManager->getRepository('Account')->where(['name' => $name, 'emailtmp' => $email])->findOne();
return (!empty($result)) ? $result->id : null;
}
catch (\Throwable $e) {
return null;
}
}

[tanya]

"Class \Espo\Core\Formula\Functions\IfthenelseType was not found."
Ifthenelse - do you have such a function?
IfThenElse -functionexists, but function name is case sensitive

[Ellen]

Dear Tanya,
Another error.
Please help to check below code in this function is correct or not.
And log in attachement;

namespace Espo\Core\Formula\Functions\StringGroup;

use \Espo\ORM\Entity;
use \Espo\Core\Exceptions\Error;


class GetidType extends \Espo\Core\Formula\Functions\Base
{
public function process(\StdClass $item)
{
if (!property_exists($item, 'value')) {
return '';
}

if (!is_array($item->value)) {
throw new Error();
}

if (count($item->value) < 1) {
throw new Error();
}

$table = $this->evaluate($item->value[0]);
$name = $this ->evaluate($item->value[1]);
$email = $this ->evaluate($item->value[2]);
try
{
$result = $entityManager->getRepository('Account')->where(['name' => $name, 'emailtmp' => $email])->findOne();
return (!empty($result)) ? $result->id : null;
}
catch (\Throwable $e) {
return null;
}
}
}

Thanks
Ellen.

[tanya]

I sent you an example...

https://github.com/espocrm/espocrm/blob/master/application/Espo/Core/Formula/Functions/EntityGroup/CountRelatedType.php#L70

in log you can find the reason - $entityManager is undefined
and entity group is better for this function, I think so