ANY(Select... always returning the first record it...

1minManager · 08-07-2020 09:39 AM

I have an App with 3 Tables (Vehicles, Staff and Allocated) which track who is allocated to a particular vehicle.

So from the Vehicles table, I can use this formula
ANY(Select([Related Allocateds][Allocated To],ISBLANK([Returned to])))
to get the ID record from the Allocated table of the last record the vehicle was allocated but not returned. This works fine. When I show it in a table I can see lots of different ID numbers against each vehicle. As expected.

But when I add this encapsulation to pull in the users name from the Staff table
ANY(Select(Staff[Name],[StaffID]=
ANY(Select([Related Allocateds][Allocated To],ISBLANK([Returned to])))
))
it always pulls in the first record if finds in Staff[Name]. It’s almost like its ignoring the equals sign

Any ideas?

1minManager

Steve

Try this instead:

ANY(
  Select(
    Staff[Name],
    ANY(
      Select(
        [Related Allocateds][Allocated To],
        ISBLANK([Returned to])
      )
    )
    = [StaffID]
  )
)

ANY(Select... always returning the first record it finds