1 Reply Latest reply on Apr 28, 2014 5:58 AM by philmodjunk

    If Statement Based on Most Recent Record in Related Table

    ElizabethHillier

      Title

      If Statement Based on Most Recent Record in Related Table

      Post

           Good Afternoon,

           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.

      Plant_Screenshot.jpg

        • 1. Re: If Statement Based on Most Recent Record in Related Table
          philmodjunk

               The last function does not actually return the last related record. I returns a value from the last related record where the field is not empty.

               So

               IsEmpty (Last (Movements::Returned Date))

               will never be true.

               But you may be able to sort the records in the relationship such that the most recently added record is the first record instead of the last. And then your expression can be:

               If( IsEmpty ( Movements::Returned Date); Movements::Allocated To )