Ability to replace the system generated Id field values with own values

[murugappan]

Hi,

Is there a possibility for us to replace the system generated Id field values with own values? Our scenario is this:

(1) We need the ID field value to be that of out company's account number which will be entered by the staff when the account is created
(2) We could insert a formula to replace the ID field value with the entered account number.

The reason for doing this that when we create new records in another entity which is related to the Account entity we could do that with the account number instead of an arbitrary generated ID. Example: Subscriptions related to an Account. This way when we migrate to another instance we could just export and import the linked entities.

[yuri]

Hi,

I would not recommend to change ID generation. I'm not sure whether there's a safe way. You could try using before-save formula by setting id = $someString.

Maybe this improvement from the next release will be helpful for you: https://github.com/espocrm/espocrm/issues/2956

[murugappan]

Hi yuri

Thank you for the update and advise. The improvement is a much needed one. The concerns i have with the improvement are these:

(1) When we export a Related entity, the linked field is an Id of the Parent entity. The id is not a general user readable value.
(2) This id value will change when we import the Parent entity.
(3) When we import this Related entity using CSV, we would have a problem with setting the Parents Id.

As a resolution and work-around, this is what i am planning to do and need your advise on this:

My Plan

(1) A field labelled "System Id" is created in each entity with new option "Read-only After Create".
(2) In the before-save formula, we initialize this field with id of the record created.
(3) Another field, called "Account Number" is created and initialized by user input.
(4) When import CSV file for the related entity will contain the "Account Number" value
(5) When the related entity is imported, a formula will retrieve the Parent entity "System ID" field using the Account Number field and insert into the Related Entity link field.

This way the Account Number field in the Parent can change but limked record will remain the same. I am not sure steps 1 and 2 would be necessary at this moment.

Please advise, thank you.

[yuri]

I need to understand the model first.

1. Account entities on both systems.
2. Subscription (or Related) entities on both systems. There are related to Account as many-to-one.
3. Account IDs (system IDs) are different in both systems.

Question. What is the Parent entity? Is it Account?

With the new functionality coming in Espo.

1. You create a Number field in Account.
2. You create an Account Number foreign field in Subscription, mark it 'Relate on Import'.
3. When exporting and importing Subscriptions, you just omit accountId column (a link field created by Espo). Instead you export Account Number.
4. When importing, the system will relate imported Subscriptions with matching by the Account Number Accounts.

[murugappan]

Hi @yuri

Sorry for the delay. I am in process of design. I will provide much more details later.

[murugappan]

Hi yuri

I just analyzed the new functionality. Fantastic, that is exactly what we need.

We are has also included additional feature into our design (not related to import/export). In the Subscription entity we also store the linked-Account ID and Name values in a special fields called "Account DBKey" and "Account Name". For display purposes we show the "Account Name" field. For back-end processing, the back-end app will use the Account ID field in the accounts to get all the records the Subscription table using "Account DBKey" field. This will reduce the number DB accesses significantly especially for Lists layout and Bottom Panel listing.

Any advise?

[murugappan]

yuri

I am re-engineering our CRM implementation. White importing the an entity i encountered a problem in importing the enum fields. The export function exports the "value" of "key-value" pair but when importing, it imports the "value" into "key" part of the "key-value" part in the target instance. Am i doing something wrong or does this need to be fixed in the next release?

[yuri]

When you export CSV, it exports a real value. When export XLSX, it exports a translated value.

[murugappan]

Thank you yuri . I tried it and it worked.