Integrating a unique field with Sugar

[tomkerswill]

Hi,

This has been touched on before in the forums but I could do with a little more info if anyone is able to help...

Our company uses Sage, which assigns a unique ID to each customer account. I'd like to add this to the list of fields in the Accounts module.

I've done this using the custom fields panel, editing the necessary forms in the studio to display the new customer_id field.

However, I need to make sure the field is unique. The only way I could find to do this was to use phpmyadmin and assign a unique index to the new customer_id field.

This is fine, but when a user *does* type a non-unique ID, Sugar gives no error, but the customer ID is not recorded. Is there any facility for handling unique fields in Sugar itself?

Thanks in advance

Tom

[sadek]

Hi,

This has been touched on before in the forums but I could do with a little more info if anyone is able to help...

Our company uses Sage, which assigns a unique ID to each customer account. I'd like to add this to the list of fields in the Accounts module.

I've done this using the custom fields panel, editing the necessary forms in the studio to display the new customer_id field.

However, I need to make sure the field is unique. The only way I could find to do this was to use phpmyadmin and assign a unique index to the new customer_id field.

This is fine, but when a user *does* type a non-unique ID, Sugar gives no error, but the customer ID is not recorded. Is there any facility for handling unique fields in Sugar itself?

Thanks in advance

Tom

It sounds like what you are looking for is an auto_increment column. You can create a custom field in sugar, and change it to an auto_increment in the database (accounts_cstm table) via your mysql interface of choice. Then, you can remove the field from the EditView, leave it in the DetailView, and every time you create an account it will automatically create that column value, which is guaranteed to be unique.

I have not attempted to do what I described above, but in theory there should be no problems. I suggest testing this on a dev instance.

Let us know if you decide to do that, and how it works out for you.

-Sadek

[tomkerswill]

Hi Sadek,

Thanks very much for this. That makes sense and sounds like it would be a good solution for most people... alas, in my case, Sage assigns alphanumeric IDs, so the field can't be auto-increment in this case...

I guess what I need is to write a tiny php script that would generate a unique account ID when a new account is created - and some kind of post-submit hook that triggers it. Do such hooks exist in SugarCRM?

Tom

[sadek]

Hi Sadek,

Thanks very much for this. That makes sense and sounds like it would be a good solution for most people... alas, in my case, Sage assigns alphanumeric IDs, so the field can't be auto-increment in this case...

I guess what I need is to write a tiny php script that would generate a unique account ID when a new account is created - and some kind of post-submit hook that triggers it. Do such hooks exist in SugarCRM?

Tom

I don't think we offer any post-submit hooks for this, but don't quote me on that.
What I would do is:

1) create a new file (like "additionalUtils.php")
2) write a function to generate a unique id
3) include the file in the Accounts EditView.php, and then generate and assign the id when any new account is created.

Doing so means you will have to merge that EditView.php during upgrades, but doesn't seem like too high a price to pay

-Sadek

[tomkerswill]

Ah excellent - that sounds good. I'll get to work on that and let you know how I get on!

Thanks very much for the help!

Tom

[sadek]

That's what we're here for. Let us know how that works out for you

-Sadek

[vat]

It sounds like what you are looking for is an auto_increment column. You can create a custom field in sugar, and change it to an auto_increment in the database (accounts_cstm table) via your mysql interface of choice. Then, you can remove the field from the EditView, leave it in the DetailView, and every time you create an account it will automatically create that column value, which is guaranteed to be unique.

I have not attempted to do what I described above, but in theory there should be no problems. I suggest testing this on a dev instance.

Let us know if you decide to do that, and how it works out for you.

-Sadek

I have implemented the suggestion above but when I convert a lead into an account the id_c column is not populated so the unique identifier is not generated. Do I need to add code to insert the account id in the account_cstm table?

Thanks Victor

[vladimirn]

That's what we're here for. Let us know how that works out for you

-Sadek

Good thing is that they answered on previous post... not as on many of questions on this forum...
Unfortunately i am stuck with this application...