Multiple Quote Numbering Formats for Different Brands

[5k18a]

Is there a simple way to set up multiple quote numbering formats in EspoCRM?

I’m working with several brands and would like to generate quote numbers in the following format:

QT/{year}/{brandCode}/{month}/{auto-incremented}

Where:

• year is the 4-digit year (e.g. 2025),
• brandCode is a 3-letter code representing the brand the quote is created under,
• month is the 2-digit month (e.g. 08),
• sequence is an automatically generated incremental number.

The sequence doesn’t have to be brand-specific it can just be a globally unique incremental number.
The key point is that the format should include the brand code depending on the brand used when creating the quote.

Is there a way to achieve this using EspoCRM’s formula or extension system?

[yuri]

If you have the Workflows tool, you can utilize formula like:

number = string\concatenate(
    'QT/',
    datetime\year(datetime\now()),
    '/',
    numberA
);

The `numberA` is the built-in field of the number type. You can configure it so that it does not include the default prefix.

[5k18a]

Ok but I guess having the Workflows only for documents numbering is not the best idea. There is actually one more thing which is the final numbering (auto-incremented) should reset on every new month .../01/01 ...01/02 ....02/01/ and so on. Is it possible to achieve it with PHP hooks?

[5k18a]

I have an additional question related to invoice numbering by date.
I tried to use the dateInvoiced field in formulas, in order to extract month/year and save it into another helper field. Unfortunately, it looks like I can’t retrieve any value from this field – it always returns empty.

For testing I tried for example:
cTest = string\concat('', dateInvoiced);


and also a more complete version (formatting as yyyy/MM):
if (dateInvoiced != null) {
year = string\substring(dateInvoiced, 0, 4);
month = string\substring(dateInvoiced, 5, 2);
cTest = string\concat(year, '/', month);
} else {
cTest = '';
}


The issue is that cTest never gets populated – it looks like dateInvoiced is not accessible in the formula context.

Does this field need to be referenced differently (e.g. via the Invoice entity), or does it have a type that cannot be directly used in formulas?

[yuri]

> it looks like dateInvoiced is not accessible in the formula context.

It's accessible.

You can use the formula sandbox to craft and debug scripts. Or check logs. In your case, you used nonexistent function string\concat.

[5k18a]

> [...]you used nonexistent function string\concat.

Yes true
cTest = datetime\format(dateInvoiced, null, 'YYYY/MM');

[5k18a]

Is there any way to automatically propagate a dynamic prefix like YYYY/MM/auto-increment to the document name?

As I understand, the "Prefix" setting in numberA (auto-increment) field only accepts a static value. Currently, the document name is generated from numberA + number.
Is there a recommended way to achieve this automatically (e.g. adding year/month from dateInvoiced + the auto-increment number)?

[yuri]

I'm not sure whether I understood the problem to the full extent.

You can have YYYY/MM/auto-increment the way I have wrote in my first reply.

In the auto-increment field parameters, remove the prefix completely. There will be only string number values in the numberA field. The formula will be used to concatenate the date with the numberA value. The value will be written to the number and/or the name field.

The limitation: auto-increment number won't be reset each month automatically. It can be fine for some, it maybe not desirable for others.

[5k18a]

Thanks, Yuri. When you mentioned the Workflows, I thought it wouldn't work without the Advanced Pack, but that was my mistake. The formula works great. I just adjusted it to display the month too:

number = string\concatenate(
'QT/',
datetime\year(datetime\now()),
'/',
ifThenElse(
datetime\month(datetime\now()) < 10,
string\concatenate('0', datetime\month(datetime\now())),
datetime\month(datetime\now())
),
'/',
numberA
);

I'm not a programmer and I'm reinventing the wheel once again

[5k18a]

And one more thing - is there a way to have the same name as number? Because the name field contains only incremented value.

[yuri]

I believe it's not possible without workflows or without writing a hook. Not sure though.

[yuri]

