Hello,
I'm trying to change the date format in a query element to make it user friendly for WordFly and I can't seem to get it work. Looking at the documentation, I should be able to use the CONVERT function in the system table to do so, but I'm getting an error. Alternatively, I tried setting up a new view and did the conversion there and that seems to work, but I can't get the elements in the new view to pull into my output set. I've granted SELECT permissions to ImpUsers and Tessitura_App and WordFly in SSMS.
And also related -- even when I have the output set working (with the dates in the wrong format, but the data pulling in from another view), WordFly can't seem to find those fields -- can it not use custom output elements? They aren't showing up as fields to be mapped.
Anne
Anne,
What error do you get when you try to save the system table with the CONVERT function? Also, what format are you hoping to get output? (Put out?) (Out of your output?)
John A. Moskal II
Sometimes the embedded SQL code options in parts of Tessitura are more limited in syntax options than TSQL itself: I'm often stymied in my attempts to do something clever (most recently setting up disable clauses in report parameters). So I would then go with a view, as you have. Now, the output set elements are displaying properly within Tessitura, right? The first thing I'd think of with Wordfly rejecting an element is that Wordfly will not accept elements that may result in multiple rows. Is that the case here, or is the element configured in such a way that it looks like it might return multiple rows? Is the whole output set failing to be picked up in Wordfly or only that column?
Hi John,
We're trying to set up email renewal notices so I need to include the expiration date -- either current or past (hence the custom view). Ideally, it would be "August 29, 2023," but I can live with "Aug 29, 2023" (which seems to be the closest I've gotten).
The original view I created is rendering properly in Tessitura -- the data fields are pulling in -- but they aren't showing up in WordFly.
The fields from the new view I created -- to try to convert the date field without breaking the view that was working -- aren't showing up in Tessitura, so it makes me feel like there is another level of permission that I'm missing.
I'm only seeing one line per constituent in the output of my list, but I'm also using a filter -- is that the issue? I'm starting to think that we may not be able to do this until v16. I need to pull in the most recent membership expiration date for lapsed members. This should be easy in v16, but maybe it isn't possible in v15 (although I feel like someone else must be doing this).
And the documentation says you can use CONVERT in Query Elements, which is the only reason I tried it.
Yeah, if you want a full month name, you are going to have to do some concatenated DATEPART() + OTHERSTUFF() sort of thing to get you there. I am not aware of any CAST() or CONVERT() out of the box that spits out full month names.
As for the rest, Gawain Lavers speaks the truth. It can be odd what SHOULD work that, for whatever reason, does not.
It saves okay in the system table, but I get an error parsing the field when I try to view the results in list manager.
I wonder if it has to do with the datatype somehow. Like the output rendering is expecting a DATETIME but getting something else.
Anne M Robichaux said:but I'm also using a filter -- is that the issue?
Yes, I'm pretty sure that's the problem. If the field is capable of returning more than one row Wordfly will reject it.
Anne M Robichaux said:I need to pull in the most recent membership expiration date for lapsed members.
Not trivial but not impossible. I have a view that does this. If you have multiple membership organizations, you might need to build views/output sets for each.
Yeah, I'm not seeing a full month name option here:
learn.microsoft.com/.../cast-and-convert-transact-sql