Associating Specific List Item to Specific Library Document

(0) Share

Report

Posted on by Phineas

5,331 Moderator

I need to create a SP list of certifications from an Excel spreadsheet.

I need to create a SP library to archive existing and new certification documents/images and upload existing document/image to library.

The certification documents are/can be pdf or image files - (should this library be a 'Document' or 'Image' library?).

Each Employee may appear on the certification list once or mulitple times based on the number of certifications they have.

Each Employee may have one or more certification documents in the library.

There are fifteen different certification titles available to choose from.

Each Member has a 'UniqueID'. The UniqueID is formatted as follows and appears allowing side each 'Item' (certification) on the SP list associate with the Member.

UniqueID = First Initial First Name / First Initial Last Name / Last Four of SSN

Member: Joe Schmoe

Last Four: 1234

UniqueID: JS1234

The Member's will need to ability to update, edit, or delete items from the SP list and associated item in the SP library.

At list and library creation (and for all addition of modifications to the SP list), how do I associate each line item on the SP list to its unique and specific document/image in the library?

Categories:

Building Power Apps

I have the same question (0)

All responses (13)

Answers (1)

Sort by

WarrenBelz 155,315 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

A lot of questions there.

Firstly, I would consider a Sharepoint List instead of Excel for many reasons, but the comments below apply to both.

There is on one "type" of SP Library and it can contain most types of files (including images).

You would need to maintain another list of Employees with their email and UniqueID - I assume you only want them to be able to view their own records.

Add a field to the Library for the UniqueID (give it a different name) and put in the Student's ID - that way it is easy to put a gallery in Power Apps with the linked files.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Yes; I meant SP List, not Excel online or any such.

I do have list of employees with their UniqueID.

I understand how adding a column to the Library with the employee's UniqueID would allow for a query of a member's certification by UniqueID, returning ALL of the member's documents.

What I need is to be able to recall/update/edit/delete a specific document in the Library based on the current item chosen in the List.

Using the UniqueID would access all not just one specific, yes?.

What if I only want to edit/update/delete a specific item in the List and its associated document in the Library through a patch of update condition?

For instance, if a member has an existing drivers certificate and the date of that certificate needs to be 'updated' (using a condition) in the list, and the existing document in the library needs to be replaced/overwritten/deleted (not sure what happens), in the Library?

How do I make sure the correct document in the Library is being modified based on the item in the List be edited?

If I merely use the member's UniqueID that doesn't identify a specific document in the Library, correct?

Was this reply helpful? Yes No
WarrenBelz 155,315 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

That is the idea of the gallery - you simply display all the items, then select the one you want from the gallery, You can make a Form referring to it in the Item or simply make the gallery editable and do it there. I use the process in a whole bunch of related List and Library items.

Also I was referring to a Table from an Excel Spreadsheet in OneDrive used as a Data Source - Shane Young has a good video on this.

I also have a reference in the third paragraph (under the image) of my blog on data structure.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Roger, to all of that.

Everything you have referenced I am currently able to do and am doing.

Maybe my issue is in the condition language in the 'Patch/Update' buttons.

The site will go live with pre-loaded data. The Members will be able to view/review/update/delete existing data and add new data and documents/images.

I am able to edit SP List column specific line item data through a button and a Patch function in Power Apps, but the language in the condition is not overwriting/replacing the existing document/image in the Library with the new Attachment from the Power Apps action; it merely creates a new document.

1. I have a local existing Excel spreadsheet with all the certifications (est. 2,000+ line items). I am going to use the 'Import Spreadsheet' function in SP to create the SP List.

2. I will create a blank Library in SP and drag and drop the document/images.

3. How do I tie each specific line item in the SP List to it's corresponding document/image in the SP Library?

4. How do I ensure any NEW (not currently existing in the List or Library) items added by Members have the same connectivity as desired above, for editing/updating/deleting in the future?

Using the Member UniqueID will return all items associated with the Member to a Gallery.

If I transition to an edit Form from that Gallery how do I ensure the action in the edit Form ONLY impacts the specific document/image associated with the line item from the Gallery and in the list being edited/updated?

What is the specific tie between an existing the line item on the List and the corresponding existing document/image in the Library? It can't be the Member UniqueID, that's not specific enough, correct?

