Hello, new Coda user and new-ish to database relationships.
I have a large table of information that I want to import from another IT system. It has about 300 rows, and 60 columns. It contains information on individuals, things like Name, DoB, Grade Point Average, scores from other types of tests, dates, etc.
I would like to import this data and have it in its own master table, so that I can easily overwrite it with new imports. I don’t ever want to change this information in Coda. The information should always be changed in the other IT system, then exported and re-imported into Coda again.
My question is how best to relate this master table to all of the other things I want to do.
For instance, I want to have a way to ‘Rack and Stack’ the individuals in the master table. I want to have all of the info in the master table available, but add columns like a 1 to N list of the best performers in the group. This would also have a lot of sorting and contextual coloring on it. If GPA is above 3.0, make it green, etc.
I also want to have status trackers for people in the master table. For instance, as they progress toward medical qualification, I want to track if they’ve started the process, if they have submitted information, if they’re waiting on paperwork, and then add comments as well.
But I want all of that information to be in a separate table, so that it never interferes with the master table. I don’t want to add columns to the master table.
And as the master table changes, with people being added or removed from the list, I need the other information in the other trackers to fall away, or be blank, so that I only see information for those in my master table.
I’ve tried using custom views of the master table, but when I add a new column, it adds it to the master table as well, which I don’t want, as that makes it too hard to re-import the data from the other IT system.
So I need master table to populate the other tables somehow, but only enough to link in all the data in the master table as needed, and then allow for status tracking, comments, rankings, etc.
Any help would be appreciated!