Power Automate Get SQL Rows and update to Sharepoint list

[craig_01]

Help Please.. I am new to power automate and having issues with pulling data from SQL into a Sharepoint List. Basically from what i can distil down within SQL Table has 4 x date/time columns and i believe its these that are creating the issue. 

Datetime data format within SQL table is 2023-05-29 08:30:00:000

Associated field in the Sharepoint List - 29/05/2023 08:30 AM (should look like this)

I have tested and tried to understand some expressions within the flow but still getting issues, any help would be appreciated. When i remove the date/time fields from the flow.. The others fields are populating ok.

Error in Power Automate

Flow save failed with code 'OpenApiOperationParameterValidationFailed' and message 'Input parameter 'item' validation failed in workflow operation 'Create_item': The parameter with value '"@items('Apply_to_each')?['absence_from']"' in path 'item/absence_from' with type/format 'String/date-no-tz' is not convertible to type/format 'String/date-time'.'.

Do i need to change any datetime format within the sharepoint columns also.

Any guidance on the expression would be a massive help.

[v-chengfen-msft]

Hi @craig_01 ，

Based on the error message you provided, it looks like the problem is in the "Create item" action, where the type format of the "item/absence_from" parameter does not match the value provided.

Specifically, the parameter was expected to be a datetime type, but the value provided was parsed as a string type, so a validation error occurred.

Please try:

Convert it to date format by formula

formatDateTime(items('Apply_to_each')?['absence_from'], 'yyyy-MM-ddTHH:mm:ssZ')

Best Regards

Cheng Feng

[craig_01]

@v-chengfen-msft Thanks for the reply. I have actioned what you suggested above which i believe i have done the correct via the expression against each "item".

The input body for trigger 'manual' of type 'Request' did not match its schema definition. Error details: 'Invalid type. Expected String but got Null.,Invalid type. Expected String but got Null.'.

To confirm i am doing a manually trigged flow initially to do a bulk upload of the existing SQL data into the Sharepoint list.. Then this will changed to a automated flow triggered on a new entry to SQL.

Updated DateTime FormatManual Flow

Regards

Craig

[v-chengfen-msft]

Hi @craig_01 ,

Can you tell me that all four columns are date type?
Format the date column when I provide the formula

Please do not fill in two data for all four columns.

This may affect its format, resulting in an error

This formula is only for the ['absence_from'] column.

formatDateTime(items('Apply_to_each')?['absence_from'], 'yyyy-MM-ddTHH:mm:ssZ')

Judging from the error report you provided later, the reason for the error report is that there is a null value in the value returned by SQL.
Please use if(empty(item()?['column name']),'null',item()?['column name']) to judge whether it is a null value before performing format conversion

Best Regards

Cheng Feng

[craig_01]

@v-chengfen-msft Cheng, thanks for your assistance.

Yes all four columns are datetime from SQL table.

I used the same formula in each column, but changed the [field name] to suit each column name.

I will remove the two data for each column and try what you have suggested.

Regards

Craig

[craig_01]

@v-chengfen-msft Hi Cheng, i run the command you suggested and got this error report when the flow failed.

"Judging from the error report you provided later, the reason for the error report is that there is a null value in the value returned by SQL.
Please use if(empty(item()?['column name']),'null',item()?['column name']) to judge whether it is a null value before performing format conversion"

Result>

The 'inputs.parameters' of workflow operation 'Create_item' of type 'OpenApiConnection' is not valid. Error details: Input parameter 'item/absence_from' is required to be of type 'String/date-time'. The source type/format 'String/date-no-tz' of the value '"2023-08-28T00:00:00Z"' is not convertible to type/format 'String/date-time'.

Regards

Craig

[v-chengfen-msft]

Hi @craig_01 ,

There are two problems with this column:
1. Cannot be empty
2. Must meet the ISO86 date format

Please combine these two formulas:

if(empty(item()?['column name']),utcNow('yyyy-MM-ddTHH:mm:ssZ'),formatDateTime(items('Apply_to_each')?[ 'column name'], 'yyyy-MM-ddTHH:mm:ssZ'))

Best Regards

Cheng Feng

[craig_01 · Accepted solution]

Hi @v-chengfen-msft , i am getting an invalid formula with the suggested combined formula above, i have changed the applicable column names etc..

If i apply, formatDateTime(items('Apply_to_each')?['absence_from'], 'dd-MM-yyyy') to the absence_from & equalivent to the absence_to column, i can get at least some data to pass into Sharepoint without failure..

It appears to be the restrict_from / to columns that could be the issue becasue these technically are just time.?

Any suggestions.

Thanks

Craig