Saved Search with more information

Is it possible to get custom information inside the “Query Builder”? We
are trying to get “time worked” based on transactions between a time
frame to show in a custom search. Basically we want to have a page for
clients to log in with their user/pass and see a page that shows,
tickets with time added between 2011-07-01 and 2011-07-31, and run a sum
on the time. We have a query that works, but it requires joining
Tickets and Transactions, and selecting information from the
Transactions database. Does anyone know of a way that this can be
accomplished. Thanks in advance.

Scott Benson
A1 Networks
(707)570-2021 x203

No update on this?

Scott Benson
A1 Networks
(707)570-2021 x203On 8/2/11 2:13 PM, Scott Benson wrote:

Is it possible to get custom information inside the “Query Builder”?
We are trying to get “time worked” based on transactions between a
time frame to show in a custom search. Basically we want to have a
page for clients to log in with their user/pass and see a page that
shows, tickets with time added between 2011-07-01 and 2011-07-31, and
run a sum on the time. We have a query that works, but it requires
joining Tickets and Transactions, and selecting information from the
Transactions database. Does anyone know of a way that this can be
accomplished. Thanks in advance.

I am not sure but you might be looking for ColumnMap
GerardOn 2011-08-05 17:43, Scott Benson wrote:

No update on this?


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/2/11 2:13 PM, Scott Benson wrote:

Is it possible to get custom information inside the “Query Builder”?
We are trying to get “time worked” based on transactions between a
time frame to show in a custom search. Basically we want to have a
page for clients to log in with their user/pass and see a page that
shows, tickets with time added between 2011-07-01 and 2011-07-31,
and run a sum on the time. We have a query that works, but it
requires joining Tickets and Transactions, and selecting information
from the Transactions database. Does anyone know of a way that this
can be accomplished. Thanks in advance.

Thanks for the reply Gerard. Not sure what ColumnMap is, but basically
what we’re looking to do is only list tickets in a “custom search” that
have had time worked, within a certain time frame. Also showing the
Sum(Transactions.TimeWorked) as a field.

This can be accomplished via Mysql because you can join databases and
select data on a per transaction level.

Example Mysql Code:
select Tickets.EffectiveId, Tickets.Subject, Tickets.LastUpdated,
Transactions.Created, sum(Transactions.TimeTaken) from Tickets left join
Transactions on Tickets.id = Transactions.ObjectId where
Transactions.Created between DATE_FORMAT(NOW(),"%Y-%m-01") - interval 0
month and DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month group by
Tickets.EffectiveId;

Example Mysql Output:

| EffectiveId | Subject | LastUpdated |
Created | sum(Transactions.TimeTaken) |

| 21984 | Example Ticket 1 | 2011-08-03 23:56:50 | 2011-08-03
00:02:50 | 510 |
| 23322 | Example Ticket 2 | 2011-08-06 00:11:39 | 2011-08-03
00:52:14 | 480 |
| 25497 | Example Ticket 3 | 2011-08-01 22:25:10 | 2011-08-01
22:25:10 | 180 |
| 29560 | Example Ticket 4 | 2011-08-02 00:16:59 | 2011-08-02
00:16:58 | 45 |

4 rows in set (0.13 sec)

I hope this information helps.

Scott Benson
A1 Networks
(707)570-2021 x203On 8/8/11 8:34 AM, Gerard FENELON wrote:

I am not sure but you might be looking for ColumnMap
Gerard

On 2011-08-05 17:43, Scott Benson wrote:

No update on this?


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/2/11 2:13 PM, Scott Benson wrote:

Is it possible to get custom information inside the “Query
Builder”? We are trying to get “time worked” based on transactions
between a time frame to show in a custom search. Basically we want
to have a page for clients to log in with their user/pass and see a
page that shows, tickets with time added between 2011-07-01 and
2011-07-31, and run a sum on the time. We have a query that works,
but it requires joining Tickets and Transactions, and selecting
information from the Transactions database. Does anyone know of a
way that this can be accomplished. Thanks in advance.


2011 Training: http://bestpractical.com/services/training.html

If everything works as expected then ticket’s time worked field should hold
correct sum. This value you can display in search results without custom
code.

Regards, Ruslan. From phone.

написал:

