cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KenArthur
Frequent Visitor

Finding Latest Date for filtered record

Hi,

 

I have two tables (SP Lists- One list is Resources which contains information about an individual Resource (Contractor).  It has a PK associated with the ResourceID. 

 

The other table is PayRates, also with a PK on ResourceID.  It contains the ResourceID, PayRate, BillRate, and effective date.  At any time the PayRate or BillRate fields may be empty, but will always have an effective date.  Example, we give someone a pay increase, but we don't change the billing rate to the client.

 

I want to be able, in a gallery text field, lookup the LATEST date in the EffectiveDate column and associate it with it's resource ID and deliver the payrate and billrate (in separate text fields, of course). 

The records in the gallery are filtered based on a selected item in another gallery  using  "Filter(Resources, ProjNum=Gallery1.Selected.L_ProjID.Text)".   

 

I am able to display the Resource's full name, which is just a ThisItem.ResFullName.  

 

Can someone help with the code to obtain this info?  This is complex and after 2 days, I finally need to ask for assistance.

 

Thank you

2 REPLIES 2
v-siky-msft
Community Support
Community Support

Hi @KenArthur ,

 

Is there any realtionship between Resource list and PayRates list?

If there is no relationship based on ResourceID field, and my understanding is correct, please try to put the following formulas to Text property of label in gallery.

 

First(SortByColumns(Search(PayRates,ResourceID=ThisItem.ResourceID),"EffectiveDate",Descending)).EffectiveDate

 

 Also the same as payrate and billrate:

 

First(SortByColumns(Search(PayRates,ResourceID=ThisItem.ResourceID),"EffectiveDate",Descending)).payrate
First(SortByColumns(Search(PayRates,ResourceID=ThisItem.ResourceID),"EffectiveDate",Descending)).billrate

 

Hope this helps, if not, please let me know.

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

Hi Sik - the relationship is the ResourceID that is common in both.  It is PK in Resources only.  Many-to-1 in the Payrates list.

I tried the code but received several errors.

SortByColumns states "Has some Invalid Arguments

Search states "Invalid number of arguments, received 2, expected 3 or more"

The specified column "EffectiveDate" does not exist (It does - in the Payrates table)  Spelling is correct

The term 'payrate' at the end has an error - "Name isn't valid.  The identifier isn't recognized"

 

Thanks for any help you can provide.

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (2,730)