My database is set up to help us navigate sessions offered by our team of experts. We navigate these sessions by searching through a list of subjects, similar to how you’d find books in a library on a series of subjects as opposed to knowing the author or book titles. In fact, thinking of our database like a library database is very helpful in understanding the problem I’m trying to solve.
To understand the problem I’m encountering, it’s probably helpful to understand how our data tables are set up. We categorize all our sessions through a hierarchy of: Topic/Application, Level, Area, and then Subject. If this were a library, and you wanted to find a book on Medieval Archeology, you could think of our classification system like this: Medieval Archeology would be the subject, Archeology would be the Area, Anthropology would be the Level, and Social Studies would be the Topic/Application.
Because the Subject is the lowest part, the great grandchild, in the hierarchy, we have set up our connections in this way. However, it’s important to note that we mostly operate at the Area level and the Subjects are simply there to help narrow down sessions during searches.
In practice, this looks like this: In the data table for our sessions, each session’s subjects are filled in, and rules are set in place to allow the higher levels of subject categorization to be automatically filled in
Each session also has a field connected to the Experts data table, which allows us to choose the correct expert for each session. In the library example, you could think of this as the Author field, and perhaps a data table that has all the authors in the library with additional information about them.
But here is where I am having trouble. I need each expert’s session titles to show up in a field in the Expert data table. I also need any Areas those sessions are connected to, to show up as a list in a field in the Expert data table as well. So, in the library example, every time a book was indicated as being written by the author, that book title would automatically show up in a list of books by that author in the author database. And every Area those books were categorized as would show up as that author’s area’s of expertise.
Here’s an example to demonstrate what I’m trying to achieve:
1.) We have two sessions “Understanding SDGs” and “Artificial Intelligence.” In the Sessions data table, the appropriate subjects have been filled in for both, and because of the rules and connections, the appropriate areas, levels and topic/application have been automatically filled in.
2.) Both of those sessions are taught by the expert Ada Lovelace, so they have been chosen from the list of experts in the field “Experts” which connects to the “Expert Name” field of the Experts data table.
3.) PROBLEM: In the Experts data table, I need “Understanding SDGs” and “Artificial Intelligence” to show up in the “Sessions Offered” fields. I also need all the Areas that were automatically filled in under those sessions to populate in the “Areas of Expertise” field.
I am including a picture of one of the rules of how I am attempting to get that data in the Experts data table automatically populated, but it’s not working, so I am doing something wrong, but I’m not sure what. Any help is appreciated!! Thank you!
