Filtering queries from a relationship multi-select

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
Nick26
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Filtering queries from a relationship multi-select

Post by 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:

1. Set up form in the LoggedInRegular User and made a swap select to choose staff.
2. Made a process to find that form for the selected user
(FIND RegularUser WHERE RegularUser=LoggedInRegularUser
View form)
3. Put in the query BO.Staff=LoggedInRegularUser.StaffFilter
4. 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
Posts: 1457
Joined: Tue Jan 24, 2017 5:51 am
Location: 'Stralya

Re: Filtering queries from a relationship multi-select

Post by 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
Posts: 2391
Joined: Mon Jul 02, 2012 12:24 am
Location: Ulaanbaatar, Mongolia

Re: Filtering queries from a relationship multi-select

Post by 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
Cheers,
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Image
Nick26
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Re: Filtering queries from a relationship multi-select

Post by 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
Attachments
Grid.png
Grid.png (104.98 KiB) Viewed 7346 times
entry window.png
entry window.png (87.63 KiB) Viewed 7346 times
Nick26
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Re: Filtering queries from a relationship multi-select

Post by 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
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Re: Filtering queries from a relationship multi-select

Post by 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 - https://www.awareim.com/forum/viewtopic.php?f=1&t=9259
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Filtering queries from a relationship multi-select

Post by 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)
Tom - V8.8 build 3137 - MySql / PostGres
Nick26
Posts: 27
Joined: Fri Aug 07, 2020 6:59 am
Location: Melbourne, Australia

Re: Filtering queries from a relationship multi-select

Post by Nick26 »

Great, all good now. Thanks guys
Post Reply