{"id":1743,"date":"2011-10-21T23:01:08","date_gmt":"2011-10-21T23:01:08","guid":{"rendered":"https:\/\/www.wapshere.com\/missmiis\/?p=1743"},"modified":"2011-10-23T20:09:14","modified_gmt":"2011-10-23T20:09:14","slug":"generating-reference-attributes-from-string-data","status":"publish","type":"post","link":"https:\/\/www.wapshere.com\/missmiis\/generating-reference-attributes-from-string-data","title":{"rendered":"Generating Reference attributes from String data"},"content":{"rendered":"<p>Once upon a time we used to be able to write Advanced Flow Rules for reference attributes. Admittedly this sometimes led to horribly inefficient code, but it was useful &#8211; particluarly when paired with FindMVEntries to lookup and then reference an existing Metaverse object.<\/p>\n<p>With FIM we lost this capability, and Microsoft claim we were never supposed to be doing it anyway &#8211; that it was &#8220;unsupported&#8221; all along. So what do you do if you&#8217;ve got string data and you really need references? One Sync-based way is to loop the data through a SQL MA, bringing it back in as a reference.<\/p>\n<p><!--more--><\/p>\n<p>The method outlined here\u00c2\u00a0generates a <strong>manager<\/strong> attribute from the two string attributes <strong>position<\/strong> and <strong>superiorPosition<\/strong>, which hold position numbers (as distinct from employee numbers).<\/p>\n<h3>Create SQL Tables<\/h3>\n<p>Create the following tables:<\/p>\n<ul>\n<li>GenerateRef_Objects<\/li>\n<\/ul>\n<p style=\"padding-left: 60px;\">DN [nvarchar] (200)<\/p>\n<p style=\"padding-left: 60px;\">objectType [nchar] (50)<\/p>\n<ul>\n<li>GenerateRef_MultiValue<\/li>\n<\/ul>\n<p style=\"padding-left: 60px;\">DN [nvarchar] (200)<\/p>\n<p style=\"padding-left: 60px;\">attribute [nchar] (50)<\/p>\n<p style=\"padding-left: 60px;\">Reference [nvarchar] (200)<\/p>\n<p>The plan for these tables is to export data to them so that the Objects table lists the possible position numbers\u00c2\u00a0along with an objectType of &#8220;position&#8221;, and the MultiValue table shows the relationship between the positions, with the one in the Reference column being the manager.<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/sqlmv.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1759\" title=\"sqlmv\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/sqlmv.jpg\" alt=\"\" width=\"365\" height=\"128\" srcset=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/sqlmv.jpg 365w, https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/sqlmv-300x105.jpg 300w\" sizes=\"auto, (max-width: 365px) 100vw, 365px\" \/><\/a><\/p>\n<p>Note that the &#8220;x&#8221; entry is just a placeholder I put in while creating the MA, because it needs to see at least one objectType specified. Once the MA is created I can delete that line.<\/p>\n<h3>Create a SQL MA<\/h3>\n<p>Start by creating the SQL Management Agent in the usual way:<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA1.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"size-full wp-image-1747 alignnone\" title=\"MA1\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA1.jpg\" alt=\"\" width=\"526\" height=\"387\" \/><\/a><\/p>\n<p>Set the anchor to the DN column:<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA2.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1750\" title=\"MA2\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA2.jpg\" alt=\"\" width=\"540\" height=\"395\" srcset=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA2.jpg 658w, https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA2-300x220.jpg 300w\" sizes=\"auto, (max-width: 540px) 100vw, 540px\" \/><\/a><\/p>\n<p>You will also have to configure the Multi-Value settings. These are a little obscure, and I&#8217;ve explained them in more detail <a href=\"https:\/\/www.wapshere.com\/missmiis\/configuration-of-the-sql-ma\">elsewhere<\/a>, so I&#8217;ll just show a piccy here:<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA3.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1751\" title=\"MA3\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA3.jpg\" alt=\"\" width=\"326\" height=\"377\" srcset=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA3.jpg 408w, https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA3-259x300.jpg 259w\" sizes=\"auto, (max-width: 326px) 100vw, 326px\" \/><\/a><\/p>\n<p>As well you have to set an object type. For flexibility I&#8217;m just going to point it at my &#8220;objectType&#8221; column, meaning I could, potentially, support multiple types with this MA.<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA4.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1752\" title=\"MA4\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA4.jpg\" alt=\"\" width=\"350\" height=\"183\" srcset=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA4.jpg 350w, https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA4-300x156.jpg 300w\" sizes=\"auto, (max-width: 350px) 100vw, 350px\" \/><\/a><\/p>\n<p>Set a join rule between the column where the position number is stored in the Objects table (in this case the &#8220;DN&#8221;) and the position attribute in the Metaverse.<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA5.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1754\" title=\"MA5\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA5.jpg\" alt=\"\" width=\"520\" height=\"387\" \/><\/a><\/p>\n<p>Now for the flow rules: we want to flow the superiorPosition string value out to manager, and then the same value back to the manager attribute in the Metaverse &#8211; but now magically transformed to a reference.<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA6.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1755\" title=\"MA6\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA6.jpg\" alt=\"\" width=\"526\" height=\"387\" srcset=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA6.jpg 661w, https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA6-300x220.jpg 300w\" sizes=\"auto, (max-width: 526px) 100vw, 526px\" \/><\/a><\/p>\n<p>Finally set your deprovisioning rules:<\/p>\n<p><a href=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA7.jpg\"><img loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1756\" title=\"MA7\" src=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA7.jpg\" alt=\"\" width=\"460\" height=\"338\" srcset=\"https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA7.jpg 657w, https:\/\/www.wapshere.com\/missmiis\/wp-content\/uploads\/2011\/10\/MA7-300x220.jpg 300w\" sizes=\"auto, (max-width: 460px) 100vw, 460px\" \/><\/a><\/p>\n<h3>Metaverse Code<\/h3>\n<p>The final step is to write the provisioning code that creates the position objects in the SQL table.<\/p>\n<p>(As this is a Sync-based method I&#8217;m going classic with none of that &#8220;declarative&#8221; malarky. If you had the Portal in the mix you may well be sorting out this manager stuff in there anyway.)<\/p>\n<pre>    Public Sub Provision(ByVal mventry As MVEntry) Implements IMVSynchronization.Provision\r\n        If mventry.ObjectType &lt;&gt; PersonObjectClass Then\r\n            Exit Sub\r\n        End If\r\n\r\n        Dim DoesExist As Boolean = False\r\n        Dim ShouldExist As Boolean = False\r\n\r\n        If mventry.ConnectedMAs(MAName).Connectors.Count &gt; 0 Then\r\n            DoesExist = True\r\n        End If\r\n\r\n        If mventry(\"position\").IsPresent Then\r\n            ShouldExist = True\r\n        End If\r\n\r\n        If ShouldExist And Not DoesExist Then\r\n            Dim csentry As CSEntry\r\n            csentry = mventry.ConnectedMAs(MAName).Connectors.StartNewConnector(\"position\")\r\n            csentry(\"DN\").Value = mventry(\"position\").Value\r\n            csentry.CommitNewConnector()\r\n\r\n        ElseIf DoesExist And Not ShouldExist Then\r\n            Dim csentry As CSEntry\r\n            csentry = mventry.ConnectedMAs(MAName).Connectors.ByIndex(0)\r\n            csentry.Deprovision()\r\n        End If\r\n    End Sub<\/pre>\n<h3>What should happen<\/h3>\n<p>When you sync your person objects you should see &#8220;position&#8221; objects being provisioned. Export, Import and Sync and you should see the reference value flow back into the Metaverse.<\/p>\n<p>Note that this method does assume position numbers are unique &#8211; if you have a possibility of duplicate position numbers (such as with a job share) then you will need to get a bit more creative.<\/p>\n<p>If you have a lot of data you should also look at <a href=\"https:\/\/www.wapshere.com\/missmiis\/delta-and-multivalued-combined\">generating a Delta table<\/a> for the confirming Import step.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Once upon a time we used to be able to write Advanced Flow Rules for reference attributes. Admittedly this sometimes led to horribly inefficient code, but it was useful &#8211; particluarly when paired with FindMVEntries to lookup and then reference an existing Metaverse object. With FIM we lost this capability, and Microsoft claim we were&#8230;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"footnotes":"","jetpack_publicize_message":"","jetpack_is_tweetstorm":false,"jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":[]},"categories":[42,5,30],"tags":[],"class_list":["post-1743","post","type-post","status-publish","format-standard","hentry","category-fim-2010","category-sql","category-vbnet"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_shortlink":"https:\/\/wp.me\/pkp1o-s7","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1743","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/comments?post=1743"}],"version-history":[{"count":12,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1743\/revisions"}],"predecessor-version":[{"id":1770,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/posts\/1743\/revisions\/1770"}],"wp:attachment":[{"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/media?parent=1743"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/categories?post=1743"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wapshere.com\/missmiis\/wp-json\/wp\/v2\/tags?post=1743"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}