First update timestamp including time and filtering out user

[AbiBeggs]

Hi all,

We have a simple calculated field that shows us the date the first update is left on a request. I'd like to modify it to add the time and filter out updates sent from one particular account (we have a 'fusion' wf account that'll sometimes help us with more generic updates.) any recommendations on how best I should modify this to reflect that?

IF(ISBLANK({DE:First Update Timestamp}), $$NOW, {DE:First Update Timestamp})

[Richard_Le_ · Accepted solution]

Hi,

Give the following a try:

IF(ISBLANK({DE:First Update Timestamp}),IF({lastUpdatedByID}!="ID OF FUSION ACCOUNT" $$NOW,{DE:First Update Timestamp}),{DE:First Update Timestamp})

In plain English, what is calculation is saying is; If DE:First Update Timestamp is blank then check that the ID of the person who made the last update does not equal the Fusion user ID. If the person who made the last update is not Fusion return a value of NOW, otherwise return the existing value in DE:First Update Timestamp, ELSE if {DE:First Update Timestamp} is not blank return the existing value in {DE:First Update Timestamp}.

I strongly recommend testing this in your preview environment, or in a separate calculated first to make sure it works as expected before updating the live field.

Best Regards,

Rich

[AbiBeggs]

Thank you! I really appreciate the explanation, this is so helpful.

This is capturing any updates made on the request right? not just an update left in the update section?

[Doug_Den_Hoed__AtAppStore]

Hi @AbiBeggs, 

To add a half-twist to the excellent forward-flip @Richard_Le_ suggested...

Since you are considering changing existing data in a calculated parameter that currently has data you prefer it did not (i.e. was Last Updated by the Fusion Account's User ID), if you'd like to also cleanse such data, you could:

• replace the current formula with this one, to reset the unwanted data: IF(ISBLANK({DE:First Update Timestamp}),IF({lastUpdatedByID}="ID OF FUSION ACCOUNT","",{DE:First Update Timestamp}))
• check the "Apply to existing calculations" box, then save the custom form
• wait a bit, and confirm (either by sample, or full fledged report) that the formula has completed its update
• rinse and repeat, but this time, using Rich's formula (going forward)

It might be wise to choose an "off hours" window where you can do this quickly to minimize missing a Bonafide Last Update you DO want to capture while you're making the switch, and -- echoing his emphasis -- I too would recommend carefully practicing in Sandbox first.

Regards,

Doug