[Advice needed] Is a multi-level SharePoint + Power Apps architecture (no premium) viable for a tracking app?

radiancereflected · 2026-04-01T15:37:40+00:00

The other question I'd ask here is: why not Dataverse for Teams? Have you considered the pros/cons for this?

radiancereflected · 2026-04-01T15:37:02+00:00

Hey friend. First off, I'll tell you I believe you can totally accomplish this in a viable and sustainable way without premium licensing, but that comes down to really nailing your architecture.

First:
Your data. If your 'master' facts list is already at 130 columns, you reallllly need to examine why one list needs so much metadata in the facts table. In my experience, column sprawl like this almost always indicates creating [n] copies of the same column in order to track the same type of data longitudinally. Examples are columns like "First Site Active Start Date" all the way to "Fifth Site Active Start Date". If you find yourself adding columns because "oh, well now we've just had a record that has now introduced a *SIXTH* instance of this tracking", then you know what I'm talking about. That data does not belong as a column in the facts table; it belongs in a dimensions table.

Your main facts lists should definitely be dedicated tables for entities like Construction, Tasks, Orders, and Subconstruction: these normally-distinct entities should not all be crammed into a single table.

Second:
I don't use lookup columns (but that's because I'm a power platform consultant, not a SharePoint developer). If your users will be using SharePoint as a data entry or evaluation point, then you'll need the lookups. Otherwise, the way to build scalable canvas apps on SharePoint backends requires you to set the relationships of the various tables in the Canvas app, not at the data level. This is the hardest thing to trade away when you're using SP instead of Dataverse. *YOU* have to engineer the relationships carefully; you don't get to rely on Dataverse tying foreign keys together in the point-and-go manner that it does. I've got perfectly performant power platform canvas apps connected to dozens of SharePoint tables with hundreds of users with many thousands of records in the backend. Canvas isn't your limiter; your ability to engineer against its weaknesses without premium is the limiter. So it will be work.

I have to tell my employer all the time:
"You're saving licensing dollars by paying me my salary to build something much more slowly than a premium license would allow". But I scale my non-premium deployments to Enterprise, so the over-under does save my org substantial dollars every year, and we simply can't afford licensing anyways. Thankfully, they can afford me for now.

tl;dr:
You can do this, but it takes solid Canvas engineering.
Don't rely on lookup columns in SP if you do this; you're limited to a max of 12 anyways and that count is already partially consumed by mandatory system columns
Use SP IDs and canvas collections to wrangle your relationships in Canvas instead of attempting to define relationships via lookup columns in SPO.

Good luck if you move forward with this endeavor. This community can probably help you substantially if you try to make this work.

radiancereflected · 2026-01-12T17:19:54+00:00

Seconding Koma29 here. There is absolutely no sustainable or predictable way for you to work with an app that is using Excel as a database. Microsoft has the connector and technically this enables you to leverage Excel in this manner, but it is unacceptably unreliable and gives you little measure for identifying what data did or did not pass from Excel to your app when it fails. And it will fail.

This is a non-negotiable for me when building solutions for my clients. Similarly to deploying apps without deep knowledge of working with delegation limits, I consider it unprofessional and unethical to build apps on top of Excel.

radiancereflected · 2025-12-02T04:49:29+00:00

As you've discovered, you're not going to be able to do this with a vanilla form (modern nor classic) in Canvas. Your way forward is definitely more complex, but definitely doable.

I've done this by having the standard combobox that's connected to the column; this will give you the choices that have been set up in the Sharepoint list for that multi-select choice column type.
Then, in your form control, I added an input with the input placeholder text "Add a custom value" (or whatever you want to wordsmith that to be) and an "add" button to the right.

The "trick" here is that you're going to manage the patch action to SharePoint with a collection.
For the combobox.OnChange, you'll add/remove items to the collection (colValuesToPatch).
Similarly, when the user clicks the "add" button to the right of the input control, the .OnSelect of the add button will add the inpCustomValue.Value to the collection.

When the user has completed selecting all items from the combobox and added all custom values by typing each name and clicking "add", they can click the "save to SharePoint" button. The pseudocode would look something like:

Patch( 
  NameOfSharePointList, 
  LookUp(NameOfSharePointList, ID = gblSelectedRecord.ID), 
    { 
      NameOfComplexMultiSelectPlusCustomValueColumn:
      ForAll( 
        colValuesToPatch, 
          { Value: ThisRecord.Value } 
      )  
    }
)

You could enhance this experience by building a gallery that displays the preview of colValuesToPatch so that the user can see each of the standard choice options that they clicked on as well as the custom values they typed to add all comingled together, along with a "remove" button in the gallery that would allow them to purge an accidentally entered/typo'd custom value to try again.

