How to query a table via a scauto event

[tommy]

It is possible to query tables in sc via a scauto-event.

Following example shows how to query status on a call.

First an event must be registered, this is done in Utilities->Event Services->Administration->Registration.

Fill in following fields:

On notepad Basics

Event Code: choose a name for the event
Input or Output: choose Input
Delete condition: decide wether the event should be deleted in succesfull completion, fill in false in testphase.

On notepad Application

Application name: enter the name of an application it could be script.execute
Execute condition: fill in false which means that the application will never be executed.

On notepad Expressions

This is here all the code resides, it is not necesary to run an application in an event.


Following lines that start with -> should be pasted into the lines on the Expressions notepad. Dont paste the ->, and dont paste the lines beginning with ## that is just my comments.

I recommend to use one line per expression but all can be put on one line just delimit them with semicolon.


->$md.smid=1 in $axces.fields
## The first value in the input event is the call number to query, the events fields is helt in the variable $axces.fields

->$x=rtecall("rinit", $L.rc, $smquery, "incidents")
## Use rtecall to initialise the table we want to query
## "rinit" = rutine to call
## $L.rc = return code
## $smquery = the variable that will hold the data from the returned call
## "incidents" = the table to query

->$x=rtecall("select", $L.rc, $smquery, "incident.id=""+$md.smid+""")
## Run the query
## "select" = rutine to call
## $L.rc = return code
## $smquery = the variable that will hold the data from the returned call
## The last parameter is the query to run, since the query should be in the format incident.id="CALL1022"
## it is necesary to use the escape character to tell sc that a " should be part of a string.

->$md.smquery=open in $smquery+"^"+owner.name in $smquery
## Setup the text that should be returned in the output event (Status and Owner)

->$x=rtecall("rinit", $L.rc, $tkjevent, "eventout")
## Use rtecall to initialise the eventout table to prepare for writing an output event
## "rinit" = rutine to call
## $L.rc = return code
## $tkjevent = the variable to prepare to write to the eventout table

->evtype in $tkjevent="smquery"
## evtype is the name of the output event

->evsepchar in $tkjevent="^"
## Which character is used as delimiter between fields

->evexpire in $tkjevent=str(tod())
## datetime stamp

->evfields in $tkjevent=$md.smquery
## set the output fields to the prepared tekst

->$x=rtecall("getnumber", $L.rc, $L.number, "event", $L.field)
## Use rtecall to generate a unique number in the eventout record
## "getnumber" = rutine to call
## $L.rc = return code
## $L.number = number will also be returned in this field
## "event" = which number record to use
## $L.field = cant remember why this was necesary

->evsysseq in $tkjevent=str($L.number)
## put the generated number in the event record

->$x=rtecall("radd", $L.rc, $tkjevent)
## Write the event record to the file

When this has been saved it is necesary to restart sc in order to get the globallists updated.

To test the new event go to the input event queue and add an event


Fill in following fields:
Event code: The name choosen when creating the event registration
System sequence: A unique number
First expiration: a datetime stamp
External information string: The number of a call, including any prefix or postfix

Click add and wait a few minutes, then go to the event out queue. If evenrything works an output event will be in the queue.

The code without comments - easier to read

$md.smid=1 in $axces.fields
$x=rtecall("rinit", $L.rc, $smquery, "incidents")
$x=rtecall("select", $L.rc, $smquery, "incident.id=""+$md.smid+""")
$md.smquery=open in $smquery+"^"+owner.name in $smquery
$x=rtecall("rinit", $L.rc, $tkjevent, "eventout")
evtype in $tkjevent="smquery"
evsepchar in $tkjevent="^"
evexpire in $tkjevent=str(tod())
evfields in $tkjevent=$md.smquery
$x=rtecall("getnumber", $L.rc, $L.number, "event", $L.field)
evsysseq in $tkjevent=str($L.number)
$x=rtecall("radd", $L.rc, $tkjevent)

[ba72nsc]

We are thinking of using SCAuto to perform our contact load from our HR system. We have some business rules that make a straight import insufficient.



Before adding, updating, or deleting a contact, we have a need to read SC tables to apply our business rules. If the business rules are not satisfied, we will write to a exception table that will be taken in the next run with the regular file.



We are thinking of doing this process with a VB program. If we use your method below for readimg tables, how will the program know when the event has actually processed? Will we need to loop until the output event is in the queue?



Thanks.

[tommy]

Yes the program would have to loop until the output event has been generated. But that should not take too long depending on how well your system performes.



But maybe You should consider writing your custom application in Delphi, that might result in a faster process then VB can provide.

[ba72nsc]

The rtecall functions seem to be a very powerful tool to access RAD without writing RAD code. Is there any way these functions can be called from a program such as VB or a Windows batch file?



Also, when ServiceCenter retrieves a record (e.g. a problem or a contact), this is done instantly, without having to wait for an event to execute. Can this type of access be done from an external source, such as a VB program, or is this type of access built into RAD itself and not accessible to an external module?



Thanks again.



Nick

[tommy]

I am not sure You can do that directly.



Maybe it is possible to do something through the DDE interface. The DDE interface is used to establish interaction between SC and Computer Telephony. I have no experience with that yet.

[TimF]

do you think this approach is appropriate/possible for a general query for records eg. "find all open problems for logical.name = XXX and priority=1"

I suppose it would need to put multiple records into the output queue ??

thanks,

Tim

[tommy]

Yes I think that could be done and agree that it would require one output event per result in the query.

[TimF]

OK, I've got a general query working that can return 1 record and write 1 output event. How do you 'loop' inside an expression to output multiple records?

TIA

[Marcade]

We are thinking of doing this process with a VB program. If we use your method below for readimg tables, how will the program know when the event has actually processed? Will we need to loop until the output event is in the queue?

You will have to mark the 'syncronously' field in the event register.

Whenever you will shoot in an event in the eventin table using SC automate, the event immediately will get triggered and run.

SC automate will *not* report anything back to your Visual Basic application, until it is completely done with processing the event register.

Thus if the event register runs a script which plays around with RTEcalls, those first will be processed and when it is done (and your script has created eventout records already) your SC Automate will give an "ok" to your Visual Basic application.

I'm not sure wether there is something like a timeout built in SC Automate; but that easily can be made in Visual Basic.

..

Does anyone know by the way what Peregrine plans to do with SC Automate in future? I heard some rumors that they will stop support for it, and want to concentrate on Connect IT. SC Automate isn't exactly secure.