AddColumns causes DataTable Filter to lose rows?!?!

[tewksbum]

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

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

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

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

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

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

[tewksbum]

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...

Filter(
col_BookingSlotParticipant,
BookingSlotId = 1180
)

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

[tewksbum]

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]

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]

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]

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 · Accepted solution]

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
)

[tewksbum]

@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:

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

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

[v-bofeng-msft · Accepted solution]

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]

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
   )
)