Hi Listers!!
I have a problem, I need to create entries in a remedy form, this entries are generated by query with a group by instruction.
anybody can to provide me a API? or where can I download any samples??
Thanks and greetings


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Guillermo,
Let me open with the possibility that you may not need an API
program to get this job done. :)

1) If the data in an a table that the Remedy server can access:
How about:
An escalation to select the records that you want to "create" based on...
and a PUSH action that creates a record in the target form using
a PUSH IF qualification that only produces one record per "group by"
condition?

2a) If the data is in a table that the Remedy server can not access...
Fix that problem and see option #1. :)

2b) If the data is in a table that the Remedy server can not access,
and you can not change that. How about getting a CSV file from that
table and using the ARIMPORT tool to load the data to a remedy form in
bulk. Then use Remedy work flow to do the work of either "rejecting"
duplicate imports or using a similar PUSH to a child record as
mentioned in Option 1. (This would require no Remedy API work, but
some SQL/OS Scripting to automate the data pull/push activities.)


Now if you need Remedy API samples... see the servers API directory.
There is a very good example program called "Driver"[C API] (or
JavaDriver for the Java API) that uses most ARS API commands.

Hope that helps.

--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)

Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.

http://www.fellowshipchurch.com


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Hi Carey!!
Thanks by your replay..
The form where I do the query is the HPD:Helpdesk, on the table T (t4711)
My querys is the next:

SELECT c536851024,count(*)
FROM t4711
WHERE c3>='inidate' and c3<='enddate'
GROUP BY c536851024

I like the first suggestion, How can I implement this suggestion with a escalation on HPD:Helpdesk??
inidate: is the initial date of the actual month, p.e. 12/01/2004
enddate: Is the end date of the actual month, p.e. 12/31/2004

Happy new year and my best desires for all from Mexico

Greetings




De: Action Request System discussion list(ARSList) en nombre de Carey Matthew Black
Enviado el: Jue 30/12/2004 08:47 a.m.
Para: arslist@ARSLIST.ORG
Asunto: Re: Create Entry



Guillermo,
Let me open with the possibility that you may not need an API
program to get this job done. :)

1) If the data in an a table that the Remedy server can access:
How about:
An escalation to select the records that you want to "create" based on...
and a PUSH action that creates a record in the target form using
a PUSH IF qualification that only produces one record per "group by"
condition?

2a) If the data is in a table that the Remedy server can not access...
Fix that problem and see option #1. :)

2b) If the data is in a table that the Remedy server can not access,
and you can not change that. How about getting a CSV file from that
table and using the ARIMPORT tool to load the data to a remedy form in
bulk. Then use Remedy work flow to do the work of either "rejecting"
duplicate imports or using a similar PUSH to a child record as
mentioned in Option 1. (This would require no Remedy API work, but
some SQL/OS Scripting to automate the data pull/push activities.)


Now if you need Remedy API samples... see the servers API directory.
There is a very good example program called "Driver"[C API] (or
JavaDriver for the Java API) that uses most ARS API commands.

Hope that helps.

--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)

Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.

http://www.fellowshipchurch.com


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)




UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Guillermo Alfredo Torres Barron wrote:
> Hi Carey!!
> Thanks by your replay..
> The form where I do the query is the HPD:Helpdesk, on the table T (t4711)
> My querys is the next:
>
> SELECT c536851024,count(*)

You can count the number of actions.

> FROM t4711
> WHERE c3>='inidate' and c3<='enddate'

This might be difficult to do directly through SQL as Remedy stores
dates in UNIX Epoch format. However, you can do this through a MACRO
(yes they still exist) by using the Advanced Query Bar.

> GROUP BY c536851024

The sort function can do this.

I did something like this at a job I had over two years ago. I used a
Remedy Report to count the number of items and I used the field contents
to list out what it was. You stated that you want to do this for a
secondary form. This should be as easy as running the macro, but I have
no experience doing this.

It sounds like this is what you want to do:

Search through the HPD:HelpDesk (Help Desk Cases) form searching by a
date period. You want to sort and count the entries based upon a field
(536851024) and create one entry in the second form for different data
in the HPD:HelpDesk form.

>
> I like the first suggestion, How can I implement this suggestion with a escalation on HPD:Helpdesk??
> inidate: is the initial date of the actual month, p.e. 12/01/2004
> enddate: Is the end date of the actual month, p.e. 12/31/2004

Now, here is the base question, can you hard code in the init-date and
the end-date without user intervention? If so, then your task is much
easier. If not, then you may have to use a third form to get the dates
from to run the escalation.

