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&nbsp;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

markshnier__you · 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.

meaganmcolin · Answer

The challenge with adding Advanced Privileges to the Physician Info table is that it primarily contains biographical information, making it an unsuitable place for them. Advanced Privileges are more closely aligned with credentialing but are unique to the individual and the credentialing program, which is why I’m feeling a bit stuck.

markshnier__you · Answer

You said&nbsp; that these Advanced Privilege "Differ from person to person."&nbsp; I interpreted that to mean that the actual possible values vary from person to person so having say 10 checkboxes on a Join table record would not make sense.&nbsp; How about if you put that same suggested multi-select field on the join table record and show it for only the relevant Credential.If you go that route, I find that he appearance of a multi select field is pretty horrible in View mode or on a report., so I make a formula text field with the formula below to show the selections as a vertical list.SearchAndReplace(ToText([my multi select field]), " ; ", "
")&nbsp;So you would add this field as a column on the embedded report.

Forum Discussion

How to Show Conditional Data in a Related Join Table