formula in saved search that pulls the date when the status field new value is Approved

AI generated answer (using internal Slack threads):

To pull the date when the status field new value is 'Approved' in a saved search, you can use the system notes. The system notes keep track of all changes made to a record, including status changes. Here is a formula that you can use:

sql
MIN (CASE WHEN {systemnotes.oldvalue} = 'Pending Approval' AND {systemnotes.newvalue} = 'Approved for Posting' THEN TO_CHAR({systemnotes.date}, 'DD/MM/YYYY HH:MI AM') END) OVER(PARTITION BY {internalid} ORDER BY {line} DESC)

This formula checks the system notes for a change in status from 'Pending Approval' to 'Approved for Posting'. It then returns the date of this change. The

MIN

function is used to ensure that if there are multiple such changes, only the earliest date is returned. The

OVER(PARTITION BY {internalid} ORDER BY {line} DESC)

part ensures that this is done for each record separately. Please note that the date format used in the

TO_CHAR

function ('DD/MM/YYYY HH:MI AM') should match the date format used in your NetSuite account. ****** Disclaimer: This response was generated by an artificial intelligence assistant based on previous conversations from multiple Slack channels, and may contain inaccuracies or mistakes. ******