Within the workorders screen I am trying to create a report where I wish to subtract a custom field, that has been formatted as a date, from the date the workorder was created. So basically I have a three column table where I have

<Date Workorder created> <Custom field date> <Custom Field Date-Date Work Order Created>

Regards,

Martin

Regards,
Martin Parsons

Not sure why - but you posted this question twice? - it was already posted in http://forum.ayanova.com/Topic3110-100-1.aspx

A suggestion is to take a look at the report template and its script in the topic http://forum.ayanova.com/Topic3035-103-1.aspx and use that as a reference - Joyce

AyaNova Sales & Technical Support

http://www.ayanova.com

Do note that you can reply to an existing topic by selecting the Reply button to the upper right-hand corner of the topic - just in case that was what was going on.

Or if there is a problem with the forum (i.e. duplicating topics), do let me know so that I can get it resolved.

Thank you

- Joyce

AyaNova Sales & Technical Support

http://www.ayanova.com

tried teh report you recommended but I keep getting a zero for the difference. Have tried numerous things with no luck. I have attached the report.

Regards,

Martin

Regards,
Martin Parsons

	Post Attachments
	Status1.zip (1 view, 30.96 KB)

I've taken a look, and modified the script so that I can see what it believes dt1 and dt2 is, and they are coming up both as 1/1/0001 12:00:00AM so that is why they are showing an amount of 0 when subtracted.

I'm will have to take a look into this further and get back to you on a possible suggestion.

- Joyce

AyaNova Sales & Technical Support

http://www.ayanova.com

When using GetCurrentColumnName script in a detailed report template format (as opposed to a summary report template format), one must always identify the band from where the label / datafield is located.

To figure out what was going on, I first created dragged over additional Label fields and than edited the script so that it would place the actual value of dt1 and dt2 into each of the labels - for example

xrLabel22.Text =  dt1.ToString();

But they showed 1/1/0001/ 12:00:00AM which meant that the value was 0 - which means that if you subtract 0 from 0, you will get 0. So it was a case of figuring out why the GetCurrentColumnValue was resulting in 0 for each of the datafields.

That's when I clued in, that in a detailed report template format, you must specify the band name from where the labels are located in. This is because unlike a summary report template format which is "flat", a detailed is made up of tables and sub-tables

I attached a copy of your report template with the script edited as so that the total hours difference now displays.

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)

{

DateTime dt2 = Convert.ToDateTime(detailReportBand1.GetCurrentColumnValue("LT_Workorder_Label_Modified"));
DateTime dt1 = Convert.ToDateTime(detailReportBand1.GetCurrentColumnValue("LT_Workorder_Label_Created"));

TimeSpan ts = dt1.Subtract(dt2);
//this displays the timespan in total hours (if you want to display in minutes, than change TotalHours to TotalMinutes
//or if want to display in Days change TotalHours to TotalDays)

xrTableCell3.Text =  ts.TotalHours.ToString();
}

This should now get you going. Have a good evening

- Joyce

AyaNova Sales & Technical Support

http://www.ayanova.com

	Post Attachments
	CopyofStatus1.zip (1 view, 28.63 KB)

firstly thanks for the help.

I have attached a report where I am subtracting dates and I need just a bit more help.

xrTableCell8 contains the the creation date of the workorder.

xrtableCell6 contains custom field 4. This is a date field. This gets displayed correctly.

xrtableCell10 should display custom field 4 - creation date. It does not. The reason is that even though custom field 4 is showing the correct date I am actually extracting the date 1/1/0001 (i.e. 0). I think it is because I am actually pointing at the wrong place. I have attached the report.

I have tried numerous methods but without success.

Thanks,

Martin

Regards,
Martin Parsons

	Post Attachments
	Job Status.zip (2 views, 33.98 KB)

Two issues are the cause:

1. You were calling a datafield from the WorkorderHeader band that is not actually in the WorkorderHeader area, but is from the WorkorderItem. The Custom fields are from the WorkorderItem area.

2. Custom field is not maintained in the database as a time field (unlike the Created time field) - as a custom field could be any type of data format and it could also be empty

1. First I added the DetailReport band for Workorder Item
2. Then I highlighted all fields that you had in the WorkorderHeader band andmoved them down to the Workorder Item band
3. Then I edited the script for xrTableCell10 with the following:

private void OnBeforePrint(object sender, System.Drawing.Printing.PrintEventArgs e)
{

DateTime dt2;
if (DateTime.TryParse(Convert.ToString(DetailReport.GetCurrentColumnValue("LT_WorkorderItem_Label_Custom4")), out dt2))
    {
    DateTime dt1 = Convert.ToDateTime(detailReportBand1.GetCurrentColumnValue("LT_Workorder_Label_Created"));
    TimeSpan ts = dt2.Subtract(dt1);
    xrTableCell10.Text =  ts.TotalDays.ToString("#.0");
    }
else
    xrTableCell10.Text="No Date";

}

Because you might have a Custom4 field that is empty, you have to check it first to see if it has any data otherwise you would get an exception if any workorder item has the custom4 field empty - that's the TryParse statement.

Notice the DetailReport. when getting the GetCurrentColumnValue for the Custom4 field - and notice the detailReportBand1. when calling the Created datafield because Created is from the workorderheader, but Custom4 is from the workorder

I've attached an new copy of your report template

- Joyce

AyaNova Sales & Technical Support

http://www.ayanova.com

	Post Attachments