I've got CRM Customer Support on ARS 4.03 and SQL 7.0. I've created several of my own tables in the ARSystem database to hold data which is pulled in from an external DB of customer information using DTA. Only brand new records or records which have been recently updated in the external database will be held in these tables.

I'm using Direct SQL statements in filters to update existing CRM Contact records from these tables (such as phone number changes, address changes, etc). However, I cannot find a way to *create* new Contact records using Remedy workflow for new user profiles pulled into my SQL tables. Is there an API I can call in a Run Process action to do this? Or any other way which might be possible besides inserting the record directly through SQL? I'd like to use Remedy workflow to avoid problems with Request ID and other Remedy system fields. Thanks in advance!

~Mike Balogh
Alpha Technologies, Inc.

Exchange/Outlook. We also use a Remedy form called 'Customers'. The
'Customers' database ONLY contains info that is NOT in the Exchange
Addressbook, along with the user's 'Alias' - used as the foreign and primary
keys in the Customers table and the Exchange database. the Customers form
does have fields for name, phone number, building, room, etc. However,
these are display-only fields that are set via SQL-Set Fields 'On Display'.
We use the Customers for to track users that are on our "VIP List", etc.

I've setup triggers against the exchange SQL table (an export of the
Exchange DB) that fire on insert, update, delete. These triggers add,
modify, or delete records via SQL in the Customers-related tables (T123,
H123). Setting Core/System and required fields is easy enough - once you
get the hang of it. And a simple query helps determine what to use for the
value for C1 and EntryID (T123 and H123, respectively).

Ex: select nextid from arshema where schemaid = 123 --this provides the
value you need to insert for the unique record id, don't forget to include
leading zeros - as appropriate.

Idea #2: Another way that WILL enforce your workflow is to export your
external db via DTA, then use ARImport - via run process - to import/update
your CRM Contacts.

I've done this both ways, but I'm sticking with the first method at this
time. One reason is because it's immediate - changes made in the sql
exchange db are pushed to the Customers form right away.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

-----Original Message-----
From: Michael Balogh [ mailto:Mbalogh@ALPHA88.COM
]
Sent: Thursday, July 06, 2000 9:52 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: creating Contact records



Hi -

I've got CRM Customer Support on ARS 4.03 and SQL 7.0. I've created several
of my own tables in the ARSystem database to hold data which is pulled in
from an external DB of customer information using DTA. Only brand new
records or records which have been recently updated in the external database
will be held in these tables.

I'm using Direct SQL statements in filters to update existing CRM Contact
records from these tables (such as phone number changes, address changes,
etc). However, I cannot find a way to *create* new Contact records using
Remedy workflow for new user profiles pulled into my SQL tables. Is there
an API I can call in a Run Process action to do this? Or any other way
which might be possible besides inserting the record directly through SQL?
I'd like to use Remedy workflow to avoid problems with Request ID and other
Remedy system fields. Thanks in advance!

~Mike Balogh
Alpha Technologies, Inc.

I have lots of questions for ya. I'd like more info on your setup with
triggers against the exchange SQL table. 1st, I have questions about the
exchange SQL table, our exchange guys say that exchange uses a flat file to
store users info, are they incorrect? Also, do you have an interactive
setup between exchange and remedy? do you modify users info in just Remedy
or just exchange or do you have to do it in both? if you only have to
update one or the other, are the changes implemented immediately? and can
you explain in further detail.

I'm very interested in a integration between remedy and exchange and user
manager. I would like the main interface to be Remedy. I have recieved
perl scripts from another lister but they were only form Usrmnger.
Unfortunately I haven't had time to go over them and I'd still like to
collect more information before I get started on that project.

Thanks
SrA Sherri A. Moore

-----Original Message-----
From: Evans, Eric W. [mailto:evanse@EUCOM.MIL]
Sent: Friday, July 07, 2000 12:58 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


We're doing something similar here. We're running SQL 7.0 as well and use
Exchange/Outlook. We also use a Remedy form called 'Customers'. The
'Customers' database ONLY contains info that is NOT in the Exchange
Addressbook, along with the user's 'Alias' - used as the foreign and primary
keys in the Customers table and the Exchange database. the Customers form
does have fields for name, phone number, building, room, etc. However,
these are display-only fields that are set via SQL-Set Fields 'On Display'.
We use the Customers for to track users that are on our "VIP List", etc.

