API Query ActualEffort

Hi Bill, ande sorry it took me some time to respond.

TimeSheetViewQuery is typically used to retrieve the list of relevant work items, by period and optionally project filter. 

I didn't figure out what you were trying to achieve with the Actual Effort filter. Perhaps if you send your code over I may be able to make suggestions. 

Two notes about this:

1. I'm not sure TimeSheetViewQuery does support the Where condition.

2. Here's a sample of a Compare condition with a TimeEffort field & constant:

LeftExpression = new FieldExpression { FieldName = "ActualEffort" },

Operator = Operator.GreaterThanOrEqual,

RightExpression = new ConstantExpression { Value = new Duration { Unit = DurationUnit.Hours, Value = 4 } }

Hope this helps,

Ophir

Thanks,

That gets me part way there. Actually, my requirements are pretty simple. I want to get all tasks that have hours entered in them during a certain time period. All tasks with hours entered for the month of October. With the return info having the project name and the customer name.

I would have imagined that this is a pretty standard query but cannot find any examples.

Thanks

Hi Bill.

I'd suggest you

1. Use the EntityQuery with a TypeName of Timesheet.
2. Specify the date range with conditions on ReportedDate field.
3. Include Workitem.Project.Name on the result fields. 

As for the customer name, if you have a custom field (say 'CustomerCustomReference') on the project referencing it, you could just add it to the result fields (Workitem.Project.CustomerCustomReference.CustomerName). 

If you don't, you'd need to query the CustomerLink entity to detect the customers, once you have all the Project Id's from the Timesheet EntityQuery.

You may also find TimesheetQuery message useful, having some built in Timesheet filters (including the CustomerId if you need information for a single customer). Anyway, the above applies for it too.

Hope this helps,

Ophir

Thanks, that got me a lot closer. A general question. Is their a document that provides all the fields associated with a specific entity? 

Hi Bill,

You can find the API names of the fields in the Settings -> Configure page in Clarizen, or use the _DescribeEntitiesMessage _to get it programmatically.

Hope this helps,

Ophir

Thanks. Is it possible to query on a timesheet query to retrieve the project SYSID. 

EntityQuery eq = new EntityQuery

{

TypeName = "timesheet",

Fields = new string[] { "Workitem.Project.Name", "Duration.Length", "ReportedDate", "Workitem.Project.SYSID", "ExternalID" },

Where = new And

{

Conditions = new Condition[]

{

new Compare {LeftExpression = new FieldExpression { FieldName = "ReportedDate" }, Operator = Operator.GreaterThanOrEqual,RightExpression = new ConstantExpression { Value = date_start }},

new Compare {LeftExpression = new FieldExpression { FieldName = "ReportedDate" }, Operator = Operator.LessThanOrEqual,RightExpression = new ConstantExpression { Value = date_end }},

new Compare {LeftExpression = new FieldExpression { FieldName = "Duration" }, Operator = Operator.NotEqual, RightExpression = new ConstantExpression { Value = new Duration() { Unit = DurationUnit.Hours, Value = 0 } } },

new Compare {LeftExpression = new FieldExpression { FieldName = "Workitem.Project.SYSID" }, Operator = Operator.Equal, RightExpression = new ConstantExpression { Value = sysId } }

}

}

};

Hi Bill.

You can specify Workitem.Project.SYSID as a result field. However, you cannot specify a referenced field in a FieldExpression. 

If you need to specify a filter on a referenced field, you should use the In operator and a sub query. There are several examples for that in this forum. BTW, it would be better to use the Project's external ID and specify a filter on the Project field of the Workitem directly. 

Hope this helps,

Ophir