Compose even when a Null date is found HELP!

[Skiroy]

Hello, 

Looking for some assistance with some SharePoint date formatting when a null value is present. 

I've taken some of the inspiration from the following post, but I'm not sure it completely applies to my particular situation. 

My flow is as follows: 

The body in the Apply to each 3 is coming from the output of the filter array above. 

Open Compose date is looking at the possible dates being returned from the SP list (theoretically without the null values due to the filter array, but they're still hidden in there it seems)

In prior iterations, until I ran across a null / blank date which then threw an error, I was using the following formula.

formatDateTime(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'dd-MM-yyyy')

Now I'm trying to scrub out the Null fields. 

if(
 equals(
     coalesce(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'NULL'),'NULL'),'',
     formatdatetime(coalesce(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'01-01-1901'),'dd-MM-yyyy'))

My other Compose Year, Month, and Quarter were referencing the outputs from Open Compose Dates

Year

formatDateTime(outputs('Open_Compose_Date_2'), 'yyyy')

Month

formatDateTime(outputs('Open_Compose_Date_2'), 'MM')

Quarter / Year

if(or(equals(outputs('OpenMonthNum_2'), '01'), equals(outputs('OpenMonthNum_2'), '02'), equals(outputs('OpenMonthNum_2'), '03')), 'Q1', if(or(equals(outputs('OpenMonthNum_2'), '04'), equals(outputs('OpenMonthNum_2'), '05'), equals(outputs('OpenMonthNum_2'), '06')), 'Q2', if(or(equals(outputs('OpenMonthNum_2'), '07'), equals(outputs('OpenMonthNum_2'), '08'), equals(outputs('OpenMonthNum_2'), '09')), 'Q3', if(or(equals(outputs('OpenMonthNum_2'), '10'), equals(outputs('OpenMonthNum_2'), '11'), equals(outputs('OpenMonthNum_2'), '12')), 'Q4', 'N/A'))))

Unfortunately, If I have the Open Composure date 2 set to 

formatDateTime(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'dd-MM-yyyy')​

I throw the following error: 

Unable to process template language expressions in action 'Open_Compose_Date_2' inputs at line '1' and column '8828': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'. Please see https://aka.ms/logicexpressions#formatdatetime for usage details.'.

When I try to solve for the previous error,  I set the Open_Compose_Date_2 to the following formula, 

if(
 equals(
     coalesce(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'NULL'),'NULL'),'',
     formatdatetime(coalesce(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'01-01-1901'),'dd-MM-yyyy'))

and I throw an error code on the following step (Compose Year)

Unable to process template language expressions in action 'Open_Compose_Year_2' inputs at line '1' and column '8828': 'In function 'formatDateTime', the value provided for date time string '' was not valid. The datetime string must match ISO 8601 format.'.

My thoughts were it was still not processing the null value in this step, so I tried the following (note: it didn't seem like there was an output from this step but it didn't give me the option to export the output to check, so who knows) 

if(
   equals(
      coalesce(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'NULL'),'NULL'),'',
      formatdatetime(coalesce(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'1901'),'yyyy'))

 Not really sure what I'm missing here, but it's incredibly frustrating and I really could use some help on this one.   

@Expiscornovus@Paulie78@tom_riha@v-monli-msft

[tom_riha]

Hello @Skiroy ,

I think your problem is in the first expression you're using, if it's a date column it doesn't have /Value part. It's just the date column. 

not ok:
formatDateTime(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value'],'dd-MM-yyyy')

ok:
formatDateTime(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4'],'dd-MM-yyyy')

[Skiroy]

@tom_riha 

Hey Tom, 

Thanks for the reply. I tried implementing your solution of 

formatDateTime(items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4'],'dd-MM-yyyy')

 in the Open Compose Date 2 section

And I came back with the following error. 

Unable to process template language expressions in action 'Open_Compose_Date_2' inputs at line '1' and column '8828': 'The template language function 'formatDateTime' expects its first parameter to be of type string. The provided value is of type 'Null'.

To complicate things, this field ['LU_x003a_4_x002e_01_x0020_x002d4'] is a LookUp column that is referencing a date column in a source list. In my dynamic selections I have the option for the column as is, column ID, and column Value.

On another note, the filter array seems to not output the correct number of items if I do not reference the /Value when assessing if that item <> null.

When I include /Value in the filter array I'm getting the correct number of items in the output, but that still doesn't help the following step which is the Open Compose Date 2. 

[tom_riha]

Hello @Skiroy ,

ok, I thought it's a date column. I'd try adding a compose action into the flow before the 'Open Compose Date 2', just to display the value in the dynamic content.

items('Apply_to_each_3')?['LU_x003a_4_x002e_01_x0020_x002d4/Value']

Maybe if you see what values enter the expression it might give you a hint what's wrong.