I've setup triggers against the exchange SQL table (an export of the
Exchange DB) that fire on insert, update, delete. These triggers add,
modify, or delete records via SQL in the Customers-related tables (T123,
H123). Setting Core/System and required fields is easy enough - once you
get the hang of it. And a simple query helps determine what to use for the
value for C1 and EntryID (T123 and H123, respectively).

Ex: select nextid from arshema where schemaid = 123 --this provides the
value you need to insert for the unique record id, don't forget to include
leading zeros - as appropriate.

Idea #2: Another way that WILL enforce your workflow is to export your
external db via DTA, then use ARImport - via run process - to import/update
your CRM Contacts.

I've done this both ways, but I'm sticking with the first method at this
time. One reason is because it's immediate - changes made in the sql
exchange db are pushed to the Customers form right away.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

-----Original Message-----
From: Michael Balogh [ mailto:Mbalogh@ALPHA88.COM
]
Sent: Thursday, July 06, 2000 9:52 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: creating Contact records



Hi -

I've got CRM Customer Support on ARS 4.03 and SQL 7.0. I've created several
of my own tables in the ARSystem database to hold data which is pulled in
from an external DB of customer information using DTA. Only brand new
records or records which have been recently updated in the external database
will be held in these tables.

I'm using Direct SQL statements in filters to update existing CRM Contact
records from these tables (such as phone number changes, address changes,
etc). However, I cannot find a way to *create* new Contact records using
Remedy workflow for new user profiles pulled into my SQL tables. Is there
an API I can call in a Run Process action to do this? Or any other way
which might be possible besides inserting the record directly through SQL?
I'd like to use Remedy workflow to avoid problems with Request ID and other
Remedy system fields. Thanks in advance!

~Mike Balogh
Alpha Technologies, Inc.

Yes, I'd agree with your exchange gurus. I don't know if I'd call it a
'flat-file', but it's sure not SQL.

So, we export the Exchange Addressbook to (what I WOULD call) a flat-file,
comma-delimited (*.csv), then this is imported into an SQL table. All of
this is automated, running nightly. Your Exchange gurus will know how to do
this, in case you don't - or don't have Exchange admin permissions.

Note: In our setup, we only keep customer info in Remedy that isn't
available in the Exchange Addressbook. Or, if the info provided by a
customer when they call the HelpDesk is different from what we have in
Exchange.

Ex #1: Customer doesn't have an Exchange account.

Scenario: HelpDesk begins to enter a new ticket for a customer, the contact
info (phone, full name, etc) is auto-filled from the SQL-Exchange exported
table. This occurs via an AL that fires on ENTER for the 'Name' field.
HelpDesk just types in the first few characters of the name, hits ENTER.
Then, they select the correct customer from the picklist. This is a very
common way to do this.

If none of the Customer choices presented is correct, HelpDesk selects 'None
of These'. This opens a dialog form that HelpDesk fills-in. HelpDesk
clicks OK, the info is passed to the ticket form and a NEW record is created
in the Customers schema.

Ex #2: Customer's info in Exchange isn't correct.

HelpDesk always double-checks contact info with the customer, before
submitting a new ticket. If the customer says the phone number is wrong,
for instance, HelpDesk clicks an 'Update' button on our "Service Request"
form which opens a dialog with the customer's info filled-in. HelpDesk
changes the phone number, then clicks OK. The dialog closes, passes the new
contact info to the new ticket form (that's on-screen) AND 'pushes' the
updated info to all OPEN tickets for that customer. Also, a new record is
created in our 'Customers' schema.

From here, it's possible to use info in the Customers SQL table to update
info in the Exchange Addressbook. Your Exchange gurus will know how to do
this, too; update from SQL to Exchange. This way, you can update Exchange
Addressbook info via data input into a Remedy form. Also, when the Exhange
SQL table is updated with a customer's correct info, you can have the Remedy
Customers table entry auto-removed - it's not needed if the Addressbook is
correct.

Clear as mud? It's early here, so I hope this makes sense.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

PS: Once upon a time, I was a SrA, too. ;-)

-----Original Message-----
From: Moore, Sherri A SrA 341CS/SCBBH
[mailto:Sherri.Moore@MALMSTROM.AF.MIL]
Sent: Friday, July 07, 2000 6:44 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


Hey Eric,
I have lots of questions for ya. I'd like more info on your setup with
triggers against the exchange SQL table. 1st, I have questions about the
exchange SQL table, our exchange guys say that exchange uses a flat file to
store users info, are they incorrect? Also, do you have an interactive
setup between exchange and remedy? do you modify users info in just Remedy
or just exchange or do you have to do it in both? if you only have to
update one or the other, are the changes implemented immediately? and can
you explain in further detail.

