1

How do I add fields to the opportunities table so that they propagate?

From within Studio > Opportunities > Fields I added fields. Pardon, I don't understand the next step to effect a change on the database structure and GUI for creating and viewing opportunities.

The Fields that are available for the module are listed here by Field Name.

To configure the properties for a field, click the Field Name.

To create a new field, click Add Field. The label along with the other properties of the new field can be edited after creation by clicking the Field Name.

After the module is deployed, the new fields created in Module Builder are regarded as standard fields in the deployed module in Studio.

Yet, there's no change to the underlying database:

mysql> 
mysql> describe sugarcrm.opportunities;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| id               | char(36)     | NO   | PRI | NULL    |       |
| name             | varchar(50)  | YES  | MUL | NULL    |       |
| date_entered     | datetime     | YES  |     | NULL    |       |
| date_modified    | datetime     | YES  |     | NULL    |       |
| modified_user_id | char(36)     | YES  |     | NULL    |       |
| created_by       | char(36)     | YES  |     | NULL    |       |
| description      | text         | YES  |     | NULL    |       |
| deleted          | tinyint(1)   | YES  |     | 0       |       |
| assigned_user_id | char(36)     | YES  | MUL | NULL    |       |
| opportunity_type | varchar(255) | YES  |     | NULL    |       |
| campaign_id      | char(36)     | YES  |     | NULL    |       |
| lead_source      | varchar(50)  | YES  |     | NULL    |       |
| amount           | double       | YES  |     | NULL    |       |
| amount_usdollar  | double       | YES  |     | NULL    |       |
| currency_id      | char(36)     | YES  |     | NULL    |       |
| date_closed      | date         | YES  |     | NULL    |       |
| next_step        | varchar(100) | YES  |     | NULL    |       |
| sales_stage      | varchar(255) | YES  |     | NULL    |       |
| probability      | double       | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
19 rows in set (0.00 sec)

mysql> 

Nor do the additional fields appear in the GUI when at "create opportunity".

What do I need to do to cause the fields I created in the Studio to actually get created, not just in the database, but in the interface for SugarCRM?

Surely I don't have to delve into the PHP?

  • Www.lornajane.net%2Fposts%2F2008%2Fsugarcrm-custom-fields&tabId=23. But I don't see the buttons. I use the community edition. – Thufir Jun 21 '14 at 20:43
2

If I am understanding you correctly you are asking how you can change the Sugar CRM interface to have custom fields.

As I am learning the interface myself I have finished adding a new field on the Cases pages for a JIRA ticket number and it shows up on the database as well. I will attempt to add the field you are talking about to the communication section and then walk you through each part as it is used.

If you are not technical this is one of those times you may want to take your technical buddy out to lunch and then have them help you do it. It requires a functional understanding of how databases work and user interfaces.

I am using a utility vm to run SugarCRM CE 6.5.17 with a php admin database client. I suggest that you find a good SQL client that allows you to see the underlying database and run searches against it.

Index

  1. Login
  2. Add the field to the available components of sugarCRM
  3. Change the web form to include a new field
  4. Create your new data record in SugarCRM
  5. Find our data!
  6. How to find new data anywhere, anytime.

Steps

  1. Login as admin to the SygarCRM CE system.

  2. Add the field to the available components of sugarCRM

    • Admin -> Studio -> Opportunities -> Fields -> Add Field
    • Field Name: New_Field
    • Max Size: 30
      • (leave the rest as some fields will automatically fill in)
    • Click Save
  3. Change the web form to include a new field

    • Admin -> Studio -> Opportunities -> Layouts -> EditView
    • Drag “New Row” box from Tools over the the Layout section under Description
    • Locate the field we just created. It should be called new field.
    • Drag the field from the Toolbox over to the Layout section where the first box called (filler) exists.
    • You should now observe a change to the (filler) labeled box as it should be called “new field” and have a + or - symbol icon to the left. This adjusts the number of columns on the right of the field. (play with the icon to understand)
    • Click Save & Deploy
  4. Create your new opportunity

    • Sales -> Opportunities -> Create Opportunity
    • NOTICE: The “new field” should be at the bottom of the first section after Description. (or where you placed it)
    • Create a new opportunity buy filling in the required fields
    • When you need to fill in the “new field” we will use a unique name to allow a database search
      • Type in the unique name: AAA_XX_193rnafl93ra
        • (if this is not unique enough create your own unique name)
    • Click Save
  5. Find our data!

    • Login to the sugarcrm database as admin (I get to use phpMyAdmin because I am on a developer vm)
    • We are looking for a table called fields_meta_data
    • Query this table and you will notice that the new field you just added exists here with its descriptive data. Most likely called LBL_NEW_FIELD. It has a date stamp so you can also search the table by date_modified and filter for today.
    • Most likely the table will also have an id column called Opportunitiesnew_field_c
    • This is the META DATA that gets created in step 2

    • We now look for our data that was stored in the new field.

    • Tables with the suffix "_cstm", as in opportunities_ctsm, will most likely hold the custom fields.
    • Query the opportunities_ctsm table and you should notice that our field name exists here
    • I see a new_field_c in my opportunities_ctsm table and it holds the value: AAA_XX_193rnafl93ra
    • Take note of the id_c column as this becomes a point where we can join back to the opportunities table on the id field.
  6. How to find new data anywhere, anytime.

    • So why did we use “AAA_XX_193rnafl93ra” as our unique string?
      • We can search throughout the entire sugarcrm database looking through every field for this value to locate which table it was stored in.
    • In phpMyAdmin this is as simple as clicking on the database name in the breadcrumb trail at the top of the web page.
      • localhost -> Sugarcrm (db)
      • Click on the search tab above the list of database tables
      • Type in the following search string: AAA_XX_193rnafl93ra
      • Select all of the tables
      • Click Go
      • Use [ctrl] F to search on the webpage for “Browse” as that will be displayed next to the table where the item was found
      • Enter the table and you should find where your new field was placed.
    • If you do not have phpMyAdmin, find out how to do a total database search for content, or ask your DBA to turn on logging for SQL sent to the database and search for your unique name. Oracle has logging capability that can do this.
    • In short we have created a tracking device that allows you to quickly navigate your database to locate what changed.
    • Creating a developer environment will vastly improve search times as the database will be mostly empty.

You should now have a new datapoint in the database and new field on your sugarCRM form.

It would appear that the guys who built the sugar system did a good job as they have kept the base tables separate from the new fields. This will allow less issues when you upgrade to a newer version. As the base table could be completely restructured and as long as it still included the “id” column it could connect with your custom fields.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.