Flow Help - issue with Strings vs. Arrays

[HG_2021]

Hiya,

I'm trying to build a scheduled flow whereby an automated email is sent if a null value is recorded after 3 days of a date value in a separate column.

I.e. Item added to SP List, 27/08/21 [Column A] - Scheduled flow checks date input in [Column B] 29th/30/31st

On the 31st, no date value is in [Column B], the flow runs and an email is sent to confirm that [Column A] hasn't been actioned.

How can I build this? The columns are not going to be 'empty' to start with and will always be updated/contain data, I have been trying to use the 'String' formula:

@And(less(item()?['@{items('Apply_to_each')?['ColumnA']}'], addDays(utcnow(), -3)),not(equals(coalesce(item()?['@{items('Apply_to_each')?['ColumnB']}'],'null'),'null'))

So far no luck and error of:

The 'from' property value in the 'query' action inputs is of type 'String'. The value must be an array.

Any help much appreciated!

Thank you 😁

[Expiscornovus]

Hi @HG_2021,

You could use the utcNow function and compare that with the Column A datetime field. With the and function you can also check if Column B is empty.

Below is an example expression for that. The outcome of this is either true or false.

and(equals(utcNow('yyyy-MM-dd'), formatdatetime(adddays(items('Apply_to_each')?['ColumnA'], 3), 'yyyy-MM-dd')), empty(items('Apply_to_each')?['ColumnB']))

[HG_2021]

Hi There,

Thank you for your reply, unfortunately it's still chucking out the same error requesting an array, not a string:

The 'from' property value in the 'query' action inputs is of type 'String'. The value must be an array.

Do you know any way around this at all? Driving me mad!

Thank you 😁

[Expiscornovus]

Hi @HG_2021,

Can you share a screenshot of the action were you are using this expression and/or getting this error? 

[HG_2021]

Hi There,

Thank you for your reply, please see below

Thanks again 😁

[Expiscornovus]

Hi @HG_2021,

I see you are using a Filter array action. Based on the error it looks like you are inputting a string value instead of an array in that action. Is there a specific reason why you are using a filter array action within an apply to each? Can you share a screenshot of the setup of that action in edit mode?

Alternatively, you could remove the filter array and use a condition action with that expression instead.

[HG_2021]

Hi there,

Thank you for your response, it's still not a fan of the coding. 

I have set out as per a condition as you suggested as it doesn't necessarily require a Filter Array but I am still experiencing some glitching in the formula.

Thank you again for your help so far! 😁

[Expiscornovus]

Hi @HG_2021,

It looks like some of your items have an empty Column A, is that correct?

If so, we need to add another check for empty.

and(if(empty(items('Apply_to_each')?['ColumnA']), false, equals(utcNow('yyyy-MM-dd'), formatdatetime(adddays(items('Apply_to_each')?['ColumnA'], 3), 'yyyy-MM-dd'))), empty(items('Apply_to_each')?['ColumnB']))

[HG_2021]

Hi There,

Sorry for the slow reply!

This has made the flow run so thank you - however it isn't then carrying out the next action as the system is returning 'False' There are empty items in both Column A and Column B I don't know if this effects the formula at all?

Thank you again for your help 😁

[Expiscornovus]

Hi @HG_2021,

I was assuming that if Column A is empty it is hard to determine what three days later is, since you don't have any value 😁. That is why I am returning false. 

When Column A is not empty it will check if it is three days later.

Do you have a different requirement for an empty Column A?

[HG_2021]

Hello hello!

So if Column A is empty it can be ignored. If Column B is still empty 3 days after a date input in Column A the Flow needs to fire off an email to advise Column B needs to be followed up.

Thank you very much for your help 😁

[Expiscornovus]

Hi @HG_2021,

In that case the last expression I shared yesterday should be ok 😁 

[HG_2021]

PLEASE IGNORE THIS RESPONSE - I AM A MUPPET

Hi there,

Thank you for confirming, I've just looked at it this morning and it is running but I'm not getting emails from it.

I've put some test files in which fit the criteria of a date in Column A and then nothing in Column B (Column A's date set 3 days earlier of course). Nothing through this end?

It's showing all inputs coming back as False, is this correct?

Thank you again! 😁

[HG_2021]

Hi There - Me again!!

Actually resolved it, it happily runs under the false which is FAB!!

Another quick one, I have a Column C 👀

Column C contains an additional option that I need it to filter by before it sends the email.

I've tried adding an additional condition but as the options in Column C are a 'Choice' it won't filter by it.

I need it to filter by 'Live Sale' so only those returning True to that send out emails, is there a work around for this that you know of at all?

Big thank you again!! 😁

[Expiscornovus]

Hi @HG_2021,

With a choice column label you can use the value property of that field. So, that would be ColumnC/Value.

In your case you can add another and function. Try the expression below:

and(and(if(empty(items('Apply_to_each')?['ColumnA']), false, equals(utcNow('yyyy-MM-dd'), formatdatetime(adddays(items('Apply_to_each')?['ColumnA'], 3), 'yyyy-MM-dd'))), empty(items('Apply_to_each')?['ColumnB'])), equals(items('Apply_to_each')?['ColumnC/Value'], 'Live Sale'))

[HG_2021]

Hi there,

Thank you for the additional, unfortunately it's not as yet filtering on the Column C 'Live Sale' and is still providing me with all of the data instead of the filtered options.

Is there another way of filtering at all?

Thank you again 😁

[Expiscornovus]

Hi @HG_2021,

Can you share a screenshot of the setup/configuration of that Column C?

It might have a different internal name for instance. And does your choice column allow multiple values?

[HG_2021]

Hi there,

Sorry for the slow reply, I have been faffing with some other projects at this end.

Column C is known just as Status [this is what I have input into the formula already], Status allows multiple values which have been pre-input as per your screenshot so it needs to filter on Live Sale and ignore the other options.

There is also the vague possibility that prior to me getting to the system Status has been left blank, I presume we need to account for this also?

Status options are presently:

Live Sale
Aborted
Exchange
Completed
Paid
On Hold
-

- being the default value

Thank you again so much for your help, it's greatly appreciated 😁

[Expiscornovus]

Hi @HG_2021,

Because it allows multiple values to be selected we need to take a different approach for that column.

1. Place an Filter Array action just before your condition. Use the ColumnC in the from and the ColumnC value field to check if it is equal to Live Sale.

2. Use this new expression for the condition action. In the last part of this expression we are checking of the Filter Array results are not empty (which means it found a selection of the Live Sale option).

and(and(if(empty(items('Apply_to_each')?['ColumnA']), false, equals(utcNow('yyyy-MM-dd'), formatdatetime(adddays(items('Apply_to_each')?['ColumnA'], 3), 'yyyy-MM-dd'))), empty(items('Apply_to_each')?['ColumnB'])), not(empty(body('Filter_array'))))

[HG_2021]

Thank you for your reply!

I have input as the above screenshots but am receiving the below error, do you have any ideas?

Thank you again for your help! 😁