Finding Latest Date for filtered record

[KenArthur]

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

[v-siky-msft]

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.

[KenArthur]

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.