Answered

Reference to a Column in Sharepoint?

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

I'm sure this title is confusing but I'm not sure how else to put it. Here's my scenario:

I'm building an inventory tracking app. I have a Sharepoint list where the columns are the items we're counting and the rows are counts by date. There's a constant "Current Inventory" at the top of the list that is updated, and a history in the rows below.

I want a page in our app where the user can click on one of the things to count (so a column) and edit the number, which would patch the row in Sharepoint. Everything works except for one:

I can't find a way to reference the specific row/column to patch it. In other words, I want to do something like:

Set(varSelectedCount, LookUp([@'Inventory Tracker'], ID=1).'Laptops');

This kind of works, but it's only a reference to the number in that location (ID=1 being the row and 'Laptops' being the column). I can't find any way to hold the information I would need to patch that. When you patch you can specify a row, but then you have to do the columns as direct references. What I want is a way to hold the columns in variables. That way I could do something like:

Patch('Inventory Tracker', LookUp([@'Inventory Tracker'], ID=1, {
varSelectedColumn:Something.Value }

If this makes any sense at all I'd really appreciate any assistance. I'm avoiding a form because I want better controls (+/- 1 and 10 buttons, for example) that don't work with a form.

Categories:

Building Power Apps

All responses (7)

Answers (1)

Sort by

RusselThomas 4,014 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Anonymous ,
I'm afraid you can't variabilise (if that's even a word?) columns.
They are absolute references to data source constructs and cannot be contained in variables for use in expressions or formulas.

Some pictures might help - of your data source table construct and perhaps of the screen, to see what you're trying to achieve. Usually, when people are trying to reference variables as columns in PowerApps, it means their data construct doesn't really support what they're trying to do.
There might be another way to achieve what you're trying to do, but you definitely won't get there with Column type variables...
Kind regards,
RT
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hey RT thank you for the quick reply here.

This is what I'm trying to do (unfinished but it's the concept at least). In the onVisible it goes ahead and selects the Current Inventory row of our Sharepoint list, and then each button is meant to be a reference to a column of it. On pressing the button, it sets the selected item text at the top of the screen, and the text input fills with the current value of that column on the selected row. This can all be set up to work, but what I can't figure out how to do is update that number.

And here's a shot of our data source:

That first row is always selected by the app. The columns are the count items. There's a flow that runs to duplicate the "Current Inventory" column before it's updated and change the title to a timestamp, acting as a count history.

Thanks again for any assistance.
Verified answer

RusselThomas 4,014 on at

Like
a
(2)

Report
Copy link

Link copied!
Thanks for the pics @Anonymous, it does help, but I have more questions 😁
I'm not sure I get the button pressing and count experience - I may need some more explanation here....
Do the < and > icons move between devices, or does it increase/decrease the count for that item you pressed, which you then want to patch? If so, wouldn't it be easier to just let them edit the numbers next to each "button"?
Are you wanting to patch each individual item count, or update them all and do one patch?
Are you using a form here? If so, shouldn't you be using the SubmitForm() function to update data instead of Patch()?
Lastly, and possibly more importantly, is there a reason your list has to be constructed in this manner?
Your column names are effectively an element of your data, which is why you're trying to variabilise them into expressions. Column names are simple references that usually describe what data the column contains - they should never be considered data themselves.
Add to that the fact that new devices will always be coming along - with this model that means adding more columns, and having 300 columns on a list is not a good idea.
Rethinking this may save you many, many headaches down the line.

I understand these things aren't always easy or practical to change, but consider the following table construct as an alternative:
Device _Current Inventory Date
Dell Latitude 37 05/03/2022 07:13PM
Surface 3 (i5) 24 05/03/2022 07:13PM
Surface 3 (i7) 12 05/03/2022 07:13PM
Firstly, yes, you will have more records per timestamp - we've effectively unpivoted your original model, but with good reason. But now you have three columns. Performance-wise, that's a win in many respects.

It will also allow you to work with the data a lot easier, instead of having to hard code column references in your formulas (because you can't variabilise them).
If you want to run analytics on this data at some point this type of construct would be also be useful.

What you will lose is the ability to reference all the data for a specific timestamp in a single row - meaning you can't use the Form() control to update multiple records here - but considering you were trying to use Patch() instead of SubmitForm() in the first place I'm guessing this wouldn't be that big an issue for you...?

But the benefits are multiple - You can Filter() by device and timestamp, do lookups on certain devices for certain timestamps and patch data back in bulk or for specific device for a specific timestamp.
Your Date data will also be of a Date type, saving you having to convert it everywhere outside the list (like in Power Automate, Power Apps or Power BI) - and this means easier-to-use date functions.

Anyway, the model doesn't have to look exactly like this, and I understand it's not always possible to change, but I would seriously consider redesigning it to get your device names into your actual data instead of having them as columns...

Kind regards,
RT
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hey RT thanks again for the input- I'll try to unpack and answer all the questions I can here:

Do the < and > icons move between devices, or does it increase/decrease the count for that item you pressed, which you then want to patch? If so, wouldn't it be easier to just let them edit the numbers next to each "button"?

Increase/decrease the count. This was a request from the team- they're placeholders right now but they wanted +1, -1, +10, and -10. I thought it would be easier to just edit the number as well, but that's what they asked for haha.

Are you wanting to patch each individual item count, or update them all and do one patch?

Optimally, all at once with a single patch. I can already see I may have issues with that as it would likely mean even more variables to hold numbers prior to patching.

Are you using a form here? If so, shouldn't you be using the SubmitForm() function to update data instead of Patch()?

My first attempt at this used a form until I found out that the OnSelect function of anything within a Form seems to not work at all. I couldn't find any possible combination of settings where the click was recognized. It seems to be screened by the DataCard itself, which doesn't offer OnSelect functionality. Maybe I missed something here but I could never get it to register. Form was definitely my preferred solution but nothing I attempted could make it work as our design goals instructed (picking a singular object to count and being able to edit the field and increment it with buttons).

I understand these things aren't always easy or practical to change, but consider the following table construct as an alternative:

Firstly, yes, you will have more records per timestamp

To be frank, I hadn't considered a layout like this mostly due to that. The biggest concern is number of records- Sharepoint stops playing nicely with PowerApps at 2000 records, searching stops working, etc. I hate it but I also understand Sharepoint isn't meant to be a database so I get it. With it how it is in my current setup, we could probably hold a couple of years worth of records with no issues. With one record per count item I probably can't even keep a single year's worth of records before I hit that soft limit. Another part of this app is being able to show history and query back through old records (compliance/audits, etc.).

That said, I may have to consider this solution simply because of how PowerApps/Sharepoint work together. To be clear- I'd always rather SubmitForm over Patch, I'm just using Patching because I can't find another way. The setup you outlined would require a major redesign of the other part of the app (inventory history viewer) but that may be acceptable to get the functionality how it's been requested.

If it's not clear, I am by no means a data specialist or anything. My background is largely in C# and getting used to PowerApps and working with data like this is quite new to me, so I appreciate the assistance. I'll mull it over, but it sounds like your solution may be the right move.
RusselThomas 4,014 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,
So, a couple of things; 😁
User experience - there's a threshold to giving users what they want, and building klunky apps and UI logic. If it really makes sense to them to work like that, and it doesn't make your life miserable with maintenance or code updates, then by all means give them what they want. But keep an eye on how hard it is to understand your code as a result. Based on their response, my guess is they don't like adding numbers - so their count is likely a delta (I have 10 more laptops), as opposed to a total (I have 37 + 10 = 47 laptops). You can perhaps make this easier by instead asking them to just input the delta and add it yourself to the total - but at the end of the day you can give them what they need provided it doesn't make your app impossible to understand.
Forms definitely support controls that fire OnSelect: functions. Not sure why it didn't work for you, perhaps there were some scoping issues in your formulas, but buttons definitely work inside forms....
SharePoint lists support up to 30mil rows of data. This number reduces the more columns and complex data types you have per list. Less columns with more rows to me is better than more columns with less rows. Obviously there's a threshold there to consider somewhere, but when you're talking reasonably large datasets (like yours is likely to become over time), I would take less columns and more rows any day. Make sure you index your lists using your primary filter columns and you'll have no issues. I've got apps using 100k + rows in lists without any performance issues. Remember, your app can only fetch so many rows at a time anyway, so the number of rows in the source is immaterial to performance - 2000 rows is a PowerApps limitation on maximum data rows to return, not a SharePoint limitation on rows it can store - and it's there to protect your users from massive data queries and slow apps.
Forms are only good for single row submissions - they have handy features, but in the backend, it's highly likely that SubmitForm() and Patch() use the same endpoint method to send data to the source, so one is not necessarily better than the other, they're just more or less useful depending on what you're trying to do. With your dataset, a single row update is what you were trying to do, so a form makes sense - but your data model makes it difficult to do a bunch of other stuff, so either you stick with the model and try panelbeat a form to work, or look at changing your model and using Patch instead.
By the way, you can update each item as it's entered using SubmitForm as well with some clever tricks...but as I said, probably easier and better to batch update using Patch.
Consider creating a duplicate app, and then create a new list and trying it out - that way you don't necessarily throw everything away if you decide it's not the way to go - let me know if you need a hand figuring it out.
Kind regards,
RT
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Based on their response, my guess is they don't like adding numbers

Exactly this, and I've considered doing exactly what you proposed (just letting them put an addition/subtraction amount in a data entry field and calculating with it). My design isn't finalized yet so that could definitely be the way I go with it.

Forms/OnSelect

I'll grab some screenshots of the issue in a separate post. I'll start from a new screen and run through the process over again.

2000 rows is a PowerApps limitation on maximum data rows to return, not a SharePoint limitation on rows it can store

This is what I was referencing. There's a lot of non-delegable functions (Sharepoint side) that cause issues because of the PowerApps size limitation on returned rows. I ran into this with another of my apps where I couldn't use an "in" to search for names because we have more than 2000 entries. The alternative would be "Starts With" but all names are not formatted the same so that also wasn't an acceptable solution. I never found a solution for this.

The real problem is we should probably be using Dataverse/SQL for datasets of this size, but that's beyond the scope of my experience a bit and also beyond what I have a license for (in the case of Dataverse).

Thanks again for all the input and information. I'll spin up another list and do some alternative testing to see if I like it better, at the end of the day I can probably limit the returned rows to under 2000 prior to trying to use any delegable functions in this case.
RusselThomas 4,014 on at

Like
a
(1)

Report
Copy link

Link copied!

Sounds good!
Perhaps some parting comments then:
Based on your data and app, it looks like you're planning on storing historical timestamped data. Presumably then, it's eventually going to go over 2000 rows in your source.

Whether that source is SQL, SPO or Dataverse, you will still only be able to return 2000 rows to PowerApps from a query - whether the source supports delegation or not. This is a mistake many people make in their interpretation of delegable queries and how the Data Row limit is applied.
Delegation just means the source understands and can apply your query condition across the entire dataset before returning a response - if it can't, it will just return the first 2000 rows and PowerApps will apply the query to that subset of data itself.
But - and this is a big but - if your query can be delegated and the response to the query exceeds 2000 rows, the data returned to PowerApps will still be truncated at the data row limit - yes, you'll be getting a delegated response, but anything past the 2000 row mark even on a delegable query will not be returned to PowerApps... which is the Data Row limit in action.

This is by design, so don't make the mistake of assuming a different data source will change that.

I would agree on using SQL or Dataverse if it's possible to do so - robust data sources are always preferable with large data sets.
That said, SPO does give you some nice headroom to play with in terms of scale, especially useful while you're figuring things out and if your query logic works on SPO, it's pretty much guaranteed it will work in other sources (except maybe Excel), so it's a great sandbox. Push it as far as you can.

Lastly, you can use Dataverse for Teams without a premium license, so you can play with Dataverse as a source quite a bit before you're forced to move to Dataverse Pro or SQL. Personally though I think you can do everything you're doing in SPO.

All the best and good luck!

RT