locked
how to combine two list column values and show in another list RRS feed

  • Question

  • Hi

          Scenerio: We have tables in SQL Server Database like States and columns are like StateName and StateCode and I want to use BDC to show States Table in Sharepoint List(L1) and I have created another list like ConcatenatedList(L2).

        Problem Description: I want to Concatenate values in StateName and StateCode and show in L2 ConcatenatedList

        Kindly let me know any proposed solutions for this.

    Thanks


    Razvi444

    Saturday, November 9, 2013 9:14 AM

Answers

  • Hi Razvi,

    You can use the formula below to concatenate information such as document name and version, or to create unique IDs in SharePoint.

    1. Create a Calculated value column

    2. Use the formula below,
            =CONCATENATE([ColumnName1],[ColumnName2])

    Your case...You need to create...

    1. Create a List column with External Content Type

    2. Create another column based on external content type and concatenate State and City.

    I would still think creating sub lists is good for State and City values...

    http://stackoverflow.com/questions/645378/how-to-create-sub-lists-in-sharepoint

    Reply if this helps...

    Regards,

    Raghu

    • Proposed as answer by DawnYu Tuesday, November 12, 2013 7:40 AM
    • Marked as answer by star.wars Tuesday, November 19, 2013 10:41 AM
    Saturday, November 9, 2013 2:47 PM
  • Hi Ravi,

    Instead of creating other list and storing any concatenated Values.The easy way will be modify the data in the backend(Sql Server) itself.

    There are two thing you can do. 

    1. Create a WCF service and add one more column and return the concatenated value in that column and create your External content type.

    2. Other easy solution is create a View in SqlSever with one more column for concatenated value and create your Extrnal Content Type based on your view instead of table.

    Please find the script for View below.

    USE [Jayanttest]
    GO

    /****** Object:  View [dbo].[StateView]    Script Date: 11/09/2013 05:14:45 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[StateView]
    AS
    SELECT     State, StateCode, State + StateCode AS CombinedColumn
    FROM         dbo.State

    GO

    Please find the screen shot below.

    *Please remember to mark your question as answered & Vote helpful,if this solves/helps your problem.* 

    Regards,

    Jayant


    jayant prabhakar

    Saturday, November 9, 2013 10:19 AM

All replies

  • Hi Ravi,

    Instead of creating other list and storing any concatenated Values.The easy way will be modify the data in the backend(Sql Server) itself.

    There are two thing you can do. 

    1. Create a WCF service and add one more column and return the concatenated value in that column and create your External content type.

    2. Other easy solution is create a View in SqlSever with one more column for concatenated value and create your Extrnal Content Type based on your view instead of table.

    Please find the script for View below.

    USE [Jayanttest]
    GO

    /****** Object:  View [dbo].[StateView]    Script Date: 11/09/2013 05:14:45 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO

    CREATE VIEW [dbo].[StateView]
    AS
    SELECT     State, StateCode, State + StateCode AS CombinedColumn
    FROM         dbo.State

    GO

    Please find the screen shot below.

    *Please remember to mark your question as answered & Vote helpful,if this solves/helps your problem.* 

    Regards,

    Jayant


    jayant prabhakar

    Saturday, November 9, 2013 10:19 AM
  • Hi Razvi,

    You can use the formula below to concatenate information such as document name and version, or to create unique IDs in SharePoint.

    1. Create a Calculated value column

    2. Use the formula below,
            =CONCATENATE([ColumnName1],[ColumnName2])

    Your case...You need to create...

    1. Create a List column with External Content Type

    2. Create another column based on external content type and concatenate State and City.

    I would still think creating sub lists is good for State and City values...

    http://stackoverflow.com/questions/645378/how-to-create-sub-lists-in-sharepoint

    Reply if this helps...

    Regards,

    Raghu

    • Proposed as answer by DawnYu Tuesday, November 12, 2013 7:40 AM
    • Marked as answer by star.wars Tuesday, November 19, 2013 10:41 AM
    Saturday, November 9, 2013 2:47 PM