• Replies 7 replies
  • Subscribers 337 subscribers
  • Views 547 views
  • Users 0 members are here
Related

Creating a custom list criteria

Matthew Hodge
$organization

Hi everyone!

Hope we're all looking forward to the New Year. Bit of a technical one, so bear with me:

I'm trying to modify the criteria on Tessitura so that we can pull a list of people with a particular subscription package code. The reason I'm modifying this criteria is that by default, the current subscription package code only pulls people from the ticket history table, but some of these people haven't been seated yet, so we can't pull their details when we pull using that standard criteria.

So what I'm doing is trying to set up criteria that will pull straight from the T_LINEITEM table.

So I've done the following steps:

1. I created my own view of T_LINEITEM called LVS_LINEITEM. The view is identical to T_LINEITEM, except that I added an extra column with the customer_no column joined from the T_ORDER table.

2. I created my own view of T_PKG called LVR_ORD_PKG_CODE. This is just a straight select of T_PKG.

3. In T_KEYWORD, I added an extra line for this new criteria. The lookup tables and columns are are:

Detail Tbl - LVS_LINEITEM

Detail Col - !.pkg_code (this contains an integer which represents the specific package in the LVR_ORD_PKG_CODE view)

Ref Tbl - LVR_ORD_PKG_CODE

Ref Idcol - pkg_code

Ref Descol - description

 

Where it's falling over is that when you to go to List Builder, and choose the Criteria, the drop-down populates correctly (ie with a list of the description column from the LVR_ORD_PKG_CODE), but when you select the options, instead of displaying the pkg_code number in parentheses like you would expect, this text displays:

('if(inactive="Y", RGB(100,100,100),RGB(0,0,0))')

 

What am I doing wrong?

Parents
  • $organization

    Hi Matthew,

    I don't see "pkg_code" in t_lineitem.
    Try changing detial col to
    !.pkg_no
    and ref idcol to
    pkg_no
    and see if that works.

  • $organization in reply to Ryan Rowell

    Hi,

    Thanks for replying, Ryan! I thought I might have fixed it there for a minute, but I realised that I had sadly just put a mistake in the post I had up there.

     

    You're quite right, it should be pkg_no, and when I went and checked it, that's what I'd put in:

    So I do have a detail col of !.pkg_no and ref idcol of pkg_no, but it's still not working.

    I've had a play around with it, and it seems to be something to do with the fact that the columns in question are integers. For instance, there is a pkg_code column in T_PKG which is a varchar column and if I set that as the refid column, it picks up the criteria fine. (Unfortunately, I can't use this to search the T_LINEITEM table, because the foreign key is the pkg_no column.)

    And I tried a few different columns just to check. Whenever the column was an int column as ref idcolumn, Tessitura would throw up that error code I listed above. So there must be something in the underlying Tessitura code behind the list criteria function that registers something as being wrong with that column.

    I might have to track down one of the Tessi programmers to see if I can find out what's going on.

  • $organization in reply to Matthew Hodge

    I think your data type should be number, is that what you are using?

Reply Children
  • $organization in reply to Ryan Rowell

    Well, the column that's being referenced in the detail tbl (and I'll just ignore views for the moment and talk about the raw table) is pkg_no in the T_LINEITEM table. And pkg_no in that table is an int column.

    However, to populate the drop-down, I'm using the description column from T_PKG, which lists all the full package names. So description is the ref descol  and the ref Idcol is pkg_no, which is an int.

    So I would have assumed that if you selected the description you wanted from the drop down, that it would then use the associated pkg_no, and then pull all customers from T_LINEITEM that have that pkg_no.

  • $organization in reply to Matthew Hodge

    Right, which means the data type in t_keyword would be number due to pkg_no being the actual reference that you are using.
    I think you have everything correct except if you are using something other than number as the data type in t_keyword.

    Take a look at the t_keyword for Contribution Campaign as an example of using number as a data type but still having the dropdown be a description.

  • $organization in reply to Ryan Rowell

    Ahhhhh . . . .

    Ryan, you are a genius of the highest order.

    I had been copying off an example that I'd been using previously that had a string search rather than a number.

    If you ever venture down to Sydney, Australia, there is a beer and/or chamber music tickets with your name on it.

    I shall now go off and have a much better day.

  • $organization in reply to Matthew Hodge

    Glad to help and hope one day to be able to take you up on that offer.

    Cheers!