How to Show Conditional Data in a Related Join Table

Question

Hello all,I have an app with three tables:Faculty Records - Contains the various credentialing programs available for physicians.Physician Info - Stores static information like name, title, and person number.Physician Credentials - A join table that shows the relationship between physicians, credentials, and the dates those credentials were approved.The relationships between these tables are as follows:Here’s the issue I’m facing:One specific credentialing program from the Faculty Records table includes advanced privileges (checkboxes) unique to individual physicians. These privileges:Are only relevant to this one credentialing program.Differ from person to person.Ideally, should appear in the Physician Credentials table alongside the credentialing program, approval dates, and physician name.While I could add the advanced privileges to the Physician Info table, how could I then see them in the Physician Credentials table? Would queries be a solution for this? (I’ve never used those before.)What’s the best way to structure this?Thanks in advance for your help!Meag

MarkShnierYou · Answer

I think I would keep this out of the Join table and instead have a Multi select field on the Physician table where you can select their Advanced Privileges. Of course you can position that multi-select field near the embedded table of Physician Credentials.

But to keep nth form tidy and not clutter up the Phsician form, I would go to the relationship between Physicians and Physician Credentials and make a summary checkbox field for if any of the Physician Credentials is the type that gets these Advanced Privileges.

Then have a form rule to show that multi-select field if the Physician has that specific Credential.

Sign up

Login to the community