Unanswered

Formula works with 1 table, but not the other

(0) Share

Report

Posted on by Community Power Pla...

I have a formula that is supposed to find a name in a table and return a "Yes" if the name is in the table, or a "No" if the name is not on the table. When I use my Table_Audits the formula works, but it does not work with my Table_Records. These tables are exactly the same except in different excel workbooks. The app submits audits to Table_Audits and then the records are moved to Table_Records and deleted from Table_Audits. Is the issue the separate workbook?

Working Text formula on a label in a gallery: If(ThisItem.Student in Table_Audits.Student, "Yes","No")

Non-working Text formula on a label in a gallery: If(ThisItem.Student in Table_Records.Student, "Yes","No")

Working Text formula on a label referencing a combo box: If(cboxSTUDENTcnt.Selected.Student in Table_Audits.Student, "Yes","No")

Non-working Text formula on a label referencing a combo box: If(cboxSTUDENTcnt.Selected.Student in Table_Records.Student, "Yes","No")

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (1)

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Anonymous

Is there possibly an issue with how that second table is added in? Possibly try removing the table and data source and then adding it back in?

Does the table have actual values in it (in PowerApps)? If you use it as a data source for, let's say, a Gallery, does it have values in it as you expect?

Check out some of those questions and see if that leads you to a clue. If not, post back and let's see what's next.

I hope it is helpful for you.

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

Like (0)

Report

@RandyHayes Thank you for helping! I now can't get either formula to work 😞

I tried disconnecting all data sources and reconnecting. No luck. I tried moving the Records table to my original excel workbook, disconnecting and reconnecting that data source. No luck either. Lastly, I copied and pasted all 4366 records into the Audit table and it still did not work.

The Records table does populate a gallery as normal.

I submitted a new audit which populates the Audit table. The formula worked for that newly submitted student when linked to the Audit table. I then copied and pasted the new audit into the Records table and changed the formula to that table, and it did not work. Next, I tried making a new app, one for each of my excel data sources and neither worked.

This makes me feel like it's an issue of the copy and pasting of records. I tried deleting the powerappsID, then disconnecting & reconnecting the data source to no avail.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

Well...sounds like you hit the bottom! No where to go but up from here.

So, first question - what connector are you using? OneDrive for Business or the Excel import?

How big are these tables (rows and file-size)?

Excel can get temperamental sometimes. The key for Excel usage (IMO) is to set it up and use the Excel file ONLY as a datasource from that point on. Don't work with it like a regular Excel file if you can avoid it.

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

Like (0)

Report

@RandyHayes I'm happy to at least be at a starting point, even if it is the bottom LOL

OneDrive for Business.

The tables currently have 4,359 rows and counting. Most all columns are general formatting. 6 of them are yes/no checkboxes which populate true/false. The current size of the workbook is 550KB.

Was this reply helpful? Yes No
Verified answer

RandyHayes 76,297 Super User 2024 Season 1 on at

Like (0)

Report

@Anonymous

Ah, you're issue is with delegation then. Unless your filter is delegable to the datasource (which it's not to Excel) then the most that your filter will work on is the first 2000 records (and only if you've bumped up the standard 500 limit to the max of 2000).

That's going to be an issue for you. You might want to consider SharePoint or SQL as a data source instead. Still doesn't solve limits, but you can at least delegate queries to those sources to get what you want.

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

Like (0)

Report

@RandyHayes dang delegation strikes again!

If I were to create a SPL and upload my current 4300+ records, would I then be able to
A) change my audit form to the same SPL instead of the excel table?
or
B) continue to manually export records from excel to the same SPL? (when I tried multiple exports from excel to SP, I had to make a new list each time)

Also, when I had the records on SP before, I received this error message. Will this affect the app? The SPL would just be used as a database for storage.

Was this reply helpful? Yes No
RandyHayes 76,297 Super User 2024 Season 1 on at

Like (1)

Report

@Anonymous

A couple of things here...

1) As for the Excel over SharePoint...I'd go with SharePoint. You are limited in the number of things you can Delegate, but at least you can, and you can plan for it. Excel doesn't give you any delegation options.

2) As for exporting from Excel...yep, that's a pain. The best thing to do is to create a SharePoint list from the Excel file from within SharePoint. That *should* get most, if not all, of your data in the list. If it doesn't, then you can copy and paste large blocks of Excel into SharePoint at least.

As for the warning in SharePoint, no that will not impact the PowerApps side, just your View in SharePoint.

BUT...keep in mind that your logic on the original filters will need to change some. The 'In' operator is not delegable. So, consider ways to utilize things like StartsWith in your formula. That will work much better. AND, if you expect that any filter will return more than the maximum number of rows (2000) then you'll need to consider pulling the data into your app as a local collection.

So...a few things to consider for your design. But, I hope this is all clear and helpful for you.

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

Like (1)

Report

@RandyHayes Thank you so much for taking the time to hatch this out with me.

Was this reply helpful? Yes No