I could use some advice on getting a list control populated from a custom function called with the filter value, as I have done below, because I can't make heads or tails from the obfuscated entries in the help system where custom coding and functions are concerned.
The project I am working on requires a UX component that will have to be capable of functioning offline, and will populate a list control after a selection is made from a drop-down box, but this methodology seems to be backwards from the way Alpha is designed to function where a list control contains the master data that populates detail/edit view controls, choices in drop-down boxes, etc.
Further complicating the project is our use of many-to-many linking tables, which do not lend themselves to easy cascading of the drop-down boxes.
The project presents instructions to a user concerning the steps to be taken to complete a task. The list of instructions is quite large, and requires the project to allow it to be extended or edited, so the instructions are grouped into categories and sub-categories by filter values queried from the linking tables.
This part of the project involves creation of a screen that allows user to add, edit, or delete records from both the instruction list, and the category list.
Within the UX are two drop-down boxes, and one list control.
The choice list in the first drop-down box is populated with major categories, and the second with minor categories selected by query when a selection is made from the first drop-down. I need to have the list control become populated with a list of instructions queried from a table based on the selection made from the second drop-down box.
For this particular part of the project, I am working with four SQL tables:
1. The instruction list table, containing an auto-increment ID field, the instruction string text, and a text field containing a string representing the type of instruction...
2. The category list table, containing an auto-increment ID field, and the category text. This table contains entries representing both major categories, and minor categories...
3. The sub-category link table, containing an auto-increment ID field, an integer field for the category ID lookup, and an integer field for the sub-category ID lookup...
4. The instruction link table, containing an auto-increment ID field, an integer field for the category ID lookup, and an integer field for the instruction ID lookup...
Upon displaying the screen, the UX component's "onRenderComplete" event fires an AJAX callback function in Xbasic that loads DISTINCT records from the category list. But since the category list contains rows with both categories and sub-categories, the query is run against the sub-category linking table which is attached to the category text table by an inner-join, providing distinct pointer values for the category, and its related sub-category, the result being that the first drop-down, "Category", becomes populated with distinct values from the top categories:
This works very well, and I did it because I was not able to get the drop-down boxes to cascade using their built-in properties, perhaps because the join statement confused the index value expected by the child control, or maybe I missed something, but either way, this worked, even if it did take three days to figure it out.
Now that the choices have been populated in the "Category" drop-down, the next function is another AJAX callback triggered by the on Change event fired when the user makes a selection from the "Category" drop-down. It is almost straight-forward since it receives the index value from the "Category" drop-down, and then queries the category/sub-category linking table using the category ID index, which returns several rows of data containing pointer indices into the category text table, from which it retrieves the sub-category text by looping through the result set and querying for each matching record:
This also works very well, so now we have the major categories listed in the "Category" drop-down, and upon selecting one, the entries in the sub-category drop-down change as well, only requiring two additional days to work out using the help docs.
Now I need to populate the list control with entries queried from the instruction text table, which is likely to be very easy to do, but as usual the Alpha "help" system hasn't been very helpful (I think learning to do housekeeping and use delegates and pointers in C# was easier than accomplishing some of the simplest things in Alpha when custom features must be created).
I have been going in circles trying to extrapolate some comprehensible method to accomplish this, which can probably be done in a way similar to the above, but the tutorials and help documentation are all over the place, usually falling just short of providing the right example. And when the answer is finally found, it usually comes after days of studying multiple tutorials and entries in the "help system" because no single, linear path exists from which to find complete, relevant information on a specific topic, function, reference, or anything beyond the specific examples provided by Alpha to illustrate a specific feature.
I don't place the blame for this squarely on Alpha, since we are circumventing its convenience features by having to resort to hand-coding despite the fact that the bulk of its documentation is geared toward insulating users from this very thing, but I seriously expected better detail and organization from the help doc's.
Either way, I will continue to sift through the documentation and will likely stumble upon the solution sooner or later, unless some kind soul can point me in the right direction.
The project I am working on requires a UX component that will have to be capable of functioning offline, and will populate a list control after a selection is made from a drop-down box, but this methodology seems to be backwards from the way Alpha is designed to function where a list control contains the master data that populates detail/edit view controls, choices in drop-down boxes, etc.
Further complicating the project is our use of many-to-many linking tables, which do not lend themselves to easy cascading of the drop-down boxes.
The project presents instructions to a user concerning the steps to be taken to complete a task. The list of instructions is quite large, and requires the project to allow it to be extended or edited, so the instructions are grouped into categories and sub-categories by filter values queried from the linking tables.
This part of the project involves creation of a screen that allows user to add, edit, or delete records from both the instruction list, and the category list.
Within the UX are two drop-down boxes, and one list control.
The choice list in the first drop-down box is populated with major categories, and the second with minor categories selected by query when a selection is made from the first drop-down. I need to have the list control become populated with a list of instructions queried from a table based on the selection made from the second drop-down box.
For this particular part of the project, I am working with four SQL tables:
1. The instruction list table, containing an auto-increment ID field, the instruction string text, and a text field containing a string representing the type of instruction...
2. The category list table, containing an auto-increment ID field, and the category text. This table contains entries representing both major categories, and minor categories...
3. The sub-category link table, containing an auto-increment ID field, an integer field for the category ID lookup, and an integer field for the sub-category ID lookup...
4. The instruction link table, containing an auto-increment ID field, an integer field for the category ID lookup, and an integer field for the instruction ID lookup...
Upon displaying the screen, the UX component's "onRenderComplete" event fires an AJAX callback function in Xbasic that loads DISTINCT records from the category list. But since the category list contains rows with both categories and sub-categories, the query is run against the sub-category linking table which is attached to the category text table by an inner-join, providing distinct pointer values for the category, and its related sub-category, the result being that the first drop-down, "Category", becomes populated with distinct values from the top categories:
Code:
function populateCategory as c (e as p)
Dim txt as c
Dim list as c
Dim catlist as c
Dim subcatlist as c
Dim js as c
Dim flag as l
Dim sqlcommand as c
Dim cn as sql::Connection
'Dim args as SQL::Arguments
Dim rs as SQL::ResultSet
flag = cn.open("::Name::MRIAPP01_MRISCOPE")
If flag = .f. then
populateCategory = "alert('Could not connect to MRIAPP01_MRISCOPE database.');"
exit function
end if
sqlcommand = "SELECT DISTINCT scp_block_instructions_category.category, scp_block_instructions_category.id FROM scp_block_instructions_sub_category_link scp_block_instructions_sub_category_link INNER JOIN scp_block_instructions_category scp_block_instructions_category ON scp_block_instructions_sub_category_link.category_id = scp_block_instructions_category.id ORDER BY scp_block_instructions_category.id"
flag = cn.Execute(sqlcommand)
If flag = .f. then
cn.close()
populateCategory = "alert('Could not execute SQL statement.');"
exit function
end if
rs = cn.ResultSet
flag = rs.NextRow()
If flag = .f. then
'No records in ResultSet
cn.FreeResult()
cn.close()
populateCategory = ""
end if
txt = ""
'Create JavaScript Array
'debug(1)
txt = "['Select Instruction Category','0']" + crlf()
while flag
txt = txt + "['" + js_escape(rs.Data("category")) + "','" + js_escape(rs.Data("id")) + "']" + crlf()
flag = rs.NextRow()
end while
'Populate "list" string with crlf delimited JavaScript array...
list = "[" + crlf_to_comma(txt) + "]"
js = <<%txt%
var data = __data__;
var curData = '';
var clearExisting = true;
{dialog.object}.populateDropdownBox('DD_CATEGORY',data,clearExisting);
%txt%
js = stritran(js,"__data__",list)
populateCategory = js
end function
Now that the choices have been populated in the "Category" drop-down, the next function is another AJAX callback triggered by the on Change event fired when the user makes a selection from the "Category" drop-down. It is almost straight-forward since it receives the index value from the "Category" drop-down, and then queries the category/sub-category linking table using the category ID index, which returns several rows of data containing pointer indices into the category text table, from which it retrieves the sub-category text by looping through the result set and querying for each matching record:
Code:
function populateSubCategory as c (e as p)
Dim txt as c
Dim list as c
Dim js as c
Dim idx_flag as l
Dim out_flag as l
Dim sqlcommand as c
Dim cn as sql::Connection
Dim idx_rs as SQL::ResultSet
Dim out_rs as SQL::ResultSet
Dim Category_LU as n
Dim SubCategory_LU as n
'Assign category variable sent to this sub at "e._currentRowDataNew.DD_CATEGORY" to "Category_LU"
Category_LU = e._currentRowDataNew.DD_CATEGORY
idx_flag = cn.open("::Name::MRIAPP01_MRISCOPE")
If idx_flag = .f. then
populateSubCategory = "alert('Could not connect to MRIAPP01_MRISCOPE database.');"
exit function
end if
'Get result set from "scp_block_instructions_sub_category_link"
sqlcommand = "SELECT * FROM scp_block_instructions_sub_category_link WHERE category_id = '" + Category_LU + "'"
idx_flag = cn.Execute(sqlcommand)
If idx_flag = .f. then
cn.close()
populateSubCategory = "alert('Could not execute SQL statement.');"
exit function
end if
idx_rs = cn.ResultSet
idx_flag = idx_rs.NextRow()
If idx_flag = .f. then
'No records in ResultSet
cn.FreeResult()
cn.close()
populateSubCategory = ""
end if
'The result set for "scp_block_instructions_sub_category_link" has been collected into SQL result set object "idx_rs"
txt = ""
txt = "['Select Instruction Sub-Category','0']" + crlf()
while idx_flag
'Get subcategory ID from current record in "scp_block_instructions_sub_category_link" result set
SubCategory_LU = idx_rs.Data("sub_category_id")
'Assemble SQL command to get matching record from "scp_block_instructions_category"
sqlcommand = "SELECT * FROM scp_block_instructions_category WHERE id = '" + SubCategory_LU + "'"
'Execute assembled query...
out_flag = cn.Execute(sqlcommand)
'Test for successful execution against "scp_block_instructions_category"
If out_flag = .f. then
cn.close()
populateSubCategory = "alert('Could not execute SQL statement.');"
exit function
end if
out_rs = cn.ResultSet
'Test for empty result set...
out_flag = out_rs.NextRow()
If out_flag = .f. then
'No records in ResultSet
.Hide("DD_SUB_CATEGORY")
cn.FreeResult()
cn.close()
populateSubCategory = ""
end if
'If we made it this far, we have one row from "scp_block_instructions_category"
'where "scp_block_instructions_category.id" = "scp_block_instructions_sub_category_link.sub_category_id"
'Create JavaScript Array
txt = txt + "['" + js_escape(out_rs.Data("category")) + "','" + js_escape(out_rs.Data("id")) + "']" + crlf()
idx_flag = idx_rs.NextRow()
end while
'Populate "list" string with crlf delimited JavaScript array...
list = "[" + crlf_to_comma(txt) + "]"
js = <<%txt%
var data = __data__;
var curData = '';
var clearExisting = true;
{dialog.object}.populateDropdownBox('DD_SUB_CATEGORY',data,clearExisting);
%txt%
js = stritran(js,"__data__",list)
populateSubCategory = js
end function
Now I need to populate the list control with entries queried from the instruction text table, which is likely to be very easy to do, but as usual the Alpha "help" system hasn't been very helpful (I think learning to do housekeeping and use delegates and pointers in C# was easier than accomplishing some of the simplest things in Alpha when custom features must be created).
I have been going in circles trying to extrapolate some comprehensible method to accomplish this, which can probably be done in a way similar to the above, but the tutorials and help documentation are all over the place, usually falling just short of providing the right example. And when the answer is finally found, it usually comes after days of studying multiple tutorials and entries in the "help system" because no single, linear path exists from which to find complete, relevant information on a specific topic, function, reference, or anything beyond the specific examples provided by Alpha to illustrate a specific feature.
I don't place the blame for this squarely on Alpha, since we are circumventing its convenience features by having to resort to hand-coding despite the fact that the bulk of its documentation is geared toward insulating users from this very thing, but I seriously expected better detail and organization from the help doc's.
Either way, I will continue to sift through the documentation and will likely stumble upon the solution sooner or later, unless some kind soul can point me in the right direction.
Comment