Creating custom view for SugarCRM module

Sugar Developer Blog - SugarCRM

Hello everyone, i guess for most of us is another day at work :)

Today i want to share with you all, how you can create a custom view for any of your modules. You can use this view to integrate some script or anything inside sugar and it will look like it comes from there. So lets get started :)

lets imagine we have created a module semi_Metadata, and we want to add a new view and menu button called administer metadatas inside it.

If you look at the picture you will see that we have a new link in the menu called Administer metadata, and with it we don’t want to show editview,detailview or listview. We want to show our custom view. To create a link we will add this code to the Menu.php inside our semi_Metadatas module.

if(ACLController::checkAccess('semi_Metadatas', 'edit', true)) $module_menu[]=Array(
"index.php?module=semi_Metadatas&action=semi_AdministerMetadata", "Administer metadata",
"Createsemi_Metadatas");

To make this link…

View original post 279 more words

Advertisements

Creating custom view for SugarCRM module

Sugar Developer Blog - SugarCRM

Hello everyone, i guess for most of us is another day at work :)

Today i want to share with you all, how you can create a custom view for any of your modules. You can use this view to integrate some script or anything inside sugar and it will look like it comes from there. So lets get started :)

lets imagine we have created a module semi_Metadata, and we want to add a new view and menu button called administer metadatas inside it.

If you look at the picture you will see that we have a new link in the menu called Administer metadata, and with it we don’t want to show editview,detailview or listview. We want to show our custom view. To create a link we will add this code to the Menu.php inside our semi_Metadatas module.

if(ACLController::checkAccess('semi_Metadatas', 'edit', true)) $module_menu[]=Array(
"index.php?module=semi_Metadatas&action=semi_AdministerMetadata", "Administer metadata",
"Createsemi_Metadatas");

To make this link…

View original post 279 more words

Get Reports To User in Array Format – Sugar CRM

The Following Function will return the Users Reports To one by one
like category and its parent category concept

/*
$userId is the id of user
$teamArray is the result which will return array of reports to hierarchy
*/

function getReportsToUserArray($userId, $teamArray = array()) {
    if (!empty($userId) && $userId != 1) {
        $reportingUser = new User();
        $reportingUser->retrieve($userId);
        if (!empty($reportingUser->reports_to_id) && $reportingUser->reports_to_id != 1) {
            $teamArray[] = User::staticGetPrivateTeamID($reportingUser->reports_to_id);
            $nextuserId = $reportingUser->reports_to_id;            
            return getReportsToUserArray($nextuserId, $teamArray);
        }
        return $teamArray;
    }
}

Primary Keys: IDs versus GUIDs

GUID Pros

  • Unique across every table, every database, every server
  • Allows easy merging of records from different databases
  • Allows easy distribution of databases across multiple servers
  • You can generate IDs anywhere, instead of having to roundtrip to the database
  • Most replication scenarios require GUID columns anyway

