Answered

Creating a reference application that allows you to change data source (to avoid 2000 line limit) and review selected tables?

(0) Share

Report

Posted on by Community Power Pla...

I am currently in the process of making an application for our team that allows users to review SKUs and Tax rates for a variety of products our company offers - and have a question about transitional data sources.

The scenario is that we have a large Excel workbook with around 10 independent tabs. Each tab has a table of our products with their associated SKUs, tax rates, and other useful information for our team to review.

Originally I was going to make the app read from a single table which would be a 'master' version of our independent tables. That way when you use the app, you could simply review every product we have. Unfortunately, it seems that PowerApps is limited by 2000 lines of data and we are nearing 15,000.

Would there be a way to allow the user to change the data source within the application (per a drop down or some similar selection) that would change which table the application pulls from?

I.E. - We offer products to different countries. The user can select 'American Products' at the top of the application, the application would then pull data from a 'AmericanProducts' table (out of an Excel file shared via OneDrive), and populates the BrowseGallery with those entries (Nearly 1,000 unique lines). Then, the user decides they want to see 'Australian Products' from the drop down, and it refreshes the data from a 'AustralianProducts' table (out of the same, or if necessary a different, Excel file shared via OneDrive) bringing in those new entries (say around the 1,500 mark).

Would this be possible? I am attempting to find a solution here rather than copy+pasting the design across 10 unique apps due to the line limit.

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

WarrenBelz 153,117 Most Valuable Professional on at

Like (1)

Report

Hi @Anonymous ,

The first suggestion I will make is to change to SharePoint - as a database, Excel is a good spreadsheet . . .

However it you must have Excel and can have separate tables on (preferably) different tabs, you can connect to them all as separate lists and collect the appropriate table, then have the collection as the Items of the gallery.

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Thank you for the prompt response @WarrenBelz. Currently we're limited to Excel as we will be continually changing the data (adding and removing entries) from our Excel files - and this is more a 'pitch' application. Once I can show the usefulness of the app, I would start considering, and ask our upper management to use, SharePoint as a better alternative for storing our information.

Based on your response, I would create a list within the drop down and assign each option in that drop down's list to a corresponding table (and unique tab) within the Excel file, correct? That is, bring in each table as a 'data source' in powerapps, and find a way to tag each one to an item on the drop down list?

I am completely new to how I would actually do this but wanted to confirm that is what you meant before I start using google furiously to figure it out.

Was this reply helpful? Yes No
Verified answer

WarrenBelz 153,117 Most Valuable Professional on at

Like (1)

Report
Hi @Anonymous ,

You can "tag each one as a data source" providing all the lists are identical in structure in every respect (field names and field types) - you would us a Switch() statement on the Items of the gallery

Switch( Dropdown.Selected.Value, "Australia", Australia, "America", America, . . . . )

This assumes the names of the list are as below (and I put Oz on top as people always prefer our products)

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

@WarrenBelz - The tables are identical in their shared column header names and data types. They are just separated in different tables for each independent market for our products.

I think I am having the most difficulty with understanding how to make each table a separate list - and then collect the appropriate table (from your initial reply) to bring into my application. I tried bringing in each independent table into PowerApps (TableAU, TableCN, TableUS, . . .) and they are listed as data sources now, but I think I misunderstood how I should tie them together. Which I would assume you meant it should be brought in as a single Table and tied together through lists.

Would I follow this protocol for each tab?
https://support.microsoft.com/en-us/office/create-a-list-based-on-a-spreadsheet-380cfeb5-6e14-438e-988a-c2b9bea574fa

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (1)

Report

Hi @Anonymous ,

They need to remain separate lists (data sources) and simply the one you need selected in the Items of the gallery as per my Switch code example. If you are dong any user-driven filtering, a collection is probably a better way, The post you refer to relates to static data tables (which cannot be edited) and must be re-reloaded completely with any changes.

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report
Thank you for clarifying, @WarrenBelz . I feel that I am nearly there with your assistance! This has been EXTREMELY helpful.

Currently, I have the dropdown working with populating the BrowseGallery. On selecting a new item, the gallery then knows to update the dataset based on the DropDown.Selected.Value - but it is only working about 80%.

I populated the Dropdown with ["Australia","China","US"] - then changed the DataSource for both the Gallery and the DetailForm to reflect on the dropdown selection.

They look like the following:
DetailForm -
DataSource
Switch( Dropdown1.Selected.Value, "Australia",TableAU, "China",TableCN, "US",TableUS)
BrowseGallery -
Data
Switch( Dropdown1.Selected.Value, "Australia",SortByColumns(Search([@TableAU], TextSearchBox1.Text,"SKU","Description","HTS_x0020_US"), "Description", If(SortDescending1, Descending, Ascending)), "China",SortByColumns(Search([@TableCN], TextSearchBox1.Text,"SKU","Description","HTS_x0020_US"), "Description", If(SortDescending1, Descending, Ascending)), "US",SortByColumns(Search([@TableUS], TextSearchBox1.Text,"SKU","Description","HTS_x0020_US"), "Description", If(SortDescending1, Descending, Ascending)) )

