Is there a simple way (built in) to handle record versions.

[jefftaste]

Looking to create an app based on tables in data verse.  I would like to add versioning to it.  Basically the tables will represent a document, and I want to track the multiple versions of a document.  Was just thinking about using a SharePoint library and SharePoint built-in versioning. but it has specific limitations for our use case.   I am looking to see if there is something built in to use.  I don't think auditing works because I don't think I could expose the "older version" from the auditing in any meaningful way from within the power app.

Right now I have "Table 1" which represents the document container basically.  A document for me hold data from two other tables "Table 2" and "Table 3".  I new version of the document would basically mean something was changed in either "table 2" or "table".  And Changes to "Table 1" would really be considered a new/different document not a version of the same document.

[cchannon · Accepted solution]

No, there is no OOB mechanism for this. You would have to build it. 

[jefftaste]

Okay, ty.  I was figuring as much since I was not finding anything.  I guess I will look at how to implement on my own.

[cchannon]

What you're going to find quickly is that the storage becomes expensive, so you will want a specialized solution for this.

One option is to actually create a SP document and use SP's native versioning. This will give you config options and prevent you from needing to code the complex multi-stakeholder update version increment logic, but search capabilities will not scale super-easily if you are talking about hundreds of thousands of records.

Or, you might just use Blob storage and write one row per snapshot.

If you think there will be really big volume and you need rapid querying, then you might use Azure SQL to store the history.

Regardless of which of the above you choose, you'll want the integration to happen server-side for security and to avoid CORS issues, and really any of the ones above will be a much cheaper way to do this than increasing your dataverse storage by an order of magnitude or more (unless your Db is very small and you only want this capability on, say, one table).

[jefftaste]

Hmm, my additional thought was to use SharePoint documents.  specifically excel files.  I had a problem with trying to figure out how to use multiple excel files as data sources.  Especially ones created by the app.  And I need to be able to perform queries of the documents based on the data they contain.  

What i am creating a menu/dish/recipe managers for a restaurant.  I wan to be able to support multiple version of recipes.  I can do a lot with just a sharepoint document library.  But I want to be able to have a dish/menu builder.  Where based on the season and ingredients for example in one dish, it recommends other dishes with similar ingredients.  This is where i think i need to build a power app.  And i don't think i can index the contents of all those individual excel files to do the querying needed, which brings me to just using dataverse.  

Definitely open to suggestions though

[cchannon]

Oh, I get you. You are just trying to build a queryable list and hoping DV can get you there, not trying to add this capability to an existing model driven app. In that case, this is TOTALLY doable!

The short version is you're going to use state/status to ensure that your canvas app user experience only ever pulls back the latest UNLESS you want to see all, then you will use a shared key to easily relate joint recipes without needing to build actual relationships. Initially (when the first recipe iteration comes up), you will just create your row, with a field/fields for the version, a Recipe Key of some kind (unique to the recipe but shared across all versions), and an Active/Active state/status. Then, every time your users go to make an update (assuming canvas app?), you DON'T just update the record; you do a Create and Update instead. The Create will create a brand new recipe with that SAME Recipe Key, but a new version value/values and an Active/Active state/status. The Update will update the previously Active recipe to set it to Active/Not Latest (or whatever terminology makes sense to you and your users).

Now, whenever your users search for recipes, you can choose whether to show them ALL matches or just the LATEST match (based on status). and then when they look at one recipe and want to know more you can show them all other versions of the recipe (by searching for all recipes with that shared key).

---

Why am I only recommending this now? 

Previously, I thought you were already in a Dataverse Model-Driven App. Over there, users have a lot more latitude to make their own queries, views, do their own searches, etc. which makes it hard to lock down what you WANT them to see vs. what they can just find on their own. In a world like that, keeping all your version history in one table creates an absolute mess where people just get too many results when all they really want is something simple, like "latest". What's more, the relationships between these records need to be real, queryable relationships and this scenario would only work with an N:N relationship and a self-referential N:N relationship is very problematic (that's a whole other rant, but for now, just trust me).

But if you're not in a model-driven app but some other UI and just trying to store the data where YOU control filtering, then none of that really matters. You can filter out latest/all without the user needing to understand why. You can search for matching IDs without needing to use a real relationship, and therefore, this is actually a very simple and viable solution.