I'm very interested in a integration between remedy and exchange and user
manager. I would like the main interface to be Remedy. I have recieved
perl scripts from another lister but they were only form Usrmnger.
Unfortunately I haven't had time to go over them and I'd still like to
collect more information before I get started on that project.

Thanks
SrA Sherri A. Moore

-----Original Message-----
From: Evans, Eric W. [mailto:evanse@EUCOM.MIL]
Sent: Friday, July 07, 2000 12:58 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


We're doing something similar here. We're running SQL 7.0 as well and use
Exchange/Outlook. We also use a Remedy form called 'Customers'. The
'Customers' database ONLY contains info that is NOT in the Exchange
Addressbook, along with the user's 'Alias' - used as the foreign and primary
keys in the Customers table and the Exchange database. the Customers form
does have fields for name, phone number, building, room, etc. However,
these are display-only fields that are set via SQL-Set Fields 'On Display'.
We use the Customers for to track users that are on our "VIP List", etc.

I've setup triggers against the exchange SQL table (an export of the
Exchange DB) that fire on insert, update, delete. These triggers add,
modify, or delete records via SQL in the Customers-related tables (T123,
H123). Setting Core/System and required fields is easy enough - once you
get the hang of it. And a simple query helps determine what to use for the
value for C1 and EntryID (T123 and H123, respectively).

Ex: select nextid from arshema where schemaid = 123 --this provides the
value you need to insert for the unique record id, don't forget to include
leading zeros - as appropriate.

Idea #2: Another way that WILL enforce your workflow is to export your
external db via DTA, then use ARImport - via run process - to import/update
your CRM Contacts.

I've done this both ways, but I'm sticking with the first method at this
time. One reason is because it's immediate - changes made in the sql
exchange db are pushed to the Customers form right away.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

-----Original Message-----
From: Michael Balogh [ mailto:Mbalogh@ALPHA88.COM
]
Sent: Thursday, July 06, 2000 9:52 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: creating Contact records



Hi -

I've got CRM Customer Support on ARS 4.03 and SQL 7.0. I've created several
of my own tables in the ARSystem database to hold data which is pulled in
from an external DB of customer information using DTA. Only brand new
records or records which have been recently updated in the external database
will be held in these tables.

I'm using Direct SQL statements in filters to update existing CRM Contact
records from these tables (such as phone number changes, address changes,
etc). However, I cannot find a way to *create* new Contact records using
Remedy workflow for new user profiles pulled into my SQL tables. Is there
an API I can call in a Run Process action to do this? Or any other way
which might be possible besides inserting the record directly through SQL?
I'd like to use Remedy workflow to avoid problems with Request ID and other
Remedy system fields. Thanks in advance!

~Mike Balogh
Alpha Technologies, Inc.

questions about your process. Once you have modified or created the persons
info in the Customer form, I was wondering what steps you take to modify NT
User Manger? Do you have any automation with this or do they modify User
Manager seperately? Also, how do you go about deactivating accounts?

Thanks for all the info,
SrA Sherri A. Moore
PS So are you out of Ramstein? Do you work with or know of MSgtTurbyfill or
SSgt King. They presented a lot of information to CITS in regards to the
TTS. They seem to know a bit about the application either that or someone
that works for them does. Are you using a modified TTS app? If so would
you be willing to share some more info about your modifications? You guys
should be more advanced since you've had the new TTS app longer than anyone
else. I know a bunch of other bases would appreciate it.



-----Original Message-----
From: Evans, Eric W. [mailto:evanse@EUCOM.MIL]
Sent: Monday, July 10, 2000 1:57 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


Hi, Sherri.

Yes, I'd agree with your exchange gurus. I don't know if I'd call it a
'flat-file', but it's sure not SQL.

So, we export the Exchange Addressbook to (what I WOULD call) a flat-file,
comma-delimited (*.csv), then this is imported into an SQL table. All of
this is automated, running nightly. Your Exchange gurus will know how to do
this, in case you don't - or don't have Exchange admin permissions.

Note: In our setup, we only keep customer info in Remedy that isn't
available in the Exchange Addressbook. Or, if the info provided by a
customer when they call the HelpDesk is different from what we have in
Exchange.

Ex #1: Customer doesn't have an Exchange account.

