This discussion has been locked.
You can no longer post new replies to this discussion. If you have a question you can start a new discussion

Synch Mapping FK relation ADSAccount <> EX0Mailbox

Hello all,

I have a Synch-Mapping question.

I am in the AD Synch-Project mapping from the ADSAccount.

From that point, I want to have the values "EmailAddresses" which are stored in the table "EX0Mailboxes".

There is an FK relation from EX0Mailboxes to ADSAccount. How can I get the value EmailAddresses from EX0Mailboxes?

I tried with scripted properties but I cannot use
Connection.GetSingleProperty("EX0Mailbox","EmailAddresses", f.Comparision("UID_ADSAccount), $UID_ADSAccount$)
as the Connection object is based on VI.Projector and unfortunately I dont have any insight how to use it correctly.

Thanks, Fatih

  • Just asking, why do you need the EmailAddresses from EX0Mailbox in a AD sync project?
  • Hello Markus,

    good question, this was just an example for my given scenario.

    I dont need it, as I can map directly EmailAddresses(EX0Mailbox) <-> EmailAddresses (Mailbox), but in general how can I get a value from a table, where the FK relation is set from the the target table.

    You know what I mean?

    Thanks, Fatih
  • Hi Fatih,

    the sample code for your read-script in the scripted property for your example with ADSAccount and EX0MailBox would look like the following.

    Imports VI.Projector.Connection

    Dim email = SystemObject.Connection.QueryObject(SystemQuery _
    .From("EX0MailBox") _
    .Select("EmailAddresses") _
    .Filter(String.Format("UID_ADSAccount='{0}'", $UID_ADSAccount$)) _
    ).Result.First.GetValue("EMailAddresses").AsString

    Return email

    Please note, that I assumed that you want to return a single string value and not a multi-value property. You may have to convert the String into a 1-dimensional array of String.

    Additional Note

    You need to have the table EX0MailBox in your schema of the AD Sync Project. To keep the table sticky during schema shrinks, you have to exclude the table from the schema shrink.

    Performance - Advise

    Please consider if you really have to map the data from a CR relation in that fashion. Every read access to that property will result in an additional database roundtrip to fetch the value.

  • Hello Markus,

    thanks for the answer and the advise.

    Thanks, Fatih
  • Hello Markus,

    Thank you for you example how the scripted property can be used. I have an additional question about: String.Format("UID_ADSAccount='{0}'", $UID_ADSAccount$)

    There could be situations where the value could contain single quotes. Is there a SQL formatter I can use? And if so how can I use this?

    Thanks, Werner
  • Hi Werner,

    you do have a limited filter formatter available using the SystemObject. It is limited in a way that it only supports some of the operations the full SQL formatter is capable of doing. When the SystemObject is created by a OneIM connector connection the filter formatter will be mapped to the SQL formatter of the OneIM connection.

    The same sample as above with the filter formatter would look like the following.

    Imports VI.Projector.Connection

    Dim email = SystemObject.Connection.QueryObject(SystemQuery. _
    From("EX0MailBox").Select("EmailAddresses") _
    .Filter(SystemObject.Connection.FilterFormatter.Comparison( _
    SystemObject.SchemaType("UID_ADSAccount"), _
    $UID_ADSAccount$, Filter_
    .Formatter.FilterCompareOperator.Equal) _
    ) _
    ).Result.First.GetValue("EMailAddresses").AsString


    Return email

    Hth

  • Hi Markus,

    Followed the sample in the post, I attempted to use Write script to set state or province according to its country in Locality table. But it did not set the state or province. Can you please point out where it could be wrong?

    Here is what I did in v7.1.2 -

    State in CSV mapped to Locality.CP01 and CP01 value is updated in Locality during sync

    Country in CSV mapped to Locality.UID_DialogCountry (Key resolution by reference) and the value is updated in Locality during sync

    virtual property "vrtState" on 1IM side (left) with Write script to set Locality.UID_DialogState but nothing set

    Imports VI.Projector.Connection 

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

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

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

    $CustomProperty01$, $UID_DialogCountry$))

    ).Result.First.GetValue("UID_DialogState").AsString

     $UID_DialogState$ := state

    If I do below -

    Dim state As ISystemObject... I got the error.

  • If you remove the .GetValue("UID_DialogState").AsString you will get rid of the error message as the .First returns an ISystemObject.

  • The value still could not be set by the write script. Where else should I look into the cause?
  • Do you have mapping rule configured to write something to the property itself to trigger your script?

    You try to debug the script in Visual Studio using the DEBUG button. In the new Sync Editor - open your Sync Project, go to the target system browser of the OneIM connection and try to set the virtual property to something. You will see, that the script will be triggered. (Do not forget to set a break point in your property script in Visual Studio.