I am able to switch the tables based on my DropDown and populate each data card on the browse gallery but when I click on a specific entry and view the detailed view, I am missing some values to call upon for display. It appears to be the same issue on the primary gallery screen.

I have 7 columns in my table that are as follows:
SKU - integer
Description - string
Market - string
HTS US - float
Market HTS - float
Duty Rate - input in excel as a string I would assume (i.e. 5% would be the input for one of the cells)
Notes - string

The 5 items in bold are available when setting the default values for my DataCards (ThisItem.Notes for example) - but two of these values are not populating or being recognized. The Duty Rate and Market (Columns F and C respectively). I would possibly understand why they would be left out if they were toward the end of the entire table but they are mixed in with the other data that is being received successfully.

Have I done something wrong to pull my data in during the switch?

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (0)

Report
Hi @Anonymous ,

I did not realise this was a Form as well and this is becoming a bit of an epic work-around (all this goes away with SharePoint). A large collection is a possibility with a gallery if the data is static, and also a form if you do not want to edit the data and you have a unique identifier (preferable numeric) on the record. So firstly, do you want the user to edit the data and do you have an identifier?

Going back to your question, I have not tried a Form like this before, but other controls such as combo boxes have a habit of not acting as expected with conditional data sources. Onto your gallery code, you can try the below, but you are correct in assuming if one field of a type displays, the rest should.

SortByColumns( Search( Switch( Dropdown1.Selected.Value, "Australia", [@TableAU], "China", [@TableCN], "US", [@TableUS] ), TextSearchBox1.Text, "SKU", "Description", "HTS_x0020_US" ), "Description", If( SortDescending1, Descending, Ascending ) )

The Form Item also will need attention and can be referred to the Gallery Selected Item

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Hi @WarrenBelz - so this is actually not an editable form. This is strictly for external colleagues to reference our products without the risk of them adjusting the datasets. My team will be the only members adjusting the data (by adding new entries/products/SKUs) by manually changing our Excel files. The application itself is extremely simplistic in its base, as its just a modified version of the Power Apps Canvas 'auto generated' application when you import a file from OneDrive.

In all actuality, this is supposed to be a simple reference gallery in which a user can search for a SKU or product (within a specific market [AU, CN, US, etc]) and find all of the necessary information they may want. Particularly by clicking on a gallery item and having a more detailed view of a product. This is where they may review tax rates, unique HTS/HS codes, and other various data stored on our Excel tables.

As far as a unique identifier goes, we have not created any but noticed that Powerapps created a new column with their own PowerappsId - which I would assume would be the unique identifier here.

In summary:
1. We want individuals to be able to access the app and search for items based on the description, SKU, product, etc - that seems to be working flawlessly at the moment.
2. The user should not be able to edit any values of the original data. This is simply a reference application.
3. The user should be able to change the market at the top of the gallery to circumvent the line limit of Powerapps, generating a list of all items to that specific market.
4. The user then should be able to click on a gallery item to get a 'detailed view' of the product. Displaying information from each of the columns on our excel file.

I implemented the code you typed out and it still works just about the same. The entries all come up as expected - but I cannot seem to bring in a couple of columns from our file (which should be there!).

When I was working with a singular data set, the detailed view (and associated 'values/variables') came together without an issue. I guess it's the utilization of a DropDown that is messing with the data, while I try to do this massive workaround.

You mentioned that SharePoint would eliminate all of this. Would it still make sense to use SharePoint if we are going to be editing our files weekly, which changes the dataset for the application? I'm trying to conceptualize what the setup would look like here.

In an ideal situation (for what I'm doing now) it would be Have a master excel file that my team can edit (with new products) -> save/share this over OneDrive -> PowerApps reads the file from the OneDrive and allows the users to search/browse products easily with detailed info.

What changes in this situation with SharePoint? I am completely daft when it comes to using it.

Was this reply helpful? Yes No
WarrenBelz 153,117 Most Valuable Professional on at

Like (1)

Report
Hi @Anonymous ,

A coupe of things here

If you simply want to display the data, do not use a Form, but simply Labels arranged to look like a form and make their Text (whatever the field name)

GalleryName.Selected.FieldName

You do not need to worry about the unique identifier then (although the Power Apps ID is not actually available to query) Also there is no reason all fields will not display if they are simply referencing a record selected in the gallery.

Be aware with Excel that you cannot have the sheet concurrently open in Power Apps and anywhere else and adding new records outside Power Apps can be problematic as they could be outside the defined table area.

As to SharePoint, you can bulk edit and add items in a data grid view, which looks much the same as an Excel sheet and as I noted earlier, you can filter the country with a Delegable query (just ensure you index the column in SharePoint if the list is going to be more than 5000 entries)

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.

Visit my blog Practical Power Apps

Was this reply helpful? Yes No