Cannot update Sharepoint list

[MajP]

I'm thinking of migrating from the lookup field to lists using sql and vba. Thoughts?

Not sure what that means.
Issue_WorkArea is a list but acts exactly like a junction table in Access

If you use a lookup field in your new the list Issue_WorkArea, when you pick an Issue description it saves the Issue ID even if you thing you are picking a descriptive field. If you pick a descriptive Work_Area it will save the work area ID in the lookup field, even if what you see in SP is the descriptive field.

You can verify this by linking to Access and you will now see the actual key and not the descriptive field.
Example here is a linked list in Access showing the lookup Site Code

But these show a descriptive field in SP.

So you can enter information from either Access or Sharepoint, but there is not nice form you can build in SP to do the many to many. Normally this would be a Mainform, Subform.

 

[MajP]

George beat me, but I think we are saying the same thing.

 

[GPGeorge]

Not sure what that means.
Issue_WorkArea is a list but acts exactly like a junction table in Access

If you use a lookup field in your new the list Issue_WorkArea, when you pick an Issue description it saves the Issue ID even if you thing you are picking a descriptive field. If you pick a descriptive Work_Area it will save the work area ID in the lookup field, even if what you see in SP is the descriptive field.

You can verify this by linking to Access and you will now see the actual key and not the descriptive field.
Example here is a linked list in Access showing the lookup Site CodeView attachment 121071
But these show a descriptive field in SP.

So you can enter information from either Access or Sharepoint, but there is not nice form you can build in SP to do the many to many. Normally this would be a Mainform, Subform.

I agree, yes. Once the tables are migrated, building the Access interface is largely the same except for the meta data fields added in SharePoint which you don't need to include in Access forms, etc. I just ignore them.

 

[lexkinter]

