Unanswered

Using Initialize Variable for Dates when Field May be Blank

(0) Share

Report

Posted on by JesWils

Hello. I'm quite new to using PA and I'm teaching myself for the most part so I can really use some help (also apologies for the incorrect PA language!)

I have created a flow for a MS Form submission where there is branching which results in empty date fields depending on what the individual who's filled out the form has chosen. In the flow, I have variables initialized for formatting the dates into UK format, but when there is an empty date field I get an error message:

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

I'm assuming that it's because there's a blank date field but perhaps it's not. Any help on how to solve this would be great. I've looked online but I probably don't know the right questions to ask. Thanks for your help!

Categories:

General topics

I have the same question (0)

All responses (10)

Answers (0)

wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

You'll need to use the empty() expression which checks if a field/value is blank and returns true if it is empty. Then use that with an if() expression.

It will be something like If(empty(DateField),'',parsedatetime(DateField))

So it checks if it is empty, and if it is, then it will just remain blank with two single quotes '' but if it is not empty, then it will use the parsedatetime expression.

That should at least get the expression working.

Was this reply helpful? Yes No
JesWils 20 on at

Like (0)

Report
Copy link

Link copied!

Thank you so much for your help. I've updated the expressions as suggested. I've run another test and the first variables I've used the new expression in seem okay; however, I've now got an error for the second variable I'm using to format the date on the form which is stopping the testing:
The 'start index' for function 'substring' must be equal to or greater than zero and must be less than '0' which is the length of the string.

The expression I'm using is:
concat(substring(variables('CurrentPlaceAdmisDateasText'),8,2),'-',substring(variables('CurrentPlaceAdmisDateasText'),5,2),'-',substring(variables('CurrentPlaceAdmisDateasText'),0,4))

Can you offer some further help?

Thanks again!

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

I would need to see a photo/screenshot of the steps. And I especially need to see the format and results of the data that is being used. I don't know what data/format you are using as inputs to the variable CurrentPlaceAdmisDateasText, so I can't really help solve an error.

For the error, I'm guess it has to do with the values you are putting into the variable CurrentPlaceAdmisDateasText and them not being filled.

https://manueltgomes.com/reference/powerautomate-function-reference/substring-function/

If you checkout the site link above, it gives some examples of using substring() expression. You are probably asking it to get values from specific place in the string, but the string doesn't contain the values so it errors. Like if you say substring('123456',8,10) then it will error because there are not 8 characters in '123456' value.

Was this reply helpful? Yes No
JesWils 20 on at

Like (0)

Report
Copy link

Link copied!

Thanks for your reply. Below is an image of the first two variables giving me grief in the flow:
The data being used is being taken from the MS Forms submission and they are coming through as YYYY/MM/DD rather than the other way around which is why I have added these variables. I have 4 other variables before these variables that are working just fine, but they are against fields on the Form which will not be empty as they're required fields, as opposed to these and the other variables which are against fields which could be empty.

I'm not sure if this is the information you need but as I said, I'm pretty new to this and I don't know all of the language yet. I'm grateful for your help though.

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

Very close, but I need to see the equation/expression that is in each. So what is the rest of the if() expression in CurrentPlaceAdmisDateasText?

I know the equation for the variable CurrentPlacAdmissionDate is:
concat(substring(variables('CurrentPlaceAdmisDateasText'),8,2),'-',substring(variables('CurrentPlaceAdmisDateasText'),5,2),'-',substring(variables('CurrentPlaceAdmisDateasText'),0,4))

The other thing I need to see is the value(s) of what is being used in the variable CurrentPlaceAdmisDateasText. So for example if your expression is if(empty([FormDate]),'',[FormDate]) I need to see the [FormDate] output by itself. So you could put a Compose step above initialize variable, simply drop the [FormDate] dynamic content into the Compose step. Run the flow, screenshot the output results.
I want to see the output when someone fills in a date as well as what it looks like when it is empty or skipped on the form.

Was this reply helpful? Yes No
JesWils 20 on at

Like (0)

Report
Copy link

Link copied!

Hi. Thanks for your reply. I see what you mean now. The expression is:
if(empty(outputs('Get_response_details')?['body/rc7273ad997b64527a955f0a725834360']),'',parseDateTime(outputs('Get_response_details')?['body/rc7273ad997b64527a955f0a725834360']))

This is a screenshot of the outputs for the variable.

My aim is to have it as dd-mm-yyyy.

Thanks again for your help!

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (0)

Report
Copy link

Link copied!

Is the Compose 96 value '2023-06-01' you showed the output of:
outputs('Get_response_details')?['body/rc7273ad997b64527a955f0a725834360']

OR

Is the Compose 96 value '2023-06-01' the output of one of the variables?

Just want to be sure I'm checking the correct thing.

Was this reply helpful? Yes No
JesWils 20 on at

Like (1)

Report
Copy link

Link copied!

Hiya. It's the Get Response Details output, not the variable. Thanks!

Was this reply helpful? Yes No
wskinnermctc 6,519 Moderator on at

Like (1)

Report
Copy link

Link copied!

Just to start from square one, I want to show what the ParseDateTime() expression will do. You are providing a datetime string value, and then telling the parseDateTime() what format that string value is in.
So if your initial value is 2023-06-01 it is basically 'yyyy-MM-dd' format. So your parseDateTime() will be parseDateTime('2023-06-01','en-us','yyyy-MM-dd') so it knows what value it is reading.
If your initial value was 06-2023-01 as in 'dd-yyyy-MM' then your parseDateTime() expression would be parseDateTime('06-2023-01','en-us','dd-yyyy-MM')

It then turns it into a legitimate datetime value. So if there is no time value in your original string, it will be converted to that date at midnight in ISO format. 2023-06-01T00:00:00.0000000

Then if you want to strip off the time that was just added, you can use the expression formatDateTime() which you use the new ISO datetime, and put the format you want which is 'dd-MM-yyyy' output.

Parse Date Time Sample

I made that quick example just so we know what the expression is trying to do.

Below I'm going to show what happens when the initial string is blank. We can use the empty() expression to check the string and then either leave it blank '' or use the parseDateTime() expression if it is not blank.

However the issue is the error on the next step after parseDateTime() where the formatdatetime() expression is used. Since it doesn't have anything like empty() helping it prevent an error, it will error and break the flow.

Empty ParseDateTime Expression

I made this example to show why you are getting an error in your concat() expression for the CurrentPlacAdmissionDate variable. The previous variable CurrentPlaceAdmisDateasText has an empty() expression to keep it from error, but when the variable CurrentPlacAdmissionDate tries to use concat() on the variable that is empty, then it will have an error.

Was this reply helpful? Yes No
JesWils 20 on at

Like (0)

Report
Copy link

Link copied!

Thank you for your help with this. I actually got the flow working properly with the right dates, then the next day it wouldn't work again. So frustrating. I'm going to keep trying but I thank you very much for your help.

Was this reply helpful? Yes No