(21 votes, average: 4.71 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 ...