Skip to main content

Hello,



I am trying to use Last Modified Date for particular fields to evaluate whether those fields were updated today, and if so, concatenate those updated values, separated by commas, and output those into my formula field. The final formula result will be included in a daily email digest of changes to my database, intending to show the recipient which of the specific fields were updated.



I first tested the OR function by creating a simpler formula just to make sure I had the right idea, and this does return “Updated today!” for the one record that I updated today, with “No update today” returned for records that don’t match (the ERROR values are due to many of my records not having Last Modified Times yet, as those fields are new).


I initially had trouble getting the dates to match up properly before realizing that I needed to convert the Last Modified Time fields via DATESTR() to make sure that I was matching text strings only.



My IF/OR formula, working as intended:



IF(


OR(


{LM DS: Winter Instructor}={LM DS: Last Update Today Equiv},


{LM DS: Winter Section #}={LM DS: Last Update Today Equiv},


{LM DS: Winter Section Title}={LM DS: Last Update Today Equiv},


{LM DS: Winter Day/Time}={LM DS: Last Update Today Equiv},


{LM DS: Winter Location}={LM DS: Last Update Today Equiv},


{LM DS: Winter Grading Scheme}={LM DS: Last Update Today Equiv},


{LM DS: Portal Comment Archive}={LM DS: Last Update Today Equiv}),


“Updated today!”,


“No update today”


)



Screenshot:




I am having trouble with the Concatenate portion of the formula. I want the formula to return concatenated values if any of the fields were updated today. It seems feasible to split these tasks across multiple formulas – perhaps an IF statement for each field where the last modified time matches today, then if it matches, return the field’s updated value; do this for each field I want to include, then create a separate Concatenate formula field to join the updated values – but I am hoping to accomplish this with one formula field given that I will need to replicate this for several other field groupings.



Here is my non-working formula:



IF(


OR(


{LM DS: Winter Instructor}={LM DS: Last Update Today Equiv},


{LM DS: Winter Section #}={LM DS: Last Update Today Equiv},


{LM DS: Winter Section Title}={LM DS: Last Update Today Equiv},


{LM DS: Winter Day/Time}={LM DS: Last Update Today Equiv},


{LM DS: Winter Location}={LM DS: Last Update Today Equiv},


{LM DS: Winter Grading Scheme}={LM DS: Last Update Today Equiv},


{LM DS: Portal Comment Archive}={LM DS: Last Update Today Equiv}),


CONCATENATE(


"Winter Instructor: “{Winter Instructor},”, ",


"Winter Section #: “{Winter Section #},”, ",


"Winter Section Title: “{Winter Section Title},”, ",


"Winter Day/Time: “{Winter Day/Time},”, ",


"Winter Location: “{Winter Location},”, ",


"Winter Grading Scheme: “{Winter Grading Scheme},”, ",


"Portal Comments: "{DO NOT EDIT: Comment Archive}


),


“No Updates Today”


)



Any tips would be greatly appreciated. Thank you!! 😊



Sydney

Hm, I think you’re missing commas in your CONCATENATE section



Could you try replacing it with this?



CONCATENATE(


  "Winter Instructor: ", {Winter Instructor},", ",


  "Winter Section #: ", {Winter Section #},", ",


  "Winter Section Title: ", {Winter Section Title},", ",


  "Winter Day/Time: ", {Winter Day/Time},", ",


  "Winter Location: ", {Winter Location},", ",


  "Winter Grading Scheme: ", {Winter Grading Scheme},", ",


  "Portal Comments: ", {DO NOT EDIT: Comment Archive}


),


It looks like your formula’s going to output all of the field values even if only one of the fields has been updated too. Is that intended?

Hm, I think you’re missing commas in your CONCATENATE section



Could you try replacing it with this?



CONCATENATE(


  "Winter Instructor: ", {Winter Instructor},", ",


  "Winter Section #: ", {Winter Section #},", ",


  "Winter Section Title: ", {Winter Section Title},", ",


  "Winter Day/Time: ", {Winter Day/Time},", ",


  "Winter Location: ", {Winter Location},", ",


  "Winter Grading Scheme: ", {Winter Grading Scheme},", ",


  "Portal Comments: ", {DO NOT EDIT: Comment Archive}


),


It looks like your formula’s going to output all of the field values even if only one of the fields has been updated too. Is that intended?


Hello,



That edit worked for getting the formula to return the concatenated values, thank you!!



I am trying to get the formula to only return updated fields for this, but haven’t figured it out yet. I was last working on this after a long workday, so I’ll try again today! Any tips would be greatly appreciated :grinning_face_with_sweat: thank you again!!

Hello,



That edit worked for getting the formula to return the concatenated values, thank you!!



I am trying to get the formula to only return updated fields for this, but haven’t figured it out yet. I was last working on this after a long workday, so I’ll try again today! Any tips would be greatly appreciated :grinning_face_with_sweat: thank you again!!


Hmm! I’m thinking you can just break it up into multiple IFs



e.g.



IF(


  {LM DS: Winter Instructor}={LM DS: Last Update Today Equiv},


  CONCATENATE("Winter Instructor: ", {Winter Instructor},", ")


) & 


IF(


  {LM DS: Winter Section #}={LM DS: Last Update Today Equiv},


  CONCATENATE("Winter Section #: ", {Winter Section #},", ")


) & 


etc etc


(Not 100% sure the formula above will work as I haven’t tested it)

Hmm! I’m thinking you can just break it up into multiple IFs



e.g.



IF(


  {LM DS: Winter Instructor}={LM DS: Last Update Today Equiv},


  CONCATENATE("Winter Instructor: ", {Winter Instructor},", ")


) & 


IF(


  {LM DS: Winter Section #}={LM DS: Last Update Today Equiv},


  CONCATENATE("Winter Section #: ", {Winter Section #},", ")


) & 


etc etc


(Not 100% sure the formula above will work as I haven’t tested it)


This worked perfectly, thank you!! Here is the final formula:



IF(





  {LM DS: Winter Instructor}={LM DS: Last Update Today Equiv},





  CONCATENATE("Winter Instructor",", ")





) & 





IF(





  {LM DS: Winter Section #}={LM DS: Last Update Today Equiv},





  CONCATENATE("Winter Section #",", ")





) 


& 





IF(





  {LM DS: Winter Section Title}={LM DS: Last Update Today Equiv},





  CONCATENATE("Winter Section Title",", ")





) 


&





IF(





  {LM DS: Winter Day/Time}={LM DS: Last Update Today Equiv},





  CONCATENATE("Winter Day/Time",", ")





) 


& 





IF(





  {LM DS: Winter Location}={LM DS: Last Update Today Equiv},





  CONCATENATE("Winter Location",", ")





) 


& 





IF(





  {LM DS: Winter Grading Scheme}={LM DS: Last Update Today Equiv},





  CONCATENATE("Winter Grading Scheme",", ")





) 


& 





IF(





  {LM DS: Portal Comment Archive}={LM DS: Last Update Today Equiv},





  CONCATENATE("Portal Comment Archive")





)


Many of the records returned “#ERROR!” initially after updating the formula, since my Last Modified Time fields were blank for many records – I added those fields more recently than I had edited the records themselves (i.e., no “last modified time” to return, according to those new fields). To solve this, I edited the formulas in my “LM DS” (“Last Modified Date String”) fields to check for blank fields before returning anything, and leave a “?” instead of erring out if no date was found:



IF(


  {LM: Winter Section Title},


  DATESTR({LM: Winter Section Title})


  , "?")


This resolved the “#ERROR!” getting returned in my LM Concat field:





Thank you again!! This will make updates WAY easier to spot for my coworkers who need to track changes. :star_struck:

Reply


Terms & Conditions