1IM V7.1 Synchronisation Projects - One to Many Relationships

I'm in the process of creating a connector to an application that has a specific use case; and wanted to see if anybody else has done anything similar.

The application in question uses a simple SQL database that doesn’t have any primary keys set as users can have multiple entries (depending on their needs). It's possible to create a Column Group that will contain a unique value to serve as the primary key.

I had the idea of using group memberships to create the associated records in the user table of the application. Basically, once a request for a level of access has been processed, users would obtain a single Account Definition which would create an entry in the UNSAccountB space and membership to a UNSGroupB based on their requested access. A synchronisation project would be configured to map the details from UNSAccountBInUNSGroupB to the application’s table.

As a PoC, I’ve managed to get the synchronisation project provisioning new records to the application table, however I seem to be having troubles with the use of a Column Group as a Primary Key when it comes to mapping the existing records. This means that once 1IM provisions the records, consequent synchronisation attempts failed with the error “An item with the same key already exists”.

  • Has anybody tried doing something similar in the past?
  • Has anybody had any issues in the past using Column Groups as Primary Keys and then in Join Rules?

I have noticed that browsing the target system shows the objects with a slightly different key structure than what’s actually written to the virtual attribute. I have tried all sorts of combinations and configurations for the joining rule attributes but all have had the same results.

  • One possible way to work around it is to use the Property Join with a delimiter, and check the flag field. You would need to apply this consistently in all your mappings.

    Simple example:

    Suppose your account table is a mapping table with columns for userId, creation date and AutoNumber where account IDs are reused, then a property join creating "AutoNumber_UserID" as a key can be used as the object GUID to differentiate between different instances of the same ID.

    Suppose there's also a mapping table for memberships showing only the userID, groupID and date created. A script property can be used to work out which AutoNumber instance of userID was active at the time.

    Instead of creating the mapping from userID to UID_UNSAccountB you map the property join to UID_UNSAccountB.ObjectGUID.
  • Hi Sayed,

    Thanks for taking the time to respond.

    I had tried using a property join on both sides, however it still yielded the same result where 1IM would not match the two records.

    I managed to get around the issue but not creating a Column Group, and manually selecting two columns as "preferred primary keys" and mapping appropriate virtual attributes to them from 1IM.

    It would seem the synchronisation project exhibits strange behaviour with using Column Groups as primary keys.

    Cheers.