Is anyone using the custom data tab in the orders window?

We are implementing next month with some of our education data being entered into the Custom Data tab in the order.  Unfortunately, I don't have access to SQL to show you exactly how that info is getting pulled into a report, but I know that we will be able to pull it.  All of the education reports I'll be launching with are custom reports, but I'm sure there's got to be some established way to access the data in that tab.  I've attached a snippet of our custom data screen in the hopes it might help in some way.  I'll see if I can find out more for you!

Thanks, Justin! I was able to get that far (although I didn't get a dropdown list figured out because I wanted to see if I could get the data out first). It's the pulling the info out that I'm stuck on. If it is going to mean another custom report, then we'll probably just do it with a CSI since we don't expect many phone orders for these. The documentation says that the data can be printed on tickets and acknowledgements, so there's somewhere in the system that can access the info. Unfortunately, these fields aren't available as elements in output sets, because that would be an easy solution.

I finally figured out how to make the custom fields show up in List Manager. But they are there as criteria. Really I just need to search for orders for specific performances and then pull out the data in the custom fields. I won't have something to search on. Now if I can just figure out how to get these fields into output set manager.

That and to figure out the dropdown lists...that one has me stumped.

Try the output set cookbook.

https://www.tessituranetwork.com/en/Files/Docs/Customizations/Output-Set-Manager-Cookbook

And this video that goes with it.

https://www.tessituranetwork.com/en/Items/Videos/Training/Extending_Output_Sets

--Tom

P.S. I think when I learned this I looked at the previous version

https://www.tessituranetwork.com/en/Items/Videos/Training/Output_set_custom_element_groups

Thanks, Tom! I knew you were a wealth of information on Analytics, I should have known you'd be a true Tessitura Renaissance man!

Hi Anne,

Those are great resources that Tom pointed you to for learning how to set up custom output elements. I just wanted to note that the custom order elements aren't examples that are explicitly included in the cookbook. For a custom order field output element, here are the key pieces you'll need:

Add a new group to TR_QUERY_ELEMENT group that points to T_ORDER (Data From column).

Add a row for each custom order field to TR_QUERY_ELEMENT that are assigned to the output element group you added, and with the Data Select column set to the column that corresponds to each custom order field (e.g. !.custom_1 for custom field 1).  

-Kevin

Thanks, Kevin! I had noticed that the cookbook didn't have them (maybe they should be in there! hint! hint! :-) ). Those extra pieces of info are very helpful. I'm waiting on a Live to Test copy over night and then I'm going to get started tomorrow.

Anne

Just to clarify, Kevin Sheehan -- I first need to add a group to TR_QUERY_ELEMENT_GROUP, right? (not TR_QUERY_ELEMENT)

Yes, TR_QUERY_ELEMENT_GROUP. Sorry about that mistake in my original post!

Thanks, again! I got those set up, but now I have another question. I'm trying to use an output set to pull the custom fields out of an Attendance by Performance report that was saved as a list. Here's what my output set looks like:

I have a filter on the Ticket Performance Name to try to limit it to just the two events that these custom fields are going to be used on. But my results are giving me a line for every performance on the record (my own so there are quite a few from testing!) and nothing is showing up in the Tickets - Performance Name column. I know the red ! means that it can pull more than one row for each constituent, but is there anyway to filter it to just these two perfs and then make the perf names show? (I also realized that I should rename my custom output set elements to match what the actual fields say in the order window so the answers make more sense).

Now I'm off to see if I can figure out dropdown lists in the custom fields...wish me luck!

Here's what happened (and what I didn't think about at first when telling you how to set up the output elements for the custom order fields): 

The Performance Name element is referencing a different table (or in this case, a view) than the table referenced by the custom fields elements your created. More specifically, the performance name element references a view of the ticket history table while the custom elements are referencing the T_ORDER table directly. More simply, they are in different output element groups, which means that the filter you applied to the performance name element doesn't apply to the custom order field elements. So you are going to get rows for all orders belonging to the constituents on your list for the custom order field elements, mixed with the ticket history rows that meet the performance name filter for the performance name element, resulting in undesired exponential inflation of your results. For of a more detailed explanation of how that works, go to the 2:40 mark of the Lesson 9 video on the Lists and Output course.

To accomplish your goal, you are going to need a new view of the ticket history data that includes performance name, custom order fields, and any other order data you might want to combine with those fields in your output and filtering. You would then create a new output element group that references that view, new output elements you put in that group for the different pieces of data you want to return from the view, and filters assigned to that group to limit the data you get returned. Code for how to create that view is a little outside of my area of expertise, and probably more appropriate for a consulting request (you could always open a help ticket and if its definitely a consulting issue the support staff will get you in contact with the right people). The Output Set Cookbook has some examples of creating views to use with custom output elements, so that might help you figure it out on your own. You could also take a look at the view used by the ticket history output elements (VS_ELEMENTS_TICKET_HISTORY) and use a copy of that as a basis of your own custom view that includes the custom order fields. One other place to consider seeking some advice is the Administration and IT forum, which has a broader viewership than this user group, and has many smart people who often swap code for things like this.

I hope that's somewhat helpful pointing you in the right direction.

Hi Kevin, That makes total sense. Thank you! I've been taking some SQL courses, so I can understand the issue and the need for a new view. I might try it on my own and if I can't figure it out I'll ask the Admin and IT group. That's a great idea. I didn't realize my request was going to be so involved and technical, but I'm always happy to learn more.

Hi Kevin, That makes total sense. Thank you! I've been taking some SQL courses, so I can understand the issue and the need for a new view. I might try it on my own and if I can't figure it out I'll ask the Admin and IT group. That's a great idea. I didn't realize my request was going to be so involved and technical, but I'm always happy to learn more.