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.