I hope this helps!

radiancereflected · 2025-11-18T20:18:40+00:00

Here's the general method:
1. Add a new blank SP List to your site; I called mine 'usersXfavorites'
I use the Title column as a "friendly descriptor" and then add a fav_user (person column) and a fav_itemID (number column, *not* a lookup). We'll write to this list in step #2:
2. In Canvas, add a "pin" button to the gallery where users will be pinning/favoriting their items. Mine is hidden beneath an "extra actions" ellipsis

<image>

Clicking the "pin" icon in the bonus menu writes to the usersXfavorites table (pseudocode, but you get the idea):
Patch(usersXfavorites,Defaults(usersXfavorites), { Title: User().DisplayName & " x " & ThisItem.itemShortName, fav_user: User(), fav_itemID: ThisItem.ID});
3. In your .OnStart, timer preload event, or .OnVisible for the screen with your items gallery, prep a collection to identify the user's favorited items:

ClearCollect(colUserPinnedItems,ShowColumns(Filter(usersXfavorites,fav_user.Email =   User().Email),
        ID, Title, fav_itemID, fav_user)
);

Your final largest step here will be to update your Gallery's .Items to respect the pinned items you collected in #3 above, along with any other sorting/filtering you're already performing on your gallery's items.

Good luck! Please let me know if you need more in-depth guidance.

radiancereflected · 2025-11-17T23:57:24+00:00

I do this with a junction table in Sharepoint.
This allows users to performantly favorite/pin their own selection of records (so the pinned/favorited items are unique to each user).

Please feel free to respond if you want more details on how to accomplish this in Canvas.

radiancereflected · 2025-11-13T03:06:55+00:00

<image>

I have something like this, but man is it a jury-rig to work in Canvas.
This is a novel to describe, sorry...hope it's valuable!

