Jump to content

Multi-dimensional query - SQL hard-code or related tables?


Recommended Posts

Good morning - so this is something I have struggled with for some time and it seems easy enough but I can't seem to figure it out. 

For every simple relationship in my database, I can very easily show related records. It's literally one direct related record away and can be processed easily in formulas, lists, scripted - it just works. 

However, I struggle when more than one relationship is involved. 

 

As an example, assume I want my solution to manage a team of physicians in different insurance networks. 

So my basic tables are: 

PHYSICIANS

INSURANCE

then I have other tables, including: 

SPECIALTY (of the physician)

CREDENTIALS (which contains records for which physician is credentialed with which insurance)

 

So all the data is there to give me the following: 

For every insurance, a user should go to a dropdown of all the medical specialties and pick any one of them. 

The expected result should then be a list of all physicians matching the selected specialty who are credentialed with that insurance.

 

I believe the reason I am struggling is the information is spread across several tables - what I believed to be a simple related query over two tables simply never worked for me. 

 

What is the best approach to achieve this - should I just try to run nested ExecuteSQL queries? Or am I missing a very obvious related calculation here? 

 

Thank you and Happy New Year!

 

 

Edited by DataCruncher
Link to post
Share on other sites
24 minutes ago, DataCruncher said:

For every insurance, a user should go to a dropdown of all the medical specialties and pick any one of them. 

The expected result should then be a list of all physicians matching the selected specialty who are credentialed with that insurance.

I am not sure I understand this requirement correctly. You want the user to be able to pick a different specialty for each insurance? And then show the combined results in a single list? That would be problematic in terms of where to put the user's choices: you cannot use a global field to select a different value for each record; OTOH, if you use a field of the INSURANCE table, then you cannot have each user make their own selections.

 

 

Link to post
Share on other sites

I'm sorry I didn't explain this properly. 

 

The dropdown field for specialty would be from within the context of a specific insurance company. 

In other words, you could start from no context and make it two dropdown boxes: 

- Select any one medical specialty

- Select any one insurance company

Result list should then show all physicians matching specialty and insurance company. 

 

Any doctor may have one or more medical specialties and any doctor may be credentialed with one or more insurance companies.  

Link to post
Share on other sites
17 minutes ago, DataCruncher said:

- Select any one medical specialty

- Select any one insurance company

Result list should then show all physicians matching specialty and insurance company. 

Well, assuming you have the following structure:

INSURANCE -< CREDENTIALS >- PHYSICIANS -< SPECIALTIES

it should be a simple matter of going to a layout of the PHYSICIANS table, entering Find mode, entering the wanted insurance company into a field of the INSURANCE table, entering the wanted specialty into a field of the SPECIALTIES table, and performing a find.

Alternatively, if you are already showing the record of the wanted insurance company, you could enter the wanted specialty into a global field and use it to filter a portal to PHYSICIANS so that it only shows related physicians having the wanted specialty.

 

Edited by comment
Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.