Archiving SharePoint List Items with Lookup Fields

Question

Hello everyone,

I’m working on a project where I need to archive a SharePoint list, and I’m running into some challenges with capturing data from lookup columns effectively. I’m looking for suggestions or best practices from others who have tackled this use case.

Use Case Description:

• I have a SharePoint list that contains several lookup columns linking to other lists.
• My goal is to archive this list along with all the lookup values for every item, ensuring that when I retrieve or access the archive, it will contain both the main list data and all relevant values from the related lookup lists.

Challenges:

Capturing Lookup Data: When exporting or archiving, the lookup fields seem to store only the IDs or just the display value, which doesn’t always capture the full context (especially if the data in the lookup lists changes).

Consistency of Data: I want to ensure that all the lookup data is preserved accurately and reliably, even if the original lookup lists are modified or deleted in the future.

Automation: I’d prefer an automated solution so that the archive process is triggered whenever there are changes to the list or periodically without manual intervention.

Two Approaches for Storing Lookup List Data:

I’m also considering how to store lookup list data in the archive. Here are two approaches I’m evaluating:

1. Single Column for Storing All Lookup Values as Key-Value Pairs:

• Instead of having separate columns for each lookup field, I could consolidate all the lookup values into a single column in the archive, using key-value pairs.
• For example, a column like LookupData might store values such as:

json
CopyEdit
{"LookupField1":

• Pros:
  • Simpler structure with fewer columns.
    • Easier to manage if there are many lookup columns.
    • Cons:
      • Data is less structured and could be harder to query or analyze.
        • Requires parsing the JSON or key-value pairs when retrieving the data.

2. Multiple Columns for Storing Individual Lookup Fields and Their Values:

• Another approach is to create separate columns for each lookup field, where each column contains the respective lookup data.
• For example:
  • LookupField1Value: Stores the text value for LookupField1.
    • LookupField2Value: Stores the text value for LookupField2.
      • LookupField3Value: Stores the text value for LookupField3.
      • Pros:
        • More structured and easier to query for individual lookup field values.
          • Simpler to work with for reporting and analysis.
          • Cons:
            • More columns to manage, which could lead to data redundancy if you have many lookup fields.
              • Slightly more complex structure in the archive.

Questions:

1. Has anyone implemented a reliable and automated solution for archiving SharePoint lists with lookup columns?
2. How do you ensure the lookup values are correctly captured in the archive, even if the original data in the lookup lists changes?
3. Is Power Automate a good fit for this scenario, or are there other recommended tools or approaches that I should consider?
4. If using Power Automate, do you have any examples or templates that can help with retrieving and archiving both list data and lookup data efficiently?
5. For the lookup data storage: Which approach do you think would be better—single column with key-value pairs or multiple columns for individual lookup fields?

I’m looking forward to hearing any tips, solutions, or experiences that could help address these challenges. Thanks in advance for your input!

Answer 1

Hi @Jatin Saini,

Thanks for your trust and we are pleased to offer assistance.

Based on your description, we understand that you are considering using Power Automate to archive your SharePoint lists and want the Lookup columns to be unaffected by the source lists.

The best experience we've had using Power Automate to archive lists is creating a flow to move list items to the archive list at regular intervals. To ensure that no data is lost in the Lookup column, I recommend not using a column of type Lookup in the archive column but using a text to hold multiple Lookup values.

You can get the value of the Lookup column by following the method in this article:

Getting the value from a lookup column in Power Automate

Note: non-official, just for reference.

If you are new to using Power Automate to archive lists, you can refer to these articles:

Archive SharePoint List Items with Power Automate

Note: non-official, just for reference.

Effortlessly Archive SharePoint with Power Automate!

Note: non-official, just for reference.

For the two ways of storing Lookup column data that you mentioned, it is likely that you will need to make a choice based on your actual needs to see which way has more advantages that you need. Divorced from specific needs, we can't tell which of these two approaches is more appropriate.

If you have any questions, please do not hesitate to contact me.

Moreover, if the issue can be fixed successfully, please click "Accept Answer" so that we can better archive the case and the other community members who are suffering the same issue can benefit from it.

Your kind contribution is much appreciated.

Answer 2

Hi @Jatin Saini,

I would like to apologize for the belated reply.

1. Duplication of data storage: You can save only one copy of the items in the target list and the archive list. That is, when the item is copied to the archive column, the item in the target list will be deleted.
2. Other better way to save data is to use retention policy. A retention policy sets a retention time for your list, during which your list cannot be deleted. You can set the retention time to never expire, which is also a good way to archive your list.

You can start with this article to learn about retention policy: Learn about retention for SharePoint and OneDrive.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. It will be beneficial to more community members reading here. Your contribution will be highly appreciated.