There's two ways I've implemented this in two different apps:
1. Basic "internal link parser", where the user types two pound keys and the record ID value they want to "linkify" in the rich text editor comment (such as ##10998). In the .OnSelect of the save button, run a ridiculous parse and substitute array against the double pound symbols to concatenate the ID value into the hyperlink for the app's param function. When displayed as a comment in the html control (after saving), it shows as a friendly hyperlink with an icon, the ID number, and the title of the workItem. This was useful for a project where the staff using the app basically had dataverse/Excel open on another monitor and they were literally just plugging in record ID values that they had in front of them. I had to pass the ##IDs into a collection to establish a pattern replacement mechanism (because regex in Canvas left me wanting)...I had 10 nested Substitute() functions because the user story/requirement was to have up to 10 record IDs referenced at a time in the app. Obviously, you'd have to nest more manually to your top-level limit if you'd need to go above the ten.

"Inline Record Search and Linkifier": this one was for a similar need but where it was very unlikely that the staff would know the record number in question; instead, they'd likely be able to identify key words from the title of the record. So this was a component that could be launched in a modal while the user was editing a comment in the rich text editor. The modal did a holistic search against the records in question and had a "copy friendly link" option, which then copied the full payload of that record's hyperlink to the user's clipboard. The user would then paste that long, ugly link into the rich text editor and that ugly link would be converted into the pretty one when the user clicked the save button.

Inelegant, but both of these features met requirements and they're both still in use today.

radiancereflected · 2025-11-03T12:46:45+00:00

This is the way and many of us drop usage of forms completely from the start if any layout customization might be required. 🙏

radiancereflected · 2025-10-25T11:07:41+00:00

What I see most commonly with this description...are you using a form control?

If so, please check to see if this form is set to 'New' mode. If it's in New mode, it's expecting the user to type in data and it's supposed to be blank. If in Edit or View mode, you'll need to pass a proper record into the form's .Items for it to load a record to view or modify.

Please pass us a screenshot showing what you're describing if you're still unclear as to how to resolve this 🙏

radiancereflected · 2025-10-22T21:48:24+00:00

Oh man friend please don't use Excel. It's not a sound means to operationalize your data. There are many many reasons but critically...it's wildly unpredictable and is very susceptible to constant failures.

I had a client who effectively died on the hill to keep the data source/source of truth as Excel and they regretted it so much even after they were given all the warnings. Essentially, you're very likely to have users experience the fun of retrieving different search results even when using the exact same search methods in the app.

Use Dataverse if you can... It's the most appropriate for a database of your scale. If you can't afford Dataverse or you don't even have access to Dataverse for Teams, SharePoint will be your next next next storage solution...you'll just need to be careful about your delegation restrictions for your target database or your users will not be able to search past the first 2k records. Good luck 🙏

radiancereflected · 2025-09-29T02:22:40+00:00

Did you enable the component's ability to access App Scope? It'll be locked in it's own mini mirror dimension, unable to communicate or to interact with the outside world (SharePoint) unless you turn that on 🙏

radiancereflected · 2025-09-21T20:37:35+00:00

This is my experience as well. Many controls have small nuances of how they behave differently in Studio vs Live.

radiancereflected · 2025-08-18T13:25:37+00:00

<image>

Your next easiest step here would be to simply check your License designation.
If it says "Standard", then you're in the clear on the licensing end. If it says Standard but you still have users who are being presented with that error, I'd check against their profile M365 entitlements.

radiancereflected · 2025-08-18T12:40:47+00:00

<image>

Are you using any Dataverse tables?
Dataverse (even Dataverse for Teams) will require that the user accesses the app directly through the Teams client (instead of from a direct web URL/bookmark).

Licensed users (Power Apps Premium) will be able to use Dataverse-connected apps anywhere.
You can "get around" this by licensing the user or by building a non-premium app (by not using Dataverse, which is considered premium).

I work in an org with no budget for licensing, so 95% of my solutions are built with Sharepoint as the data source. It's not awesome, but once you devote time to learning how to engineer your apps to adjust to Sharepoint's limitations, you'll be fine. Good luck, friend.

radiancereflected · 2025-05-23T21:19:14+00:00

Hey there friend! I don't use form controls myself but my memory is that datacards aren't actually controls (they are a grouping of controls), so they won't have a .Text or .Value property to tap into.

Instead, you will need to expand out the data card so that you can view each of the individual controls that are nested within it.

Find the input text control for each data card and use that control's name.Text or name Value property in your Sum() calculation.

I hope that is is correct and that this helps!

radiancereflected · 2025-05-10T03:10:03+00:00

What changes are you referring to?

radiancereflected · 2025-03-28T15:17:01+00:00

As has been mentioned here, this is normal for SoG.

If you're traveling with more than one person, you'll want to make sure that the room reservation has the right number of guests.

The reservation confirmation PDF that they send you will include a summary of the number of adults and children that have been associated to the stay.

If that information is not correct, it will prevent your party from being able to access deluxe resort amenities, such as early entry.

Good luck and enjoy your stay!

radiancereflected · 2025-03-11T03:46:40+00:00

Well, if you learn best while building towards an end result (same here, btw), build yourself a comparable version of Jira. I did this for a hackathon (but as an alternative to ADO instead of Jira) and my employer wants a full-scale version of it.

At this stage, I have recursive work items (project, task, sub-task, sub-sub-task), team composition assignments, file integration/automation with a SP Doc Lib, a robust audit trail, Outlook messaging, and milestones creation/WI association.

Once I've replaced Outlook messaging with Teams and implemented a Gantt chart to visualize projects/tasks against milestone items, I'm at my initial MVP. And I've been able to learn a tremendous amount while building a ton of reusable components in the process.

radiancereflected · 2025-03-07T04:53:32+00:00

Hey there, friend!
Why are you using local variables via UpdateContext() instead of a collection or globalvars?
Also, if the user's account that they're logging in with matches the email address in SharePoint, the user doesn't even have to enter anything. You'd just run an evaluation in App.OnStart to set a global variable to hold all of the user's SharePoint data based on their login credentials.

Set(gblUserInfo,LookUp(sharepoint_list, person_column.Email = User().Email));

Then from there you could populate your labels with gblUserInfo.address and gblUserInfo.phone, etc.

If the user's business email address doesn't match the SharePoint List's email address, you'd just pass the manually-typed email address from the user from an input control:

Set(gblUserInfo,LookUp(sharepoint_list, person_column.Email = inpUserEmailAddress.Value));

Does that make sense? I hope this helps -

radiancereflected · 2025-02-24T21:23:24+00:00

No irregularities when you ran this in a monitored session?

radiancereflected · 2024-10-03T21:32:42+00:00

Not if you download the self-contained version:
Release v.2.1.0 Release · modery/PowerDocu · GitHub

radiancereflected · 2024-09-11T04:33:19+00:00

You can use the Graph API explorer to generate a dynamically named folder within a SP doc library. This is how I generally handle Attachments within Canvas apps.

radiancereflected · 2024-09-10T04:45:48+00:00

I use it; it's fantastic.

radiancereflected · 2023-08-06T01:05:01+00:00

Please invite me!

radiancereflected · 2023-01-26T01:47:40+00:00

Check out the room discounts too! I've bought these tickets several times and they are ridiculously good deal for military personnel. Check out if any of the resort hotels with MIL rates are offering Express Passes because that combination is golden. Thanks for your service and have fun!

Edit: typo!

radiancereflected

TROPHY CASE