If you have questions or if you want to share your opinion about Aware IM post your message on this forum
#54491 by Nick26
Mon Aug 10, 2020 7:20 am
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!
#54492 by PointsWell
Mon Aug 10, 2020 9:56 am
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.
#54493 by eagles9999
Mon Aug 10, 2020 10:06 am
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
#54494 by Nick26
Mon Aug 10, 2020 10:54 am
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 1258 times
entry window.png
entry window.png (87.63 KiB) Viewed 1258 times
#54495 by Nick26
Mon Aug 10, 2020 11:07 am
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
#54498 by Nick26
Mon Aug 10, 2020 1:15 pm
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
#54499 by tford
Mon Aug 10, 2020 4:06 pm
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)

Who is online

Users browsing this forum: No registered users and 41 guests