Computed fields

[Kedevlin]

Hello,

Is there a way to make a computed field that calcultates the days between a date field and the date a specific workflow action is made? 

Thank you,

Kelly

[broepke]

I don't think there is any direct access to anything other than the fields on item details in a computed field.  That being said - you can take a small action script that runs each time the WF is transitioned that writes the workflow date into a field on item details... that way you can use it for comparison.

Best i can think of right now.  Hope that helps.

[Kedevlin]

Thanks! Do you have any resources you can point me to that would help with creating this action script?

[broepke]

The help system has tons of information and examples.  Also - chances are your tenant already has a bunch of scripts that you can leverage as examples.

http://help.autodesk.com/view/PLM/ENU/?guid=GUID-9A80518C-DFF0-4692-94CE-17A043BDECBE

Take a look around there and see if you can start to figure some of it out.

[dkeeley]

Hi,

Just as Brian said I usually create an 'admin' restricted section on the item details to record things like;

- dates that WF transitions occured

- previous values for key attributes

- days between key dates

It's very useful for recording metrics.

While we're on it Brian I've been doing some tesing with the API and some PHP scripts to create a kind of 'historian' for the metric data that is stored in the 'admin' sections. This info is then fed via the API directly into our BI application. I'll bring it up at the next EMEA CAB.

David.

[Kedevlin]

Hello,

Thank you both for your help.  This is my first time creating a script; I do not understand how to capture the date a workflow transition is made.

Do I want an "item" to be created when the transition is made?  The item being a timestamp of the transition?  How would I get a field in Item Details to capture this timestamp?

This is what I have so far - any resources or instructions targeted at a more novice user would be much appreciated. 

createItem('WS_Wine_Information')
item.workflowActions['Production Complete'].timeStamp

Thank you!

[jared.sund · Accepted solution]

Hello Kelly,

A couple things to note about PLM 360’s scripting API. 

“item” is the current item that the script is running against.  As an example, if we’ve created an action, and this action script is set to run on a particular WF Action (transition).  When the WF Action is performed and the script runs, that item you ran the WF Action on is “item” in the script.

So, to access fields on the item details tab for this current “item”, we use the syntax:  item.FIELD_ID.

Also, in scripting item.workflowActions is an array (a list) of all the workflow action that have been performed against “item” at run time.  This list of workflow actions is ordered in the list from most recent to oldest.  The first index in the array [0] is the latest WF Action that has been performed.  The next element in the array, [1], will be the second most recent WF Action.  When you look at the Workflow Actions tab for an item, the order of the Workflow History is in the same order as the array “item.workflowActions”.

So, let’s take an example.  Let’s assume that I have a Change Order.  On the item details I have two fields:  a date field that is my expected date for a particular workflow action to occur, and a number field to hold the days difference between the expected due date and the date the workflow action took place.  The result I want, is to calculate the difference in days between the expected due date and the date the workflow action took place and record this value in the days different field.

Due Date has a field id:  DUE_DATE, and Days Diff has a field id of DAYS_DIFF

On a particular workflow transition, I’ve assigned my new action script to run when this transition is completed.

When the WF Action is performed..., my new action script "DateDiff" will run.

Now we just need the action script:

var WFDate = new Date(item.workflowActions[0].timeStamp) ;
var dueDate = new Date(item.DUE_DATE);

item.DAYS_DIFF = (WFDate-dueDate)/(1000*60*60*24);

• In the first line we create a new date variable, with the timestamp from the latest workflow action "[0]".  This is the workflow action that has just occurred.
• In the second line we create a new date variable based off the Due Date field (item.DUE_DATE).
• Finally on the last line, we calculate the difference between these two dates, and store the result in the Days Diff field (item.DAYS_DIFF).

Subtracting dates directly in JavaScript gives a result in milliseconds.  So the simple math here, converts our result into days.

Hope this is helpful, but feel free to ask any other questions as the come up!

-Jared

[Kedevlin]

Hi Jared,

Thanks so much for your help.  This makes sense to me.  I've created the script but something seems to be off.

Here's my script:

var planDate = new Date(item.Planned_Release_Date);
var WFDate = new Date(item.workflowActions[0].timeStamp) ;

item.DAYS_BETWEEN_WAITING_FOR_AND_PLANNED = (planDate-WFDate)/(1000*60*60*24);

In this exampe, Planned_Release_Date = 02/01/2015 and the workflow transition happened today (01/13/2015).  Therefore, the field DAYS_BETWEEN_WAITING_FOR_AND_PLANNED should show 19, but it shows -16449.  Do you know what I might be doing wrong?

-Kelly

[Kedevlin]

