Cannot update Sharepoint list (1 Viewer)

[Isaac]

I've never been able to update a Sharepoint list from Access, where in the row I'm trying to update, there's a column that's of type PEOPLE

 

[GPGeorge]

I've never been able to update a Sharepoint list from Access, where in the row I'm trying to update, there's a column that's of type PEOPLE

Interesting, I'll have to try that. But is PEOPLE a multi-value or choice data type? Or what we might call normal datatypes in other databases?

 

[GPGeorge]

Interesting, I'll have to try that. But is PEOPLE a multi-value or choice data type? Or what we might call normal datatypes in other databases?

Or did you mean "Person or Group"? Or something else? SharePoint is a different environment.

 

[Isaac]

Interesting, I'll have to try that. But is PEOPLE a multi-value or choice data type? Or what we might call normal datatypes in other databases?

I've always thought of it as its own special sharepoint beast - probably Access considers it multi value I would think

 

[Isaac]

Or did you mean "Person or Group"? Or something else? SharePoint is a different environment.
View attachment 121032

Person or Group, yeah

 

[MajP]

I bet People displays a person name, but it is pulling data from the exchange server so I bet it is actually storing some kind of exchange ID. So I would think it is a kind of lookup field.

 

[GPGeorge]

I bet People displays a person name, but it is pulling data from the exchange server so I bet it is actually storing some kind of exchange ID. So I would think it is a kind of lookup field.

Yes, that's why I was being nitpicky. It refers to someone in that SharePoint tenant. If it is a required field, and I think that's optional, then that might account for the error.

 

[LarryE]

Thank you. We will all miss UA.

Right now, we have summer weather in Western Washington. In two weeks? Anybody's guess.

From Aberdeen originally George. Left in 1970. Different place now.

 

[GPGeorge]

From Aberdeen originally George. Left in 1970. Different place now.

I arrive in Seattle in 1980. Different place now. I moved to Puyallup a year ago. Love it here.

 

[lexkinter]

@MajP and @GPGeorge If I want to make a junction table using Sharepoint list, do I just create a two number fields when creating the list, and let Sharepoint create a primary key? I'm thinking of using Powerbi for reporting on Sharepoint and Powerapps for data entry. I'm also want to use the tried and true Access, but *think* I could control the keys within Access, but am still learning Power products. Any thoughts? Once I figure that out, I will migrate the data.

 

[LarryE]

I arrive in Seattle in 1980. Different place now. I moved to Puyallup a year ago. Love it here.

Lived in Seattle and surrounding area 30 years. Arrived 1970 and moved to the Palm Springs CA area in 2000. I don't miss the rain...

 

[GPGeorge]

@MajP and @GPGeorge If I want to make a junction table using Sharepoint list, do I just create a two number fields when creating the list, and let Sharepoint create a primary key? I'm thinking of using Powerbi for reporting on Sharepoint and Powerapps for data entry. I'm also want to use the tried and true Access, but *think* I could control the keys within Access, but am still learning Power products. Any thoughts? Once I figure that out, I will migrate the data.

Yes, use Lookup Fields where you would, in Access, add number fields for the Foreign Keys to the two parent tables and enforce RI. The Lookups should probably include only the required Primary Key field, no display fields, as is usually done in Lookups.

For some use cases, PowerApps makes sense, but not always. If you have a mix of on-premises and remote users, and if one or more of those remote users needs to be able to do data entry in a mobile location, i.e. on a job site or in a moving vehicle, then PA is a great solution for that piece. Such apps are generally referred to as hybrids. The heavy lifting is done in the application best suited for that: MS Access. The light-weight mobile data entry works best in PowerApps.

Keep in mind that PA comes with additional licensing costs, which are roughly $5.00 (US) per month per user. That's going up to $5.25 (US) in the near future. I just received notice of that. SharePoint lists are a "free" back end source. If you want to move to a more robust data source like SQL Server or Azure SQL, add $20 a month per user for that Premium service. Check out my YouTube playlist for ideas about using PowerApps. I mostly use SQL Server because of the power it brings to the table as a Back End, but for this application, SharePoint should be adequate.

 

[GPGeorge]

Yes, use Lookup Fields where you would, in Access, add number fields for the Foreign Keys to the two parent tables and enforce RI. The Lookups should probably include only the required Primary Key field, no display fields, as is usually done in Lookups.

For some use cases, PowerApps makes sense, but not always. If you have a mix of on-premises and remote users, and if one or more of those remote users needs to be able to do data entry in a mobile location, i.e. on a job site or in a moving vehicle, then PA is a great solution for that piece. Such apps are generally referred to as hybrids. The heavy lifting is done in the application best suited for that: MS Access. The light-weight mobile data entry works best in PowerApps.

Keep in mind that PA comes with additional licensing costs, which are roughly $5.00 (US) per month per user. That's going up to $5.25 (US) in the near future. I just received notice of that. SharePoint lists are a "free" back end source. If you want to move to a more robust data source like SQL Server or Azure SQL, add $20 a month per user for that Premium service. Check out my YouTube playlist for ideas about using PowerApps. I mostly use SQL Server because of the power it brings to the table as a Back End, but for this application, SharePoint should be adequate.

