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

[DataCruncher]

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 January 2, 2021 by DataCruncher

[comment]

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.

 

 

[DataCruncher]

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.  

[comment]

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 January 2, 2021 by comment

[DataCruncher]

Yes, that would allow me to script that result, but ultimately, I need that result list as dropdown to be rendered on a website. So I need all of that to work in a Field calculation?

[comment]

5 minutes ago, DataCruncher said:

I need that result list as dropdown to be rendered on a website.

I am afraid I don't know what that means.