I think the issue might be that the timestamp and item (Planned_Release_Date) are in different date formats.  The timestamp looks like this: 01/20/2015 11:49:19 AM, whereas the item looks like this: 02/01/2015.  So if there is a way to convert the timestamp to dd/mm/yyyy before subtracting it from the item, the script might work.  Does anyone know how to do this?  I've created the following script, but it results in an error when it runs:

var planDate = new Date(item.Planned_Release_Date);  
var WFDate = new Date(item.workflowActions[0].timeStamp) ;  
 
var date = new Date(WFDate);     
var formattedDate = d.getDate() + "/" + (d.getMonth()+1) + "/" + d.getFullYear();
alert(formattedDate);
 
item.DAYS_BETWEEN_WAITING_FOR_AND_PLANNED = (planDate-date);

[Kedevlin]

I think the issue might be that the timestamp and item (Planned_Release_Date) are in different date formats.  The timestamp looks like this: 01/20/2015 11:49:19 AM, whereas the item looks like this: 02/01/2015.  So if there is a way to convert the timestamp to dd/mm/yyyy before subtracting it from the item, the script might work.  Do you know how to do this?  I've created the following script, but it results in an error when it runs:

var planDate = new Date(item.Planned_Release_Date);  
var WFDate = new Date(item.workflowActions[0].timeStamp) ;  
 
var date = new Date(WFDate);     
var formattedDate = d.getDate() + "/" + (d.getMonth()+1) + "/" + d.getFullYear();
alert(formattedDate);
 
item.DAYS_BETWEEN_WAITING_FOR_AND_PLANNED = (planDate-date);

[Kedevlin]

I think the issue might be that the timestamp and item (Planned_Release_Date) are in different date formats.  The timestamp looks like this: 01/20/2015 11:49:19 AM, whereas the item looks like this: 02/01/2015.  So if there is a way to convert the timestamp to dd/mm/yyyy before subtracting it from the item, the script might work.  Do you know how to do this?  I've created the following script, but it results in an error when it runs:

var planDate = new Date(item.Planned_Release_Date);  
var WFDate = new Date(item.workflowActions[0].timeStamp) ;  
 
var date = new Date(WFDate);     
var formattedDate = d.getDate() + "/" + (d.getMonth()+1) + "/" + d.getFullYear();
alert(formattedDate);
 
item.DAYS_BETWEEN_WAITING_FOR_AND_PLANNED = (planDate-date);

[gasevsm]

Hi,
Date and timestamp conversion is automatic. You should not need to convert explicitly. Have you considered that the calculation returns the number of *full* days between the dates. So today 1pm - two days from now is 1 full day. If need to calculate differently, add the same timestamp (maybe plus a msec) to the date only field and then subtract to get 2 days diff.

[Kedevlin]

Hi Martin,

Thanks for your reply.  The reason I think it is a formatting problem is the calculation is returning a number very far off from what it should be.  For example, it returns -16449 as the difference between 02/01/2015 and 01/13/2015 when that should be 19.  Your reasoning would make sense if it was only 1 or 2 days off, if I understand you correctly. 

Also, how would I add a timestamp to the date only field?  It is a manually entered field. 

Thank you!

Kelly

[gasevsm]

You would use the DATE_PART('day', ENDDATE, STARTDATE). I'll run some tests tomorrow as you have a timestamp and a date to diff. may need to call within the above function conversions to either ::timestamp or to_date(YOURDATE, 'MM/DD/YYYY') to make it happen. Cheers,

[Kedevlin]

Hi Martin,

I found the error.  I didn't realize the field IDs had to be in all capital letters.  This seems to work:

var planDate = new Date(item.PLANNED_RELEASE_DATE);
var WFDate = new Date(item.workflowActions[0].timeStamp);

item.DAYS_BETWEEN_WAITING_FOR_AND_PLANNED = (planDate-WFDate)/(1000*60*60*24);

It does show the full days between the two dates, so it will always be one day off from what I'm looking for but at least the script seems to be working. 

Thanks for your help!

Kelly

[Kedevlin · Accepted solution]

This script works to generate the number of days instead of the number of full days by extracting the day month and year only:

var planDate = new Date(item.PLANNED_RELEASE_DATE);
var WFDate = new Date(item.workflowActions[0].timeStamp);

var planDateNoTime = new Date(planDate.getMonth() + 1 + "/" + planDate.getDate() + "/" + planDate.getFullYear());
var WFDateNoTime = new Date(WFDate.getMonth() + 1 + "/" + WFDate.getDate() + "/" + WFDate.getFullYear());

item.DAYS_BETWEEN_WAITING_FOR_AND_PLANNED = (planDateNoTime-WFDateNoTime)/(1000*60*60*24);