How to combine multiple linked records into one fi...

[Nicole_N]

Hi,

I have a series of lookup fields that return multiple dates.  I'd like to combine those fields into one, listing all of the dates returned in M/D/YYYY.  I've messed with datetime_parse and format with a concatenate, but the formatting is either not M/D/YYYY or it only returns the first date listed in a series in the lookup field.

Thanks for any help!

[Alexey_Gusev · Accepted solution]

Hi,
You can use something like this:

REPLACE(
  SUBSTITUTE(
    REGEX_REPLACE({lookup one}&{lookup two}&{third lookup},
    '(20\\d{2})-(\\d{2})-(\\d{2})', ', $2/$3/$1'),
 'T00:00:00.000Z', ''),
 1,2,'')

I suppose your years are >=2000.
if you have 19xx , try to change  20\\d{2} with \\d{4}

[Nicole_N]

Thank you!  That worked!  What would I need to do to have it be m/d/yyyy and not mm/dd/yyyy? Sorry, I'm not a coder (obviously)!

[Alexey_Gusev]

You mean remove zero if day or month less than 10? 
I suspect that task might have easier common solution, but here I'm just add another level of replace,
removing zero from {space)0 OR \0

REPLACE(
  SUBSTITUTE(
    REGEX_REPLACE(
      REGEX_REPLACE(start&end,
      '(20\\d{2})-(\\d{2})-(\\d{2})', ', $2/$3/$1'),
    '([ |/])0','$1'),
 'T00:00:00.000Z', ''),
 1,2,'')