i don’t want the entirety of the time worked…only time worked based on
the month. IE Time worked this month 30mins, time worked last month 255
minutes. Hence the “Transactions.Created between
DATE_FORMAT(NOW(),”%Y-%m-01") - interval 0 month and
DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month"

Scott Benson
A1 Networks
(707)570-2021 x203On 8/8/11 12:37 PM, Ruslan Zakirov wrote:

If everything works as expected then ticket’s time worked field should hold
correct sum. This value you can display in search results without custom
code.

Regards, Ruslan. From phone.
08.08.2011 22:39 пользователь "Scott Benson"sbenson@a-1networks.com
написал:

Thanks for the reply Gerard. Not sure what ColumnMap is, but basically
what we’re looking to do is only list tickets in a “custom search” that
have had time worked, within a certain time frame. Also showing the
Sum(Transactions.TimeWorked) as a field.

This can be accomplished via Mysql because you can join databases and
select data on a per transaction level.

Example Mysql Code:
select Tickets.EffectiveId, Tickets.Subject, Tickets.LastUpdated,
Transactions.Created, sum(Transactions.TimeTaken) from Tickets left join
Transactions on Tickets.id = Transactions.ObjectId where
Transactions.Created between DATE_FORMAT(NOW(),"%Y-%m-01") - interval 0
month and DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month group by
Tickets.EffectiveId;

Example Mysql Output:

±------------±-------------------±-------------------------±------------------------±------------------------------------+

| EffectiveId | Subject | LastUpdated |
Created | sum(Transactions.TimeTaken) |

±------------±-------------------±-------------------------±------------------------±------------------------------------+

| 21984 | Example Ticket 1 | 2011-08-03 23:56:50 | 2011-08-03
00:02:50 | 510 |
| 23322 | Example Ticket 2 | 2011-08-06 00:11:39 | 2011-08-03
00:52:14 | 480 |
| 25497 | Example Ticket 3 | 2011-08-01 22:25:10 | 2011-08-01
22:25:10 | 180 |
| 29560 | Example Ticket 4 | 2011-08-02 00:16:59 | 2011-08-02
00:16:58 | 45 |

±------------±-------------------±-------------------------±------------------------±------------------------------------+

4 rows in set (0.13 sec)

I hope this information helps.


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/8/11 8:34 AM, Gerard FENELON wrote:

I am not sure but you might be looking for ColumnMap
Gerard

On 2011-08-05 17:43, Scott Benson wrote:

No update on this?


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/2/11 2:13 PM, Scott Benson wrote:

Is it possible to get custom information inside the “Query
Builder”? We are trying to get “time worked” based on transactions
between a time frame to show in a custom search. Basically we want
to have a page for clients to log in with their user/pass and see a
page that shows, tickets with time added between 2011-07-01 and
2011-07-31, and run a sum on the time. We have a query that works,
but it requires joining Tickets and Transactions, and selecting
information from the Transactions database. Does anyone know of a
way that this can be accomplished. Thanks in advance.


2011 Training: http://bestpractical.com/services/training.html


2011 Training: http://bestpractical.com/services/training.html

Hi,

Then for sure you need ColumnMap definition. Search for ColumnMap
files in share/html/Elements, open any and examine. It’s pretty
straightforward how to modify these files, however code you need at
the end can be complex. Start from a basic experiments of adding new
column and using it in searches. Return back with questions.

The following section in an article from the wiki is a good starting point:

http://requesttracker.wikia.com/wiki/ShowStatusInColor#Full-featuredOn Tue, Aug 9, 2011 at 12:15 AM, Scott Benson sbenson@a-1networks.com wrote:

i don’t want the entirety of the time worked…only time worked based on the
month. IE Time worked this month 30mins, time worked last month 255
minutes. Hence the “Transactions.Created between
DATE_FORMAT(NOW(),”%Y-%m-01") - interval 0 month and
DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month"


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/8/11 12:37 PM, Ruslan Zakirov wrote:

If everything works as expected then ticket’s time worked field should
hold
correct sum. This value you can display in search results without custom
code.

Regards, Ruslan. From phone.
08.08.2011 22:39 пользователь "Scott Benson"sbenson@a-1networks.com
написал:

Thanks for the reply Gerard. Not sure what ColumnMap is, but basically
what we’re looking to do is only list tickets in a “custom search” that
have had time worked, within a certain time frame. Also showing the
Sum(Transactions.TimeWorked) as a field.

This can be accomplished via Mysql because you can join databases and
select data on a per transaction level.

Example Mysql Code:
select Tickets.EffectiveId, Tickets.Subject, Tickets.LastUpdated,
Transactions.Created, sum(Transactions.TimeTaken) from Tickets left join
Transactions on Tickets.id = Transactions.ObjectId where
Transactions.Created between DATE_FORMAT(NOW(),"%Y-%m-01") - interval 0
month and DATE_FORMAT(NOW(),"%Y-%m-01") - interval -1 month group by
Tickets.EffectiveId;

Example Mysql Output:

±------------±-------------------±-------------------------±------------------------±------------------------------------+

| EffectiveId | Subject | LastUpdated |
Created | sum(Transactions.TimeTaken) |

±------------±-------------------±-------------------------±------------------------±------------------------------------+

| 21984 | Example Ticket 1 | 2011-08-03 23:56:50 | 2011-08-03
00:02:50 | 510 |
| 23322 | Example Ticket 2 | 2011-08-06 00:11:39 | 2011-08-03
00:52:14 | 480 |
| 25497 | Example Ticket 3 | 2011-08-01 22:25:10 | 2011-08-01
22:25:10 | 180 |
| 29560 | Example Ticket 4 | 2011-08-02 00:16:59 | 2011-08-02
00:16:58 | 45 |

±------------±-------------------±-------------------------±------------------------±------------------------------------+

4 rows in set (0.13 sec)

I hope this information helps.


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/8/11 8:34 AM, Gerard FENELON wrote:

I am not sure but you might be looking for ColumnMap
Gerard

On 2011-08-05 17:43, Scott Benson wrote:

No update on this?


Scott Benson
A1 Networks
(707)570-2021 x203

On 8/2/11 2:13 PM, Scott Benson wrote:

Is it possible to get custom information inside the “Query
Builder”? We are trying to get “time worked” based on transactions
between a time frame to show in a custom search. Basically we want
to have a page for clients to log in with their user/pass and see a
page that shows, tickets with time added between 2011-07-01 and
2011-07-31, and run a sum on the time. We have a query that works,
but it requires joining Tickets and Transactions, and selecting
information from the Transactions database. Does anyone know of a
way that this can be accomplished. Thanks in advance.


2011 Training: http://bestpractical.com/services/training.html


2011 Training: http://bestpractical.com/services/training.html


2011 Training: http://bestpractical.com/services/training.html

Best regards, Ruslan.