SQL code for Flex Header - Batch Number?

Hi Kim thanks for posting - wondering if you'd find it useful to cross-post to the Database Managers community group? Hopefully you'll receive a reply over there.

- Gill Tasker, Community Manager

This is the coding:    select top 1 batch_no from T_BATCH where owner = dbo.fs_user() and status = 'O'

In the TYPE field:  String

Could you clarify what you mean by "I have to reset them"? Are you changing existing standard elements? You can add your own flex header elements in TR_FLEX_HEADER_ELEMENT and then change which elements are associated with which headers (or make a new header) in Flex Header Configuration (TR_FLEX_HEADER). While it's fine to edit the queries used for standard elements if your internal definition is slightly different, I wouldn't recommend repurposing an existing standard element to do something entirely different. I'm not sure if you've done that but wanted to mention it just in case.

You'll want to use dbo.fs_username(). I'm also wondering if there's a specific reason you chose string rather than number?

You may want to review the Help article regarding flex header elements. It sounds like the issue may be (from the SQL Code section of that link) All queries must include @customer_no in the WHERE clause.

That said, in my environment what you're trying to do works whether I set the type to number or string, and without @customer_no in the where clause. So if none of the above helps, I recommend opening a support ticket.

Thanks for your reply!  I ended up submitting a ticket on this as I really don’t know much about SQL.

Here are the answers to your questions in case anyone else references this post.

My user group has never had the flex header customized.  I initially wanted to customize just one of the fields but keep the other 7 fields the same as we had been using for 15 years.  Setting up just one of the fields in the system tables resulted in the other 7 fields names becoming blank when I looked at an account.  I had to program all 8 fields to get the information back that I desired.  Some of the fields that we have been using were listed in the Standard Elements and/or Flex Header Recipe book documentation.  While user batch number was one of the fields, we have always had in the flex header, it is not listed in documentation.

Tessi support provided me with the code and told me to use string. 

Here is the code I was provided.

select top 1 batch_no from T_BATCH where owner = dbo.fs_user() and status = 'O'

Thanks for your reply!  I ended up submitting a ticket on this as I really don’t know much about SQL.

Here are the answers to your questions in case anyone else references this post.

My user group has never had the flex header customized.  I initially wanted to customize just one of the fields but keep the other 7 fields the same as we had been using for 15 years.  Setting up just one of the fields in the system tables resulted in the other 7 fields names becoming blank when I looked at an account.  I had to program all 8 fields to get the information back that I desired.  Some of the fields that we have been using were listed in the Standard Elements and/or Flex Header Recipe book documentation.  While user batch number was one of the fields, we have always had in the flex header, it is not listed in documentation.

Tessi support provided me with the code and told me to use string. 

Here is the code I was provided.

select top 1 batch_no from T_BATCH where owner = dbo.fs_user() and status = 'O'