cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
tewksbum
Helper I
Helper I

AddColumns causes DataTable Filter to lose rows?!?!

Here is the underlying Postgres table... showing 7 rows w/ BookingSlotId = 1180

tewksbum_0-1632950923096.png

 

 

 

So... basic filter applied w/ BookslotId = 1180... returns all the rows... great

tewksbum_0-1632949240512.png

Filter(
'xxx.BookingSlotParticipant',
BookingSlotId = 1180
)

 

If I update this... to use an addColumns...

tewksbum_1-1632949426399.png

 

Filter(
AddColumns(
'xxx.BookingSlotParticipant',
"BSID",
BookingSlotId
),
BookingSlotId = 1180
)

 

boom - I'm down to 3 rows... I'm stumped!  Any help much appreciated! 

2 ACCEPTED SOLUTIONS

Accepted Solutions
timl
Super User
Super User

Hi @tewksbum 

I'd try calling AddColumns on the return value of Filter to see if that makes a difference -

AddColumns(
    Filter('xxx.BookingSlotParticipant'
            BookingSlotId = 1180
    ),
    "BSID",
    BookingSlotId
)

View solution in original post

Hi @tewksbum :

May be you could try:

AddColumns(
   Filter(
     'xxx.BookingSlotParticipant',
     BookingSlotId = 1180
   ) As AAAA,
   "BSID",
   BookingSlotId,
   "Trainer",
   LookUp(
      col_Trainers,
      TrainerId=AAAA[@CreatedById],
      Email)
)

Best Regards,

Bof

View solution in original post

11 REPLIES 11
tewksbum
Helper I
Helper I

Adding to this... I can replicate the same issue using collections w/o AddColumns... so maybe AddColumns is a false positive here...

first, run the concurrent App.onStart()

 

Concurrent(
ClearCollect( col_BookingSlotParticipant, 'xxx.BookingSlotParticipant' ),
)

but the basic DataTable exhibits the same issue as when pointing at the table connection... w/o the AddColumns...

tewksbum_0-1632949982857.png

Filter(
col_BookingSlotParticipant,
BookingSlotId = 1180
)

 

So.... something isn't the same between the actual table connector... and a collection based on that connection...

 

 

 

tewksbum
Helper I
Helper I

The only other thing I can think to add... the 4x rows not showing up... were pushed to Postgres w/ a Patch.  The Id column is an Identity column (As Default)... meaning it should be an auto incremented int... but you can override it.  I presume because of lack of complete support, the Patch() forces me to send values for EVERY field... so I'm looking up the next value... and passing the incremental int for the Id.  The DB and other platforms touching it don't have a problem w/ this... apparently... but it is the 4x records I created w/ Patch that aren't appearing in the Collection and being dropped by AddColumns.

tewksbum
Helper I
Helper I

Ran a test... this was not an issue w/ the patch... I think it has something to do w/ the table size... this table... in dev mode has 2,000 rows... and it's only analyzing the first 500 I believe... I manipulated other newer records... and those don't appear either.

http://powerappsguide.com/blog/post/how-to-return-the-last-record-from-a-table

this post deals w/ something similar I think... having to go to last record specifically... 

tewksbum
Helper I
Helper I

Solved: Gallery not showing all items - Power Platform Community (microsoft.com)

 

Tried upping this to the max 2,000... got 1 additional row returned... 1,922 rows in source... this would definitely appear to be related to some type of fetching cap.

tewksbum
Helper I
Helper I

Power Apps Collection Returning only 500 records | Power Apps Exchange (powerappsug.com)

creative approach... create NUMEROUS collections of 2K upfront... then combine those into one large collection... wow - that seems like an INCREDBILE workaround?

timl
Super User
Super User

Hi @tewksbum 

I'd try calling AddColumns on the return value of Filter to see if that makes a difference -

AddColumns(
    Filter('xxx.BookingSlotParticipant'
            BookingSlotId = 1180
    ),
    "BSID",
    BookingSlotId
)

@timl thank you much for the response...

 

so... yes... that does solve the row returns issue... so that's a win!  I had started here and had walked back though... because it created another problem for me - maybe you can help with that!

 

I'm trying to use a lookup w/ the "AddColumns"...

 

AddColumns(
Filter(
'xxx.BookingSlotParticipant',
BookingSlotId = 1180
),
"BSID",
BookingSlotId,
"Trainer",
LookUp(col_Trainers,TrainerId='xxx.BookingSlotParticipant'[@CreatedById],Email)
)

 

normally this exact pattern works for my lookups... here however, I now get:

tewksbum_0-1632964731933.png

so like, now the relative row lookup isn't being referenced?

 

Again, thank you for getting us at least part of the way there!

 

 

Hi @tewksbum :

May be you could try:

AddColumns(
   Filter(
     'xxx.BookingSlotParticipant',
     BookingSlotId = 1180
   ) As AAAA,
   "BSID",
   BookingSlotId,
   "Trainer",
   LookUp(
      col_Trainers,
      TrainerId=AAAA[@CreatedById],
      Email)
)

Best Regards,

Bof

timl
Super User
Super User

Hi @tewksbum 

Can you clarify the data types of the columns in your col_Trainers/data source? Is TrainerId a numeric column, or are any of the columns of type table?

I'm guessing there's also a CreatedById column in both col_Trainers and 'xxx.BookingSlotParticipant'? If that's the case, an alternative would be to rename the CreatedById column in col_Trainers like so. By renaming the column, there won't be any clash in naming and you won't need to fully qualify the field names.

AddColumns(
   Filter(
      'xxx.BookingSlotParticipant',
      BookingSlotId = 1180
   ),
   "BSID",
   BookingSlotId,
   "Trainer",
   LookUp(RenameColumns(col_Trainers
             "CreatedById",
             "colCreatedById"
          ),
          TrainerId=CreatedById,Email
   )
)

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (4,152)