I'm not sure where it will work for new records without workflows. Is the numberA value available in the formula before-save script when a new record is being created?

[yuri]

The next number for a specific number field is stored in the NextNumber entity (next_number table). You can create a scheduled job that will update a specific number field in the beginning of each month.

[item]

Hi,

here a scheduled job .. .you can see how i do (maybe add if ($nextNumber) {...} for security

<?php
namespace Espo\Custom\Jobs;

use Espo\Core\Job\JobDataLess;
use Espo\Core\ORM\EntityManager;
use Espo\ORM\Entity;
use Espo\Core\Utils\Log;
use Espo\Core\Utils\Json;

use Espo\Core\Utils\Metadata;

class Month implements JobDataLess
{
    private string $year;
    private string $month;
    private string $administratifTeamId;
    private array $entityList;

    public function __construct(
        private EntityManager $em,
        private Log $log,
        private Metadata $metadata,

    ){
        $this->year = date('y');
        $this->month = date('m');
        $this->entityList = ['Box', 'Transmission'];
    }

    public function run() : void
    {
        foreach($this->entityList as $entityName)
        {
            $nextNumber = $this->em->getRDBRepository('NextNumber')
                ->where([
                    'entityType' => $entityName,
                    'fieldName' => 'number'    // <-- must your increment field
                ])
                ->findOne();

            $nextNumber->set([ 'value' => 1 ]);
            $this->em->saveEntity($nextNumber);

        }

        $this->setPrefix();
 
    }

    private function setPrefix() : void
    {  

        foreach($this->entityList as $entityName)
        {
            $this->metadata->set('entityDefs', $entityName, [
                'fields' => [
                     'number' => [   // <-- must your increment field
                          'prefix' => $this->year .$this->month,
                     ]
                ]
            ]);
            $this->metadata->save();

        }
    }
}

[5k18a]

I've posted it in the wrong thread so i copied it and pasted here.
OK, the formula is done and everything is working as it should, but without the auto-reset incrementing the value after a new month. Maybe this can be achieved with a PHP hook. I have added a field for selecting a sub-prefix and a field for extracting the currency (in the correct format for my organisation). The formula is copied from number to name:

number = string\concatenate(
    ifThenElse(
        cMarka == 'aaaaa', 'AAA',
        ifThenElse(
            cMarka == 'bbbb', 'BBB',
            ifThenElse(
                cMarka == 'ccccc', 'CCC',
                ifThenElse(
                    cMarka == 'dddddd', 'DDD',
                    'UNK'
                )
            )
        )
    ),
    '/',
    amountCurrency,
    '/',
    datetime\year(datetime\now()),
    '/',
    ifThenElse(
        datetime\month(datetime\now()) < 10,
        string\concatenate('0', datetime\month(datetime\now())),
        datetime\month(datetime\now())
    ),
    '/',
    numberA
);

name = number;

[5k18a]

I notice that this formula needs some small tweak because it is better to use

datetime\year(dateInvoiced)
datetime\month(dateInvoiced)

instead of:

datetime\year(datetime\now())
datetime\month(datetime\now())​

So the corrected version is:

// Generowanie numeru z autoinkrementacją z krótkimi prefiksami i walutą
number = string\concatenate(
    ifThenElse(
        cMarka == 'aaaaa', 'AAA',
        ifThenElse(
            cMarka == 'bbbbb', 'BBB',
            ifThenElse(
                cMarka == 'ccccc', 'CCC',
                ifThenElse(
                    cMarka == 'ddddd', 'DDD',
                    'UNK'
                )
            )
        )
    ),
    '/',
    amountCurrency,
    '/',
    datetime\year(dateInvoiced),
    '/',
    ifThenElse(
        datetime\month(dateInvoiced) < 10,
        string\concatenate('0', datetime\month(dateInvoiced)),
        datetime\month(dateInvoiced)
    ),
    '/',
    numberA
);
// Ustawienie tego samego dla pola "name"
name = number;​