Cannot update Sharepoint list (1 Viewer)

[lexkinter]

@MajP and @GPGeorge This might be optimistic for me, but I'm going to try to control the keys in PowerApps, so will have my junction with two number fields for the foreign keys. I now am migrating data over. If I run into trouble, I may have to bug you again. Thanks

 

[lexkinter]

@MajP and @GPGeorge Goofiness? I changed from lookups to number and then refreshed in access and, of course, it's goofy. When I view attributes in access (see attached), it has fkIssueID as a pk, why would this be? When I view in Sharepoint, it shows ID. Also, note the fkWorkAreaID1. I had this happen before and the second field went away after a while. I've compacted/closed/opened 3 times over 3 minutes.

 

[MajP]

I just ask that in your junction list, if you are not using a lookup, that you get rid of the "lkp" prefix for those column names. That will confuse most anyone who is looking at this.

It is like when someone drops some code and they have some thing like
txtBoxABC.rowsource = ...
Then I look at the code and tell them that a textbox does not have a rowsource and of course the code fails on that line.
Then they reply that they forgot to rename it and it is a combobox.

I think I would go this way too, and manage the data integrity. What is the worse that could happen? You get an orphan work type for an issue. Not like this is sales order information and get an orphan product.

Another thing (I just learned from asking Chat) is that you can do composite indexes in SP lists. Never did that.

But you do not want duplicates. So for Issue 25, you may have Work Areas 1,2, 4
25 1
25 2
25 4
You do not want to be able to add another
25 2

Obviously in your junction you require both columns.

So a lot of the concerns of RI and data integrity and not really an issue.
1. You have required fields
2. You cannot add duplicates
3. Your entry is from PA ensuring valid entries (user not typing anything)

. You do not have cascade deletes, or RI. This means you can delete a Work Area even though there are child records in the junction list. So there is your orphan. If you delete Work Area 2 in your list you still have the record 25, 2 where issue 25 has non existent work area 2. You just have to be aware and handle this.
4. You do not

 

[MajP]

When I view attributes in access (see attached), it has fkIssueID as a pk, why would this be?

I would not change the data type. I would create 2 new fields. This is like changing an autonumber field in Access you will get issues.

 

[MajP]

If there is data in the lookup tables then do an insert query into the new columns. If there are no existing records simply delete the old columns and create new ones. Never change the datatype of a complex column, that will cause issues.

 

[lexkinter]

@MajP thanks for all the comments. My brain is trying to catch up with all that you're saying. I'm not sure you saw my attachments. I setup both fk's as in this attached. Also maybe you didn't see attachment with "fk..." naming intending that it is no longer a lookup. I know (you reminded me) I'll have handle checking for related records as I'm not using lookup type. I did (see attached) see what I believe to be a "composite key" option. I did not hear you comment on the screenshot where Access is showing a different primary key than SP. Can you comment, please?

 

[lexkinter]

@MajP also in "Never change the datatype of a complex column", What is a "complex column"? Wait - I *think* you believe I changed column type - I didn't. I made new ones and they're not displaying correctly in Access

 

[lexkinter]

@MajP It's now showing correctly in Access (attached) - and I can now SQL to migrate

 

[MajP]

"Never change the datatype of a complex column", What is a "complex column"? Wait - I *think* you believe I changed column type - I didn't. I made new ones and they're not displaying correctly in Access

That is what I thought. Complex column is an attachment, mulitvalue field, and memo history. In SP I think a lookup is also a complex data type where in access I think it is just a display thing.
But this goes to my point about a name that does not reflect what it really is. You showed it called "lkp" originally but it maybe was not a lookup. So that is why I would be confused.

 

[MajP]

FYI you can change the ID column name, but I would not recommend it. So unlike in access I would never have all my PKs named ID. I give them a unique. Here is how and why I would not change the name.

SharePoint's default "ID" column is a system-generated field that automatically assigns a unique, incremental number to each item added to a list or library. While it's a critical component for backend operations, its default display name can be confusing for end users.

