• Replies 6 replies
  • Subscribers 337 subscribers
  • Views 72 views
  • Users 0 members are here
Related

Updating system table with constraint

Lisa Rudnitsky
$organization

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

Parents
  • $organization
    Hi Lisa --

    +1 for a trigger.  Treat the [fyear] column just as you would audit columns (last updated date, created by, etc.) -- just make sure to uncheck the editable option for the column in TR_REFERENCE_COLUMN.

    I'm curious -- why would a trigger be a last resort?  What is the concern?  That may help in finding an appropriate solution for your particular situation.

    One other potential option, though I've not used it and it is likely it won't be able to do what you're looking to do, would be to try validating the data in the [fyear] column using a PowerBuilder expression in the "Col Valid" column in TR_REFERENCE_COLUMN.  According to the documentation, this allows you to validate data and prevent the row from being saved if incorrect data is entered, though I'm not certain how extensible this feature is (whether you can perform a data lookup based on other data being entered).

    Good luck!

    DGomez 



    On Jun 20, 2017, at 10:14 PM, Lisa Rudnitsky <bounce-yelizavetarudnitsky8842@tessituranetwork.com> wrote:

    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!
Reply
  • $organization
    Hi Lisa --

    +1 for a trigger.  Treat the [fyear] column just as you would audit columns (last updated date, created by, etc.) -- just make sure to uncheck the editable option for the column in TR_REFERENCE_COLUMN.

    I'm curious -- why would a trigger be a last resort?  What is the concern?  That may help in finding an appropriate solution for your particular situation.

    One other potential option, though I've not used it and it is likely it won't be able to do what you're looking to do, would be to try validating the data in the [fyear] column using a PowerBuilder expression in the "Col Valid" column in TR_REFERENCE_COLUMN.  According to the documentation, this allows you to validate data and prevent the row from being saved if incorrect data is entered, though I'm not certain how extensible this feature is (whether you can perform a data lookup based on other data being entered).

    Good luck!

    DGomez 



    On Jun 20, 2017, at 10:14 PM, Lisa Rudnitsky <bounce-yelizavetarudnitsky8842@tessituranetwork.com> wrote:

    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!
Children
No Data