Integer field has a maximum limit of 10 digits?

[mansi.kakkar]

I created a new integer field and want to store 12 digits in it. Apparently, it gives me an error if I try to add more than 10 digit number. Can we increase it?
Also, I haven't set any limit on the field.

[victor]

In your particular case, it is better to use a different field type (for example, Varchar) or you can try the following:

1. In your_instance_name/custom/Espo/Custom/Resources/metadata/entityDefs/Your_entity.json find your field and under the line

"type": "int",

add

"dbType": "bigint",

You should end up with something similar to:


2. After saving the changes in the file, make Rebuild via CLI: https://docs.espocrm.com/administrat...mands/#rebuild.

After these changes, I was able to put "9999999999999".

[mansi.kakkar]

victor Ok, I think its much easier with a varchar field. How can I ensure that this field only accepts 0-9 digits, nothing else?

[victor]

mansi.kakkar,

In Administration > Entity Manager > Your_entity_name > Formula > Before Save Custom Script put:

cForumVarch = string\match(cForumVarch, '/^[0-9]*$/');

Replace cForumVarch with the name of your Varchar field.

If a value other than 0-9 is put in cForumVarch, it will return null.

[yuri]

If you set min value to 0, it won't accept negative values, meaning only 0-9 digits will be valid.

[mansi.kakkar]

victor Thank you, this formula works. But, if a value other than 0-9 is put in cForumVarch, I would like it to through an error - Invalid. Can we modify this formula?

[shalmaxb]

probably here: https://docs.espocrm.com/administrat...hrowbadrequest

[victor]

victor Thank you, this formula works. But, if a value other than 0-9 is put in cForumVarch, I would like it to through an error - Invalid. Can we modify this formula?

shalmaxb gave a very good idea.

1. Delete the formula suggested above https://forum.espocrm.com/forum/gene...942#post106942 from Administration > Entity Manager > Your_entity_name > Formula > Before Save Custom Script.
2. Add the formula in Administration > Entity Manager > Your_entity_name > Formula > API Before Save Script.

$cForumVarch = string\match(cForumVarch, '/^[0-9]*$/');

if (cForumVarch != $cForumVarch) {
recordService\throwBadRequest('field "Forum varch" accepts ONLY 0-9 digits');
} 

[mansi.kakkar]

victor Thanks for sharing this formula, but its not working. I have tried the exact formula, but even the alphabets are getting saved now.

[shalmaxb]

I think, the last semikolon should not be there.

[victor]

Possible reasons why it doesn't work for you:
- Forum added an extra character at the end of the formula, which you then copied. I already edited it.
- You didn't replace my Varchar field with your own.
- You are confused Administration > Entity Manager > Your_entity_name > Formula > Before Save Custom Script and Administration > Entity Manager > Your_entity_name > Formula > API Before Save Script. For the last option, the formula should be used exactly in API Before Save Script.

[mansi.kakkar]

shalmaxb victor I tried without the semicolon but it didn't work. Also, I just see the Before Save Custom Script in Formula (using version 7.0.9).

[shalmaxb]

API before save formula only works from version 7.5 and greater

[mansi.kakkar]

shalmaxb Ah, ok. Is there any other way out for this problem?

[shalmaxb]

I recommend updating your instance. You will have to update one day, when espoCRM is already in ver 9 or even 10. And THAT will be difficult. From my experience, it is always preferable to update.

[mansi.kakkar]

I will update soon, is there a way until then? There are two options:
- Use an integer field but it should be able to store 12 digits, currently it stores upto 10 digits. Is there a way to fix that?
- Use a varchar field. Can I put some condition to make this field invalid? (It should show invalid if contains alphabets or special characters)

[shalmaxb]

As already recommended in thread #2, change the field to BIGINT or VARCHAR. To limit the digits to 12 and only numbers, I guess regex would help. Here the AI solution (did not test it):

To create a regular expression that limits a number to exactly 12 digits and allows only numeric characters, you can use the following pattern:

^[0-9]{12}$


Here’s what each part of the regex means:

• ^: The match must start at the beginning of the string.
• [0-9]: Any character between ‘0’ and ‘9’.
• {12}: Exactly 12 occurrences of the preceding character class.
• $: The match must end at the end of the string.

This pattern ensures that the input consists of exactly 12 numeric digits. If you need to allow leading zeros, you can modify it accordingly. ?

For example, the regex would match:

• 123456789012
• 000000000001

And it would not match:

• 1234567890123 (13 digits)
• abc123456789 (contains non-numeric characters)
• 12345678a901 (contains non-numeric characters)

Feel free to adjust the regex based on your specific requirements¹². ?