Creating Multiple Versions of the Same List and showing Changes Over Time

[DPozo]

Hello!

For our organization, I'm working on a view/interface to track changes to lists of content over time. We "merchandise" our content for users in sequential order; e.g. Row 1 of content contains Title A in Position 1, Title B in Position 2, etc... Each of our titles has a custom numerical ID, e.g. 10339894. 

We frequently make changes to the order of titles in each list, based on user engagement with each piece of content. I'm looking for a way to build an interface or view that will easily track these changes over time. It also has to serve a dual purpose and be easily updatable – ideally, members of our organization could simply drop in the new order of titles in the existing table, date them, and be able to compare them to past iterations. As such, I'd like all permutations of titles and lists in one easy-to-wield table.

For now I've set up the records and fields as such. The list of Titles 1 –10 from 4/1/2023 includes all the same titles as the list from 4/2, but the titles on 4/2 have been re-ordered:

I've created a second linked table with some rollup views and linked back to this original table, to create these fields as well:

Lastly, I've managed to create this interface, which compares the current version of the list to the previous version. 

Here's where I'm stuck:

1. I don't know how to make this interface easily filterable. Right now, the "Previous Title Order Iteration" is set up by a filter which references the "Days Before Most Recent Update" field, which is a calculation. A user will have to know exactly how many days it has been since the most recent update to be able to filter by this view. Meaning, if a user simply filters by "<1," it'll show all past versions of the list across multiple timestamps, instead of just a single iteration from a single day. 
2. I don't know how to easily show the "delta" between title positions. I'd like to add a column that shows, in the example above, Title 1 (10339527) moved up one position in the list since the last update. Basically a "Change DoD" calculation that demonstrates the difference in position for any one given title.

Any help would be super appreciated!

[TheTimeSavingCo]

>1. I don't know how to make this interface easily filterable. Right now, the "Previous Title Order Iteration" is set up by a filter which references the "Days Before Most Recent Update" field, which is a calculation. A user will have to know exactly how many days it has been since the most recent update to be able to filter by this view. Meaning, if a user simply filters by "<1," it'll show all past versions of the list across multiple timestamps, instead of just a single iteration from a single day. 

Hmm, if I were you I'd use the same logic you're using to get the most recent title position update date to get the second most recent title position update date, and I would create a formula field that would check whether that record's date matched the second most recent date.  I'd then use that formula field with the interface

>2. I don't know how to easily show the "delta" between title positions. I'd like to add a column that shows, in the example above, Title 1 (10339527) moved up one position in the list since the last update. Basically a "Change DoD" calculation that demonstrates the difference in position for any one given title.

Hmm...I think you'd need another table for this where each record was a single title, and with the same logic mentioned above to get the most recent and second most recent dates, you could then use conditional lookups to grab the most recent and second most recent title positions, and then use a formula field to find the difference.  You could then use a lookup field to bring it back to your original table so that you could display it in the Interface, does that make sense?

[DPozo]

Hey, thanks Adam!

I think where I'm stuck is how to get that second most recent title position date. The calculation used to get Most Recent Title Position Update is based on a rollup field from a separate table, the Playlists Table, which I've used to identify the "Max(Value)" from the Title Position Update column in my first table. Basically, this is used to identify the most recent date any of the Titles in X Playlist (in this case, the Test Playlist) were updated:

From there, I go back to the Titles Table and pull in that Max value in another rollup:

Lastly, I use the formula field "Days Before Most Recent Version" to subtract that "Date Title Position Modified" value from the "Most Recent Title Position Update," i.e. subtracting any given update date elsewhere on the table from the most recent date.

So the way I set it up, it seems like I'm beholden to the "Most Recent Title Position" value as the minuend in the subtraction equation. I'm not sure how to take that raw value in "Days Before Most Recent Version" calculation and identify the second-most recent title update from there.

[TheTimeSavingCo]

Ah, to get the second most recent, duplicate your "Date Title Position Modified Rollup (from Titles)" and add a conditional to it to not pull in any records that have a "Most Recent Title Position?" value of "Most Recent Title Position"

You'll then have the second most recent title update