I am using SP Server 2010.  The requirements of a project are to:-

1. Search an external database so that we can bring back rows from that database with say 5 fields.
2. Create a list in SP to hold about 25 site columns
3. Use the list in (2) to add rows sourced from (1) and fill in the site columns specified in (2)
4. Poll the (1) to check if new rows have been added and, if they have, add them to the list in (2)

Could anyone please advise upon the best way to model these requirements in SP given this MSDN documentation?

Using BCS presumably?

Should we favour External Columns in (2) as opposed to lookups for example (see my post here)?

We are basically trying to convert an Excel Workbook to SP. If we have the option (and we may not) is Excel Services the best way to do this?

What is List Pattern we should adopt?

Thanks,

Any ideas please?

Hi,

For your first requirement, yes, you can use business connectivity service to bring rows from the external database field.

You can refer the detailed article below:

Using Business Connectivity Services in SharePoint 2010

For your second and third requirement, yes you need to create external data column to fill the external database field data to the list in the list. As external list has limitation as the source of look up column directly.

More information about external data column:

Create a SharePoint List with External Data Columns

If your requirement "convert excel workbook to SP", yes, you can use excel service to display workbook content in browser directly.

More information:

Excel Services in SharePoint Server 2010

For your fourth requirement, you can create a console application with Server Object Model to check the data in the external list in first requirement, if there is data in the list ,then you can add the data to external columns in requirement 2.

Programmatically set external column

For the list pattern, it will depend on your business logic and you data count,if your data is very large, then you can use the large list pattern.

Thanks

Best Regards

Thanks Jerry So it is possible to have a Lookup field on an internal SP list pointing to an External List but you are saying an External Column is better ? And it is better why? In terms of my requirement 4 I am thinking of a timer job to get all items from the External List and iterate through them checking if an entry exists in the internal list. If it does not, add a new

Hi,

External list has limitation when do look up for field value except id column in external list.

More information, please refer:

SharePoint BCS 2010: Limitations and Challenges

For requirement 4, yes, you can use Timer Job to check if the entry exist in the internal list.

Thanks

Best Regards