Query regarding External SQL Source shadow tables and filtered portals
I have a database which I initially set up on my local Filemaker Pro Advanced copy (Mac OSX). I imported a whole lot of student records from our school via a text file, and set up the database around that.
I have a number of different portals that show information about those students activities, and the activities are generally filtered by the user selecting a year in a global field.
The database is now up and running on Filemaker Server, and I have access to the student record information which is held in a SQL database via an ODBC connection. Initially I was going to have a view into the student records (which my users don't need to edit, just view) via ESS and the shadow tables it creates in my relationship graph.
However, this seems to mean that if I base a layout on that shadow table, I can't then add a global field to that table and use it as the basis for a relationship to filter out the records I want from other tables.
As I'm writing this I'm just starting to realise that surely I can put the global field in another table (eg Resources) and use that as a secondary relationship to filter out the records?
At the moment, instead of viewing the data via ESS, I am updating the student records each night via an update matching records script.
This may be the best way to go anyway, but does anyone have any experience with this particular issue that they can share?
Does working with the shadow table create limitations/complications?
In this scenario, where the student records themselves won't be edited by my users, but there just for viewing, can anyone see advantages/disadvantages in importing/updating records via a script?