Need help with Query (LIST_LINE) (Brain Teaser)

Jaymer

I'd like to build a text field using LIST_LINE, so that in a User record (on a grid), I can see the Facilities he is associated with.

Facility has many Dept.
Dept. has many Users
Users can be in Many Dept.

User.FacilityString = LIST_LINE ( xxx ) // Note, if the LIST_LINE confuses you, just focus on the Query Part: FIND Facility WHERE EXISTS....

Result string should be: (example) "Acme Hospital, Express Care of Boston, Wilson Family Care"

Note: It would be easy to get JUST the Departments, but its less useful (example: "OR, Oncology, OR, Radiology")
because the Department Name doesn't specify the Facility. This string COULD BE "OR (Acme), Oncology (Acme), OR (Wilson), Radiology (Wilson)" but you see the Facility repeated. In this case, I'd only want "Acme, Boston" as the solution.

Find Facilities.PNG

Jaymer

This gets me a few Departments:
FIND Department WHERE Department IN LoggedInUsers.pm_Department ----> let this equal XXX

So I'm having trouble with the syntax of taking that whole thing (3 Departments in my case) and FINDing the 2 Facilities that have those Depts.

FIND Facility WHERE XXX IN Facility.pm_Department

Rennur

Try:

FIND Facility WHERE EXISTS Department WHERE (Department IN LoggedInUsers.pm_Department AND Department.Facility=Facility)

customaware

FIND Facility WHERE COUNT Department WHERE (Department IN User.pm_Departments AND Department.ps_Facility=Facility)<>0

tford

In addition to the FIND solutions above for your LIST_LINE challenge, I've had similar scenarios where I eventually added a pm_Facilities attribute to the User BO + added rules to automatically maintain this list of Facilities for each User when Department info changes for a user.

While LIST_LINE of Facility is the current need, I've found that later I needed to do more with that list for each User. Having pm_Facilities makes that much easier.

Jaymer

thx, solved, update to come (swamped)