How to retrieve current date and specified time inside a Variable?

[Usernametwice23]

Hi all

I have a flow connected to Dataverse. Right now I have some users with different start times next to their record in dataverse but as you know, dataverse does not allow time only columns so Im stuck with a past date next to their name which is why im trying to retrieve the current date and time associated with the employee record in my flow. 

So this is what I have tried - 

Initialize Variable 

Set  Variable with the employee start time record it's pulling from dataverse (01/11/2021 7:00AM) which is past date. 

 Set condition so that if time now is less than whatever time the employee has next to them, update rows.  

 

The issue here is that the variable is picking up the set dataverse date and time in the employee record. What I want is the variable to have the current date (today) and whatever time is set in the dataverse next to employee record. 

So basically I need to somehow modify this variable below so that it still picks up the employee record in dataverse but instead of having the past date, I need today's date and whatever time is next to that past date in dataverse. 

Here are some records in dataverse that the variable would retreive - 

 So these dates are past dates but have the correct times I need to retrieve. 

Let me know if you need any more info, I'm still learning flows so thanks for the help. 

 

[Expiscornovus]

Hi @Usernametwice23,

You could use an expression to concat the utcnow (with only the date) and the time value of your custom field.

Below is an example of that approach.

In this example I am using the Created field. You need to replace that by your Dataverse field. Also change the formatting if needed. I assumed you wanted dd/MM/yyyy HH:mm

concat(utcNow('dd/MM/yyyy'), ' ', formatdateTime(outputs('Get_a_row_by_ID')?['body/createdon'], 'HH:mm'))

[Usernametwice23]

Hi @Expiscornovus 

THanks i have tried this but I am getting an error when I run the flow. This is the error - 

 This is the expression I am using - 

concat(utcNow('dd/MM/yyyy'), ' ', formatDateTime(outputs('List_Employee')?['body/fhp_defaultstarttime'], 'HH:mm'))

I cant see anything wrong with it so im unsure what the error is about. 

The 'List Employee' branch is filtering based on employee code so it should have no trouble finding the correct row for the date/time,

Hoping you can help if possible. Cheers. 

[Expiscornovus]

Hi @Usernametwice23,

I just noticed in the screenshots of your opening post that there can be rows with empty time values in the fhp_defaultstarttime field.

The current expression can't handle that. This is the reason why you are getting a null error. What do you want to do when it is empty? We can adjust the expression so it can handle the error. Or go for the easy solution and always fill in a date time value in that field 😉

[Usernametwice23]

Hi @Expiscornovus 

Okay that makes sense now. The issue is that some of the employees do not yet have start times so there will be rows with empty values. It basically needs to somehow ignore the variable/condition if the value in row is empty and continue the rest of the flow like normal, which would just record the employee start time as current date/times and ignore the condition. Hoping its not too difficult to adjust the expression. It sounds like I might need to use another condition? 

Thanks for the help. Im learning here so I appreciate it. 

[Expiscornovus · Accepted solution]

Hi @Usernametwice23,

You could add the if and empty functions to the expression I shared earlier.

Below is an example of that.

When the value is empty, the expression prints the string 'Empty'. You can use that to check in a condition action.

if(empty(outputs('List_Employee')?['body/fhp_defaultstarttime']), 'Empty', concat(utcNow('dd/MM/yyyy'), ' ', formatdateTime(outputs('List_Employee')?['body/fhp_defaultstarttime'], 'HH:mm')))

[Usernametwice23]

Hi @Expiscornovus 

Thanks for that. It seems that I am really close because I can now get the correct time (hh:mm) for users that have set times but the flow is giving me dates as 1 day behind. I have started a new post for this and I'll mark this solution as resolved because I think it would work for most people. I think because I am from Australia NSW, UTCNOW is different for us. 

Here are the formula's I have tried to resolve it - 

if(empty(items('Apply_to_each_Employee')?['fff_defaultstarttime']), 'Empty', convertFromUTC(UtcNow(), 'AUS Eastern Standard Time', concat(utcNow('yyyy/MM/dd'), ' ', formatdateTime(items('Apply_to_each_Employee')?['fff_defaultstarttime'], 'HH:mm'))))

if(empty(items('Apply_to_each_Employee')?['fff_defaultstarttime']), 'Empty', concat(addDays(utcNow(),1),'yyyy/MM/dd', ' ', formatdateTime(items('Apply_to_each_Employee')?['fff_defaultstarttime'], 'HH:mm'))))

Just need to get that date moved 1 day forward and i'll be set. 

Thanks for all your help.