Multiple Quote Numbering Formats for Different Brands

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • 5k18a
    Member
    • Jul 2025
    • 31

    #1

    Multiple Quote Numbering Formats for Different Brands

    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
    EspoCRM product developer
    • Mar 2014
    • 9279

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

    Code:
    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.
    If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

    Comment

    • 5k18a
      Member
      • Jul 2025
      • 31

      #3
      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?

      Comment

      • 5k18a
        Member
        • Jul 2025
        • 31

        #4
        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?

        Comment

        • yuri
          EspoCRM product developer
          • Mar 2014
          • 9279

          #5
          > 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.
          If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

          Comment

          • 5k18a
            Member
            • Jul 2025
            • 31

            #6
            Originally posted by yuri
            > [...]you used nonexistent function string\concat.
            Yes true
            cTest = datetime\format(dateInvoiced, null, 'YYYY/MM');

            Comment

            • 5k18a
              Member
              • Jul 2025
              • 31

              #7
              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)?

              Comment

              • yuri
                EspoCRM product developer
                • Mar 2014
                • 9279

                #8
                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.
                If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

                Comment

                • 5k18a
                  Member
                  • Jul 2025
                  • 31

                  #9
                  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

                  Comment

                  • 5k18a
                    Member
                    • Jul 2025
                    • 31

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

                    Comment


                    • yuri
                      yuri commented
                      Editing a comment
                      I believe it's not possible without workflows or without writing a hook. Not sure though.
                  • yuri
                    EspoCRM product developer
                    • Mar 2014
                    • 9279

                    #11
                    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?
                    If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

                    Comment

                    • yuri
                      EspoCRM product developer
                      • Mar 2014
                      • 9279

                      #12
                      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.
                      If you find EspoCRM good, we would greatly appreciate if you could give the project a star on GitHub. We believe our work truly deserves more recognition. Thanks.

                      Comment

                      • item
                        Active Community Member
                        • Mar 2017
                        • 1540

                        #13
                        Hi,

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

                        PHP Code:
                        <?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' => ]);
                                    
                        $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();

                                }
                            }
                        }
                        If you could give the project a star on GitHub. EspoCrm believe our work truly deserves more recognition. Thanks.​

                        Comment

                        • 5k18a
                          Member
                          • Jul 2025
                          • 31

                          #14
                          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:

                          Code:
                          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;

                          Comment

                          • 5k18a
                            Member
                            • Jul 2025
                            • 31

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

                            Code:
                            datetime\year(dateInvoiced)
                            datetime\month(dateInvoiced)
                            instead of:

                            Code:
                            datetime\year(datetime\now())
                            datetime\month(datetime\now())​
                            So the corrected version is:

                            Code:
                            // 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;​

                            Comment

                            Working...