I should have noted that I find the form factor for a smart phone makes PowerApps sketchy, albeit possible. I've settled on a cellular-enabled tablet as the better choice. Maybe the 20-somethings with perfect eyesight can find stuff on a phone, and there are thousands of apps for phones. I guess that depends a lot on how much information has to be displayed.

 

[MajP]

If I want to make a junction table using Sharepoint list, do I just create a two number fields when creating the list, and let Sharepoint create a primary key? I'm thinking of using Powerbi for reporting on Sharepoint and Powerapps for data entry. I'm also want to use the tried and true Access, but *think* I could control the keys within Access, but am still learning Power products. Any thoughts? Once I figure that out, I will migrate the data.

That is where we are going in our company. We use SP but mostly as a data repository and navigation tool. A lot of the entry and edit is a custom Power App. Instead of views we use a lot of BI for viewing the data. This way the user is not touching a live list.
I do not know if that is your question.
It is kind of like using three Apps to do what Access can, but it does have a lot of benefits. What you can do in Power BI is far beyond what Access can do for reporting and data visualization. It is web based. And with Power Apps it is mobile too.

 

[lexkinter]

That is where we are going in our company. We use SP but mostly as a data repository and navigation tool. A lot of the entry and edit is a custom Power App. Instead of views we use a lot of BI for viewing the data. This way the user is not touching a live list.
I do not know if that is your question.
It is kind of like using three Apps to do what Access can, but it does have a lot of benefits. What you can do in Power BI is far beyond what Access can do for reporting and data visualization. It is web based. And with Power Apps it is mobile too.

I should have noted that I find the form factor for a smart phone makes PowerApps sketchy, albeit possible. I've settled on a cellular-enabled tablet as the better choice. Maybe the 20-somethings with perfect eyesight can find stuff on a phone, and there are thousands of apps for phones. I guess that depends a lot on how much information has to be displayed.

@GPGeorge I'm on the Government GCC cloud with M365, so have quite a bit of power, but not much support from IT (actually - they pretty much get in the way). I've been pulling from Oracle via ODBC, and Planisware (via Excel) and pushing to Sharepoint lists which has been very difficult. I don't have access to any "on-prem". THANK YOU for the link to your videos. Do you have recommended watch order for your videos?

 

[lexkinter]

That is where we are going in our company. We use SP but mostly as a data repository and navigation tool. A lot of the entry and edit is a custom Power App. Instead of views we use a lot of BI for viewing the data. This way the user is not touching a live list.
I do not know if that is your question.
It is kind of like using three Apps to do what Access can, but it does have a lot of benefits. What you can do in Power BI is far beyond what Access can do for reporting and data visualization. It is web based. And with Power Apps it is mobile too.

@MajP Yep thats it. I'm going to try watching Georges videos. I hope you're available to answer questions later. Thanks

 

[GPGeorge]

@GPGeorge I'm on the Government GCC cloud with M365, so have quite a bit of power, but not much support from IT (actually - they pretty much get in the way). I've been pulling from Oracle via ODBC, and Planisware (via Excel) and pushing to Sharepoint lists which has been very difficult. I don't have access to any "on-prem". THANK YOU for the link to your videos. Do you have recommended watch order for your videos?

I didn't plan them in any specific order because it's not oriented around a build process. I guess just sequential from oldest to newest for the Northwind series would be a good approach. The older videos refer to a library application I created a few years ago. It does more or less track the build process but not really closely.

 

[GPGeorge]

That is where we are going in our company. We use SP but mostly as a data repository and navigation tool. A lot of the entry and edit is a custom Power App. Instead of views we use a lot of BI for viewing the data. This way the user is not touching a live list.
I do not know if that is your question.
It is kind of like using three Apps to do what Access can, but it does have a lot of benefits. What you can do in Power BI is far beyond what Access can do for reporting and data visualization. It is web based. And with Power Apps it is mobile too.

I agree, but the way I see it is that PowerApps and Power BI fit in where Access is not the strongest, i.e. remote data entry and data visualization. It's not three co-equal apps overlapping each other. It is finding the best fitting app for a specific kind of requirement and creating a hybrid solution that relies on the strengths of each.

 

[lexkinter]

@MajP and @GPGeorge I don't think I made this clear, but I'm migrating data to new Sharepoint list (IT took my old site down) and want to be sure I've got this right. For M:M, I setup tables as shown in the attached images. Does this look right? I'm thinking of migrating from the lookup field to lists using sql and vba. Thoughts?

 

[GPGeorge]

That would appear to be correct, yes. The Lookups in SharePoint should include only the respective Primary Key fields, with no additional value fields being needed.

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

I'm not sure what this would entail. If the tables in the Access database are set up with the Lookup fields as shown, then migrating those tables to SharePoint lists using the built-in migration tool should handle that properly.