Filter based on date colum

(1) Share

Report

Posted on by Jan Henrik

In my Dataverse table, called Footballer I have 4 column:
Lookup Column called present club pointing to Club table
Lookup Column called last club pointing to Club table

Date only column called transfer date

Column called name

In my canvas app I have a gallery. I want to filter the items in the gallery so it shows the club currently beeing watched, if the footballer is currently playing there.

I have tried with code similar to this:

Filter(

Footballer;

Club.Club=ThisRecord.'Last club'.Club &&'Transfer date' > Today() )

This gives the wanted outcome only if I choose any of the parts in the filter.

This works:

Filter(

Footballer;

Club.Club=ThisRecord.'Last club'.Club)

And this works:
Filter( Footballer;

'Transfer date' > Today() )

Edit: I see that if I swap: Club.Club=ThisRecord.'Last club'.Club) with another criteria like Name="Random" &&'Transfer date' > Today() it gives wanted result. So the question is how can I tweak the code so it also works for lookup column?

Categories:

Connecting to Data

All responses (4)

Answers (0)

venturemavenwil 600 Super User 2025 Season 1 on at

Like (1)

Report
Filter based on date colum

If or Switch statements do work in a gallery, You can simply use a condition like

CountRows(Filter(Footballer, Club.Club = 'Last club'.Club && 'Transfer date' > Today())) > 0

Though can you maybe show us the first five rows of the Footballer table? I don't see why the original code wouldn't work unless the sub parameter is not propagating, and you are not able to access them, but then it's just a matter of making sure that the sub-properties propagate.

Cheers,

William
Jan Henrik 4 on at

Like (1)

Report

Filter based on date colum

Thank you both- I thought I had to use ThisRecord but after testing I see that was not the case

Also this code gives the wanted result; with or without ThisRecord
With (

{ FilterByClubFirst:  Filter(Footballer; Club.Club=ThisRecord.'Last club'.Club) },

// Now filter by Date

Filter(FilterByClubFirst, 'Transfer date' > Today())
);

How can I tweak the code so that it will be something like:

With (

{ FilterByClubFirst:  Filter(Footballer; Club.Club=ThisRecord.'Last club'.Club) },

// Now filter by Date

Filter(FilterByClubFirst, 'Transfer date' > Today())
);
OR
{ FilterByClub:  Filter(Footballer; Club.Club='Current club'.Club) },

// Now filter by Date

Filter(FilterByClub, 'Transfer date' <= Today())
);

The result should then be that if the transfer date has not passedthen it will show the last club, or if the transfer date has passed then it will show the current club

Is it possible also to tweak the code so that it is delegable-friendly?

Thank you very much so far for answering
Suggested answer

venturemavenwil 600 Super User 2025 Season 1 on at

Like (2)

Report

Filter based on date colum

Could you clarify what you are referencing with ThisRecord?

If you are trying to filter out Footballer where the current club is equal to the last club and they have a transfer date greater as today, then you don't need 'ThisRecord'

If, on the other hand, you are attempting to filter record selected elsewhere (and ThisRecord is just representative of the selected object and not used literally) then check first that the property 'Last Club' is cascading. One property of Dataverse is that if a record is too long, the properties of a lookup column will not populate. You can test this by either using the IsEmpty() function or by simply creating a test collection based on the property 'Last Club'. If the property turns out to return empty, then you can force the property to cascade by using the ShowColumns() command and include only the columns you need.

If all of the above assumptions are false, could you clarify what ThisRecord refers to, and include your full expression so we have a bit more info to troubleshoot?
Suggested answer

Michael E. Gernaey 41,317 Super User 2025 Season 1 on at

Like (2)

Report

Filter based on date colum

Hi,

Can you try this please

With (

{ FilterByClubFirst: Filter(Footballer; Club.Club=ThisRecord.'Last club'.Club) },

// Now filter by Date

Filter(FilterByClubFirst, 'Transfer date' > Today())
);

Cheers,