firstly I would like to apologise for the lengthy first post, hopefully I can make it as concise as required.
Background - working on a company project involving Salesforce (Front end) and Sharepoint (document repository). In this context, various metadata is being pushed from SF to SP as part of a workflow/lifecycle of a record. All required meta is flowing in into specified columns in SP nicely.
Objective - The meta stored in SP is required to be used to populate areas of word documents via the use of quick parts. The required date format for any quick parts containing dates is: 29-JUL-2020 / DD-MMM-YYYY. I have a total of 8 date fields that need to be pushed into documents in the required format via the quick parts mechanism.
Problem - The problem is primarily to do with columns containing dates, so far it appears impossible to achieve the required end from the Salesforce side, so currently looking for a solution in SP and I'm frustratingly close. Before I delve into the detail, I would like to state that the site collection regional settings are configured correctly for our locale (UK = (UTC) London, English UK, General Gregorian). So onto the details:
- SF pushes the date values in the following format = 2020-07-29 09:58:37 a) IF the SP column storing these values is configured as "Text", the data is displayed "2020-07-29 09:58:37". b) IF the SP column is configured as Date & Time + Standard, the data is displayed "29-07-2020 00:00"
Solution 1 attempt - Create a calculated field using an excel formula to lookup the source field and convert the date to the required format in the second field. Formula used "=IF(ISBLANK([SF Owner Date]),"",TEXT([SF Owner Date],"DD-MMM-YYYY"))". This works successfully in as much as the calculated field displays the date format as required. What I didn't know before I started is that MS word does not support the use of calculated fields in Quick Parts. Great :/
Solution 2 attempt - Create a Flow/Automate to on event convert the date format in the source field to the required display format, thus being able to use the source field in Quick Parts. This attempt is currently unsuccessful, despite the flow functioning without errors and executing when a record is uploaded and/or updated in the library. The outputs I have been able to achieve are 1) no change of status quo, or 2) blank the field out. The current flow logic looks like this:
- When an Item is Created or Modified: a) Condition: (triggerBody()?['ID'] = (triggerBody()?['Task_x0020_Performed_x0020_on_x0020_Owner'] aa)IF = Yes - Compose function: formatdatetime(triggerBody()?['SF_x0020_Owner_x0020_Date'],'dd-MMM-yyyyThh:mm') ab)Update Item: (triggerBody()?['ID'] in ID field AND (triggerBody()?['Task_x0020_performed_x0020_onx0020_owner'] + outputs()?['Compose'] in SF Owner Date field.
Prior to adding the Update Item step, the flow was executing without errors, but no state change in the values. After adding the step, the flow executes without errors, but the field values are being deleted/blanked.
For completeness, I have attempted both solutions to SF fields configured as text and date & time fields. The behaviour is the same in both scenarios with both solutions, the only difference is when the SP date & Time formatting is used, the time stamp is always 00:00, but this isn't an area of interest right now.
I'm open to any feedback towards a solution with either approach to achieving my objective, or even another alternative, I'm currently just doing brute force to crack a nut and getting frustrated.
Thanks in advance.
P.S. prepared to share more specifics environment if required, but would prefer to do so in private.