Trying to duplicate sharepoint list using Flow

(0) Share

Report

Posted on by MacGizmo

Trying to do a simple action of duplicating one sharepoint list to another list.

Both source (QuoteLogs) and destination (QuoteLog Read only) list have the same exact column and data type.

The idea is that any newly created item or any item get modified on the source list should be copied to destination list.

As you can see the source list have a few items on it and the destination list is empty

sharepoint list.jpg

Below is the flow that I created but still not working:

Trigger start when a new item created on the list or if existing item get modified.

It will then look for the destination list in order to do a comparison between each list Quote No.

If Quote No from each list is the same then it will do an update item on the destination list instead of creating a new item

If both quote number is not the same then it should do create item on the destination list.

However when I run the flow, it come up with "the input array is empty". At this point I'm a bit lost as i've tried other suggestion using empty or length function but i never could not get it to work maybe because i got the logic wrong or something.

flow result.jpg

Any help will be appreciated. Thanks

Categories:

General topics

I have the same question (0)

All responses (7)

Answers (0)

Sort by

faustocapellanj 1,662 on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MacGizmo

I've done something like in the past and this is the approach I took:

In the "Get items" action, you want to click on Show advanced options. In there, you want to add a Filter Query to retrieve only the items that match the name in the Title column. See the screenshot below.

Since the "Get items" action will try to retrieve a single item, the "Apply to each" will not be necessary. At least in my case it wasn't.

After the "Get items" action, add a "Condition". Click on "Edit in advanced mode" and enter the expression below:

@equals(length(body('Get_items')?['value']), 0)

The expression in the condition checks if the results from the Filter Query is false, meaning it didn't find a matching item. If it's false, Flow then proceeds to create the item in the second list. If it's true, Flow then proceeds to update the item in the second list. Please try it and let us know. And do not hesitate to reach out if you have any questions or concerns.

Thanks.

Was this reply helpful? Yes No
MacGizmo 32 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @faustocapellanj

Thank you for suggestion. It sort of works but create another issue 🙂

FYI only. On both sharepoint list, I specifically untick Title column is not required (due to PowerApps limitation) and hide them from the list view. So the first column (Quote No) is actually not a title, having said that the column that have a unique value should be the Quote No.

So I did update the flow although it partially works, it also create another issue.
Here are the issue:
1. The flow works when I update an item for the first time. The second time i update the same item, it will create a new item on the second list i assume this is because i don't use the column title and when I check the Flow run history it indeed return a "true" result rather than "false". If I change the filter query to something like below I get an error (see the transcript below).

{
  "message": "The expression \"Quote No eq 'XXXX_Kolicoat_JK - 9'\" is not valid.\r\nclientRequestId: b96ddaac-3b5c-4d54-905c-a4d57fa957c8\r\nserviceRequestId: 85b0659e-f078-5000-77d9-b0ac702a89a2",
  "status": 400,
  "source": "https://XXXX.sharepoint.com/sites/msanz/quotecollections/_api/SP.APIHubConnector.GetListItems(listName='8755dca2-89b8-417b-ad01-eaa2b675a04b',queryOptions=@q)?@q='%2524filter%3dQuote%2520No%2520eq%2520%2527XXXX_Kolicoat_JK%2520-%25209%2527'",
  "errors": [
    "-1",
    "Microsoft.SharePoint.Client.InvalidClientQueryException"
  ]
}

2. Also for newly created item on the source list, for some reason the flow created multiple new item on the destination list. It would create 3 or or more of the same item on the destination list, is there something that I'm missing here?

Look forward to your feedback.

Was this reply helpful? Yes No
faustocapellanj 1,662 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MacGizmo

I think your issue is in the Filter Query. I will assume the Quote No column has a space in it. Flow and OData specifically doesn't like spaces, so you need to update Quote No to Quote_x0020_No. In case you are not familiar with it, _x0020_ indicates a space. Please try that and let me know.

Thanks.

Was this reply helpful? Yes No
v-xida-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @MacGizmo,

Could you please share more details about your SharePoint list?

I have created two SharePoint lists on my side and the data structure of it as below:

Note: The Source list and the Destination list have same data structure and the Quote No column is the unique column.

I agree with @faustocapellanj's thought almost, I think there is something wrong with the formula of the Filter Query field of the "Get items" action. Please modify your formula as below:

Quote_x0020_No eq '@{triggerBody()?['Quote_x0020_No']}'

I have made a test on my side and don't have the issue that you mentioned, please take a try with the following workaround:

Add a "When an item is created or modified" trigger, specify Site Address and List Name set to the Source list.

Add a "Get items" action, specify Site Address and the List Name field set to Destination list. Within Filter Query field, type the following formula:

Quote_x0020_No eq '@{triggerBody()?['Quote_x0020_No']}'

Note: The @{triggerBody()?['Quote_x0020_No']} expression represents the Quote No dynamic content of the trigger.

Add a Condition, click "Edit in advanced mode", type the following formula:

@empty(body('Get_items')?['value'])

Within "If/yes" branch of Condition, add a "Create item" action, specify Site Address and List Name set to the Destination list. These fields that you want to fill set to corresponding dynamic contents of the trigger.

Within "If/no" branch of Condition, add a "Apply to each" action, input parameter set to output of the "Get items" action. Within "Apply to each" action, add a "Update item" action, specify Site Address and List Name set to the Destination list. Id field set to ID dynamic content of the "Get items" action, Title field set to Title dynamic content of the "Get items" action.

Image reference:

The flow works successfully as below:

Best regards,

Kris

Was this reply helpful? Yes No
MacGizmo 32 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @faustocapellanj

Using the _x0020_ as a spare on the query does not seems to work as I got an error bad gateway, below is the actual message:
{
  "error": {
    "code": 502,
    "source": "europe-001.azure-apim.net",
    "clientRequestId": "88a09f66-336e-43a3-9941-2f93c2e90903",
    "message": "BadGateway",
    "innerError": {
      "status": 502,
      "message": "Column 'Quote_x0020_No' does not exist. It may have been deleted by another user.\r\nclientRequestId: 88a09f66-336e-43a3-9941-2f93c2e90903\r\nserviceRequestId: f4ec659e-80a9-5000-9911-64a1f591f050",
      "source": "https://XXXX.sharepoint.com/sites/msanz/quotecollections/_api/SP.APIHubConnector.GetListItems(listName='8755dca2-89b8-417b-ad01-eaa2b675a04b',queryOptions=@q)?@q='%2524filter%3dQuote_x0020_No%2520eq%2520%252720180510_XXXX_Kolicoat_JH%2520-%252013%2527'"

I then tried to change the "Quote No" column to "QuoteNo" to remove the space issue on both list and I still get the same error message as above. Here is the updated filter query

Am I missing something here?
Thanks

Was this reply helpful? Yes No
MacGizmo 32 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-xida-msft

Okay so my current sharepoint list is as per below. I sort of made a slight changes to the QuoteNo column to remove the space issue. The other differences that I can see between your list and mine is that I don't use Title column unlike your example. So both of my list do not have the Title column maintain / mandatory and also it is hidden from the "All View"

Here is my current flow as you can see that I do not have Title as mandatory (*) both on Create Item and Update Item.
The interesting part is that if I change the filter to " Title eq 'Title' " it works fine on the Get Items part but since it is not unique it won't work for the second time like I mentioned in my response to @faustocapellanj
Thanks

Was this reply helpful? Yes No
faustocapellanj 1,662 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @MacGizmo,

I don't think your issue is having the Title column hidden due to PowerApps. You can use pretty much any column as a unique identifier to compare both lists.

Was this reply helpful? Yes No