cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cmorinville
Frequent Visitor

User selection of the current datasource.

I'm fairly new to Power Apps.  I'm trying to find out if there is a better way to accomplish my goal of allowing the user to change the datasource.  It was easy to pull in one table and filter the results by year.  What I'm doing is a little clumsy and limited.

 

I'm building a simple financial application connected to Excel.  I'm pulling in financial transactions (like a checkbook register) going back a few years.  I've run into the limit of 500 items that can be pulled into Power Apps (or 2000 if the setting is adjusted).  I needed a way around that so I broke out my transactions table into yearly transaction tables.  The structure of each is identical.

 

Upon loading the transaction screen with the data gallery, I statically set a variable called "TransactionTable" to the current year's table.  The data gallery calls my variable and does some filtering and sorting to get it the way I want it.  Once loaded the screen has a drop-down menu with statically assigned values for each year.  OnChange I set the variable and use the switch command to change the four-digit year to the actual table name.  This all works fine.  I can select each transactions record and view the detail in a form.  I can edit the record in a form.  I can add records in a form.

 

My solution mostly works OK, but it's got some limitations that I'm hoping you can help me work around.  These are my questions...

 

1.  I can't remove a record because the Remove and RemoveIf commands require the name of a datasource as the first argument.  For some reason, they don't accept my variable like most of the other functions do.  I have to statically enter the current year's table for the remove command.  So I'm limited to removing records only from the current year.  In my example, this isn't all that big of a deal since I don't want to delete the older records, but I have to statically change the form each year.  It's workable, but clumsy.  I haven't been able to figure out how to use Patch to delete a record and I'm not sure that it can.  I've considered adding a field to the data for something like "isDeleted" and using Patch to fill that and then only pull up records that don't have this set.  Can Patch be used to delete a record?

 

2.  My solution would work pretty well if I could set the variable in a more dynamic fashion by pulling the current four-digit year and using a concatenation of text to set the variable to my table name.  The Set command sets my variable as text and not does not use the table name.  What I need is something similar to the Value function to convert text to numbers, but only I need to convert text to a datasource name.

 

3.  Ideally, I think the best solution would would be to have a drop-down that dynamically populated the list of datasources (with a filter for only displaying the "transaction" tables).  Then just plug in this value to the gallery.   There doesn't seem to be a way of doing this.

2 REPLIES 2
WarrenBelz
Super User
Super User

Hi @cmorinville ,

I share your frustration with not being able to use variables to dynamically refer to table, control or field names - you could do that in Access 20 years ago . . .

However this does not help you, so I will give you a suggestion that I use and is very easy. Have a gallery for each year and set the visibility based on the drop-down value. So calling the control DropDown1, the Visible property of Gallery2019 would simply be

DropDown1.Selected.Value = "2019"

This produces a Boolean result which can be reflected in the Visible property of the gallery.

I have a "tabbed" (actually buttons) screen with 23 galleries on it and to the user, they are simply opening a tab. These are however filtered collections, so just watch the amount of total data being loaded on the screen.

 

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

@WarrenBelz , Thank you for posting.  This is another possible work around.  A good tip until a better solution is added to Power Apps by development.  Thank you.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Top Kudoed Authors
Users online (1,751)