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.
Hi Lisa
There's an interesting question underneath this one - Why do you want to duplicate the season fyear value into your local table?
If it's for some process/reporting task, you can always just link back to TR_SEASON and pick up the fyear value from there - in fact that's a more reliable way to do it, because it will then always be the current fyear value, if TR_SEASON ever gets corrected or otherwise changed.
Or do you just want it to be visible in your local table, for confirmation or usability reasons?
I'd be interested to know....
Ken
Hello Mr. McSwain, the reason for this is that - this data needs to be passed (displayed) and the user doesn't have access to tr_season so we need to display/provide this information. There are other columns as well and this field is just to show.
I was hoping to allieviate having the user enter the data because there is no error checking. We need to make sure a season matches a fiscal year.
I wanted to do either put something into tr_reference_column or add a constraint like we do on create_dt -- when i build a table i tend to add this line:
ALTER TABLE [dbo].[LTR_PERF_DATES] ADD CONSTRAINT [DF_LTR_PERF_DATES_create_dt_1__13] DEFAULT (getdate()) FOR [create_dt]
There has to be aline of code to add a constraint or a foreign key or something onto the table to say -- hey when season is selected, update fyear.
any and all ideas are appreicated.
yes, trigger is the last possible alternative. I was hoping to not to use a trigger - wanted to see if i can put a constraint on the table or part of the tr_reference_column set up.
--
Regards,
Troy Nelson
yes, trigger is the last possible alternative. I was hoping to not to use a trigger - wanted to see if i can put a constraint on the table or part of the tr_reference_column set up. From: Troy Nelson <bounce-troynelson3148@tessituranetwork.com> Sent: 6/21/2017 12:37:45 AM A possibility is use of an insert trigger for the local system table. code it where that column will be entered with the rest of the saved row. -- Regards, Troy Nelson This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!
From: Troy Nelson <bounce-troynelson3148@tessituranetwork.com> Sent: 6/21/2017 12:37:45 AM