Filtering queries from a relationship multi-select

Nick26

Hi all,

First post on the forum- so far really enjoying Aware IM. I will probably have a few questions over the next few weeks… First one:

I am running a query for a BO. In that BO I have a relationship field (multiple allowed) to select staff members allocated. I would like to filter the query by a staff member. e.g. see all objects that have “Bob” & “Fred” allocated.

As far as I know, this would be easy if it was a text field, as the inbuilt query filtering would work? But this doesn't really fit my need.

This is what I have tried, not really knowing if its the best way to do it:

Set up form in the LoggedInRegular User and made a swap select to choose staff.
Made a process to find that form for the selected user
(FIND RegularUser WHERE RegularUser=LoggedInRegularUser
View form)
Put in the query BO.Staff=LoggedInRegularUser.StaffFilter
Select which staff I want to filter in the form

When I run the query, it returns nothing. I assume its something to do with the comparison between two relationship fields, but I'm not sure!

PointsWell

Welcome to the forum.

Could you describe the BOs that you have and their relationships to each other? Also if you describe the outcome you are trying to achieve as often there are multiple routes to the solution.

For example if you have a 1-m relationship of
Companies have many Offices it can be easier to go from the many side of the relationship ie FIND Offices Where Offices.obCompany = xyz (where ob=owned by relationship.

Also you don’t need to FIND the LoggedInRegularUsed as this BO is always in context so you can just refer to LoggedInRegularUser directly.

customaware

Without knowing your exact BO structure and relationships and just going by your description this is what I would try the following for starters....

FIND MyBO WHERE COUNT Staff WHERE (Staff IN MyBO.pm_StaffMembers AND Staff IN LoggedInRegularUser.pm_SelectedReportStaff)>0

Nick26

Thanks,

Basically, the main BO (filtering in the query) is an Appointment object. Within that object, there is a peer relationship to a ‘Staff’ object.

The idea is a user creates the appointment object, then assigns staff (could be multiple) to that appointment.

Some of the appointment grids can be quite long, so Ideally a user can go in and filter the grid by one or more staff. e.g Find what appointments John & Bob are assigned to.

I have attached to get an idea of the setup. Visual reference may help.

Note that in the “personnel” column, I have used a LIST_Line to display staff names

entry window.png

Nick26

Hi Mark,

I quickly put in that query and it worked- Thanks very much.

The only issue I have now is that if the LoggedInRegular.Staff_filter is empty (don't want to filter any records, I just want to see them all), it doesn't return any results.

I know logically that makes sense, but its kind of a pain to put every staff member to view everything!

Cheers
Nick

Nick26

I have put the following query. The 'IS UNDEFINED' helps if there are no filter items. I have Another peer relationship I want to filter (possibly at the same time). I found an old post in the forum that had a similar filter, but I can only seem to filter one field at a time, otherwise, it cancels out the whole query.

EG I can only filter the staff OR the group, not combined

FIND ProductionSchedules WHERE COUNT Staff WHERE (Staff IN ProductionSchedules.Staff AND Staff IN LoggedInRegularUser.Staff_Prodcalfilter)>0 OR LoggedInRegularUser.Staff_filter IS UNDEFINED AND COUNT z_groups WHERE (z_groups IN ProductionSchedules.Prodgroup AND z_groups IN LoggedInRegularUser.Prodgroupfilter)>0 OR LoggedInRegularUser.Prodgroupfilter IS UNDEFINED

Ref: Tford - FIND Statement Syntax for Complex FIND>

tford

Just need to add in a couple sets of parenthesis and I think you'll be all set. I think I put them in the right places here:

FIND ProductionSchedules WHERE (COUNT Staff WHERE (Staff IN ProductionSchedules.Staff AND Staff IN LoggedInRegularUser.Staff_Prodcalfilter)>0 OR LoggedInRegularUser.Staff_filter IS UNDEFINED) AND (COUNT z_groups WHERE (z_groups IN ProductionSchedules.Prodgroup AND z_groups IN LoggedInRegularUser.Prodgroupfilter)>0 OR LoggedInRegularUser.Prodgroupfilter IS UNDEFINED)

Nick26

Great, all good now. Thanks guys