Scenario: HelpDesk begins to enter a new ticket for a customer, the contact
info (phone, full name, etc) is auto-filled from the SQL-Exchange exported
table. This occurs via an AL that fires on ENTER for the 'Name' field.
HelpDesk just types in the first few characters of the name, hits ENTER.
Then, they select the correct customer from the picklist. This is a very
common way to do this.

If none of the Customer choices presented is correct, HelpDesk selects 'None
of These'. This opens a dialog form that HelpDesk fills-in. HelpDesk
clicks OK, the info is passed to the ticket form and a NEW record is created
in the Customers schema.

Ex #2: Customer's info in Exchange isn't correct.

HelpDesk always double-checks contact info with the customer, before
submitting a new ticket. If the customer says the phone number is wrong,
for instance, HelpDesk clicks an 'Update' button on our "Service Request"
form which opens a dialog with the customer's info filled-in. HelpDesk
changes the phone number, then clicks OK. The dialog closes, passes the new
contact info to the new ticket form (that's on-screen) AND 'pushes' the
updated info to all OPEN tickets for that customer. Also, a new record is
created in our 'Customers' schema.

From here, it's possible to use info in the Customers SQL table to update
info in the Exchange Addressbook. Your Exchange gurus will know how to do
this, too; update from SQL to Exchange. This way, you can update Exchange
Addressbook info via data input into a Remedy form. Also, when the Exhange
SQL table is updated with a customer's correct info, you can have the Remedy
Customers table entry auto-removed - it's not needed if the Addressbook is
correct.

Clear as mud? It's early here, so I hope this makes sense.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

PS: Once upon a time, I was a SrA, too. ;-)

-----Original Message-----
From: Moore, Sherri A SrA 341CS/SCBBH
[mailto:Sherri.Moore@MALMSTROM.AF.MIL]
Sent: Friday, July 07, 2000 6:44 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


Hey Eric,
I have lots of questions for ya. I'd like more info on your setup with
triggers against the exchange SQL table. 1st, I have questions about the
exchange SQL table, our exchange guys say that exchange uses a flat file to
store users info, are they incorrect? Also, do you have an interactive
setup between exchange and remedy? do you modify users info in just Remedy
or just exchange or do you have to do it in both? if you only have to
update one or the other, are the changes implemented immediately? and can
you explain in further detail.

I'm very interested in a integration between remedy and exchange and user
manager. I would like the main interface to be Remedy. I have recieved
perl scripts from another lister but they were only form Usrmnger.
Unfortunately I haven't had time to go over them and I'd still like to
collect more information before I get started on that project.

Thanks
SrA Sherri A. Moore

-----Original Message-----
From: Evans, Eric W. [mailto:evanse@EUCOM.MIL]
Sent: Friday, July 07, 2000 12:58 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


We're doing something similar here. We're running SQL 7.0 as well and use
Exchange/Outlook. We also use a Remedy form called 'Customers'. The
'Customers' database ONLY contains info that is NOT in the Exchange
Addressbook, along with the user's 'Alias' - used as the foreign and primary
keys in the Customers table and the Exchange database. the Customers form
does have fields for name, phone number, building, room, etc. However,
these are display-only fields that are set via SQL-Set Fields 'On Display'.
We use the Customers for to track users that are on our "VIP List", etc.

I've setup triggers against the exchange SQL table (an export of the
Exchange DB) that fire on insert, update, delete. These triggers add,
modify, or delete records via SQL in the Customers-related tables (T123,
H123). Setting Core/System and required fields is easy enough - once you
get the hang of it. And a simple query helps determine what to use for the
value for C1 and EntryID (T123 and H123, respectively).

Ex: select nextid from arshema where schemaid = 123 --this provides the
value you need to insert for the unique record id, don't forget to include
leading zeros - as appropriate.

Idea #2: Another way that WILL enforce your workflow is to export your
external db via DTA, then use ARImport - via run process - to import/update
your CRM Contacts.

I've done this both ways, but I'm sticking with the first method at this
time. One reason is because it's immediate - changes made in the sql
exchange db are pushed to the Customers form right away.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

-----Original Message-----
From: Michael Balogh [ mailto:Mbalogh@ALPHA88.COM
]
Sent: Thursday, July 06, 2000 9:52 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: creating Contact records



Hi -

I've got CRM Customer Support on ARS 4.03 and SQL 7.0. I've created several
of my own tables in the ARSystem database to hold data which is pulled in
from an external DB of customer information using DTA. Only brand new
records or records which have been recently updated in the external database
will be held in these tables.

