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!
Filtering queries from a relationship multi-select
-
- Posts: 1460
- Joined: Tue Jan 24, 2017 5:51 am
- Location: 'Stralya
Re: Filtering queries from a relationship multi-select
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.
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.
-
- Posts: 2405
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Filtering queries from a relationship multi-select
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
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....
Mark
_________________
AwareIM 6.0, 8.7, 8.8, 9.0 , MariaDB, Windows 10, Ubuntu Linux. Theme: Default, Browser: Arc
Upcloud, Obsidian....
Re: Filtering queries from a relationship multi-select
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
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 (104.98 KiB) Viewed 7635 times
-
- entry window.png (87.63 KiB) Viewed 7635 times
Re: Filtering queries from a relationship multi-select
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
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
Re: Filtering queries from a relationship multi-select
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
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
Re: Filtering queries from a relationship multi-select
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)
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
Re: Filtering queries from a relationship multi-select
Great, all good now. Thanks guys