Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

How do I populate a list control with a selection made from a drop-down?

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • How do I populate a list control with a selection made from a drop-down?

    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:

    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
    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:

    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
    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.

  • #2
    Re: How do I populate a list control with a selection made from a drop-down?

    Originally posted by mbunds View Post
    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).
    Is the instructions table query simple? EG: Could you build a list that used a basic Query, such as "SELECT * FROM questions_table" and then apply a filter of your own created from the dropdown boxes to get the records you want?

    There's an option to 'Delay populate List till active search" in the Search Properties for the List (you'll need to check "Has Search" to enable these options.) This prevents the list from downloading the universe when it's first loaded (since it sounds like you have a large data set to choose from.) You could turn this on and then initiate the search using the {dialog.object}._filterList method. _filterList makes an ajaxCallback to filter the List.

    This is the example code for _filterList:
    Code:
    //use \n to create a cr-lf delimited list
    var params = 'Boston|||C|whatcity\nSmith|||C|whatname';
    {dialog.object}._filterList('MYLIST1','city = :whatcity AND name = :whatname','',params);
    Params could easily be built with two calls with {dialog.object}.getValue to get the value in the dropdown controls to filter your results.
    Alpha Anywhere latest pre-release

    Comment


    • #3
      Re: How do I populate a list control with a selection made from a drop-down?

      PS: I did attempt to reconstruct a database matching what you described to see if I could be more helpful. I had some questions about what you described:
      1. Does "instructions_link" map Major and Minor categories to instructions?
      2. Does "subcategory" map the Minor category to its Major category? Can a Minor category map to more than one Major category?
      3. What is "type of instruction" in the instructions table? Like, is this data type stored? Eg, logical, date, etc?
      Alpha Anywhere latest pre-release

      Comment


      • #4
        Re: How do I populate a list control with a selection made from a drop-down?

        Originally posted by TheSmitchell View Post
        Is the instructions table query simple? EG: Could you build a list that used a basic Query, such as "SELECT * FROM questions_table" and then apply a filter of your own created from the dropdown boxes to get the records you want?
        Thanks! The instructions table query is simple, fortunately. As with the routine that populates DD2, I just query all records matching the key value passed from the selection made in DD2, and Alpha returns the result set, no problem.

        I just can't figure out the code equivalent to the part of the function that returns the formatted JavaScript, as it does at the end of each drop-down box function:

        Code:
        	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
        Once the result set is ready, I imagine the routine for passing the data as I did for the DD boxes is just as simple, but there are a lot of options for populating the list control, each one having many configurable properties. I've been examining some potential solutions in some of the example videos that look very similar, but most of the examples only provide half of the solution since they were made to illustrate the use of other features.

        Once this problem is solved, I'm hoping I will be armed well enough to become productive.

        Comment


        • #5
          Re: How do I populate a list control with a selection made from a drop-down?

          Originally posted by TheSmitchell View Post
          PS: I did attempt to reconstruct a database matching what you described to see if I could be more helpful. I had some questions about what you described:
          1. Does "instructions_link" map Major and Minor categories to instructions?
          2. Does "subcategory" map the Minor category to its Major category? Can a Minor category map to more than one Major category?
          3. What is "type of instruction" in the instructions table? Like, is this data type stored? Eg, logical, date, etc?
          Thanks for looking at this, TheSmitchell!

          I have written what I hope describes my data structure a little better, and have answered the questions in your list at the bottom.

          • The table set dedicated to defining the structure of the book is hierarchical, using linking (or "mapping") tables for defining the hierarchy, and the data tables attached to each one.
          • The structure of the hierarchy is defined and maintained by the linking tables which tend to have increasing numbers of columns further down as each one must maintain relationships with the one above.
          • Each linking table has a corresponding data table, and with a few exceptions, each data table is a "flat," two-column table having one auto increment PK, and one text field.



          The structure of the linking tables (abbreviated to those immediately before and after the "instruction" data set):

          (scp_section_block_link)
          id (INT)
          cover_id(INT)
          chapter_id(INT)
          section_id(INT)
          block_id(INT)---------> (scp_block; id(INT) heading(TEXT))


          (scp_block_instructions_link)
          id (INT)
          cover_id(INT)
          chapter_id(INT)
          section_id(INT)
          block_id(INT)
          block_instructions_id(INT)---------> (scp_block_instructions; id(INT) instruction(TEXT) type(VARCHAR))


          (scp_block_comments_link)
          id (INT)
          cover_id(INT)
          chapter_id(INT)
          section_id(INT)
          block_id(INT)
          block_comments_id(INT)---------> (scp_block_comments; id(INT) comments(TEXT))


          The superstructure of the hierarchy is roughly organized like this:

          Users - (child of "jobs")
          Jobs - (parent of "scopes")
          Scopes - (parent of "covers," child of "jobs")
          Covers - (parent of "chapters," child of "scopes")
          Chapters - (...and so on...)
          Sections
          Blocks

          The linking tables above "scp_section_block_link" have fewer columns at each parent level, but that doesn't apply to the work being done to the instruction category list.


          The term "blocks" here is roughly equivalent to saying "paragraphs," but in the context of this "book," the "block" is where edit fields will appear to be filled-out by the user in the finished application, based on the procedures provided by the "block instructions."

          This level of the hierarchy looks like:

          Linking Table: scp_section_block_link Data Table: scp_block (not part of the "instruction category" routines; included for "clarity?")
          |
          |
          Linking Table: scp_block_instructions_link Data Table: scp_block_instructions -----> Linking Table: scp_block_instructions_category_link Data Table: scp_block_instructions_category
          |
          |
          Linking Table: scp_block_comments_link Data Table: scp_block_comments (not part of the "instruction category" routines; included for "clarity?")
          |
          |
          Linking Table: scp_block_controls_link Data Table: scp_block_controls (not part of the "instruction category" routines; included for "clarity?")


          Taking the child items out of line #2 above, the category/sub-category structure is organized like:

          Linking Table: scp_block_instructions_category_link ----> Data Table: scp_block_instructions_category <---- [I]Linking Table: scp_block_instructions_sub_category_link


          The "instructions category" data set is not organized under the main hierarchy because it is not used during the normal execution of the instruction book, but is used only in the management screens to reduce the list of choices when a new book is assembled, or the instruction and category lists require editing.

          Hopefully this illustrates what I'm doing a bit better, so here are the answers to your questions:
          1. Does "instructions_link" map Major and Minor categories to instructions?
          2. Does "subcategory" map the Minor category to its Major category? Can a Minor category map to more than one Major category?
          3. What is "type of instruction" in the instructions table? Like, is this data type stored? Eg, logical, date, etc?


          ANSWER 1.
          • The table named "scp_block_instructions_category_link" maps each instruction to its major category.
          • The table named "scp_block_instructions_sub_category_link" does the mapping of major and minor categories entered in the table "scp_block_instructions_category".
          • The table named "scp_block_instructions_link" links records in "scp_block_instructions" to the hierarchy, and has no part in the definition or relationship of the category/sub-category list.


          ANSWER 2.

          The linking table, "scp_block_instructions_sub_category_link," maps major and minor category entries in "scp_block_instructions_category" the same way it would be used to map two tables. It might be more intuitive to have made two tables, one for "categories," and one for "sub-categories," but since this data is non-repeating and self-related, it only needs one table.

          The table of categories (containing the entries for sub-categories as well) might contain entries like this:

          (Table: scp_block_instructions_category)
          ID CATEGORY
          1 DRAWWORKS (always MAJOR)
          2 MUD PUMPS (always MAJOR)
          3 ROTARY TABLES (always MAJOR)
          4 FRAME (MAJOR and/or MINOR)
          5 FASTENERS(MINOR only)
          6 PAINT AND FINISH(MINOR only)
          7 SHAFTS (MAJOR and/or MINOR)
          8 GEARBOXES (MAJOR and/or MINOR)
          9 SPROCKETS
          Some items on the list are top level assemblies, so they are always major categories, and some items are sub-assemblies, which can cause them to appear as major categories when the reference is made to them as a unit, or sometimes as a minor (sub-category) when they are referenced as a part of a major assembly, meaning also that they are sometimes both, and finally some items will never appear as a major category because they are not reducible into sub-components.

          If "MUD PUMPS" is a major category, it might have some sub-categories organized beneath it, because a mud pump has a frame, shafts, fasteners, and paint and finish. But a mud pump will never have a rotary table, and a fastener will never have a frame. Likewise, "SHAFTS" could be a sub-category under "DRAWWORKS," but can also be a major category because a shaft has "SPROCKETS," "FASTENERS," and "PAINT AND FINISH."

          The linking table structure might look like this:

          (Table: scp_block_instructions_category_link)
          ID CATEGORY_ID SUB_CATEGORY_ID
          01 1 6
          02 1 4
          03 1 5
          04 1 7
          05 2 4
          06 2 7
          07 2 5
          08 3 4
          09 3 8
          10 7 5
          11 7 9
          With the above configuration, the first drop-down would contain:

          DRAWWORKS
          MUD PUMPS
          ROTARY TABLES
          SHAFTS

          So if the user selects "MUD PUMPS", the DD produces the index value which is passed to the function that populates the sub-categories. It queries the link table for all records with a category id of "2", and a loop is used to iterate through the result set, one record at a time, and issue a query against the category table for records where the ID of each category record matches one sub-category ID index value.

          Since the sub-category indices for the MUD PUMP are 4, 7, and 5, the second DD is populated with:

          FRAMES
          SHAFTS
          FASTENERS

          If the user selects "DRAWWORKS", the 2nd DD is cleared, and then populated with:

          PAINT AND FINISH
          FRAME
          FASTENERS
          SHAFTS

          ANSWER 3.

          The "type" field is a VARCHAR field having a data width of "1." It was included as a place holder for a key that might be used to further define the purpose, type, or reason for the instruction. I have no concrete plans for this field as yet.



          Hopefully, this clears things up a bit. Thanks again for taking your time to look at it!

          Comment


          • #6
            Re: How do I populate a list control with a selection made from a drop-down?

            have you taken look at the screencasts in this thread of yours
            http://www.alphasoftware.com/alphafo...505#post702505
            post #22, #23?
            thanks for reading

            gandhi

            version 11 3381 - 4096
            mysql backend
            http://www.alphawebprogramming.blogspot.com
            [email protected]
            Skype:[email protected]
            1 914 924 5171

            Comment


            • #7
              Re: How do I populate a list control with a selection made from a drop-down?

              Originally posted by GGandhi View Post
              have you taken look at the screencasts in this thread of yours
              http://www.alphasoftware.com/alphafo...505#post702505
              post #22, #23?
              I have studied these excellent examples, thank you so much for posting them!

              I have also worked through several Alpha tutorials demonstrating similar methods, and it is indeed very easy, but only if the tables are related by common one-to-one or one-to-many joins.

              Many-to-many joins are very different, often requiring queries targeting the linking (mapping) table itself in order to filter records returned from data tables on either side of the linking table.

              When I followed the examples provided for automatically cascading various controls using conventional table relations, everything worked as demonstrated. But cascading failed when I tried to include a query containing the JOIN syntax for filtering records based on the linking table:


              Code:
              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
              This is a strange looking query, but it works; the seemingly redundant entries in the query are the result of the JOIN statement tying in references to records from the same table, because the linking table must be used to filter records from both its category and sub-category foreign key entries.

              Regardless, when I placed this query into the data source properties (as a custom query) of the drop-down box that was working with the single table, as in the tutorial, automatic cascading failed, and I couldn't figure out why because the debugger showed the same values being passed to the function using the custom query as it did when it was working properly using the built-in table/field selector.

              I am going to try a separate UX component and experiment using your screencasts just to see if I can get it working with the custom query. Right now though, I have to figure out how to populate a list control using the same method I used for the drop-down boxes.

              Comment

              Working...
              X