@GPGeorge Sorry, I probably wasn't clear. I'm moving from using a lookup field to manage many-to-many to a junction table. See 2025-08-13_12-49-04.jpg for new data structure and below attached for new tables. Given I have to create new records in the junction, I need to be able to relate the new IssueID to the old IssueID and create a new record in the junction table. I may have to fabricate a key using existing fields (maybe concatenating IssueTitle and Description. Does that make sense? I'm still mulling this over. Thanks for any thoughts you might have..

UPDATED - I DIDN'T SEE GEORGE AND MAJP COMMENTS 45 MINUTES AGO AND HAVE TO READ - SORRY.

 

[GPGeorge]

Perhaps the problem is the reference to lookup fields in different contexts. In the Junction table called Issue_WorkArea, you have two foreign key fields: lkpWorkAreaID and lkpIssueID.

Both lkpWorkAreaID and lkpIssueID need to be Lookup Fields in the SharePoint list/table. That is the way to enforce Referential Integrity in the junction table in the absence of true Primary and Foreign Key constraints.

 

[MajP]

Have you read this because I will argue you can do this with multi select lookups (MVFs) or do it more traditional and still get the same capability. Although it may make your head spin using MVF lookups. However, you will need to understand what is going on in the background, because what you see is not what is going on. As I point out it is simply creating the junction table hidden from you.

You can create a Many to Many lookup (in newer SP). This would allow you to use the SP interface for data entry and visualization

I am moving from using a lookup field to manage many-to-many to a junction table.

Not sure what that means. We were suggesting that the junction list uses Single selection Lookups and will save the keys into the junction fields. This will allow you to do some data entry in SP.

Or those lkp fields could simply be numeric values and you would have to do your entry in Access since you will not have pull downs.

So there are three ways to do this
1. Use a multiselect lookup without a juction list.
2. Create a junction list and use single select lookups. Can pull down selections, but not a great gui.
3. Create a junction list and make the fields simply numerics. May have to rely on Access or Power Apps for the gui.

 

[MajP]

The three choices would depend on where you are doing the bulkof the data entry, editing, and visualization. If you are doing everything in Access then go with 3. You will have to enforce RI at the form level. If you are doing data entry and visualization in SP then go with 1. If you want a little of both then go with 2.

 

[lexkinter]

Not sure what that means.
Issue_WorkArea is a list but acts exactly like a junction table in Access

If you use a lookup field in your new the list Issue_WorkArea, when you pick an Issue description it saves the Issue ID even if you thing you are picking a descriptive field. If you pick a descriptive Work_Area it will save the work area ID in the lookup field, even if what you see in SP is the descriptive field.

You can verify this by linking to Access and you will now see the actual key and not the descriptive field.
Example here is a linked list in Access showing the lookup Site CodeView attachment 121071
But these show a descriptive field in SP.

So you can enter information from either Access or Sharepoint, but there is not nice form you can build in SP to do the many to many. Normally this would be a Mainform, Subform.

@MajP I was thinking of PowerApp to allow user to create new Issue and select many Work_Area. I don't know how I would create new record in the Work_Area junction table.

 

[lexkinter]

The three choices would depend on where you are doing the bulkof the data entry, editing, and visualization. If you are doing everything in Access then go with 3. You will have to enforce RI at the form level. If you are doing data entry and visualization in SP then go with 1. If you want a little of both then go with 2.

@MajP I've heard from many the problems with lookup fields, so was leaning away from that. I believe your #1 above is a lookup field. I'm not quite sure what you mean by " junction list and use single select lookups". Do you have an image? As far as #3, I know I could do it in Access, but want to try PowerApp. Can't I use it (or PowerAutomate) to query (or get the data in the right format) for the interface and create a record in the junction table?

 

[MajP]

If I was not using traditional Sharepoint as the interface for entry and viewing then I would go with 3 and simply store numeric keys. But how do you plan to display the Many to Many in Sharepoint? If you are not then OK.
I am more a consumer of PA then a builder, but I would assume you would build a PA form very similar to how you would do it in Access. You would create basically a form and subform to select work types for an Issue or vice versa. Pick an issue and then select from the work areas to add new records to the junction table.

 

[lexkinter]

If I was not using traditional Sharepoint as the interface for entry and viewing then I would go with 3 and simply store numeric keys. But how do you plan to display the Many to Many in Sharepoint? If you are not then OK.
I am more a consumer of PA then a builder, but I would assume you would build a PA form very similar to how you would do it in Access. You would create basically a form and subform to select work types for an Issue or vice versa. Pick an issue and then select from the work areas to add new records to the junction table.

@MajP I don't know... I thought you knew how LOL

 

[MajP]

In SP you can create a lookup field and allow it to pick and store one choice, or you can have it store multiple choices.

Using a multi value lookup it will show multiple choices in a column. So you would have an issue and a column with 1 or more worktypes. Again that is only display and not what is happening behind the scenes.

You list "Issue_WorkArea" is a junction list same as a junction table in access. Here you could use single select lookups. Which is kind of a hybrid

Can't I use it (or PowerAutomate) to query (or get the data in the right format) for the interface and create a record in the junction table?

No idea how PowerAutomate comes into play here. Simply build the Power App which allows you to build a more complex form to do data entry.

@MajP I don't know... I thought you knew how LOL

I know and seen enough to say it can be done. I just do not work in it enough to do it quickly. With the AI assistant it kind of builds itself.

So if you plan to build the Power App for data entry and viewing then yes get rid of the lookups and just use your current junction list. As long as those lkp columns are simple numerics and not some kind of lookup. If that is the case get rid of the lkp prefix because that is real confusing.

 

[lexkinter]

In SP you can create a lookup field and allow it to pick and store one choice, or you can have it store multiple choices.
View attachment 121074

Using a multi value lookup it will show multiple choices in a column. So you would have an issue and a column with 1 or more worktypes. Again that is only display and not what is happening behind the scenes.

You list "Issue_WorkArea" is a junction list same as a junction table in access. Here you could use single select lookups. Which is kind of a hybrid


No idea how PowerAutomate comes into play here. Simply build the Power App which allows you to build a more complex form to do data entry.


I know and seen enough to say it can be done. I just do not work in it enough to do it quickly. With the AI assistant it kind of builds itself.

So if you plan to build the Power App for data entry and viewing then yes get rid of the lookups and just use your current junction list. As long as those lkp columns are simple numerics and not some kind of lookup. If that is the case get rid of the lkp prefix because that is real confusing.

@MajP You're awesome. Thank you so much. You must like David Lynch? Have a great day

 

[MajP]

The lookup and multivalue fields where needed by Sharepoint because of limitations in not having any real forms. You simply had a list. Now a lot of people are doing their data entry with PA and data visualization with Power BI and only using SP as the repository. If you are not reliant on SP to enter and display data then you can do yourself a favor and go away from lookup fields now you can do the display and editing without the limitation of SP. Now you are functioning more like a traditional database. The only downfall to this is that you cannot enforce the RI and data integrity at the table (list) level. You have to do this at the GUI level.

 

[lexkinter]

The lookup and multivalue fields where needed by Sharepoint because of limitations in not having any real forms. You simply had a list. Now a lot of people are doing their data entry with PA and data visualization with Power BI and only using SP as the repository. If you are not reliant on SP to enter and display data then you can do yourself a favor and go away from lookup fields now you can do the display and editing without the limitation of SP. Now you are functioning more like a traditional database. The only downfall to this is that you cannot enforce the RI and data integrity at the table (list) level. You have to do this at the GUI level.

@MajP Again, thanks! I know it'll be an adventure.