cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BrentITSS
Frequent Visitor

Convert Date Field to String to Number

I have field: [Transaction Date] in SharePoint list. 17/01/2020.  I also have a calculated field called [Transaction Date Number] - it stores a number: 20200117 (taken from the date field = year month day).

 

I use PowerApps and to avoid the delegation limitations i use the [Transaction Date Number] to help filter galleries in PowerApps (which is the only reason for the calculated column).  The problem i'm having is that in FLOW i cannot reference that calculated field (otherwise problem is solved) - this results in me trying to work out how to 're-create' the calculation in FLOW (i.e. take the transaction date = 17/01/2020 (it will be variable of course - this 17/01/2020 is just an example) - so take 17/01/2020 and turn that into 20200117 for a new "number" type field = "DateNumber".

 

I am not skilled enough to work out how to do this in FLOW (is it even possible)?

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @BrentITSS,

 

It is hard to do it without redoing the calculation in FLOW, because there is no direct function could convert the date into the string and process it into the format you want. You could refer to this doc about the SharePoint calculate function reference:

https://support.office.com/en-us/article/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b...

 

However, in Flow, it is easier to do it, you could set the DateNumber column as a text column, "When an item created or modified">>"Update item":

replace(formatDateTime(triggerBody()?['InternalDate'],'yyyy-MM-dd'),'-','')

Annotation 2020-04-20 110955.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
BrentITSS
Frequent Visitor

I think i just worked it out by mistake...maybe someone more savvy can help confirm this.  I simply added an expression on the [DateNumber] field: fx  triggerBody()?['Transaction_x0020_Date_x0020_Number'] - so the calculated field is copied across to the number field - exactly what i wanted. 

 

So even though you can't refer to the actual calculated field from SharePoint - it seems to me that you can indirectly refer to it without redoing the calculation in FLOW - as long as you have a field to map the triggerBody function to.

 

--well, at least this is what i think the function is doing (certainly seems that way from the 3 tests i've carried out so far).

 

 

Hi!
Flow and calculated columns are not good friends. Did you consider replacing calculated column, usin a single line of text instead, and implement a flow that would do such calculations everytime the item is created or modified? Please note you must be careful not to enter in a neverending loop (i.e. item updated- flow executed - flow updates item - item updated - flow executed...)
Thanx!


Each time you click on any of our inspiring answers 'Thumb up' icon...
...an ewok scapes from the stormtroopers.

Be grateful, Thumbs up! Save the Galaxy for free!


Escribo sobre Power Automate en:
https://medium.com/anyone-can-automate/

Proud to be a Flownaut!



Hi @BrentITSS,

 

It is hard to do it without redoing the calculation in FLOW, because there is no direct function could convert the date into the string and process it into the format you want. You could refer to this doc about the SharePoint calculate function reference:

https://support.office.com/en-us/article/examples-of-common-formulas-in-sharepoint-lists-d81f5f21-2b...

 

However, in Flow, it is easier to do it, you could set the DateNumber column as a text column, "When an item created or modified">>"Update item":

replace(formatDateTime(triggerBody()?['InternalDate'],'yyyy-MM-dd'),'-','')

Annotation 2020-04-20 110955.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Top Kudoed Authors
Users online (2,201)