GUID Cons

  • It is a whopping 4 times larger than the traditional 4-byte index value; this can have serious performance and storage implications if you’re not careful
  • Cumbersome to debug (where userid='{BAE7DF4-DDF-3RG-5TY3E3RF456AS10}’)
  • The generated GUIDs should be partially sequential for best performance (eg, newsequentialid() on SQL 2005) and to enable use of clustered indexes

What is index terminology or concept and how does index work in MySQL

I am new to MySQL and need to learn more about this database. I want to know about MySQL indexes. Can you explain, what is index terminology or concept in MySQL? Also, why the indexes are needed to speed up select queries?

An INDEX is essentially an ordered (or indexed) subset of table columns, with each row entry pointing to its corresponding table row.

We index the specific columns to keep them in organized versions. An indexed column just provides a sorted data structure so that some searches on that column could be faster. For example, a telephone book. It’s in sorted order by name. So it makes it a lot faster to find someone by name. So a telephone book is a form of an index.

MySQL allows to create indexes to facilitate quick retrieval of desired data. Using indexes, MySQL can jump directly to the wanted record. Without using index(s), MySQL has to read the entire data file to find the required record(s). In short, with INDEX select queries work considerably faster and without INDEX such queries works slow. Take an example, create a table called “buyers”:

CREATE TABLE buyers(buyer_id INT NOT NULL, buyer_name CHAR( 50 ) NOT NULL );

Then insert 1000 buyers with different names into
the table in a completely non-alphabetic order. A small portion of the
data file may be
represented like below:

buyer_id buyer_name
 1        Zeshan
 2        Aqeel
 3        Nick
 [...]    [...]
 998      Lara
 999      Barbara
 1000     Ken

By above there seems no
recognizable order to the “buyer_name” column. However, if we create an
index on the “buyer_name” column, MySQL will
automatically order this column’s data
alphabetically:

buyer_name
Aqeel
Barbara
Ken
Lara
Nick
[…]
Zeshan

In
case of indexed column, for each data entry, MySQL also internally
maintains a
“pointer” to the inserting row in the actual data file. So if I want to
get the buyer_id of Lara like

SELECT buyer_id FROM buyers WHERE buyer_name='Lara';

MySQL can jump directly to the
correct
row in the data file using buyer_name index “pointer”, and return the
value of buyer_id (998). So with the index MySQL only has to look at
one row to get the wanted result. Without an index
on “buyer_name”, MySQL would’ve scanned all 1000 rows in the data file
to find the wanted buyer_name ‘Lara’. In other words, with the index(s)
MySQL has to evaluate very less rows to respond the
query.

In short, indexes are one of the keys to get
speedy responses in large databases. It does not matter how
simple the table is, the scan will never be fast when a table has
500000 rows. If a dynamic site has 500000 rows table, the developer
should really analyze possible indexes and possibly has to rewrite
queries to
optimize the application.

Normalization Basics – 1NF 2NF and 3NF

Description of Normalization

Normalization is the process of organizing data in a database. This includes creating tables and establishing relationships between those tables according to rules designed both to protect the data and to make the database more flexible by eliminating redundancy and inconsistent dependency.

Redundant data wastes disk space and creates maintenance problems. If data that exists in more than one place must be changed, the data must be changed in exactly the same way in all locations. A customer address change is much easier to implement if that data is stored only in the Customers table and nowhere else in the database.

What is an “inconsistent dependency”? While it is intuitive for a user to look in the Customers table for the address of a particular customer, it may not make sense to look there for the salary of the employee who calls on that customer. The employee’s salary is related to, or dependent on, the employee and thus should be moved to the Employees table. Inconsistent dependencies can make data difficult to access because the path to find the data may be missing or broken.

There are a few rules for database normalization. Each rule is called a “normal form.” If the first rule is observed, the database is said to be in “first normal form.” If the first three rules are observed, the database is considered to be in “third normal form.” Although other levels of normalization are possible, third normal form is considered the highest level necessary for most applications.

As with many formal rules and specifications, real world scenarios do not always allow for perfect compliance. In general, normalization requires additional tables and some customers find this cumbersome. If you decide to violate one of the first three rules of normalization, make sure that your application anticipates any problems that could occur, such as redundant data and inconsistent dependencies.

The following descriptions include examples.

First Normal Form

Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.

Do not use multiple fields in a single table to store similar data. For example, to track an inventory item that may come from two possible sources, an inventory record may contain fields for Vendor Code 1 and Vendor Code 2.

What happens when you add a third vendor? Adding a field is not the answer; it requires program and table modifications and does not smoothly accommodate a dynamic number of vendors. Instead, place all vendor information in a separate table called Vendors, then link inventory to vendors with an item number key, or vendors to inventory with a vendor code key.

Second Normal Form

Create separate tables for sets of values that apply to multiple records.
Relate these tables with a foreign key.

Records should not depend on anything other than a table’s primary key (a compound key, if necessary). For example, consider a customer’s address in an accounting system. The address is needed by the Customers table, but also by the Orders, Shipping, Invoices, Accounts Receivable, and Collections tables. Instead of storing the customer’s address as a separate entry in each of these tables, store it in one place, either in the Customers table or in a separate Addresses table.

Third Normal Form

Eliminate fields that do not depend on the key.

Values in a record that are not part of that record’s key do not belong in the table. In general, any time the contents of a group of fields may apply to more than a single record in the table, consider placing those fields in a separate table.

For example, in an Employee Recruitment table, a candidate’s university name and address may be included. But you need a complete list of universities for group mailings. If university information is stored in the Candidates table, there is no way to list universities with no current candidates. Create a separate Universities table and link it to the Candidates table with a university code key.

EXCEPTION: Adhering to the third normal form, while theoretically desirable, is not always practical. If you have a Customers table and you want to eliminate all possible interfield dependencies, you must create separate tables for cities, ZIP codes, sales representatives, customer classes, and any other factor that may be duplicated in multiple records. In theory, normalization is worth pursing. However, many small tables may degrade performance or exceed open file and memory capacities.

It may be more feasible to apply third normal form only to data that changes frequently. If some dependent fields remain, design your application to require the user to verify all related fields when any one is changed.

Normalizing an Example Table

loadTOCNode(2, ‘moreinformation’); These steps demonstrate the process of normalizing a fictitious student table.

1. Unnormalized table:

Student# Advisor Adv-Room Class1 Class2 Class3
1022 Jones 412 101-07 143-01 159-02
4123 Smith 216 201-01 211-02 214-01
2. First Normal Form: No Repeating GroupsTables should have only two dimensions. Since one student has several classes, these classes should be listed in a separate table. Fields Class1, Class2, and Class3 in the above records are indications of design trouble.

Spreadsheets often use the third dimension, but tables should not. Another way to look at this problem is with a one-to-many relationship, do not put the one side and the many side in the same table. Instead, create another table in first normal form by eliminating the repeating group (Class#), as shown below:

Student# Advisor Adv-Room Class#
1022 Jones 412 101-07
1022 Jones 412 143-01
1022 Jones 412 159-02
4123 Smith 216 201-01
4123 Smith 216 211-02
4123 Smith 216 214-01
3. Second Normal Form: Eliminate Redundant DataNote the multiple Class# values for each Student# value in the above table. Class# is not functionally dependent on Student# (primary key), so this relationship is not in second normal form.

The following two tables demonstrate second normal form:

Students:

Student# Advisor Adv-Room
1022 Jones 412
4123 Smith 216

Registration:

Student# Class#
1022 101-07
1022 143-01
1022 159-02
4123 201-01
4123 211-02
4123 214-01
4. In the last example, Adv-Room (the advisor’s office number) is functionally dependent on the Advisor attribute. The solution is to move that attribute from the Students table to the Faculty table, as shown below:Students:

Student# Advisor
1022 Jones
4123 Smith

Faculty:

Name Room Dept
Jones 412 42
Smith 216 42