FIlter array by both path and modified date

[StretchFredrik]

Hello!

I am wondering how to format my filter array step to get the following:

I got 2 arrays that are the files from 2 different libraries that are supposed to be "synced" (The same files in both libraries) where one of the libraries are considered the "source" and the other library are just copies.

Now i dont want to loop every item just to check if its already "synced" or not, which is why im trying to do it in a filter array step.

Basically i want to filter out any files that have the same URL and where the modified date is less in the source than in the "mirrored library" (This means that it's already the correct version in the mirrored library).

Any help is very much appreciated. 😃

Maybe you can guide me in the right direction? @Expiscornovus 

[grantjenkins]

I've got an idea on how to do this within a single Filter array, but just off to sleep now so won't be able to get you something until later (12:30AM for me at the moment). If someone else can provide a solution prior to that then even better 🙂

Are they both Document Libraries that would have identical folder structures/files?

Assuming this would be a scheduled flow that ran daily/weekly?

[Expiscornovus]

Hi @StretchFredrik,

Normally I would say, have a look at the Except method described in this blog:

https://pnp.github.io/blog/post/comparing-two-arrays-without-an-apply-to-each/

However, you want to check two things (Url and Modified date time).

Only workaround I can think of at the moment is getting like the max modified date as the latest sync time. Not a great workaround because it doesn't compare it with the modified of the target item itself, but just with the max modified date time of the whole collection of items.

But because it is a sync process, that might be ok? 😁

@and(contains(body('Select_-_Target_Paths'), item()['File']), greater(ticks(item()['Modified']), max(body('Select_-_Target_Modified'))))

[StretchFredrik]

Thank you for your reply @Expiscornovus , i would need to compare each items modified date since the sync might take different documents each time since they will be approved or worked on at different times. So a file might not be touched for a year meanwhile another document is worked on daily. The library in question has around 50 000 documents. 

[StretchFredrik]

Thank you for your reply @grantjenkins 

Are they both Document Libraries that would have identical folder structures/files?

Yes they have the same folder structure and files.

Assuming this would be a scheduled flow that ran daily/weekly?

It will run every 15 or 30 minutes, which is why i want it to only spend time on the documents that are out of sync. 

[Expiscornovus]

Ok, thanks for clarifying. In that case what I provided isn't sufficient.

Let's wait for the response of @grantjenkins. I am sure he can come up with a great solution 😀

[Chriddle]

Have you considered using Sharepoint (and maybe OneDrive) sync capabilities instead of a Power Automate flow?

[StretchFredrik]

Yes, that does not work since the main goal of this is to have one library with ONLY major versions of files and only read permissions @Chriddle. 

[StretchFredrik]

Yes, that does not work since the main goal of this is to have one library with ONLY major versions of files and only read permissions @Chriddle 

[Chriddle]

Maybe that helps:

I created an array "source" with 500 objects (I hope this is a resonable number of changed files between two flow runs)
and an array "destination" with 50000 objects.
Maybe the amount of destination objects can be reduced by a clever odata filter.

For each object in "source", the Select action "combined" does a lookup in the destinations for an entry with same name and get its "created" with the help of xpath.

You can filter this output with date comparison,

With this amount of values this flow runs in round about 2 minutes.

Of course this is only a POC and you would have to add times, check what happens if the objects are bigger (because of longer names) and probably more 😉

source (Select):

"inputs": {
        "from": "@range(0,500)",
        "select": {
            "name": "@concat('file-',string(item()))",
            "created": "@concat('2023-02-', rand(1, 28))"
        }
    }

destination (Select):

"inputs": {
        "from": "@range(0, 50000)",
        "select": {
            "name": "@concat('file-', string(item()))",
            "created": "@concat('2023-02-', rand(1, 28))"
        }
    }

destinationXML (Compose):

"inputs": "@xml(json(concat('{\"root\":{\"item\":', body('destination'),'}}')))"

combined (Select):

"inputs": {
        "from": "@body('source')",
        "select": {
            "name": "@item()['name']",
            "created": "@item()['created']",
            "created_destination": "@first(xpath(outputs('destinationXML'), concat('//item[name=\"',item()['name'],'\"]/created/text()')))"
        }
    }

Filter array:

"inputs": {
        "from": "@body('combined')",
        "where": "@greater(item()['created'], item()['created_destination'])"
    }

[grantjenkins]

I think I might have something that will work.

One concern is the number of files in each library (50,000). We can't just apply a Filter Query within our Get files, so would need to return all files from both libraries (100,000+ files) then apply filtering. The filtering will be quick - just the initial retrieval of files.

The other concern is if there are a lot of files out of sync it will take some time to copy them across which could take quite a while depending on number of files and size of those files. One thing I haven't done here is copied the actual metadata (properties) across. Can easily do that, but not sure if your requirement is just the file sync, or properties too.

If you go with this approach, you may need to run the flow manually a few times to see how long it takes to complete, then schedule the flow accordingly.

See full flow below. I'll go into each of the actions.

Get files Library A and Get files Library B are both using Get files (properties only) actions. They both have the filter FSObjType eq 0 which means only get files (not folders). I've also set the Top Count to 5000 for each of them.

I've also gone into the Settings for Get files Library A and Get files Library B, turned on Pagination, and set the Threshold to 60000 (needs to be a number larger than the number of files you will have over the next couple of years at least). This will take a while to retrieve all your files.

Select extracts out a couple of properties from Get files Library B that we will convert to XML so we can apply XPath within the filter later. The expressions used are:

//FullPath - removes the library name from the full path
join(skip(split(item()?['{FullPath}'], '/'), 1), '/')

//Modified - replaces characters so we are left with a number (required for XPath comparison)
replace(replace(replace(replace(item()?['Modified'], '-', ''), 'T', ''), ':', ''), 'Z', '')

Filter array uses the output from our Select and the following expression to filter our items that have been updated in Library A since being copied to Library B (in need of updating). It uses an XPath expression to compare both the FullPath (including Filename) and the Modified Date. And if the length of items returned is greater than 0 then we need to update the item.

@greater(
    length(
        xpath(
            xml(json(concat('{"root": { value:', body('Select'), '}}'))), 
            concat('//root/value[FullPath = "', join(skip(split(item()?['{FullPath}'], '/'), 1), '/'), '" and Modified < "', replace(replace(replace(replace(item()?['Modified'], '-', ''), 'T', ''), ':', ''), 'Z', ''), '"]')
        )
    ), 
    0
)

Apply to each iterates over each of the items in our Filter array (files that need to be updated).

Copy file uses the following expressions for File to Copy and Destination folder.

//File to Copy
item()?['{Identifier}']

//Destination Folder - NOTE that you would need to put your Library names here
slice(replace(item()?['{Path}'], 'LibraryA', 'LibraryB'), 0, lastIndexOf(replace(item()?['{Path}'], 'LibraryA', 'LibraryB'), '/'))

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[StretchFredrik]

Thank you! I will give it a try and get back to you! @grantjenkins 

[StretchFredrik]

I think i got it close to working, will test a bit more tomorrow. But i would need the the filter array to also include any items that don't already exist in destination. @grantjenkins 

Thank you for your help this far 😃 

[grantjenkins]

@StretchFredrik Yea I was thinking about that and already started a bit of a redesign, but off to sleep now, so won't be able to get anything to you until a bit later. I know how to achieve it, but just need to rebuild a part of it.

Also, what if you delete an item from the source - would you also need to delete that from the destination?

[StretchFredrik]

Sounds awesome take your time and sleep well. the delete part ive already done by comparing urls with the filter array step. It then loops the ones present in destination and not in source and tries to find the file by document-id to see if its moved in source but not published yet. If it doesnt find it by document-id, it gets deleted.

Thank you for your time @grantjenkins 

[grantjenkins · Accepted solution]

@StretchFredrik Hopefully this works as expected. It should copy over update files and newly added files. I combined what @Chriddle did with the solution as a lot nicer and easier to handle the new files added.

See full flow below. I'll go into each of the actions.

Get files Library A and Get files Library B are the same as the original solution.

Select B extracts just the Modified date and the Full Path (excluding the Library Name) from Get files Library B. The expression used is:

join(skip(split(item()?['{FullPath}'], '/'), 1), '/')

Select A extracts out the Identifier, Modified date and Path from Get files Library A, plus the matching Modified date from Library B. The expression used to get the Modified date from Library B is:

xpath(
    xml(json(concat('{"root": { value:', body('Select_B'), '}}'))),
    concat('string(//root/value[FullPath="', join(skip(split(item()?['{FullPath}'], '/'), 1), '/'), '"]/Modified/text())')
)

Filter array uses the output from Select A with the following filter.

//ModifiedB is empty (new file added) or ModifiedA is greater than ModifiedB (file updated in Library A)

@or(
    equals(item()?['ModifiedB'], ''),
    greater(item()?['ModifiedA'], item()?['ModifiedB'])
)

Apply to each iterates over each of the items in our Filter array.

Copy file uses the following expressions to copy the new/updated files from Library A to Library B.

//File to Copy
item()?['Identifier']

//Destination Folder - NOTE that you would need to put your Library names here
slice(replace(item()?['Path'], 'LibraryA', 'LibraryB'), 0, lastIndexOf(replace(item()?['Path'], 'LibraryA', 'LibraryB'), '/'))

----------------------------------------------------------------------
If I've answered your question, please mark the post as Solved.
If you like my response, please consider giving it a Thumbs Up.

[StretchFredrik]

Thank you @grantjenkins , the filter array step fails on "Greater expects all of its paramteres to be either integer or decimal numbers, Found invalid parameter type "Null". So guessing the filter array query fails when the modifiedB is null(empty).

[grantjenkins]

Did you put the filters in the same order that I had? If ModifiedB is empty, then it wouldn't try to evaluate the second filter.

[StretchFredrik]

This is what i have, should be the same: 

[StretchFredrik]

Nevermind, im stupid, i forgot to change the input of filter array @grantjenkins . Will try again! 😃