(33 votes, average: 4.73 out of 5)
Loading ...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.
Authors bio is coming up shortly.
Loading ...
Best explantion Ever read!!!
I agree with Soj, Great Explanation, Keep up the good work Amol..
Thanks soj and Abhi for the Appreciation.
We will try our best to provide more stuff
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.
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
Thanx so much for publishing this topic in detail…helped me a lot
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.
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.
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.
hey thanks Neel…for makin it more specific:)
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
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.
Very Good explanation indeed.
thanx a lot….it really helped..
Appreciate ur pain in explaining in detail
wonderful explanation… tooo good
Using this Configuring Joins Concept, I learned how to configure the joins. It is very helpful, and the concept is very clear.
I want to know the procedure to make the joined field editable.
Pls help.
Hi Soj, I’m trying learn siebel .I didnt get you what you are trying to say in the Context of Vacant columns.Is it is not possible to add a new column in the base table directly? and the other thing is how do i know that there are coulmns vacant in the base table? Can u give me a example?
Hi soj,
As siebel recommend that you must use exiting column which are not used. It’s logical also. But it’s difficult to find.
If you are expert in SQL you can make a sql query to find which all column are not used.
I shall try to write a post on this.
Answer to your next question yes it is possible to extend column in base table. But better approach is to extend column in extension table for example s_opty has extension table s_opty_x all the _x are extension table.
Hi
The information about the join is very good.it helped me alot.
simply super
realy i understand very well
thank you so much.
Hi Amol,
Thats really a beautiful explaination for joins you posted.
It helped me to understand better than before.
Thank you Buddy
I am so glad i joined !!!!!
Hi Kumar, Venkat and JK,
Information for you guys on creation of MVG.
Goal: To configure primary records, multi-value fields (MVF), multi-value links (MVL) and multi-value Group (MVG) applets.
Scenario: An Account entity can have many industries associated to it. One of them would be its primary industry. This is an out of the box configuration. Therefore it would be an easy walk through for the readers.
Pre-requisites:
- An intersection table to store the related records from both the BCs (in this case S_ORG_INDUST). This is explained at the end of Step 1.
- A link between the two BCs
Step 1: Creating a Link
For a BC to display data as an MVG from another BC, one of the pre-requisites is to establish a link between the two BCs concerned. In this case, a link should be created between Account and Industry BCs. Here is the procedure to accomplish that (in case a link is there already, use it. You cannot create duplicate links between the same BCs):
- Lock a project to create the link
- Select “Link” object in the object explorer in Siebel Tools
- Create a new record and supply the following values in the various properties for that record
o Project – Select the locked project
o Parent Business Component: Account
o Child Business Component: Industry
o Inter Table: S_ORG_INDUST
o Inter Parent Column: OU_ID
o Inter Child Column: INDUST_ID
o Primary Id Field: Primary Industry Id
o Comments: As appropriate
The name of the link is automatically populated as Account/Industry.
The Parent BC should be the BC where you want to display the MVG. The Child BC should be the one whose data has to be displayed through the MVG. Inter table is where the relationship between the records are stored. Inter Parent Column stores the Account Id (field from Account BC). Inter Child Column stores the Industry Id (field from Industry BC). Primary Id Field is a field in the Parent BC (in this case, Account BC) that would store the row id of the primary industry record selected from the MVG.
For example, Account One has two industries IND1 and IND2. Account Two has two industries IND1 and IND3. Account Three has two industries IND2 and IND3. Let me represent these data in a tabular format.
S_ORG_EXT
ROW_ID NAME OU_ID
A1 Account One Acc1
A2 Account Two Acc2
A3 Account Three Acc3
S_INDUST
ROW_ID NAME DESC_TEXT
IND1 Export Industry One
IND2 ChemicalIndustry Two
IND3 Glass Industry Three
S_ORG_INDUST
ROW_ID OU_ID INDUST_ID
ON1 Acc1 IND1
ON2 Acc1 IND2
ON3 Acc2 IND1
ON4 Acc2 IND3
ON5 Acc3 IND2
ON6 Acc3 IND3
This explains why we need an intersection table to create an MVG. This table has to relate the two entities (Account and Industry) by way of their respective ids. That is why we have a column to store the Id of the Parent field and one to store the Id of the Child field. The records are populated into this table through the MVG.
Step 2: Create an MVL (to be created in the parent BC)
- Go to the Parent BC (i.e. Account).
- Click on the child object “Multi Value Link”
- Create a new record by specifying the following values:
o Name: Industry (It is a user defined name. You can specify any valid reasonable name. Let us called the MVL name as Industry for our example)
o Destination Business Component: Industry
o Destination Link: Account/Industry (the one you created in step1)
o Primary Id Field: Primary Industry Id (the field in Account BC which will store the row id of the industry record selected through the MVG. Without this field, you cannot assign a primary record)
Step 3: Create an MVF (to be created in the parent BC)
- Go to the Parent BC (i.e. Account)
- Click on the child object “Field”
- Create a new record by specifying the following values for every field that you want to be displayed in the MVG or stored in the BC for some other purpose. In this case, let us say that we need to display Industry Id and Industry Name. Then just create two new records with the values as mentioned below:
Name Multivalued Read Only Dest Field Multivalue Link
Industry Name Y Y Name Industry
Industry Id Y Y Id Industry
- If you observe the above table, you will notice that we have checked the Multivalued property to True. Industry Name and Industry Id are the fields in the Account BC which are mapped to the Name and Id fields in the Industry BC as specified in the “Dest Field”. “Industry“ is the Multivalue Link that is used to link these fields between the two BCs. Read Only flag is set according to the requirement.
Step 4: Create an MVG applet (if not already present)
- Create an MVG list applet based on the child BC (i.e. Industry BC)
- Select those fields that you wish to display in this applet. It is not mandatory to create fields in the Parent BC (Account) for all the fields displayed through this applet. For example, you can display Id, Name, SIC Code and Type from the Industry BC. However, please note that we have only created two fields Industry Id and Industry Name in Account BC to map to Id and Name fields in Industry BC respectively. We have not created fields to map to SIC Code and Type. This explains that we need to create fields in the parent BC for only those fields that we want to map to the child BC, although we can display as many fields as we want in the MVG applet from the child BC.
- Make sure you have “SSA Primary Field” listed and displayed in this applet. When the application is run, marking this field to True will make a particular record as the Primary record in the MVG.
Step 5: Adding the MV field to the corresponding list or form applet of the Parent BC (in this case, Account List Applet).
- Select “Applet” child object in the object explorer.
- Query for the required applet in which you want to display the MVG in the UI.
- Click on List->List Column child object and create a new record with the following values.
o Name: Industry (Any valid, non-duplicating name)
o Field: Industry Name (the field on which you want to display the MVG applet)
o Display Name – String Override: Industry (or whatever you want the UI to display)
o Available: Set it to True
o HTML Type: Field
o MVG Applet: Industry Mvg Applet (as appropriate)
o Run Time: Set it to True
The rest of the properties can default to whatever they were when you clicked on the new record. Please note that the “Run Time” property is very crucial to display to the MVG. If it is not set to True, the Mvg will not appear in the UI.
- Right click on the applet and select “Edit Web Layout”.
- Drag and drop the new control that you just created to the appropriate place in the layout.
- Save the applet.
Step 5: View in the UI
- Compile the Link, BC, MVG applet and the main applet to the SRF.
- Run the application. You should see the MVF in the corresponding applet in the UI.
Hope I have explained it to your satisfaction.
excellent explanation..and plz do explain about MVG in the similar way..
hey ..this is really gud..plz explain that how can we make this field editable…
Do you know how to build a join with date constraints such as field to compare between (startdate, end date)
Regards,
Hector Carrasco
Productive Explanation..
Thanks
Atul
Hai
1Q. And I saw that we can create link betweeen same bcs i saw in sample application what was the mean of it .
2Q.its great explanation about joins. but i have Query that we can crete a new bc on implicit joins. for example iam taking a contact i want to find that contact person first language.He can speak many languages but primary language is we have to select. so that how can we approach on this situation. Please i need responce iam siebel learner.
Iam in dilema mr.Neel.
shall i create new bc on s_contact_x or s_contact_xm table.
and how can i create link like
please explain me briefley.
vishnu,
If you have the problem like you need to show the primay language of contact and there are multiple records for same contact and you must having the flag for the the primary language.
Suppose you have multiple records for language in language bc which is base on s_contact_xm table.
you can configure a explicit join in contact bc with search specification as
Source field :- Id
Destination column :- PAR_ROW_ID
Now you have to define join constraint
suppose column for primary language flag is PRI_LANG
Destination column :- PRI_LANG
value :- Y
Now you can configure field base on this join
May be this solution will help you
For more detail click here configure constraint
monster :]
Hi,
Can anyone help me by providing some questioners with answers on the below:
Siebel Config – documentation & change management tool.
Also which company has project on the same?
Hi Amol,
Really configuring join is nicely explained.
really appreciates your valuable efforts.
Thanks.
Hi, I am experiencing a problem when i try to open the siebel application on my desk top. there is a error message popping up it says siebel web session:cannot determine default web browser for current user.please set up your web browser properly or use/B command line argument to specify one….can any body help me please…..your response will be appreciated…
siebel02
Hi Amol,
I thouroghly enjoyed reading ur content on joins.
I will alwasy remember the concept of joins hereafter.
It was very simply put.
thanks a lot for ur effort.
nice ………………i dnt have words
hi amol
that was nice man ….i was unable to find your post on configuring pick applet on a field …please send the link where it is..your way of explaining thing is excellent keep it up man
Nagalakshmi, Its such a great explination on the MVGs.But a small correction on it I guess, the row_id of the S_ORG_EXT should be the OU_ID of the S_ORG_INDS which is A1 not Acc1
hi the join which u eplained here is basic(lkg ype). exlain party to nonpparty join & non party to party join
Very Good explanation
Hi Baba,
If you can bit elaborate your question that would be easier for us to prepare another post for the same. Here I have explained the basic concept of join which will be same all around the Siebel Application.
Excelleny articles both on Joins and MVG's…. Thanks a lot to you guys…
hi this very good
markandeyulu
I hav one small conceptual doubt..
When we create join specification And in this case give OptyId field as fk which will mapped to DEst col. i.e. Row_Id column of S_opty table. So in this case to work join fine, IS THERE ANY NEED TO HAVE FK TABLE PROPRTY OF COLUMN OF S_DOC_QUOTE TABLE , ON WHICH optyid field is based , set to “S_OPTY”.
Keep up the good work.