If Statement Based on Most Recent Record in Related Table
My query is with regards to a plant and equipment database (Assets). I have a related table for the movements of each item linked via internal reference number.
I am trying to write an if statement to show who the item is currently assigned to, based on the most recent movement and the date returned field being empty. The idea being that if there is no returned date then the item is still with that person, and if the most recent record has a returned date then the item should be in our warehouse and the result should then just be blank.
My current calculation is;
If(IsEmpty (Last (Movements::Returned Date)); Last (Movements::Allocated To); " ")
This is working but only if there is one related record. If there are previous movements then the result is coming back blank.
I have attached a screenshot of a record within the database to give an idea of set up. The movement portals are also filtered based upon whether or not the returned date is populated - don't know if there is a way of getting the formula to only look to the top portal?
I have tried what feels like a million different options but do not seem to be getting anywhere, so I would be eternally grateful for any help received!!
Thank you in advance and best regards.