Hi All,
I have created a transaction search and was wondering if there is a way to display the number of hours, minutes or days between two fields? I want to work out the amount of time an order spends in a certain status within a department.
I have played around with a few things following the help files but it resulted in my results only displaying one line of information rather than all of my results.
My criteria is below (I have a few more Status' in there but I dont need them all in for example purpose) :
<strong>Type</strong> is Sales Order <span style="color:'"Blue"'"><strong>And</strong></span> <strong>Main Line</strong> is True <span style="color:'"Blue"'"><strong>And</strong></span> <strong>Date</strong> is Within This Month <span style="color:'"Blue"'"><strong>And</strong></span> <strong>System Notes: Field</strong> is Sales Order Status (CPW BS) <span style="color:'"Blue"'"><strong>And</strong></span> <strong><span style="color:'"Blue"'">(</span> </strong><strong>System Notes: Old Value</strong> is Awaiting Allocation <span style="color:'"Blue"'"><strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Awaiting Credit Check <span style="color:'"Blue"'"><strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Awaiting Stock <span style="color:'"Blue"'"><strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Credit Check Passed<span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Credit Check Failed<span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is In Despatch <span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Order Cancelled <span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Order Complete <span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Paperwork Incomplete <span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Received Into Validation <span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Validation Failed <span style="color:'"Blue"'"> <strong>Or</strong></span> <strong>System Notes: Old Value</strong> is Validation Passed <span style="color:'"Blue"'"><strong>)</strong></span>
My results page is as follows:Date Created Number Name System Notes: Old Value System Notes: New Value System Notes: Date
This will display the search results as:[INDENT]Date Created - Number - Name - Old Value - New Value - Date (System Notes)01/10/08 - 101256 - Test Company Name 1 - Awaiting Allocation - Awaiting Credit Check - 02/10/0801/10/08 - 101256 - Test Company Name 1 - Awaiting Credit Check - Credit Check Passed - 03/10/0801/10/08 - 101256 - Test Company Name 1 - Credit Check Passed - In Despatch - 04/10/0801/10/08 - 101256 - Test Company Name 1 - In Despatch - Order Complete - 07/10/0801/10/08 - 101257 - Test Company Name 2 - Awaiting Allocation - Awaiting Credit Check - 02/10/0801/10/08 - 101257 - Test Company Name 2 - Awaiting Credit Check - Credit Check Failed - 02/10/0801/10/08 - 101257 - Test Company Name 2 - Credit Check Failed - Received Into Validation - 02/10/0801/10/08 - 101257 - Test Company Name 2 - Validation Failed - Order Cancelled - 05/10/0802/10/08 - 101258 - Test Company Name 3 - Awaiting Allocation - Awaiting Credit Check - 04/10/0802/10/08 - 101258 - Test Company Name 3 - Awaiting Credit Check - Credit Check Passed - 04/10/0802/10/08 - 101258 - Test Company Name 3 - Credit Check Passed - Awaiting Stock - 05/10/0802/10/08 - 101258 - Test Company Name 3 - Awaiting Stock - In Despatch - 10/10/0802/10/08 - 101258 - Test Company Name 3 - In Despatch - Order Complete - 14/10/08[/INDENT]What I want to do is have another column at the end of the results which will display the amount of time the order has spent in each status. This will be calculated either by the date created and the system note date (when the status changed), or from the old value and new value system notes date.For example: The results would look like this (this is based on calculating the number of days between the created date and the system note date):[INDENT]Date Created - Number - Name - Old Value - New Value - Date (System Notes) - Status Time (Days)01/10/08 - 101256 - Test Company Name 1 - Awaiting Allocation - Awaiting Credit Check - 02/10/08 - 101/10/08 - 101256 - Test Company Name 1 - Awaiting Credit Check - Credit Check Passed - 03/10/08 - 301/10/08 - 101256 - Test Company Name 1 - Credit Check Passed - In Despatch - 04/10/08 - 401/10/08 - 101256 - Test Company Name 1 - In Despatch - Order Complete - 07/10/08 - 701/10/08 - 101257 - Test Company Name 2 - Awaiting Allocation - Awaiting Credit Check - 02/10/08 - 101/10/08 - 101257 - Test Company Name 2 - Awaiting Credit Check - Credit Check Failed - 02/10/08 - 101/10/08 - 101257 - Test Company Name 2 - Credit Check Failed - Received Into Validation - 02/10/08 - 201/10/08 - 101257 - Test Company Name 2 - Validation Failed - Order Cancelled - 05/10/08 - 502/10/08 - 101258 - Test Company Name 3 - Awaiting Allocation - Awaiting Credit Check - 04/10/08 - 202/10/08 - 101258 - Test Company Name 3 - Awaiting Credit Check - Credit Check Passed - 04/10/08 - 202/10/08 - 101258 - Test Company Name 3 - Credit Check Passed - Awaiting Stock - 05/10/08 - 302/10/08 - 101258 - Test Company Name 3 - Awaiting Stock - In Despatch - 10/10/08 - 802/10/08 - 101258 - Test Company Name 3 - In Despatch - Order Complete - 14/10/08 - 11[/INDENT]Any help with this (using formula's etc) would be much apprecaited as I cant work out how to do it. If possible could you provide 3 different formulas for displaying the results for Minutes, Hours and Days Many ThanksJohn