Synchronization Editor: Key resolution - Dynamically filtering schema type based on target system property. Such as DialogState

Hello OneID people,

I've stumbled across a pickle that I was able to work around for now, but I would like your opinion on how I might be able to solve it permanently.

I have a sync project which, among others, imports locations from the source system. This includes the properties such as Country Code, State, Zip Code and Street Address.

During the sync, IAM reports that it could not set the value of UID_DialogState for a number of locations, because the value for the State "NY" is not unique. Hang on... "NY = New York" is not unique? To my surprise when one filters DialogState entries with ShortName = 'NY' then 6 results are shown. Mind blown.

To my luck, I know for a fact that this source system only delivers locations in USA and Canada. So the work around was simple.

I create two key resolution properties on UID_DialogState. One which is filtered to only return states within USA and one to only return states within Canada.

I then map the two virtual properties to Right.State. One which is only active when Right.Country = 'USA' and the other only active when Right.Country = 'CAN' 

Works a charm.

But a problem would arise if the source system were to deliver locations from a number of different countries. The work around described above would become unfeasable.

So the question is: How can one dynamically filter the results of a key resolution property? Is there a way to set the filtering condition of a key resolution to point to a target system property?

In my example, the filtering condition for the key resolution would look like: UID_DialogCountry IN (SELECT UID_DialogCountry FROM DialogCountry WHERE Iso3166_3 = '$Right.Country$')

Thanks in advance for your time and help!

Regards,

Ryu

Parents
  • Ryu,

    You may give a try as followings to get State from the State's Country. HTH.

    1. create a virtual property i.e. "vrtState" on 1IM

    2. on Write script tab - create a script

    Imports VI.Projector.Connection 

    Dim state As ISystemObject = SystemObject.Connection.QueryObject(SystemQuery. _

    From("DialogState").Select("UID_DialogState") _

    .Filter(String.Format("ShortName='{0}' AND UID_DialogCountry='{1}'", _

    value.ToString, $UID_DialogCountry$))

    ).Result.FirstOrDefault

     $UID_DialogState$ := state.GetValue("UID_DialogState").AsString

    3. Map state property (shortname of the state or province) on CSV source to vrtState on 1IM

  • In case there is a Country without the State, you may use IF ELSE statement to $UID_DialogState$ line to avoid exception.

  • Wow! Thank you for your help! I will give it a go and see where it takes me.

  • Hi xd,

    so I finally got around to testing the script and I'm not sure why but the state is not being set on the base object.

    I'm trying to Import a Location whose state is set to "VIC". The shortname is unique such that Victroria is the only DialogState with this shortname. DialogCountry is being populated by a key Resolution property just one row before vrtState is synchronized (in Terms of mapping order from top to bottom).

    Have I gotten the script you sent me wrong somehow?

    Thank you very much in advance for your time!

    ' Get ShortName of State from Target System
    Dim stateShortName As String = value.ToString
    
    ' Check state is not empty
    If Not String.IsNullOrEmpty(stateShortName) Then
    	
    	Imports VI.Projector.Connection
    	' Define WhereClause (ISQLFormatter cannot be used here)
    	Dim wc As String = String.Format("ShortName='{0}' AND UID_DialogCountry='{1}'", 
    									 stateShortName, $UID_DialogCountry$)
    	' Get UID_DialogState
    	Dim dialogState As ISystemObject = SystemObject.Connection.QueryObject(SystemQuery _
    							   	 .From("DialogState").Select("UID_DialogState") _
    								 .Filter(wc)).Result.FirstOrDefault
    	
    	' Set Value of UID_DialogState
    	Dim uidDialogState As String = dialogState.GetValue("UID_DialogState").AsString
    	If Not String.IsNullOrEmpty(uidDialogState) Then $UID_DialogState$ := uidDialogState
    End If

  • Where did you define the scripted property, on the LHS or on the RHS? Your script is stored as the write script, correct?

Reply Children