Also, how do I enforce said above relationship on all NEW uploads to to the database?

Was this reply helpful? Yes No
WarrenBelz 155,315 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

I have added your questions below for ease of reference of answers

1. I have a local existing Excel spreadsheet with all the certifications (est. 2,000+ line items). I am going to use the 'Import Spreadsheet' function in SP to create the SP List.

A very good move - accessing over 2,000 Items in Excel is problematic as Excel has no concept of Delegation.

2. I will create a blank Library in SP and drag and drop the document/images.

For bulk updates, that is probably the best way.

3. How do I tie each specific line item in the SP List to it's corresponding document/image in the SP Library?

You need a Unique Identifier from the list in a field in the Library (I use the ID of the List in a field I call IDRef). You can manually input this if you "drag and drop". You also might consider making it a required field

4. How do I ensure any NEW (not currently existing in the List or Library) items added by Members have the same connectivity as desired above, for editing/updating/deleting in the future?

This is where it gets complex in Power Apps (if you want to add them from there). Shane Young has a good video of a process using a re-purposed Attachment control. If they are images, you can use an Add Picture control - I have a blog on this process. Ultimately you will be using a Flow and sending the Identifier in it to be set with Update file properties.

Using the Member UniqueID will return all items associated with the Member to a Gallery.
Correct

If I transition to an edit Form from that Gallery how do I ensure the action in the edit Form ONLY impacts the specific document/image associated with the line item from the Gallery and in the list being edited/updated?
It will be the Item of the Form and SubmitForm() will only refer to that Item. You could also use a Patch and refer to the ID of that record.

What is the specific tie between an existing the line item on the List and the corresponding existing document/image in the Library? It can't be the Member UniqueID, that's not specific enough, correct?
As I mentioned, I use the ID of the List item - you cannot get more specific than that.

Also, how do I enforce said above relationship on all NEW uploads to to the database?

Via the Flow options as I mentioned above.

Also, you mentioned earlier about replacing the file - you cannot do this from Power Apps (only from SharePoint). You need to delete the old record and add a new one.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Thank you for the clarifications!

Can I create a flow that initiates with the Patch/Update button condition that will do the below?

"Also, you mentioned earlier about replacing the file - you cannot do this from
Power Apps (only from SharePoint). You need to delete the old record and add
a new one."

I understand the ability to delete from line items for a List and items from a Library in SP; however, is there the ability to do it from Power Apps, maybe with a flow through the same 'Submit' button?

Was this reply helpful? Yes No
WarrenBelz 155,315 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!
Hi @Phineas ,

As long as you have the ID of the item you want to deleted - if selected from a Gallery, it would be

GalleryName.Selected.ID

put this before the Flow

RemoveIf( YourLibraryName, ID=YourGalleryName.Selected.ID )

You could get a bit more sophisticated and return a value form the Flow to ensure it succeeded and then test for this before deleting the old file.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

I understand.

What is your recommendation for auto-numbering new entries to the List and Library.

My thought was creating a column in the Excel (before upload as a List), that combines some form of the Member's UniqueID and an abbrevation of the certificate Title into a 'CertID' for this purpose.

Thoughts?

Was this reply helpful? Yes No
WarrenBelz 155,315 Most Valuable Professional on at

Like (0)

Report
Copy link

Link copied!

Hi @Phineas ,

The ID in both the List and Library is auto-generated, unique and sequential and is all I use if I have a one-to-many relationship on any List/Library or List/List combination (with the ID of the Parent in a Numeric reference field in the Child).

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Forgive my ignorance, is an ID created for every line item in a List when 'Import Excel' is used to create a List? Meaning, wil a number be auto-generated but hidden from view for every line item when the import is used?

If yes, to the above, how is the same number created/referenced in the Library when all of the associated documents/images are added to the Library?

I am trying to make sure I get this right (the second time around).

I have a spreadsheet. I have documents. I'm creating a List and a Library.

Questions:

1. Should I add a column to the Excel spreadsheet that already has a UniqueID for each item so that I can reference it in the Library?

2. If yes to the above how is (or is) that same sequencetial numbering continued for any new item added to the List once it is created and corresponding document/image added to the Library?

Was this reply helpful? Yes No