Leavving Access for Sharepoint

[standenman]

I currently manage with client data in MS Access.  I am considering Sharepoint and Power Apps to replace Access.  Here is what I want to accomplish:

1.  Client portal/exposure to their data.  The ability to view and edit some of their information, provide updates, and communicate with me via e-mal or chat.

2.  Ability to hire VAs to perform specific tasks within the client case workflow.

It looks like s SharePoint hub and spoke arrangement would be appropriate, with each spoke being a client.  And the hub would be only accessed by me - it would be my case management site.  

As it is now with MS access, I enter client data in the relational DB, client's identifed with a clientid.  There is access, of course, I can query my data and this of course is a vital function that I worry about losing.  Also, I worry about the issue of table relationships - one-to-many, many-to-many.  For example. as it is now any given client can have multiple illnesses treated by multiple doctor prescribing multiple things (medicine, surgery, etc).  Would lists be able to handle that?  That is, sitting on my hub site, could I query which of my clients with fibromyalgia are taking duloxetine? 

I am thinking my lists for spoke sites would be based upon a custom content type that really looks just like the design of an access table.  So for example, my list of "illnesses" would have a dropdown for name (no entering "fibermiolia" for "fibromyalgia") as well as the information I want to know about an illness (date diagnosed, diagnosing doctor, basis for diagnosis, etc).

Am I barking up the right tree here?

[#2]

@standenman 

Power Apps, maybe, but using anything other than SharePoint lists!!!!! Well, anything other than Excel and SharePoint lists. Dataverse is a possibility. Azure SQL is a possibility. Other server-based databases are possibilities.

I have spent the last couple of years exploring Power Apps for Access developers. I have a playlist of videos on YouTube documenting many aspects of that. Sample some of them to get an idea of what you are letting yourself in for. The most recent videos, on Northwind Inventory Stock Take are probably the most realistic look at what you could benefit from most.

The bottom line I've reached is that, if you have a particular function in your relational database application that would be improved by making it "mobile", that one function could benefit from adding a Power Apps app to support that one function to the existing Access relational database application.  For example, the inventory stock take I mentioned above relies on a bar code scanner that scans SKU codes on product packaging to get counts of inventory on hand. That's done moving through a warehouse. Mobile.

If you want to pay for licenses for each of your clients to access their own data, Power Apps can do that. But, remember, you pay for licenses (~$5.00/month/person) for each user. If that's in your budget, it can work. Bu you have to sign them up ahead of time and teach them how to use your app and pay for letting use it. I don't think the Power Apps model works well, in other words, for what you describe as your business model.

If you have a sound data model, with properly  normalized tables, they can be migrated from Access ACE tables to any number of other databases, as noted above. 

You can build a rudimentary interface for your app in Power Apps. I was surprised by how much I CAN do, in fact. But keep in mind that it is also a low-code environment.

In short, if you can identify a feature of your application that MUST be mobile, an extension via Power Apps for a limited set of users can prove to be very valuable. A public-facing website for clients? Probably not a good fit, unless you have the budget for it.

[#3]

Thanks you so much for your detailed and thoughtful answer. My practice is a volume practice - so at any one time I have 60-80 clients. That would be a very expensive then for each one to have a spoke SharePoint website wouldn't it?

[#4]

That's why I mentioned it so prominently, yes. Potentially, 60*5 or $300 a month just to have a way for clients to check in on their own data. Plus you would have to manage security so that each one sees only their own data. Doable, but not the default approach.

There is an option called Power Pages, which supposedly is aimed at external users, but I know little about it and I have no idea if it would support what you need. Check it out as well.

I'm torn here. I think Power Apps is a great tool. I see it as a way to free some functions--such as the inventory stock take--from the desktop without abandoning the strength of the desktop database application. It's just not the most appropriate approach here, I don't think.

Do take a look, though, at the last few videos I put up on YouTube to get an idea of how you MIGHT incorporate something like this into a larger system. There's a series on Mike's Mobile Library, which does cover the range of functions duplicated from the desktop, as well. You probably don't have time to watch them all in their entirety, but they can give you a taste of what you could accomplish.

[#5]

Another option may be to use Blazor rather than PowerApps. I chaired an Access Europe meeting about this last Wed and the video will be on YouTube soon. See https//isladogs.co.uk/aeu-18/

I believe there is also a similar item on the Denver Area Access User Group channel.

The licensing costs are likely to be significantly less.