We are building our own simple filter window, adapting from the PFC version but changing it to our own desired specs.
We want to show a drop-down list of column names that can be filtered on, and have that be exactly the data column names (the names coming from the SQL).
We were originally using the list from dw.Object.Datawindow.Objects, and using the restrictions available in the PFC code for that window to restrict it to what it considered to be "columns". But that had various problems, including on a DW where there were two displayed fields showing the data from the same SQL column - both of them showed up on the list.
So we changed to getting the column names by first getting dw.Object.Datawindow.Column.Count, then getting the names via dw.Describe("#1.name") etc.
That is basically giving what we want, except for one thing really weird in that same DW mentioned above with two display fields showing data from the same SQL column. Those fields are named "amount" and "amount_1" (with the data column being named "amount") and what is showing up from the Describe call is just one name, "amount_1".
Why would that be, and is there anything we can do about that? I really thought that sort of Describe call would give the real data column names, not display field names.
Thanks.
You will notice that at the very bottom of your original code, we changed "lineStart = nextLine + 2" to "lineStart = lineStop + 2", which would prevent an infinite loop if for some reason the DW syntax didn't exactly end with a ~r~n. (We believe it does always end that way, mind you, as I expect was your experience!)
We also simplified the testing for a line representing a data column, and added a test for a line representing a display column which then exits the loop, to prevent having to work through the entire syntax.
And finally, we added a sanity check with a messagebox at the end, to make sure the count of columns we found matches Datawindow.Column.Count as it should!
long lineStart=1, lineStop, nextLine, syntaxLength, colNo, colCount, nameStart, nameStop, ColumnsWentThrough
string lineText, dwSyntax, ls_column, empty[]
TRY
as_colName = empty
dwSyntax = lower(idw_requestor.object.datawindow.syntax)
if IsNull(dwSyntax) then RETURN // No syntax => DONE
syntaxLength = Len(dwSyntax)
if syntaxLength = 0 then RETURN // No syntax=> DONE
colCount = long(idw_requestor.object.datawindow.column.count)
if colCount = 0 then RETURN // No columns => DONE
CATCH (RuntimeError lex_error)
RETURN // DW object missing => DONE
END TRY
// Search through syntax line-by-line
do while lineStart 0 then exit
// Move forward to next line
lineStart = lineStop + 2
loop
ColumnsWentThrough = colNo
if ColumnsWentThrough colCount then
MessageBox("Internal Error!", "There had been extra column(s) found when building the list. Total of " + string( ColumnsWentThrough) + &
" columns were processed while there are " +string(colCount) + " columns in the datawindow." +&
"Please report this to support for assistance.")
End if
I noted following potential improvements while testing my code. However, tuning can complicate code reading. Rather have code example that developers can read for inspiration.
I'm happy to share what I learned for fellow developers to benefit.
Tuning options:
1) Avoid copy each line into lineText
Earlier version worked only inside dwSyntax.
Definitely harder to read.
I rewrote to debug easily and fix issue when .values included "~r~n".
2) Start with lineStart = Pos(dwSyntax, "~r~ntable(column=(") + 8 to by-pass all prior syntax
This makes lineStart for first line start at "column=(…" just like all the other column lines
Earlier version also included this tweak.
3) Stop search when the "column=(" section has completed.
Earlier version stopped at line prefix = "retrieve".
That failed for stored procedure DW because such DW ends column section with prefix = "procedure".
So in published version I simply keep parsing the syntax until the very end.
Debugging tip: I copy dwSyntax's text into VS Code to follow along while debugging in PB IDE to know exactly what text my code is parsing.
Reason: In Edit Source column #2 onwards include space character as initial character.
dwSyntax at runtime does not!
HTH /Michael