Select record with next highest date in column?

Question

This may be a weird request, but I've been trying to rack my brains for the past day and can't figure it out. I have a document library of client applications with the following columns:

Account Number, Application Effective Date, Application End Date, and Most Recent Application.

When a user uploads an application document, it parses the account number and application effective date from the name using a workflow. It then sets Most Recent Application to Yes - [Account Number]. If any new applications for the same account number are uploaded, the workflow checks to see whether the new effective date is after the effective date of the most recent application. If yes, it updates the old application's end date to the new application's effective date, and changes the newest application to Yes - [Account Number] in the Most Recent Application Column.

If a user uploads an application that isn't newer than the latest one, however, (eg. if it's a historical application upload) it will skip the above check and look for an application under the same account number with an effective date right before the uploaded one, and update it's end date to the uploaded application's effective date.

What I'd like to do is be able to select the application with the next highest effective date, and use it's effective date to update the uploaded application's end date.

I can't figure out workflow logic with if-else, as it keeps selecting the highest date value for the given application number instead of the one that is directly the next highest from the uploaded application. I'm thinking of a possible solution with calculated columns and a multi-value lookup, or auto-incrementing IDs that update every time a new application is uploaded based on application effective dates, but I'm not sure if that's feasible. Would an HTTP REST request with a loop be doable? At this point I'm just throwing out ideas and hoping one sticks.

Any help would be greatly appreciated!

Here is my workflow currently, for reference.

https://i.stack.imgur.com/EyZic.jpg

Answer 1

I'm guessing there's an easier way to do this, but the first thing that comes to mind (and that I just successfully was able to test in my sandbox) is using REST to grab the data for your library, filtered by account number and sorted by Effective Date, and then loop through the dictionary to find the first one where Application Effective Date is greater than the one in your new file.

I modeled this after this blog post, summarizing key approach and differences specific to your use case below.

To filter on Account Number and sort by Effective Date, the REST URL should look like this, filling in the code blocks with your appropriate values:

http://sharepoint/site/_api/web/lists/getbytitle('LIBRARY NAME')/items?$filter=Account_x0020_Number eq '[%CURRENTITEM:ACCOUNT NUMBER%]'&$orderby=Application_x0020_Effective_x0020_Date desc

Pull that into a dictionary, then use a loop to check each item until you find the one with an Application Effective Date newer than Current Item:Application Effective Date. Once you do, go back and change the End date on the prior item you checked.

Here's what my workflow looks like - note that I just focused on this piece of it, and didn't rebuild yours completely. You'd slot this into your Else branch where you have the code to set the old application's end date.

Hope this helped!