External Content Type - SQL - Filters

Question

I create a new external list via a new external content type SQL .

I retreive my data and i can display it.

BDC Service allows us to view 2000 items .

1st Issue:

I can filtered through a filter to limit 2000. However I'd like to display the 2000 last entry in the SQL database and I can not. It takes me the first 2,000 entries .

Is it possible to view the last 2000 entries in the SQL database ?

Edit: This problem came from my SQL base. SharePoint Designer Filter do what i want know.

2nd Problem:

He retrieves a System.Decimal type column , my values ​​are only integers, but It displays a value with ten decimal 0 ( 20.000000000000000000 ) .

Is it possible to limit the number of 0 it displays ?

Thank you in advance, EGuidez

Answer 1

Solution for 2nd Problem:

I guess your database table column type as decimal(18, 20). If you correct it into decimal(18, 0) then you will not get decimal value in suffix.

Solution in SharePoint

By default external list have Read List.aspx view.

By editing view in SharePoint Designer you can control the decimal value.

Step 1: Open the list in SharePoint designer

Step 2: click the view/create new view and open the view page in Advanced Edit mode.

Step 3: Select the column you need to control and click formula ribbon button under Filter, Sort & Group tab.

Step 4: Add formula substring-before($thisNode/@*[name()=current()/@Name],'.')

After above update you will found following XSLT code <xsl:value-of select="substring-before($thisNode/@*[name()=current()/@Name],'.')"/> in code view.

Screen shot Update:

Open External List in designer and click on Read List.aspx view page see below

Next view the page in Design View or Split as shown below

Now select your column by click the TD of your column as shown in image then click the formula as shown in picture.

Answer 2

In my BDC Model,

I added "cast([Quantity] as int) as" before [Quantity] :

And it display correctly Quantity as an int:

Problem Solved :D