Convert Date Field to String to Number

[BrentITSS]

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

[BrentITSS]

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

[efialttes]

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!

[v-litu-msft · Accepted solution]

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'),'-','')

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.