Answered

Power Automate Get SQL Rows and update to Sharepoint list

(0) Share

Report

Posted on by craig_01

153

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.

Categories:

Building flows

Capture2.JPG

Capture1.JPG

I have the same question (0)

All responses (7)

Answers (1)

v-chengfen-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
craig_01 153 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
v-chengfen-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
craig_01 153 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
craig_01 153 on at

Like (0)

Report

@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

Was this reply helpful? Yes No
v-chengfen-msft on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

craig_01 153 on at

Like (0)

Report

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

Was this reply helpful? Yes No