Subscribe by Email
Siebel CRM How to - Configure a Join.
By amol | December 28, 2007
This post is contributed by Amol Tandon a newbie to Siebel. It is his effort to provide an explanation on joins and step by step procedure on how to configure them in Siebel.
First of all I will try to explain what the join is.
There are two type of commonly used joins
- Implicit Join
- Explicit Join
Implicit Join: - Implicit Join are those which are already define in Siebel and which are internally use by the Siebel to pull the values from the other than base table.
example: - join between base table and extension table like S_OPTY and S_OPTY_X and join between S_PARTY and S_ORG_EXT
Explicit Joints: - Explicit Joints are those which are define by the developer to create the relationship between two tables.
Let’s take a real life example to understand the concept of Joins better.
We have one table of the Employee where Name, Employee Id and other information about the employee is stored.
Another table stores a list of Cities.
Requirement is to know which user stays in which city. There are two ways to do it
- Store city in the Employee table which will result in data redundancy.
- Store Primary key of city table in the employee table and pull the values at the run time, which is more efficient and makes more sense.
To implement second approach we use joins in Siebel.
You have to follow the following five steps to have joined field on UI in Siebel
1. Identify the column to be used as Foreign key.
2. Configure field which will store the primary key of join table.
3. Configure a join.
4. Configure join specification.
5. Add a field which will be used at Applet.
An example will make things more clear.
Suppose we want to have Opportunity Name on the Quote Applet. Here is a step by step procedure with screen shots explaining how to do that.
1. Identification of Column to act as foreign key.
To configure a Join, first of all we have to find out a column in the base table where we can store primary key say (ROW_ID) of opportunity in quote which will become the basis of join.
If there is no column vacant in the base table we can use extension table for that base table e.g S_DOC_QUOTE_X for the S_DOC_QUOTE.
Note: Make sure that the column you choose is not being used by any other BC or any other field of same BC.
2. Configure Field in Business Component
- Go to Business Component > Field
- Create a new record
- Enter the name of the field.
- Enter extension table name in Join field if the column you identified belongs to extension table otherwise go to step 5
- Select the Column identified in column Field.
Note: The column which you have selected must have physical Type varchar.
Our Foreign key is now ready for use. Now we have to configure a joined Field which will use this foreign key
3. Configure/Create a Join
- Go to Business Component > Join
- Create new record.
- Give name of the table with which you want create join ( S_OPTY in our case)
- Give Name in Alias (New in our case).
- Check the Outer join Flag (Very Important).
Go to Object List Explorer (OBLE) on the left hand side and click + sign beside join there will be two options Join Specification and Join Constraints
4. Configure Join Specification
- Create New Record.
- Give Name in the Name field.
- Destination Column is given by default when insert a new record. You can change it if you want to.
- In the Source field give the name of the source field (OptyID) which you have configured earlier to be used as Foreign Key.
5. Configure the field (Opportunity Name) which you want to display on the User Interface.
- Go to Business Component > Field
- Insert New Record
- Give Name in the Name Field
- Enter the Join Alias which you have given for the join you created.
- Select the Name (for Opportunity Name) in the column field. Column name here represent the data that you want to pull from opportunity.
6. Configure the Control in the Applet (UI).
- Go to Applet > Control (if Form Applet)
- Go to Applet > List > List Column (if List Applet)
- Create New Record.
- Enter the Name
- Enter the name you want to display on UI in Caption Override Field
- Enter the Name of the field you created in Field column
Don’t forget to map this field in Applet. Right Click on the corresponding Applet and map it.
Now compile the entire project which you have made the changes and you will be able to see this joined field in the Quote Applet as shown in the picture below.
When you see this field in User Interface it will be read only because when the data is pull from the join table it is only read only, to make it editable you have configure Pick Applet on this field.
Will include the procudure to make it editable in next post.
(2 votes, average: 4.5 out of 5)
Loading ...Related Posts
Categories: Configuration, How - To |
January 9th, 2008 at 7:50 am
Best explantion Ever read!!!
January 15th, 2008 at 9:12 pm
I agree with Soj, Great Explanation, Keep up the good work Amol..
January 16th, 2008 at 8:16 am
We will try our best to provide more stuff
February 7th, 2008 at 1:07 pm
Details about join is very nice.
I could not See details of SVF Pick Map.
Should all the fields in SVF Pick Map should be empty.
February 28th, 2008 at 1:58 am
Good work Amol..
Can you please proveide us the stpes to configure MVG (not with wizard)… that woulk be great help.. mail me: postmeup[at]indiatimes[dot]com
March 6th, 2008 at 1:43 am
Thanx so much for publishing this topic in detail…helped me a lot
March 13th, 2008 at 8:22 am
can you please elaborate on this step.
If there is no column vacant in the base table we can use extension table for that base table e.g S_DOC_QUOTE_X for the S_DOC_QUOTE.
March 14th, 2008 at 2:28 am
Naga,
If suppose u want to insert one more data column in ur S_DOC_Quote Table,& none of its columns are availble to stroe that extra data column,In that case you can use the colum from its extnetion table S_DOC_Quote_X,and via join establish that as a field of basetables BC.Which would do a workaround without changing the data structure of the base table.
March 14th, 2008 at 2:31 am
There is an implicit join between base tabel and extension table. They share the same row id and there is no need to create join.
So, you can actually use _X table and it will be just like you have choosen a column at base table although phsically they are different tables.
March 14th, 2008 at 4:36 am
hey thanks Neel…for makin it more specific:)
April 22nd, 2008 at 6:29 pm
Hi Amol
Can you please provide us the steps to configure MVG (not with wizard)… that woulk be great help.. mail me: mysites_suresh@yahoo.com . Thanks
April 27th, 2008 at 12:47 am
Hi,
Did anyone posted a neat ste-by-step tutorial for the MVG configuration (not with the wizard).
It would be helpful if anyone can help us with this, as MVG configuration is one of the most complex stuff for many beginners.
Thanks,
Venkat.
April 29th, 2008 at 1:23 am
Very Good explanation indeed.
May 27th, 2008 at 7:23 am
thanx a lot….it really helped..
June 3rd, 2008 at 6:28 am
Appreciate ur pain in explaining in detail