Purchase Order Approval Leadtimes

Question

Hi All,

I am in the process of doing a Generic inquiry tracking approvals and time differences between approval steps.

But I'm struggling to get the difference between one approval step and another approval step.

Step 1 Calculated Field: =iif([POApproval.StepID]='DBB0026D-FC97-ED11-9A17-00155D000300' ,[POApproval.ApproveDate] , Null)

Step 2 Calculated Field: =iif([POApproval.StepID]='423376CC-B2A9-EC11-81C1-3CECEF0440D8' ,[POApproval.ApproveDate] , Null)

Both the above returns dates, I am trying to get a datediff between the 2( Step 1 and Step 2), but my formula returns blank. 😑

Datediff Formula: =Datediff( 'dd', iif([POApproval.StepID]='DBB0026D-FC97-ED11-9A17-00155D000300',[POApproval.ApproveDate] , Null), iif([POApproval.StepID]='423376CC-B2A9-EC11-81C1-3CECEF0440D8' ,[POApproval.ApproveDate] , Null))

Anyone have any idea how I could approach getting the date difference?

Thanks in Advance.

lauraj46 · Answer

Hi ​@neo34,The formula as written will not work because the dates that you are trying to compare are on two different POApproval records. If you don’t need other details from the POApproval records,you shouldbe able to make this work by grouping on the PO Order Number and using an aggregate function in your formula, something like this:Datediff Formula:=Datediff( 'dd', max(iif([POApproval.StepID]='DBB0026D-FC97-ED11-9A17-00155D000300',[POApproval.ApproveDate] , Null)), max(iif([POApproval.StepID]='423376CC-B2A9-EC11-81C1-3CECEF0440D8' ,[POApproval.ApproveDate] , Null)))Another idea would be to join the POApproval table twice, once for each step. Then you could compare the dates between the ApproveDate of those two records.Hope this helps!Laura

Sign up

Social Login

Login to the community

Social Login

Scanning file for viruses.

This file cannot be downloaded