Use key from existing list to lookup specific record in external database?

Hi all,

I’ve tried searching the net, but I’m not able to properly, clearly state the question. I’m a noob and probaby lack the vocabulary.

Here’s the problem I’m trying to find a rough solution method for (and where I’m at now):

I have an existing site in Sharepoint 2013 with a List of systems (what is it, who owns it, who to contact, links to the documentation, etc.)

Externally there is an mssql CMDB with server information (IP-address, hostname, etc). I think I can add the CMDB to Sharepoint as an External Content Type and make a List based on it.

I’ve figured that I would probably have to supply at least the systems List with a unique ID.

So in Sharepoint I want to integrate these, ie.:

1. When showing the system details I want to show the servers attached to it. Ideally I’d want to make a real time lookup in the external database, same result as if doing something like this: “SELECT hostname FROM serverview WHERE systemForeignKey=<system uuid>”.

2. When creating/editing I’d want to be able to pick servers and attach them to the system. Preferably like the Lookup type with the two columns where you can move items from the left to the right. Ideally writing choices back to the external database.

One additional problem would be that some “hotel” servers would have one-to-many systems.  Less ideal setups might be acceptable, so don’t spend time on this if it’s a Pandora’s Box 🙂

At this point I don’t need details but mostly an indication of whether it can be done, and what the major steps involved are.

I really hope you can help.