how to auto fill Number field in existing records?

[gustavgraz]

Espo Version: 6.0.8
Advanced Pack 2.6.5

Hello!

I have been using the leads entity for a while now and already have a number of records in there.

Working with the entity, I found out, that an auto-incrementing number would help in communication with users. So I tried to add a number field with prefix, next number, path length. Exactly what I was looking for.

But as there already were some 80 records entered in the entity, I am not able to get numbers in the field of already existing records.

I also tried it with using the Auto-increment field. No problem there, it fills the auto-increment field of old records sortet by date.

But I need the number field filled. How to do that?

I tried with "Recalculate Formula". No luck...
I tried with "Mass Update". No luck...

Can you help?

Thank you!

[shalmaxb]

I think you should empty the database table of that field manually first. Then it would start from 0 again.

[gustavgraz]

Hi shalmaxb!

Not sure if I understood you correctly...

Thank you for your advice! I removed this one record with Number "Af-000001".
Then I made mass update of the field. No result.

Is this the way you described?

[shalmaxb]

No, I meant to empty the database table of that field in your MySQL database.

[shalmaxb]

After that Mass Update.

Make a Backup of your Database before.

[esforim]

I heard rumors of mass update to work with this method. But have not test that method yet.

I recommend you use the Export > Excel, Update number > Import method

[gustavgraz]

Hi espcrm!

Thank you for your advice! I am just wondering how to export the records of an entity INCLUDING the stream to each record. Its only 80 records, but some of the records have very long streams.

I have to avoid loosing these streams, as they carry valualbe informations.

Thank you!

[esforim]

You won't be able to export the stream, and why would want to export stream in this scenario? I thought you just wanted to update those Auto-numbering that were missing in older entry?

You just need a few column/field that help with import (e.g. ID, name and the auto-number). Then use Excel to quickly add those auto-number that is missing and re-import it as an Update.

[shalmaxb]

I explored this a bit more. The only workaround without messing the database is using the auto incremental field for the number, another field for your prefix and a third field, where you create an output by concatenating the two former fields by formula.
This works, but has one problem. The auto increment field will give a number only once. If for example a record will be deleted, that number will be lost and not used again. So you would not expect sequential numbers, but nonetheless the numbers will be unique always. Already, when you implement it in the described way, there will be missing numbers, because espoCRM keeps deleted records saved in the database and these records will get a number as well, though you won`t see them.

[yuri]

https://github.com/espocrm/espocrm/issues/232#issuecomment-1298638754