In Archibus, Projects Actions can generate Work Requests. This post explains how to link Work Requests generated this way back to the Projects and Project Actions that generated them.
The image above shows the relationship in Entity Relationship form
- Project Action activity_log records have a primary key: activity_log_id
- Service Request activity_log record copied_from fields are the link to Project Action activity_log record activity_log_id fields
- The Work Wequest wr.wr_id primary key field is then linked to the Service Request activity_log.wr_id field
A sample SQL Query to find WR Costs by Project:
select pa.project_id, sum(wr.cost_est_total) wr_cost_est_total, sum(wr.cost_total) wr_cost_act_total
from activity_log pa
join activity_log wa
on pa.activity_log_id = wa.copied_from
on wa.wr_id = wr.wr_id
where wa.activity_type = ‘SERVICE DESK – MAINTENANCE ‘
and pa.project_id = ‘2011-000074’
group by pa.project_id
Was this helpful?
0 / 0