I'm using Direct SQL statements in filters to update existing CRM Contact
records from these tables (such as phone number changes, address changes,
etc). However, I cannot find a way to *create* new Contact records using
Remedy workflow for new user profiles pulled into my SQL tables. Is there
an API I can call in a Run Process action to do this? Or any other way
which might be possible besides inserting the record directly through SQL?
I'd like to use Remedy workflow to avoid problems with Request ID and other
Remedy system fields. Thanks in advance!

~Mike Balogh
Alpha Technologies, Inc.

You're very welcome for the reply.

Actually, we're not modifying data in User Manager, we're only concerned
with contact info, so we're updating phone number, work location, rank, etc
between Remedy and the Exchange Addressbook.

We DO use SQL tables to update accounts and not just addressbook info, to
include additions, deletions, permissions, group membership, etc. However,
we aren't using a Remedy form as a front-end; although there's no reason we
couldn't. Changes made with our (non-Remedy-based) Accounts Manager
application write to SQL tables. Then, these tables are used to update user
accounts and Exchange Addressbook, nightly.

As I mentioned before, our Customers schema in Remedy ONLY caontains records
for folks that don't have an entry in our Exchange Addressbook or if their
contact info differs from what we have for them in Exchange.

To 'clean-up' the Customers DB, I run an SQL script nightly that matches
records in the Customers schema to our Exchange Addressbook. If the Alias
matches in both tables (Customers and Exchange) AND all the contact info is
the same, I delete the record in the Customers tables (t.customers and
h.customers).

Good luck.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

PS Reply: I'm working at Patch Barracks, Stuttgart - rather than Ramstein.
I haven't had the pleasure of meeting the two SGTs you mention. Also, I'm
not familiar with 'TTS'. I've designed all of our Remedy-based apps from
the ground, up.

-----Original Message-----
From: Moore, Sherri A SrA 341CS/SCBBH
[mailto:Sherri.Moore@MALMSTROM.AF.MIL]
Sent: Monday, July 10, 2000 6:05 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


Thanks for the thorough reply. Unfortunately I still have some more
questions about your process. Once you have modified or created the persons
info in the Customer form, I was wondering what steps you take to modify NT
User Manger? Do you have any automation with this or do they modify User
Manager seperately? Also, how do you go about deactivating accounts?

Thanks for all the info,
SrA Sherri A. Moore
PS So are you out of Ramstein? Do you work with or know of MSgtTurbyfill or
SSgt King. They presented a lot of information to CITS in regards to the
TTS. They seem to know a bit about the application either that or someone
that works for them does. Are you using a modified TTS app? If so would
you be willing to share some more info about your modifications? You guys
should be more advanced since you've had the new TTS app longer than anyone
else. I know a bunch of other bases would appreciate it.



-----Original Message-----
From: Evans, Eric W. [mailto:evanse@EUCOM.MIL]
Sent: Monday, July 10, 2000 1:57 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


Hi, Sherri.

Yes, I'd agree with your exchange gurus. I don't know if I'd call it a
'flat-file', but it's sure not SQL.

So, we export the Exchange Addressbook to (what I WOULD call) a flat-file,
comma-delimited (*.csv), then this is imported into an SQL table. All of
this is automated, running nightly. Your Exchange gurus will know how to do
this, in case you don't - or don't have Exchange admin permissions.

Note: In our setup, we only keep customer info in Remedy that isn't
available in the Exchange Addressbook. Or, if the info provided by a
customer when they call the HelpDesk is different from what we have in
Exchange.

Ex #1: Customer doesn't have an Exchange account.

Scenario: HelpDesk begins to enter a new ticket for a customer, the contact
info (phone, full name, etc) is auto-filled from the SQL-Exchange exported
table. This occurs via an AL that fires on ENTER for the 'Name' field.
HelpDesk just types in the first few characters of the name, hits ENTER.
Then, they select the correct customer from the picklist. This is a very
common way to do this.

If none of the Customer choices presented is correct, HelpDesk selects 'None
of These'. This opens a dialog form that HelpDesk fills-in. HelpDesk
clicks OK, the info is passed to the ticket form and a NEW record is created
in the Customers schema.

Ex #2: Customer's info in Exchange isn't correct.

