Question about adding/update records in contact table using Connect-IT Scenario

Dear folks,

We use the enclosed scenario to add/update records to the Service Manager contact table; the input is a text file (sample included) and the scenario either adds a record or update it depending on the existense of the 'Hire' flag at the end of the record.

Our problem is the following: some contacts already exist in the contact record because they had some previous relationship with our company (usually as contractors) and were hired.  In that case, their record will come in with the 'Hire' tag but will not be added because they already have a record on the table (they keep using the old id as the '').

One way around this will be to check the "title" field, and if it is "contractor" that's will tell me that I need to update this record instead of adding it.  Right now I do this check with the "Hire" tag in the mapping script for the "Create Contact" action:


if Mid([Action],1,4) <> "Hire" then
end if

That will send the incoming record to the "UpdateContact" branch of the scenario, but in this case the incoming record has the "Hire" tag, causing the problem that I'm having.  In case you're asking, I can't have the source record changed to eliminate the "Hire" tag for this situation because to the source system that produces that data this is a new employee and they have no way of knowing that it correspond to someone who already was in SM contact table. 

So how do I update this mapping script to check for the precense of the "Contractor" value on the contact record? Is that the right way to go about this? 

Best regards,




  • Verified Answer

    Hello Ulises,

      Have you tried using a Lookup connector? 

    For example, you can have an DB or SM connector pointing to the contacts table without being connected to the "scenario workflow", something like this (DB connector will be faster): 

    Then, what I would do on the same Hire flag mapping, is to add a query to look for the title field and add it to your IF statement. Something like:

    dim iRc as long
    dim hQuery as long
    dim status as String

    hQuery = pifNewQueryFromFmtName("lookupSM", "CONTACTSM1", "CONTAC.NAME="&chr(39)&[CONTACT NAME]&chr(39))
    iRc = pifQueryNext(hQuery)

    if iRc = 0 then
    title = pifQueryGetStringVal(hQuery, "TITLE")
    if(title="contractor") then
    PifLogInfoMsg("TITLE: " title ", Contact needs update")
    PifLogInfoMsg("TITLE: " title ", Contact needs to be created")
    End If
    End If
    iRc = pifQueryClose(hQuery)

    The idea would be to also check the Title field at the same time the validation for hire happens, you may need to adjust the code above to make it logic on your code.

    Let me know if you think this could work,


    Enrique Lobo B.  


  • Enrique,

    That seems like something that would work, but I don't have a lookup connector (in your post you seems to suggest a connector to query the database itself).  Can I do that with other connector, like another one connector to Service Manager itself just for the purpose of querying the contact table?  Or what if I just create a flat file with all the current contacts and their title and load it using the Delimited text connector?  Would that work the same way?





  • Hello Ulises,

       Yes, it should work. I have used a SM connector on the same way, the only thing that may differ is the last part, how the actual query looks like. (instead of double quotes is single quotes, remeber to use the char(x) to reference any special character).

    You may want to look for the actual function doing pifNewQueryFromFmtName


  • Hi,

    If you want to use a floating request/connector (very usefull), you can do it several ways :

    - Floating request, used in general to get a value already in a table of the targeted system. You  can put it in the existing connector or put it in a "floating" connector.

    - Floating connector (floating SM connector in your case), used when you need to query (a lot) on the targeted system.

    The choice to use a floating connector depends essentially of the number of query. If you query the destination system each time, you better have to create a floating connector instead of using the destination connector. Technically, you can do both, the main difference is that you could have separated thread by using floating connector and therefore use separated memory allocations. Another point, when you create a query in the floating connector, be carreful not to check the production checkbox. If you have a query Connect.It will do the job but if you check the box the all bunch of result will be produced each time so depending of how you manage your code, it could dramatically increase the execution time.

    Hope it helps.