Need to set up a numbering system to track your records? Define and add a unique identifier field to any SuiteCRM record. Option rich so that you can define the alphanumeric format that you need. Start tracking your Quote, Cases, Accounts and any other module today.
#64 - Database Error
I've installed the add-on, and configure a sequence to populate the "name" filed of my custom module. When I create a new record in this module I receive a database failure and the sequence is not populated into the "name" field.
The SuiteCRM log shows the following;
08/16/16 22:00:50 [2600][1][FATAL] Query Failed:BEGIN::: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near 'BEGIN'. 08/16/16 22:00:50 [2600][1][FATAL] Error receiving the max sequence of dlc_job_records for field name: Query Failed:SELECT MAX( CAST( SUBSTRING( name, 8 + 1 ) AS INT ) ) AS name FROM dlc_job_records LEFT JOIN dlc_job_records_cstm ON dlc_job_records_cstm.id_c = dlc_job_records.id WHERE LEN( name ) - 8 > 0 ORDER BY name ::: [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]The substring function requires 3 argument(s).
thanks Matt
Piero Groppuso
8 years ago
What type of database are you running?
8 years ago
i'm running SuiteCRM v7.5.3 with a SQL backend, not MySQL.
8 years ago
Do you have access to the file system? If yes, I can send you an updated file for check out
8 years ago
yes I have full access to the system, thanks.
8 years ago
matt.forrest@dlc.co.uk does not work. where to send the file?
8 years ago
ah, sorry out spam software will block zip files. can you sent it to my personal email please - matt.forrest@hotmail.co.uk
8 years ago
i've just received your email thank you, but there is no attachment.
8 years ago
sent u 2nd one
8 years ago
got it thanks, just replaced the file so will now test
8 years ago
progress, i can now create a record without a database error, but the number is being replaced with a second date entry. I have prefix, date, suffix then number to get the desired result. i'm only using mm for the date, but this is being entered in the date location and the number location.
8 years ago
can u send me a screenshot of your sequence defintion and the wrong autofill value please?
8 years ago
i've just created a second record and it did increment, but it went to 17. i have the starting number as 6630, but this changed to 16. the second record i then created, the number was 17, rather than 6631
8 years ago
i've just replied to your email with a screenshot.
8 years ago
Do you have already older records defined, which has a name value stored?
8 years ago
ah! i had two deleted records, which did have something in the name field. they were not visible within the crm, but I could see them in the table within sql, so i deleted them through sql, tried again and it's working thank you. i can no start to have a play around. one further question though, is it possible to do mmyy as a date format, it seems to be the only variation missing from the dropdown..
8 years ago
ah ok, expected sth like that. for that reason I offer an initial fill clearing old entries at the wanted field. if you want to add another date format, feel free to do so. you can do so by studio and extend the dropdown, or you touch the definition file. it must align to the PHP date() format standards. I guess, you want "mmYY".