Getting Last Date From Multiple Columns

Jonathan_S · 09-20-2020 12:50 PM

Good Day All,

Im wondering How I would Get the latest Date/most recent Date from Multiple Columns Spread over 2 Tables.

I would be placing this latest date in a virtual Column in the Parent Table, Table A.
The problem is Table A can have many many Childs in table b. How would I Sift through each one to find the latest date of all of them? It is possible that multiple columns will have the same most recent date. I only need 1 Date,

Table A
2 Date Columns

Table B.
Child of A.
Has 8 Date Columns.

Steve

Try:

MAX(
  LIST(
    [Date column 1],
    [Date column 2]
  )
  + [Related Table Bs][Date column 1]
  + [Related Table Bs][Date column 2]
  ...
  + [Related Table Bs][Date column 8]
)

Jonathan_S

So The editor is telling me
The inputs for function ‘MAX’ should be a list of numeric values

MAX(
  LIST(
  [LR Third Party Certificate]
   [LR Proposition 65]
  )
  + [Related Products][LR Product Specifications]
  + [Related Products][LR Allergen Statement]
  + [Related Products][LR GRAS Statements]
  + [Related Products][LR Organic Certificate]  
  + [Related Products][LR Gluten Free Statement]
  + [Related Products][LR NON GMO Statement]
  + [Related Products][LR Kosher Statement]
  + [Related Products][LR Radiological Statement]
)

I also thought I would Try to Remove the Blanks with this (Not sure if this is possible, or correct)
The editor still outputs the same error.

MAX(
((LIST(
  [LR Third Party Certificate]
   [LR Proposition 65]
  )
  + [Related Products][LR Product Specifications]
  + [Related Products][LR Allergen Statement]
  + [Related Products][LR GRAS Statements]
  + [Related Products][LR Organic Certificate]  
  + [Related Products][LR Gluten Free Statement]
  + [Related Products][LR NON GMO Statement]
  + [Related Products][LR Kosher Statement]
  + [Related Products][LR Radiological Statement]
) - List(""))
)

It is also possible that some entries are blank. Does this make a difference?

I’ve also tried setting all my column Types to DateTime instead.

Steve

Missing comma?

Jonathan_S

O Man

Thanks Steve,

Steve

Looking back, I omitted the comma in my suggestion, so my bad! I corrected my suggestion.