Highlighted
Absent Member.. Absent Member..
Absent Member..
280 views

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

Jump to solution

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 'contact.name').

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
  pifIgnoreDocumentMapping
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,

 

Ulises

Tags (1)
0 Likes
1 Solution

Accepted Solutions
Highlighted
Absent Member.. Absent Member..
Absent Member..

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

Jump to solution

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): 

lookup.png

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")
Else
PifLogInfoMsg("TITLE: "+title+", Contact needs to be created")
End If
else
retval=pifIgnoreDocumentMapping()
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,

Regards,

Enrique Lobo B.  

 

View solution in original post

4 Replies
Highlighted
Absent Member.. Absent Member..
Absent Member..

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

Jump to solution

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): 

lookup.png

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")
Else
PifLogInfoMsg("TITLE: "+title+", Contact needs to be created")
End If
else
retval=pifIgnoreDocumentMapping()
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,

Regards,

Enrique Lobo B.  

 

View solution in original post

Highlighted
Absent Member.. Absent Member..
Absent Member..

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

Jump to solution

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?

 

Regards,

 

Ulises

0 Likes
Highlighted
Absent Member.. Absent Member..
Absent Member..

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

Jump to solution

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

Regards, 

Highlighted
Absent Member.. Absent Member..
Absent Member..

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

Jump to solution

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.

Nicolas

The opinions expressed above are the personal opinions of the authors, not of Micro Focus. By using this site, you accept the Terms of Use and Rules of Participation. Certain versions of content ("Material") accessible here may contain branding from Hewlett-Packard Company (now HP Inc.) and Hewlett Packard Enterprise Company. As of September 1, 2017, the Material is now offered by Micro Focus, a separately owned and operated company. Any reference to the HP and Hewlett Packard Enterprise/HPE marks is historical in nature, and the HP and Hewlett Packard Enterprise/HPE marks are the property of their respective owners.