HelpDesk always double-checks contact info with the customer, before
submitting a new ticket. If the customer says the phone number is wrong,
for instance, HelpDesk clicks an 'Update' button on our "Service Request"
form which opens a dialog with the customer's info filled-in. HelpDesk
changes the phone number, then clicks OK. The dialog closes, passes the new
contact info to the new ticket form (that's on-screen) AND 'pushes' the
updated info to all OPEN tickets for that customer. Also, a new record is
created in our 'Customers' schema.

From here, it's possible to use info in the Customers SQL table to update
info in the Exchange Addressbook. Your Exchange gurus will know how to do
this, too; update from SQL to Exchange. This way, you can update Exchange
Addressbook info via data input into a Remedy form. Also, when the Exhange
SQL table is updated with a customer's correct info, you can have the Remedy
Customers table entry auto-removed - it's not needed if the Addressbook is
correct.

Clear as mud? It's early here, so I hope this makes sense.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

PS: Once upon a time, I was a SrA, too. ;-)

-----Original Message-----
From: Moore, Sherri A SrA 341CS/SCBBH
[mailto:Sherri.Moore@MALMSTROM.AF.MIL]
Sent: Friday, July 07, 2000 6:44 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


Hey Eric,
I have lots of questions for ya. I'd like more info on your setup with
triggers against the exchange SQL table. 1st, I have questions about the
exchange SQL table, our exchange guys say that exchange uses a flat file to
store users info, are they incorrect? Also, do you have an interactive
setup between exchange and remedy? do you modify users info in just Remedy
or just exchange or do you have to do it in both? if you only have to
update one or the other, are the changes implemented immediately? and can
you explain in further detail.

I'm very interested in a integration between remedy and exchange and user
manager. I would like the main interface to be Remedy. I have recieved
perl scripts from another lister but they were only form Usrmnger.
Unfortunately I haven't had time to go over them and I'd still like to
collect more information before I get started on that project.

Thanks
SrA Sherri A. Moore

-----Original Message-----
From: Evans, Eric W. [mailto:evanse@EUCOM.MIL]
Sent: Friday, July 07, 2000 12:58 AM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: Re: creating Contact records


We're doing something similar here. We're running SQL 7.0 as well and use
Exchange/Outlook. We also use a Remedy form called 'Customers'. The
'Customers' database ONLY contains info that is NOT in the Exchange
Addressbook, along with the user's 'Alias' - used as the foreign and primary
keys in the Customers table and the Exchange database. the Customers form
does have fields for name, phone number, building, room, etc. However,
these are display-only fields that are set via SQL-Set Fields 'On Display'.
We use the Customers for to track users that are on our "VIP List", etc.

I've setup triggers against the exchange SQL table (an export of the
Exchange DB) that fire on insert, update, delete. These triggers add,
modify, or delete records via SQL in the Customers-related tables (T123,
H123). Setting Core/System and required fields is easy enough - once you
get the hang of it. And a simple query helps determine what to use for the
value for C1 and EntryID (T123 and H123, respectively).

Ex: select nextid from arshema where schemaid = 123 --this provides the
value you need to insert for the unique record id, don't forget to include
leading zeros - as appropriate.

Idea #2: Another way that WILL enforce your workflow is to export your
external db via DTA, then use ARImport - via run process - to import/update
your CRM Contacts.

I've done this both ways, but I'm sticking with the first method at this
time. One reason is because it's immediate - changes made in the sql
exchange db are pushed to the Customers form right away.

Hope this helps.

Eric
Eric Evans
Senior Remedy Administrator
Computer Sciences Corporation
HQ USEUCOM, ECJ6-Engineering
Stuttgart, Germany

-----Original Message-----
From: Michael Balogh [ mailto:Mbalogh@ALPHA88.COM
]
Sent: Thursday, July 06, 2000 9:52 PM
To: ARSLIST@LISTSERV.VISTAIT.COM
Subject: creating Contact records



Hi -

I've got CRM Customer Support on ARS 4.03 and SQL 7.0. I've created several
of my own tables in the ARSystem database to hold data which is pulled in
from an external DB of customer information using DTA. Only brand new
records or records which have been recently updated in the external database
will be held in these tables.

I'm using Direct SQL statements in filters to update existing CRM Contact
records from these tables (such as phone number changes, address changes,
etc). However, I cannot find a way to *create* new Contact records using
Remedy workflow for new user profiles pulled into my SQL tables. Is there
an API I can call in a Run Process action to do this? Or any other way
which might be possible besides inserting the record directly through SQL?
I'd like to use Remedy workflow to avoid problems with Request ID and other
Remedy system fields. Thanks in advance!

~Mike Balogh
Alpha Technologies, Inc.