locked
Is it possible to insert comma's in Column Type "Money" of SharePoint External list..Data Type in SQL is Money RRS feed

  • Question

  • Any workarounds through SharePoint Designer?As i know through SharePoint designer we can put commas to numeric fields.

    What about Money or Currency fields?

    Thursday, January 26, 2012 2:40 AM

Answers

  • You may export your BDC model file from SharePoint Designer, change data type (Decimal to Double) and SELECT statement, and after that you should import modified BDC Model to SharePoint using Central Administration the. It does work for me.

    For example this type descriptor

    <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" Name="Column1">
      <Properties>
        <Property Name="Decimal Digits" Type="System.Int32">4</Property>
      </Properties>
    </TypeDescriptor>

    should be modified with this one:

    <TypeDescriptor TypeName="System.Nullable`1[[System.Double, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" Name="Column1">
      <Properties>
        <Property Name="Decimal Digits" Type="System.Int32">4</Property>
      </Properties>
    </TypeDescriptor>

    and this SELECT statement:

    <Property Name="RdbCommandText" Type="System.String">SELECT [ID] , [Column1] FROM [dbo].[MoneyTable]</Property>
    
    should be replaced with this one:
    <Property Name="RdbCommandText" Type="System.String">SELECT [ID] , cast([Column1] as float) as [Column1] FROM [dbo].[MoneyTable]</Property>
    

    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts


    • Edited by Dmitry Kaloshin Thursday, January 26, 2012 12:40 PM
    • Marked as answer by SP Support Friday, January 27, 2012 7:54 PM
    Thursday, January 26, 2012 12:39 PM

All replies

  • Hi Please clarify your needs. Are you planning to insert the data in External List which based on a table having money data type field?

    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    Thursday, January 26, 2012 8:15 AM
  • Dmitry,Thanks for your reply.

    I am displaying  the Data through an External List(SharePoint Designer).column with  float Data type displaying with commas but with the Money data type  displaying without commas.I want a uniform Look for my external list.

    Pl suggest any workaround

    Thursday, January 26, 2012 9:02 AM
  • Do you want to have this value to be displayed as 1,000,000.555 instead of 1000000.555 in the External list?

    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts
    Thursday, January 26, 2012 9:34 AM
  • Exactly
    Thursday, January 26, 2012 10:40 AM
  • You may export your BDC model file from SharePoint Designer, change data type (Decimal to Double) and SELECT statement, and after that you should import modified BDC Model to SharePoint using Central Administration the. It does work for me.

    For example this type descriptor

    <TypeDescriptor TypeName="System.Nullable`1[[System.Decimal, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" Name="Column1">
      <Properties>
        <Property Name="Decimal Digits" Type="System.Int32">4</Property>
      </Properties>
    </TypeDescriptor>

    should be modified with this one:

    <TypeDescriptor TypeName="System.Nullable`1[[System.Double, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]" Name="Column1">
      <Properties>
        <Property Name="Decimal Digits" Type="System.Int32">4</Property>
      </Properties>
    </TypeDescriptor>

    and this SELECT statement:

    <Property Name="RdbCommandText" Type="System.String">SELECT [ID] , [Column1] FROM [dbo].[MoneyTable]</Property>
    
    should be replaced with this one:
    <Property Name="RdbCommandText" Type="System.String">SELECT [ID] , cast([Column1] as float) as [Column1] FROM [dbo].[MoneyTable]</Property>
    

    Dmitry

    Lightning Tools LogoLightning Tools Check out our SharePoint tools and web parts


    • Edited by Dmitry Kaloshin Thursday, January 26, 2012 12:40 PM
    • Marked as answer by SP Support Friday, January 27, 2012 7:54 PM
    Thursday, January 26, 2012 12:39 PM
  • Great Dmitry !

    It worked like wonder

    Friday, January 27, 2012 7:55 PM