Scenario:
At my school, we have many different classes that end in a semester jury, sort of a final exam performance, which multiple faculty members evaluate. We have 11 different jury events, and different students and evaluators participate depending on grade level and major. Previously each event had a different paper form that had to be compiled by hand and shared with students and faculty. I’m building a database to automate all of this.
I have a database section filled with reusable data and datatypes. The lowest level is turning select and multiple select lists into individual tables with the various recurring options.
The middle level feeds into tables with prompts that are reused.
The issue:
I’m trying to create the big database table that will store responses to all of the various evaluation forms. I’m trying to do one big table of entries with views set up to hid columns (questions) that are not applicable to the specific jury.
I’d like to be able to have my grading scales, question list, etc., be able to feed into my DB, so if I want to make a change at some point, it will update everywhere.
However, I can’t seem to get the rows in the data tables to feed into columns in the big DB. I keep trying different formulas, but the data doesn’t seem to want to flow in this direction. I also keep running into “not a valid row reference” issue anytime the column I’m pulling from is a multiselect list.
Can you make your document public? We cannot access it.
One off the cuff suggestion: during design, build from your complete table outwards. Then you already have everything in a single table. Then from that table, select columns to make into lookup tables.
E.g. student column, evaluator column, subject column, marks column. Ah, looks like you have two “person” columns. Create a person table, with a field to distinguish between students, and faculty. Use a filtered lookup column.
Need to store attributional data about the subject? Turn into select list, then create lookup table, create additional columns as needed.
That makes sense, I just didn’t do it that way, partly because the “one big table” didn’t really make sense to me as a schema. So many of the components only relate to one piece of the whole, so there’d be so much empty, and the table would be truly massive.
The issue always is that there’s more things to expand with every project, class, production, etc, and only a portion of people are involved., and always in different ways.
The reason I set up different people DB is because they will have very different roles. They’ll actually be interacting with different docs via cross-doc. And I will have to update student statuses every term, while other people will remain more constant.
I guess I could do that with filters and views though. I’ve just experienced an issue with filters and buttons that push buttons. For instance, if I want to send an email to a subgroup of people by triggering one button, it gets sent to records that I have filtered out of view with an interactive filter. Just trying to avoid that issue.
