cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Community Support
Community Support

Re: Convert Date Field to String to Number

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
Highlighted
Frequent Visitor

Re: Convert Date Field to String to Number

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).

 

 

Highlighted
Dual Super User III
Dual Super User III

Re: Convert Date Field to String to Number

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!



Highlighted
Community Support
Community Support

Re: Convert Date Field to String to Number

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
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,953)