How to use two conditions in duplicate check

[shalmaxb]

Hi, I succeed in enabling duplicate check in any custom entity, if it refers to one condition, for example, I have a number field for every record and that should use the same number only once (in fact it is a textfield, because the number has other characters than numbers, too).

How would the duplicate check class.php look like, if I want two fields in combination checked? Both fields would have to be unique together.
Example:

I have an invoice entity, where every invoive has an unique number.
I have an invoice item entity, where every item has a simple count number (position 1, position 2 and so on).

The entities are related.

I want for every single invoice, that the items could be seperatly - depending on the invoice number - be numbered from 1 to n, but prevent from using unvoluntarily in the same invoice duplicates for the items. Means, that if there already is an item 3, you will be warned, when using 3 again for another item of that invoice. But I want to be able to use 3 in another invoice as item number.

In the result it should look like:

Invoice number IN-004 has items 1,2,3 ....
Invoice number IN-005 has also items 1,2,3 ...

In the moment this does not work, because all items, independent from invoice are in the same entity, so if I have an item number in invoice 004 with 3, in invoice 005 not, the warning will be thrown anyway, when I try to give invoice 005 the number 3.

How would the duplicate check look for both numbers invoice and item together? Is it possible at all?

[rabii]

you want to check the invoice number and also check the number of each item entity related to the invoice ? honestly read this twice but still couldn't figure out what you are trying to achieve.

[shalmaxb]

ok, I will try again.

I have an invoice with items. These items come from a related entity, where all items for all invoices are stored, each one as one record. The item number is not unique by itself, as I will need item number 1 as well for invoice number 004 as for invoice number 005 for example.

I want the items to be numbered for each related invoice from 1 to n, so each invoice has item 1, item 2 etc.

The item number will be put in manually, because there may be cases where you want to delete one item and then there would be a gap in the item number sequence, e.g. suddenly only 1, 2 and 4 are in the invoice, because 3 has been deleted. By numbering manually you can change the numbers for each record (perhaps this is a point, which I could rethink).

When putting in numbers, I want to avoid, that the user puts in a number, that is already present, for that the duplicate check.

The problem now is, that, as all item numbers of all invoices are in one entity, the duplicate check solely for the item number would throw a warning, when this number is used already in another invoice as well as in the current one. So it would not have benefits for the whole problem but cause more mess in the end.

For that I thought to vinculate the invoice number with the items number in the duplicate check, so that a duplicate would only be thrown, when that item number has been used already in that invoice (number), not in others.

In logic expression: if invoice number 001 has item number 1 already related, warning of duplicate, if the user tries to put in number 1 for an item of that invoice again. If there is another invoice number, which does not yet have item number 1 and the user will put in item number 1, don`t throw duplicate warning.

I hope it is understandable now.

[rabii]

I think it is doabled hence you just wanted to make sure that an invoiceItem doesn't have the same number when created directly from the invoice. Try the code below and see if it does help

<?php
namespace Espo\Custom\Classes\DuplicateWhereBuilders;

use Espo\Core\ORM\Entity as CoreEntity;

use Espo\Core\Duplicate\WhereBuilder;

use Espo\ORM\Entity;
use Espo\ORM\Query\Part\Condition as Cond;
use Espo\ORM\Query\Part\Where\OrGroup;
use Espo\ORM\Query\Part\WhereItem;

class InvoiceItem implements WhereBuilder
{
/**
* @param Entity $entity
*/
public function build(Entity $entity): ?WhereItem
{
$orBuilder = OrGroup::createBuilder();

$toCheck = false;

if ($entity->get('invoiceId')) {
$orBuilder->add(
Cond::and(
Cond::equal(
Cond::column('invoiceId'),
$entity->get('invoiceId')
),
Cond::equal(
Cond::column('number'),
$entity->get('number')
)
)
);

$toCheck = true;
}


if (!$toCheck) {
return null;
}

return $orBuilder->build();
}

}​

I guess this code will prevent from creating an invoiceItem with the same invoiceId and same number.

I have not tested this code but i guess it will work.

[shalmaxb]

Thank you, great. That works! It warns whenever for one invoice an item number will be used again. I can delete a former saved position and after that correct the numbers of the kept positions. I will have item count from 1 to n for every single invoice independently.
Well, exactly what I was trying to achieve.

Thanks again!

[rabii]

glad it worked for you.

you are welcome

[shalmaxb]

espcrm
Hi, perhaps you could add this procedure to the thread with the instructions, because it is a working solution for duplicate checking with more than one condition.

[rabii]

the reason why i used php code instead of Before save API script is that i don't know which version shalmaxb uses so if he uses a version which doesn't have the feature of "Before Save API" then it will be difficult to make it work.

[espcrm]

Sound good. Have you start using Before Save yet? Is it powerful enough to do the same thing? shalmaxb is a good boy, he update fast!

[rabii]

Yes i have played around with "API Before Save" it is promising and just allow to write code in one place. it is definitely a powerful feature.

[shalmaxb]

To complete this:

the code provided by rabii is the file InvoceItem.php (in this case, in any other case use the respective entity name), which goes to the folder custom/Espo/Custom/Classes/DuplicateWhereBuilders.

Then you will need another file in custom/Espo/Custom/Resources/metadata/recordDefs/InvoiceItem.json with the following content:

{
"duplicateWhereBuilderClassName": "Espo\\Custom\\Classes\\DuplicateWhereBuilders\\InvoiceItem"
}

and one file InvoiceItem.php in custom/Espo/Custom/Services with the following content

<?php

namespace Espo\Custom\Services;

class PositionenAtelierrechnung extends \Espo\Core\Templates\Services\Base
{
}

[rabii]

also if you wish to enable the duplicate check on update you can do so by adding this to the recordDefs of your entity:

"updateDuplicateCheck": true 

[espcrm]

My second try at Before API, but this will hardcode so I can't forcefully send the email. Finally fulfill a feature I have been asking for, maybe in the future there is a "Confirmation" dialogue option instead?

Maybe someone can fix it to be better cause this formula have many weakness!

if (string\contains(bodyPlain, 'attach')) {
    recordService\throwBadRequest("There is a mention of attachment, did you forgot?");
}​

Try 2

if (string\contains(bodyPlain, 'attach' && !attachmentsNames)) {
recordService\throwBadRequest("There is a mention of attachment, did you forgot?");
//recordService\throwDuplicateConflict()
}​

Was thinking of cheating it with the DuplicateConflict modal but nope. Been it seem to be possible.