Maybe someone is having better luck with this then me --
How do I write out the syntax (and where) for a system table i'm creating. In the system table a user will pick from a dropdown in tr_reference_column a season linked to tr_season. Once that is selected and the save button is clicked I want the second column to populate with a fiscal year also from tr_season.
We can do this with constraints for created date, by, location etc. How do we do this with other fields? Is there syntax I can add/write in tr_reference_column for the fyear field? If there isn't what kind of constraint (and what syntax would i use) to build the table.
Ideally I'd like to avoid triggers - yes, we can do this, but the prference is to code it into set up somehow or into a constraint. Also we'd like to avoid giving the user the option to pick the fyear from the drop down because the fyear has to link to the season and user can pick it incorrectly.
Please advise. Thank you.
-Lisa
Reference table metadata is not linked to the actual table schema in any way, to the best of my knowledge. They must be compatible with each other, of course, in order to make edits in the System Table module, but any constraints you create must be defined separately.
Unfortunately, it is not possible to create reftable metadata in TR_REFERENCE_COLUMN that references a value in a different column, like you can do with report parameters. My solution to problems like this has been to actually create a little report/utility that just adds rows to the table, and then set up the system table to be impossible to add rows manually. That way I can use utility parameters that reference one another to provide the desired user experience.
I recently shared some database code in the TN_WebShare Bitbucket that follows this pattern: https://bitbucket.org/TN_WebShare/report-server-tools/src Check out the code in the Conditional Report Scheduler folder, for the CRS_SCHEDULE table and the ADD_CONDITIONAL_SCHEDULE procedure.