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.
Need help with Query (LIST_LINE) (Brain Teaser)
Need help with Query (LIST_LINE) (Brain Teaser)
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Need help with Query (LIST_LINE) (Brain Teaser)
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
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
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL
Re: Need help with Query (LIST_LINE) (Brain Teaser)
Try:
Code: Select all
FIND Facility WHERE EXISTS Department WHERE (Department IN LoggedInUsers.pm_Department AND Department.Facility=Facility)
Last edited by Rennur on Tue Nov 12, 2019 3:35 am, edited 1 time in total.
-
- Posts: 2399
- Joined: Mon Jul 02, 2012 12:24 am
- Location: Ulaanbaatar, Mongolia
Re: Need help with Query (LIST_LINE) (Brain Teaser)
FIND Facility WHERE COUNT Department WHERE (Department IN User.pm_Departments AND Department.ps_Facility=Facility)<>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: Need help with Query (LIST_LINE) (Brain Teaser)
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.
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.
Tom - V8.8 build 3137 - MySql / PostGres
Re: Need help with Query (LIST_LINE) (Brain Teaser)
thx, solved, update to come (swamped)
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.
Jaymer
Aware Programming & Consulting - Tampa FL
Jaymer
Aware Programming & Consulting - Tampa FL