This is my very first computed View Method, and was wondering if someone would tell me if I am going in the correct direction.
The View name is ccv_HierarchyTreeTable (which uses the table - HierarchyTreeTable.). I am using the two fields [Path] and [Element Number]. I am using the computed field as a sort field, so I can get the data in the correct order for a grid view in a form.
It returns two different strings depending on if Path is blank or not (am I using the correct/best way of checking for a blank string?)
If I followed the example I took this from, I should have: public static server str cc_SortNode() -- why would it have server in that line?
Anyway - following is my code. Does it look correct? Anything else I need to know?
_________________
public static str cc_SortNode()
{
#define.ViewName(ccv_HierarchyTreeTable)
#define.DataSourceName("HierarchyTreeTable_1")
#define.FieldPath("Path")
#define.FieldElement("ElementNumber")
str sReturn,
sPath,
sElementNumber;
DictView dictView2;
dictView2 = new DictView(tableNum(#ViewName));
sPath = dictView2.computedColumnString
(#DataSourceName,
#FieldPath,
FieldNameGenerationMode::FieldList,
true);
sElementNumber = dictView2.computedColumnString
(#DataSourceName,
#FieldElementNumber,
FieldNameGenerationMode::FieldList,
true);
if (spath == "")
{
sReturn = sElementNumber + "!" + sElementNumber;
}
else
{
sReturn = sPath + sElementNumber;
{
return sReturn;
}
*This post is locked for comments
Forgot to mention, so you just need to lookup TSQL CASE statement for more information.
Yes, it is a TSQL statement syntax.
What happen is the view in AOT is translated into SQL statements when synchronizing with database.
When you have a computed column, the string returned by the view method is injected into the TSQL statement directly as a column definition.
E.g.
CREATE VIEW [dbo].[YourView] AS
SELECT col1, col2, col3, <view method return value>
FROM yourTable
WHERE ....
GO
sReturn = strFmt("CASE " + "WHEN %1 = '' THEN %2 + '!' + %2 ELSE %1 + %2 END", sPath, sElementNumber);
So - that was helpful. I changed my if statement to this, and now it compiles and works.
BUT!!! - I don't understand this CASE statement. This isn't standard X++ case statement is it? Is this more of an SQL case statement? Where would I go look this up so I can better understand what I am doing! Thanks
Hi,
What you are returning in this method should be a partial TSQL statement . The condition logic is to be included in it.
In your case, that would mean you should look to return something like:
CASE
WHEN yourTable.[Path] = '' THEN yourTable.[Element Number] + '!' + yourTable.[Element Number]
ELSE yourTable.[Path] + yourTable.[Element Number]
END AS [My Field Name]
Stay up to date on forum activity by subscribing. You can also customize your in-app and email Notification settings across all subscriptions.
André Arnaud de Cal... 291,113 Super User 2024 Season 2
Martin Dráb 229,918 Most Valuable Professional
nmaenpaa 101,156