Creating Custom Quote Numbers in SuiteCRM 7.9.7

SuiteCRM Forum - English Language Advanced OpenSales, Workflow, Portal support
#1

I am trying to create a custom number for my quotes (ID-YYYY-MM-DD-NNN). I tried to replicate the actions in another post(https://www.salesagility.com/forum/8-aos-5x/1254-custom-quote-number), but did not have any luck.

Suggestion from post:
++++++++++++++++++++++++
I had similar need. I need to count quotes per month basis, so I modified line 39 like this:

$this->number = (empty($this->id)) ? date(“Y-m-”) . ($this->db->getOne(“SELECT count(id)+1 FROM aos_quotes WHERE YEAR(date_entered)=YEAR(now()) AND MONTH(date_entered)=MONTH(NOW())”)): $this->number;

Result is YYYY-MM-N, where N is counter for running month.

+++++++++++++++++++++++++++++

I found the script in AOS_Quotes.php below and tried to make adjustments noted in the prior post, but still no joy. Any assistance is greatly appreciated to resolve the issue.

        if($sugar_config['dbconfig']['db_type'] == 'mssql'){
            $this->number = $this->db->getOne("SELECT MAX(CAST(number as INT))+1 FROM aos_quotes");
        } else {
            $this->number = $this->db->getOne("SELECT MAX(CAST(number as UNSIGNED))+1 FROM aos_quotes");
        }
#2

When you post code here, it’s better to put it inside the forums’ “code” tags, otherwise parts of it will be suppressed (like brackets).

Did you change the data type in the vardefs, like they say in the bottom of that post you linked?

What results do you get?

#3

Thank you for the response. I changed data type to vardef and did the repair as indicated in the instructions. After trial and error, I finally got a solution that appears to work. Big discalimer that I am not a programmer and have worked through this by searching posts and looking at PHP stuff online. There is probably a better/logically correct method, but i’ll take the easy win for now. Here is what I did in AOS_Quotes.php for lines 55-60. The generated number is YYMMDDNNN where the NNN resets daily.

            if($sugar_config['dbconfig']['db_type'] == 'mssql'){
                $this->number = $this->db->getOne("SELECT count(id)+1 FROM aos_quotes");
            } 
            else {
                $this->number = date("ymd").(sprintf('%02d',$this->db->getOne("SELECT count(number)+1 FROM aos_quotes WHERE YEAR(date_entered)=YEAR(NOW()) AND MONTH(date_entered)=MONTH(NOW()) AND DAY(date_entered)=DAY(NOW())")));
            }
2 Likes