Fortunately, you can easily rename the display name of the "ID" column in SharePoint. Here's how to do it:

1. Access the List Settings

• Navigate to your SharePoint list.
• Click on the Settings gear icon () and select "List settings".

2. Edit the ID Column

• Under the "Columns" section, click on any existing column, like "Title".
• You'll be taken to the "Edit Column" page.

3. Rename the ID field

• In the "Column Name" field, enter the desired new name for your ID column (e.g., "Request ID", "Item Number", etc.).
• Scroll down and click "OK" to save the changes.

Important Notes:

• Internal Name vs. Display Name: Renaming the ID column only changes its display name, not its internal name. The internal name remains the same in the backend, and if you are referencing the ID column in custom code (e.g., in Power Apps or Power Automate flows), you'll need to use the original internal name.
• Impact on Calculated Columns: If you have calculated columns or other elements that refer to the ID column, it's crucial to update those references with the new display name as well. Otherwise, your formulas might break or display errors.
• Hidden Columns: The "ID" column doesn't appear directly on the SharePoint list settings page and doesn't offer a direct "Edit" option via the user interface. This workaround of editing a different column allows you to access and modify the ID field's display name.

 

[lexkinter]

FYI you can change the ID column name, but I would not recommend it. So unlike in access I would never have all my PKs named ID. I give them a unique. Here is how and why I would not change the name.

@MajP Thanks again!!

 

[MajP]

Update us if you have success in doing this with PA. I would be interested in what you learned.

 

[GPGeorge]

I would very much like to see the results as well. Thanks.

 

[lexkinter]

@MajP I think I accidentally (I don't know how) renamed the pk of tblJobSelected_1 (see attached from access and sharepoint). How big a problem do you perceive this to be? I don't want to try to rename it back, don't want issues later, nor do i want to recreate the list and migrate the data. Thoughts?

 

[MajP]

Never done it but according to Chat only the display name changes and it is still called ID. My guess if you link to Access you will see ID and not the new name. And it seems you need to remember it is really called ID when using Power Apps. My guess ID is what you are going to see as a choice and will not see your new name anyways. If you have calculated columns (I never have) then those columns seem to have to reference the new display name.

• internal Name vs. Display Name: Renaming the ID column only changes its display name, not its internal name. The internal name remains the same in the backend, and if you are referencing the ID column in custom code (e.g., in Power Apps or Power Automate flows), you'll need to use the original internal name.
• Impact on Calculated Columns: If you have calculated columns or other elements that refer to the ID column, it's crucial to update those references with the new display name as well. Otherwise, your formulas might break or display errors.

 

[GPGeorge]

@MajP I think I accidentally (I don't know how) renamed the pk of tblJobSelected_1 (see attached from access and sharepoint). How big a problem do you perceive this to be? I don't want to try to rename it back, don't want issues later, nor do i want to recreate the list and migrate the data. Thoughts?

I have to disagree on the renaming. I think you really did rename that field. I think the "internal" name is probably something else. However, I don't know that for sure.

I can check, but it'll take a while to ferret out an example to test.

 

[GPGeorge]

I have to disagree on the renaming. I think you really did rename that field. I think the "internal" name is probably something else. However, I don't know that for sure.

I can check, but it'll take a while to ferret out an example to test.

Sorry, I was wrong. It is shown in the PowerApps environment as "ID", even when it's display name is changed.

 

[MajP]

Sorry, I was wrong. It is shown in the PowerApps environment as "ID", even when it's display name is changed.

Chatty is never wrong.

 

[GPGeorge]

Chatty is never wrong.

I have used Chatty. I like Chatty. Chatty is not never wrong. But wrong less often than I am, probably.

What Chatty has that I don't have is a great memory for things that happened 5 years ago.

 

[GPGeorge]

I feel the need to muddy the waters. It appears to be that Access DOES rely on the Display Name, rather than the internal name.

SELECT
    *
FROM
    tblAlbum
WHERE
    AlbumID > 2

By the way, note the plethora of meta-data fields in the linked SP lists shown in the Access Relationship window.