Custom tab for elevated events

Matt,

I think I tried a very similar thing a few months back and opened a support ticket for help.  I have included the results of that ticket below - you'll want to read from bottom to top.  Hope this helps.

Dale

Entered on 10/21/2009 at 15:31:13 by Dale Aucoin:
Thanks for your help Brian, I was thinking I would have to do something like that with a local table
but wasn't so excited about the prospect. I'll check out the solution you suggest.
Dale

Entered on 10/20/2009 at 12:46:58 by Brian Pedaci:
Yes, I think you're right. I tried creating my own custom screen and had the same
problem, even using a view to select from and update.

You may wish to take a look at solution #271 in the Reference Materials project of
TASK Online for an example of a custom screen (plus report) for maintaining elevated
event data. It looks like the creator of that module used local tables to hold the
guest data instead, with a trigger on the custom table to update the TX_EVENT_GUEST
table.

Entered on 10/19/2009 at 16:02:47 by Dale Aucoin:
Brian,
Thanks for your help on this. I made the changes you suggested but I'm still not able to edit
through Tessitura even though we are now on version 9. I'm thinking my issue here might have
something to do witih tx_event_guest which doesn't have a primary key. Am I correct on that?
Dale

Entered on 10/19/2009 at 13:55:13 by Brian Pedaci:
I am sorry it's taken me so long to get back to this. After much testing, I finally
found out that there was a bug in version 8.0 that made the new custom tabs in
Campaigns and Appeals not editable under any circumstances. This was addressed in
the 9.0 general release. Are you indeed still on version 8.0.0.3 as the information
on the ticket says?

Once you do the upgrade, there may be further issues, looking at the query.

There must be a column in every custom data object called id_key, defined as a long
integer. This can be aliased to the tx_event_extract.evex_no column in the query,
since that is the primary key on the table you intend to update. Also, since this
appears to be a custom tab from the Campaign screen, you need a column called inv_no
(also long) that represents the campaign_no.

So, your query might appear like this:

select id_key = a.evex_no, inv_no = a.campaign_no, a.customer_no, c.fname, c.lname,
b.description as 'status',
a.recd_amt, d.guest_name, d.guest_cust_no, e.description as 'cust_level', d.table_no,
d.seat_no,
d.perf_seat....

Make sure the edited column names appear correctly in the column specification
window, and in the datawindow design itself. Also make sure that all columns except
those coming from TX_EVENT_EXTRACT are marked with the NOEDIT tag, as updateable
columns can only come from the table listed in the 'Update Table' column of
TR_CUSTOM_TAB.

Entered on 09/17/2009 at 16:26:11 by Dale Aucoin:
Ok, I think I'm trying to do something illegal and just want to check to make sure so I don't do it.

I wanted to create a custom tab that would allow changes to Event seating (tx_event_guest) for
elevated events in a custom tab. The tab I created shows me the correct information but doesn't
allow me to edit. Is this because of a foreign key violation?

Here's my script

select a.evex_no, a.campaign_no, a.customer_no, c.fname, c.lname, b.description as 'status',
a.recd_amt, d.guest_name, d.guest_cust_no, e.description as 'cust_level', d.table_no, d.seat_no,
d.perf_seat

from tx_event_extract a

join tr_invitation_status b on a.inv_status = b.id
join t_customer c on a.customer_no = c.customer_no
join tx_event_guest d on a.evex_no = d.evex_no
join tr_event_level e on d.level = e.id

where a.campaign_no = :campaign_no
and a.inv_status not in (6,1,8)

Or is there another reason I am unable to edit?

Thanks!
Dale

Matt,

I think I tried a very similar thing a few months back and opened a support ticket for help.  I have included the results of that ticket below - you'll want to read from bottom to top.  Hope this helps.

Dale

Entered on 10/21/2009 at 15:31:13 by Dale Aucoin:
Thanks for your help Brian, I was thinking I would have to do something like that with a local table
but wasn't so excited about the prospect. I'll check out the solution you suggest.
Dale

Entered on 10/20/2009 at 12:46:58 by Brian Pedaci:
Yes, I think you're right. I tried creating my own custom screen and had the same
problem, even using a view to select from and update.

You may wish to take a look at solution #271 in the Reference Materials project of
TASK Online for an example of a custom screen (plus report) for maintaining elevated
event data. It looks like the creator of that module used local tables to hold the
guest data instead, with a trigger on the custom table to update the TX_EVENT_GUEST
table.

Entered on 10/19/2009 at 16:02:47 by Dale Aucoin:
Brian,
Thanks for your help on this. I made the changes you suggested but I'm still not able to edit
through Tessitura even though we are now on version 9. I'm thinking my issue here might have
something to do witih tx_event_guest which doesn't have a primary key. Am I correct on that?
Dale

Entered on 10/19/2009 at 13:55:13 by Brian Pedaci:
I am sorry it's taken me so long to get back to this. After much testing, I finally
found out that there was a bug in version 8.0 that made the new custom tabs in
Campaigns and Appeals not editable under any circumstances. This was addressed in
the 9.0 general release. Are you indeed still on version 8.0.0.3 as the information
on the ticket says?

Once you do the upgrade, there may be further issues, looking at the query.

There must be a column in every custom data object called id_key, defined as a long
integer. This can be aliased to the tx_event_extract.evex_no column in the query,
since that is the primary key on the table you intend to update. Also, since this
appears to be a custom tab from the Campaign screen, you need a column called inv_no
(also long) that represents the campaign_no.

So, your query might appear like this:

select id_key = a.evex_no, inv_no = a.campaign_no, a.customer_no, c.fname, c.lname,
b.description as 'status',
a.recd_amt, d.guest_name, d.guest_cust_no, e.description as 'cust_level', d.table_no,
d.seat_no,
d.perf_seat....

Make sure the edited column names appear correctly in the column specification
window, and in the datawindow design itself. Also make sure that all columns except
those coming from TX_EVENT_EXTRACT are marked with the NOEDIT tag, as updateable
columns can only come from the table listed in the 'Update Table' column of
TR_CUSTOM_TAB.

Entered on 09/17/2009 at 16:26:11 by Dale Aucoin:
Ok, I think I'm trying to do something illegal and just want to check to make sure so I don't do it.

I wanted to create a custom tab that would allow changes to Event seating (tx_event_guest) for
elevated events in a custom tab. The tab I created shows me the correct information but doesn't
allow me to edit. Is this because of a foreign key violation?

Here's my script

select a.evex_no, a.campaign_no, a.customer_no, c.fname, c.lname, b.description as 'status',
a.recd_amt, d.guest_name, d.guest_cust_no, e.description as 'cust_level', d.table_no, d.seat_no,
d.perf_seat

from tx_event_extract a

join tr_invitation_status b on a.inv_status = b.id
join t_customer c on a.customer_no = c.customer_no
join tx_event_guest d on a.evex_no = d.evex_no
join tr_event_level e on d.level = e.id

where a.campaign_no = :campaign_no
and a.inv_status not in (6,1,8)

Or is there another reason I am unable to edit?

Thanks!
Dale