James McKenzie


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Guillermo,
So it looks like you want to do this count based on the what tickets
were created in a given month? If so... then I think we may have a
better idea all the way around...


So far you have described the "this entries are generated by query
with a group by instruction" part of your question fairly well... but
you still need to be a bit more specific about "I need to create
entries in a remedy form". For now I will assume you only want a count
of tickets for a given 'C536851024' value for each month. (Best guess
based on your query.)


Since all of this data is Remedy data... then Remedy workflow can be
used in a few ways... But first it looks like you want to create a new
form to hold the results for each 'C536851024' value for each month.
Let us call this new form CountOfData. (Just so that we can talk about
it by name. :)


Depending on how accurate your counts need to be kept.. you can choose
to either run a "batch" type process on the first of every month to
count the tickets for last month. This is along the lines of what you
were thinking originally, but there is a problem with that. The
tickets 'C536851024' values may not be finalized when your batch job
runs. Meaning the tickets might still be open and being worked/changed
after the end of the month batch process. Which make your batch logic
much more complicated than you might have expected. :(


Or

Since you are basing your logic on only two fields. 'Create date'(C3)
which never changes for a given ticket. 'C536851024', which may or may
not change (I have no idea at this point) over the tickets life cycle.
The point here is that when a ticket is created (submit) or modified
you could do a push to the CountOfData form when the 'C536851024'
value changes. When the value changes from NULL to NOT NULL then you
simply add one to a record or create a new one. If the value changes
from NOT NULL to a DIFFERENT NOT NULL, then you have to add one to the
new values record and subtract one from the old values record...


So lets talk about this new CountOfData form... It needs to be a
Regular data form for Remedy. (So it will have all the normal Remedy
Core field.) Plus a few you create for your own use:
'MonthSTART'
This will hold values like... 3/1/2004, or 4/1/2004 etc... (DATETIME)
'MonthEND'
This will hold values like 4/1/2004 or 5/1/2004 (one month value
higher than the value in 'MonthStart'. :) (DATETIME)
'C536851024VALUE'
This is a given 'C536851024' that the count is being kept for..
'Count'
This is the INTEGER count of tickets for the given
'C536851024VALUE' and 'MonthStart' combination.
'zCountChange'
This is a temp INTEGER value to let the work of adding/subtracting
from the 'Count' be done on the CountOfData forms filters. :)
'zCreateDate'
This is a temp DATETIME value used to calculate the 'MonthSTART'
and 'MonthEND' values.
'zMonth'
This is a temp INTEGER value used to calculate the 'MonthSTART' and
'MonthEND' values.
'zYear'
This is a temp INTEGER value used to calculate the 'MonthSTART' and
'MonthEND' values.

NOTE: 'MonthSTART' and 'MonthEND' should be set with filters based on
the 'Create Date' (C3) passed to the record in the Submit/Create of
the records.
NOTE: It would be a good idea to have a unique index on 'MonthStart'
and 'C536851024VALUE'
NOTE: If you want to make 'zMonth' and 'zYear' a non temp field for
other reporting needs then that is fine too... (If and only if needed,
but the rest of the logic does not change.)

You need a filter :Submit/Modify
That would do a set field action to set
'Count' = $Count$ + $zCountChange$

You need a filter :Submit1
That would do a set field action to set
'zMonth' = MONTH($zCreateDate$)
'zYear' = YEAR($zCreateDate$)
New Set Field Action
'MonthSTART' = $zMonth$+"/1/"+$zYear$

You need a filter :Submit2
# correct for last month of year condition
Run If: 'zMonth' = 12
That would do a set field action to set
'zMonth' = 1
'zYear' = $zYear$ +1
Else Action
# Or just add one to the month value
# NOTE: I changed the PUSH qual to use <= and < due to this
simplification. It just makes it easier to not have to find the last
day of the month (31, 30, 28, 29) in multiple calculations.
'zMonth' = $zMonth$ +1

You need a filter :Submit3
That would do a set field action to set
'MonthEND' = $zMonth$+"/1/"+$zYear$



OK... now lets talk about the HD record and what you need there...
A new temp field: 'zOldC536851024VALUE'. This should be the same size
and type as the 'C536851024' field. It can be a "Display Only" field
as well.. (That is what I mean by "temp field")


So let us say that a ticket (HD123) is created with a 'C536851024'
value of "abc".
On Submit of HD...Filter...
Run if: 'C536851024' != NULL
If Actions
Push to CountOfData: PUsh IF: 'MonthSTART'<=$C3$ AND 'MonthEND'
>$C3$ AND 'C536851024VALUE' = $C536851024$
If no match.. create a new
If multiple match... modify all...
Set 'zCountChange' to 1, 'zCreateDate' = $TIMESTAMP$


