Unique Record Number based on the date and how many records created that day, not working

Your SQL query doesn't look correct. You are talking about records created the same day but in your query you use month() function.

My suggestion is to fully test your SQL query outside of PHPRunner first and once it works and returns correct results you can troubleshoot the rest of the code.

Admin, thank you for the reply. Even if the month was selected, it should still increase the number for the next record, which is not occuring. I get the correct format of what I am looking to get, I just do not get the next record to be a +1.

SC-VIS-20220503001, each record produces the same number instead of SC-VIS-20220503002, etc

Did you make sure that your SQL query returns correct data?

try

mbintex, thank you for the reply. I tried with your code, however, it produces the same number and does not increase the next record number. I had an SQL Database programmer look at the code and says that it should work.

I made 3 new records today and it produced - SC-VIS-20220504001 for all three records. I am not sure why it will not increase for the new record.

Hi,
the first line of your code should be: $sql="select max(substr(VISNo,15,3)) as mx from VISInspection where substr(VISNo,8,2)=month(now()) order by mx desc limit 1"; //for daily basis
and the second last line: $values["VISNo"]=$str.$str2.substr($str3, -3, 3)

I'm not sure if I truely understood whether you wanted the value to referece daily, monthly or yearly basis. But, this should definately give you increased number.

Hello ppradhan,

We would like it to have a unique number for day. However, even it was based on the month would be ok to. We can fine tune after we are able to get it to work. I tried changing the code as you suggested, however, it did not increase as I added records. It just produced the same number for all three records.

I appreciate everyone voicing their thoughts and I'm also marking this as solved as nothing else can be done here. The correct troubleshooting procedure was provided and this is the only way to fix it. Trying different code variantions without understanding of what exactly is happening is pointless and a waste of everyone's time.

This code basically breaks into two different parts, the SQL query itself and the PHP code after that. If the SQL query doesn't return correct results, then troubleshooting PHP code doesn't make any sense. You need to make sure that SQL query returns the correct data first. Once you figure out that, you can proceed with the PHP part.

I have resolved this issue on my end by using similar code with little modification. I think you could try with this code. Use this code in // Before record added event.

$sql="select max(substr(batchno,15)) as mx from batch where substr(batchno,9,2)=day(now()) order by mx"; // This line needs a critical calculation for each digit of whole number. Hope this will help.

$rs=CustomQuery($sql); // you can try with $rs=DB::Query($sql); instead

$data=db_fetch_array($rs);

$str="BT";

$str2=date("Ymd");

$str3=($data["mx"]+1);

$values["batchno"]="$str$str2-".str_pad($str3, 5, 0, STR_PAD_LEFT);