Hey all!
I am super excited about the V11 flex header. I've been receiving some requests from various people at my organization to change up our current custom header and this is the perfect opportunity to do that.
I've been working out a test flex header icon format. For the most part, I've been able to do everything I want to do - my limited SQL knowledge has been enough to create a couple of custom elements.
I've run into two issues that I'm hoping someone might be able to help me out with.
1) I really like the On Account $ icon and want to use it. However, it is showing up if the constituent has simply had on account money in the past - even though the money is now gone and their balance is $0. When I hover over the icon it says the balance is $0. Ideally, I'd like this to only show up when the person has an actual balance on account. This element is right out of the box. I've tried changing it up a bit but with no success so far.
2) In the Research Tab under Notes we have a variety of note types. Each of these types is prefixed with a 2 or 3 letter short code. In V10 our custom header pulled those short codes so that our employees could see that there might be a note of interest to them when they pulled up the account. I tried simply copying the code from our V10 header into the flex header element but it gave me an error. After playing around with the code I've realized that the error is due to an XML reference. Removing it populates the flex header with the code. HOWEVER, it only gives me the first short code if people have more than one note. I'm not a SQL expert but I'm very sure the XML part was putting all of the short codes into one line. I'm really at a loss on this one. This is the code from my V10 custom header script. If I simply delete the last part (FOR XML PATH('')) it runs without errors but doesn't display the results in a useful format:
select substring(ct.description,1,3) + ',' from TX_CUST_NOTES cn join TR_CUST_NOTES_TYPE ct on cn.note_type = ct.id where cn.customer_no = @customer_no FOR XML PATH('')
Thanks in advance for any advice you may have!
Beth,
A couple things to try here.
1) The simple solution here is to add a having clause where having > 0. For example the following will get you what you need for the on account:
Select (-1 * SUM(pmt_amt)) From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where a.customer_no = @customer_no and b.pmt_type = 2 having (Select (-1 * SUM(pmt_amt)) From [dbo].t_payment a JOIN [dbo].vrs_payment_method b WITH (NOLOCK) ON a.pmt_method = b.id Where a.customer_no = @customer_no and b.pmt_type = 2) > 0
Here we are just saying to select the on account money if the on account money is more than 0.
2) This may be a little more complex. Have a look at the scalar function for building the constituency string (FS_CONST_STRING_NEW). It selects the constituency codes for the constituent and primary affiliates if desired and puts them into a string. You could build a function for your note types and just call the function in the header. For example, to get the constituency string in the header I put the following in the header element:
select dbo.fs_const_string_new(customer_no, 'Y') from t_customer where customer_no = @customer_no
If you are trying to get more into SQL this is a good one to work on as it has some complex components but on the whole it is pretty finite.
Best,
Anna
Hi Anna!
The On Account is now working exactly as I want it. Thank you so much! I thought I tried something similar but must have fudged up the code somewhere during my attempts. :)
I will definitely have a look at FS_CONST_STRING_NEW and see if I can make it work for our Notes. What a great learning opportunity!
Thanks for the help!