Filtering a FIND using attributes from related business objects

[ask180]

Hi,

I haven't been able to structure the WHERE conditions on a FIND like I would for an SQL select, and was wondering whether this was possible. What I want to do is filter the rows found for a business object using attribute values on related business objects.

Assume the following data structure. (where --> represents one-to-many abd <-- represents many-to-one)

A Task relates to one Position, and a Position may be multiple tasks (ie: Position --> Task).
A Position can be held by multiple Persons over time, and a Person may hold multiple Position over time. Being a many-to-many, I have resolved this with an Occupancy business object, because the relationship has attributes in its own right. (ie: Position --> Occupancy <-- Person).

A need a query that lists Task instances for the logged-in Person. For a Task to be listed, the logged-in Person would need to be the occupant (have an Occupancy row) for the Position that the Task relates to.

The way I structured the FIND action is as follows.

FIND Task WHERE Task.PositionID = Position.ID AND Position.ID = Occupancy.PositionID AND Occupancy.PersonID = LoggedInPerson

The Find should list 2 tasks, but comes back with no rows found.

Question 1: Can what I need to do be done in AwareIM ?
Question 2: If so, is the format I have written to join up the business objects correct ?

Any advice would be appreciated.

[PointsWell]

FIND Task WHERE Task.PositionID = Position.ID AND Position.ID = Occupancy.PositionID AND Occupancy.PersonID = LoggedInPerson

You are mixing Objects and Object Attributes

FIND Task WHERE Task.Position = Position WHERE (Position=Occupancy.Position AND Occupancy.Person=LoggedInPerson)

Lookup sub queries

[Jaymer]

“ LoggedInPerson” should be LoggedInPerson.ID
If it was to work, but this won’t work.
Occupancy has no reference here - in sql you would have joined to it, but that’s not being done here.

You’ll also need an EXISTS

SEARCH THE FORUM FOR “EXISTS” FOR EXAMPLES of how other people have built these.
Sorry, am away now, can’t do more

[ask180]

Thanks guys, I will research sub-queries.