Our staff want to see the MOST RECENT membership information in our headers, which means if there is a Pending membership on record, they want to see that information and not the 'current' membership. The exisitng function that pulls the membership info for the flex headers didn't include membership status, and also didn't pull the 3 letter membership level code, which we prefer to see along with the expiry date.
I copied the existing FT_ELEMENTS_FIRST_AND_LAST_MEMBERSHIP and added my two peices of criteria in and saved this new function appended with _SF at the end of the name. When I query this function directly in SSMS, it correctly pulls the data that I need. However, as soon as I move the query into the TR_FLEX_HEADER_ELEMENT table, all I get is the word 'ERROR' in the header for that data.
Any thoughts on what I may be missing and why this works when in SSMS, but won't in the client?
Michele
Hey Michelle, we have the same requirement but rather than call a function we just have a standalone query in the flex header element which references VXS_CUST_MEMBERSHIP and uses a combination of TOP 1 and ORDER BY to get the most recent. For example we get the latest expiry date with
select top 1 format(expr_dt, 'dd/MM/yy')
from vxs_cust_membership
where customer_no = @customer_no and memb_org_no = 2
order by expr_dt desc
It's not the answer to your actual question but it might help solve the problem a different way :)