You will also need a few modify filters...but this gets a bit more tricky... :)
The HD123 ticket changes values of 'C536851024' from "abc" to "HIJ".

On Modify of HD...Filter1...
Run if: 'C536851024' != 'DB.C536851024' AND 'C536851024' != NULL AND
'DB.C536851024' = NULL
# That means when the field changes value AND is not null AND the data
based currently has a null value for this field
# If 'C536851024' is a required field then this is not needed
If Actions
Push to CountOfData: PUsh IF: 'MonthSTART'<=$C3$ AND 'MonthEND'
>$C3$ AND 'C536851024VALUE' = $C536851024$
If no match.. create a new
If multiple match... modify all...
Set 'zCountChange' to 1, 'zCreateDate' = $Create Date$

On Modify of HD...Filter2...
Run if: 'C536851024' != 'DB.C536851024' AND 'C536851024' != NULL AND
'DB.C536851024' != NULL
# That means when the field changes value AND is not null AND was
created/modified with a different NON null value before
# If 'C536851024' is a required field then this is not needed
If Actions
#Update the new 'C536851024VALUE' value record
Push to CountOfData: PUsh IF: 'MonthSTART'<=$C3$ AND 'MonthEND'
>$C3$ AND 'C536851024VALUE' = $C536851024$
If no match.. create a new
If multiple match... modify all...
Set 'zCountChange' to 1, 'zCreateDate' = $Create Date$
# Get the old 'C536851024' value...
Set Fields From the HD form
Set fields if: 'Request ID' = $Request ID$
Set 'zOldC536851024VALUE' = $C536851024$
#Update the old 'C536851024VALUE' value record
# This will decrease the count on the record by one since a ticket was
moved from the 'zOldC536851024VALUE' record to the 'zC536851024VALUE'
record.
Push to CountOfData: PUsh IF: 'MonthSTART'<=$C3$ AND 'MonthEND'
>$C3$ AND 'C536851024VALUE' = $zOldC536851024VALUE$
If no match.. Take no action
If multiple match... modify all...
Set 'zCountChange' to -1


And I think that will keep a to the transaction accurate count of
tickets created in a given month for a specific 'C536851024' value.

Keep in mind that untill all tickets created in a given month are
closed that the count records could keep changing. (Assuming that the
'C536851024' value can change. If it can not be changed then the logic
above gets much simplier too.)

Hope that helps.

--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)

Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.

http://www.fellowshipchurch.com


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)

Guillermo,
Oh... I forgot something... The "count as you go" method works great
for all of your new tickets created after you add the workflow, forms
etc....

But you might need to seed data based on your current data set too.

For that task I would suggest that you draw a line in the sand at the
current 'Request ID' (C1) value when you implement the new "count as
you go" logic. Then you need to take all the tickets older than that
and do a CSV export with 'Create Date' and their 'C536851024' value.

Now this is a manual "batch process", but it works well for this kind
of one time thing.

Create a Display only form that you will use to do the same type of
"Submit" logic push actions from (as you did from the HD form). Then
use the Remedy Import tool to process the old data with that form.
Let's call this form 'ProcessImportForCountOfData'

Add two fields:
'Tickets Create Date' (DATETIME)
AND
'C536851024'

So you would need a filter on MERGE on this display only form that
does the following:

On MERGE of ProcessImportForCountOfData...Filter...
Run if: 'C536851024' != NULL
If Actions
Push to CountOfData: Push IF: 'MonthSTART'<=$Tickets Create Date$
AND 'MonthEND' >$Tickets Create Date$ AND 'C536851024VALUE' =
$C536851024$
If no match.. create a new
If multiple match... modify all...
Set 'zCountChange' to 1, 'zCreateDate' = $Tickets Create Date$

That should get you a very good result from the "old data".

The export and import will take a good bit of time, so you might want
to do this in a few passes. Once with Closed ticket (things that
should not be changing while you are doing your import work). Then
once again with any open tickets or tickets that were closed since you
did your first export. Or things that changed since you did your
export may need a few "manual" one time adjustments. etc... [I hope
that all makes sense.]

--
Carey Matthew Black
Remedy Skilled Professional (RSP)
ARS = Action Request System(Remedy)

Solution = People + Process + Tools
Fast, Accurate, Cheap.... Pick two.
Never ascribe to malice, that which can be explained by incompetence.

http://www.fellowshipchurch.com


UNSUBSCRIBE or access ARSlist Archives at http://www.ARSLIST.org
(Support: mailto:support@arslist.org)