How to map User Profile to ECT field

[Michal Guzowski]

Context

I have a SQL table that contains article information: title, content and author.

I want to search in that table using SharePoint 2016 search (on-prem).

In order to do that I've created External Content Type with above data, create content source and crawl it. Then I've mapped all crawled props to managed props in search.

I was able to see articles data from SQL in my search results. 

Problem

However since article author is just a string (DOMAIN/username) SharePoint search shows 2 refiners - 1 for user field author for items that comes from SharePoint directly and 1 for author string that comes from ECT. Moreover when user access profile page of ECT item it would be nice if clicking on user name redirect to user profile, right?

Question

Is it possible to map user profile to ECT field so there will be unified user experience for author information that comes from inside and outside of SharePoint?

[#2 · Accepted solution]

Problem solved! :)

[#3]

Good that you found a solution!
Is it possible for you to share it?

[#4]

You have 2 choices:

1. Use simple string with the name of the user. When you map such crawled property with managed property that keeps user information (i.e. Author) refiners will merge those information together 

2. Use lookup notation. User fields are strings that are a lookup value to a user. Lookup notation is like <id>;#<display name> so i.e. "20;#Test User". This notation still makes refiners to display user names (i.e. Author will display only "Test User", without "20;#") and moreover you can pass ID in order to manage behavior on click (on custom BCS profile page).

Check my sample screen below.

I've created example list with some Title and UserString field that is simple text line. I fill it with some values (+ run crawl. Crawled property of that field mapped to Author managed property). Then I use search to get all list items. Observations:

- there are 2 different notations for test user, both merged and pretty rendered.

- you can pass "-1" for users that doesn't exist in UPS

- I intentionally made a typo ("#;" instead of ";#") - for that user refiner didn't take just username but whole string.

Hope it's clear. If not - ask :)