Siebel CRM How to – Configure a Join.

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.
Siebel Configuration of Join
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).

Siebel Configuration of Joins

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.

Siebel Configuration of Joins

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.

Siebel Configuration of Joins
 
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

Siebel Configuration of Joins

Don’t forget to map this field in Applet. Right Click on the corresponding Applet and map it.

Siebel Configuration of Joins

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.

Siebel Cofiguration of Joins

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.

101 Responses to Siebel CRM How to – Configure a Join.

  1. 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.

  2. 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.

  3. 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

  4. 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

  5. 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.

  6. 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.

  7. 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.

  8. 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.

  9. 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.

  10. 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.

  11. 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.

  12. 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.

  13. 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.

  14. 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.

  15. 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?

  16. 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?

  17. 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.

  18. 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.

  19. Hi Amol,
    Thats really a beautiful explaination for joins you posted.
    It helped me to understand better than before.

    Thank you Buddy

  20. Hi Amol,
    Thats really a beautiful explaination for joins you posted.
    It helped me to understand better than before.

    Thank you Buddy

  21. 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.

  22. 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.

Leave a Reply

Contribute