cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
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
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
Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

New Super Users

Meet the Power Automate Super Users!

Many congratulations to the Season 1 2021 Flownaut Crew!

Microsoft Ignite

Microsoft Power Platform: 2021 Release Wave 1 Plan

Power Platform release plan for the 2021 release wave 1 describes all new features releasing from April through September 2021.

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (24,483)