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

Filtering a list using an array or comma-delimited string

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

  • Filtering a list using an array or comma-delimited string

    Hey there friends,

    Been bashing my head on this one all day and was hoping to get some further guidance. Here's what we have going on today:

    - I have an XBasic function that is declared as such:
    Code:
    function GetUsers as n[] (e as p)
    that runs a query, stores the results of the query in an array, and returns the array. Currently it returns [1]=13 [2]=5, which is expected.

    - I have a session variable in onDialogInitialize as such:
    Code:
    session.ALLOWEDUSERS = json_generate(GetUsers(e))
    as Alpha suggests for storing arrays in session variables.

    - I have a numeric argument created called ARRAY_ALLOWEDUSERS that is pointing to session.ALLOWEDUSERS.

    - I filter the list by
    Code:
    employee_id IS NOT IN :ARRAY_ALLOWEDUSERS
    but my list is not being filtered.

    Summed up, my goal is to filter a list control based on the results of a query I'm running, and the query returns me a list of numeric IDs. I've tried making the function return a string, building a comma-delimited string from the results of the query, and then filtering the list with that (to avoid arrays in session variables / arguments) but that causes me trouble too. Not sure if it's because it's comparing a number to a character, but for some reason some MySQL functions aren't available for filtering in list controls (such as CAST or CONVERT).

    Oh, and I've also tried putting the query in the filter of the list (since Alpha lets you choose SQL Expression as a value in the filter) directly, but this causes an error as my query has a nested select inside of it.

    Thanks in advance for your time!

  • #2
    Re: Filtering a list using an array or comma-delimited string

    First... your argument must be enclosed in parens... NOT IN (:ARRAY_ALLOWEDUSERS). Second... I think it's NOT IN... not IS NOT IN.

    I think you're going to need Alpha to explain this one. This is one of the big issues I have with Alpha. They build some really good ideas... and then give you very little, if any, information on how to use it. The array argument example from Alpha involves 2 Lists... one use case example. There is no other information.

    If I hard code the select statement, it works. WHERE CUSTOMER_ID IN (41,42).

    Using Alpha's example with 2 Lists, here is the List debugging info... which works...
    ARGS_FROM_LIST.PNG

    Here is debugging info when setting a session var to "41,42" and setting the argument as an array... this does not work...
    ARG2.PNG

    Here is debugging info using the same session var, but not using an array argument... this does not work...
    ARG1.PNG

    They are essentially the same. The 2 List example turns the selected rows into a comma delimited list. Hard coding a comma delimited List works. Get the comma delimited List from a session var does not work.

    Alpha is going to have to explain this one.
    Last edited by Davidk; 04-17-2019, 11:09 PM.

    Comment


    • #3
      Re: Filtering a list using an array or comma-delimited string

      Are you doing this on a List control in a UX?

      Comment


      • #4
        Re: Filtering a list using an array or comma-delimited string

        @Davidk:
        Really appreciate the insight on this one, David! Sorry for the messup yesterday, my query is indeed NOT IN (:ARRAY_ALLOWEDUSERS) rather than IS NOT IN, I was typing this up off of memory when I got home from the office. I've attached some screenshots to show the query, a test execute of the query, and the error I get back:

        Query:
        Query.PNG

        Argument:
        Argument.PNG

        Error:
        Error.PNG

        I will reach out to Alpha to see if they can explain this a little further.

        @WebDesignShop - Yes I am indeed.

        Comment


        • #5
          Re: Filtering a list using an array or comma-delimited string

          Here's how I got it to work:
          preKeys = e._currentRowDataNew.STAFFKEYS
          StaffKeys = comma_to_crlf(preKeys)
          argument_add_array_argument(args,"array_vKeys","N",StaffKeys)

          sql = "Update Staff set Color = 'Green',DateMod = :Today where SysID IN (:array_vKeys)"

          Comment


          • #6
            Re: Filtering a list using an array or comma-delimited string

            Lee, isn't that an XBasic process... firing a SQL statement in XBasic? The issue is getting a List SQL statement to work with an array argument. Alpha demonstrates this using 2 List controls but no other detail information. The end result for the List SQL statement seems to be a comma-delimited list for the argument... but outside of the 2 List example it doesn't seem to work.

            Comment


            • #7
              Re: Filtering a list using an array or comma-delimited string

              Originally posted by Cameron.Smith View Post
              @Davidk:
              Really appreciate the insight on this one, David! Sorry for the messup yesterday, my query is indeed NOT IN (:ARRAY_ALLOWEDUSERS) rather than IS NOT IN, I was typing this up off of memory when I got home from the office. I've attached some screenshots to show the query, a test execute of the query, and the error I get back:

              Query:
              [ATTACH=CONFIG]45776[/ATTACH]

              Argument:
              [ATTACH=CONFIG]45777[/ATTACH]

              Error:
              [ATTACH=CONFIG]45778[/ATTACH]

              I will reach out to Alpha to see if they can explain this a little further.

              @WebDesignShop - Yes I am indeed.
              I can understand why the "numeric" error... so change your argument to Character. However, when I do this it doesn't work either... I only get the very first value in the argument.

              Comment


              • #8
                Re: Filtering a list using an array or comma-delimited string

                Exactly. I understand the error but even switching it to a character it doesn't work for me.

                And yes David nailed what I'm trying to accomplish @Lee. I could handle this just fine if all of the work was done inside of a function, but the problem I'm having is as follows:

                * UX has list control
                * Have a function that returns an array of numbers
                * Need to use the return value of that function to filter the list control. To filter the list, I open the Properties of the list control. I open the SQL statement that defines the list. I click Filter tab. I now have the following options:
                Value, Column, Expression, or SQL Select statement.
                * I decided to choose value and use an argument. No matter what I do, I cannot get this argument to properly filter.

                Yes, I have used json_generate to serialize the session variable into a character string. Yes I have tried setting my argument to both a character and a number type to filter the list. Yes I have tried setting my argument to be an XBasic function call instead of pointing to a session variable and just using the return value of my function. I cannot get this to work.

                I've tried reaching out to Alpha, they thought it was because I was not serializing my session variable which I have further clarified and am awaiting response. Seems like I need to find an alternate method of filtering the list, but as always I could be missing something.

                Comment


                • #9
                  Re: Filtering a list using an array or comma-delimited string

                  Alpha "thought" ??? or they tested it and showed you an example of how it's done. I'm guessing it was just a guess on their part. There is no indication that using a session variable and array_argument works in this scenario... there is no doc, there are no examples. It's a very simple test... here's hoping Alpha will provide some guidance.

                  And here's where I'm further confused. How is serializing the session variable supposed to help? What you get using a simple array is the string [41,42] . Is that what the List Array Argument wants? It doesn't seem so. And this is all guessing anyway. Alpha needs to provide an example.
                  Last edited by Davidk; 04-18-2019, 04:21 PM.

                  Comment


                  • #10
                    Re: Filtering a list using an array or comma-delimited string

                    Originally posted by Davidk View Post
                    Alpha "thought" ??? or they tested it and showed you an example of how it's done. I'm guessing it was just a guess on their part. There is no indication that using a session variable and array_argument works in this scenario... there is no doc, there are no examples. It's a very simple test... here's hoping Alpha will provide some guidance.
                    Basically some bad wording on my part caused some confusion. I said my session variable is declared like so:

                    Code:
                    session.ALLOWEDUSERS = json_generate(GetUsers(e))
                    Returns: [13, 5]

                    This is because when I look at the debugger it's returned like this:
                    ReturnValue.PNG

                    I knew it was a character string, I read the documentation (which is how I knew how to store the session variable using json_generate in the first place), but did not specify the quotation marks around the return value as the debugger did not include them. This cause confusion as they thought my function was returning a numeric array rather than a string. I was then told to use json_parse to convert the session variable back, but then I explained that I can't do that or else I can't store the value in the session variable to filter my list.... so... awaiting response. I'll make sure I update this as I get more info so others know what to do in this situation as well.

                    Comment


                    • #11
                      Re: Filtering a list using an array or comma-delimited string

                      Well this is hilarious. Got word back...

                      I was told I could instead point the argument to an XBasic function to get the value but not if it returns an array, Alpha doesn't support that. But if I wanted I could hire their consulting team to set up a complicated solution to the problem.

                      I don't need a consulting team... I need arrays to work with arguments like they're supposed to... WHAT!?!?!? I could write the logic to filter the list myself if I had the time. Come on now...

                      Comment


                      • #12
                        Re: Filtering a list using an array or comma-delimited string

                        So... Alpha is making suggestions... but not testing any information given to you.

                        The List Control supports Array_Arguments... but only under 1 condition... that the array comes from another List Control? I'd love to see the code that figures that out. Yikes.

                        Alpha created Array_Arguments... but only tested this feature by passing an array of data from one List to another... they never tested passing in an array from anywhere else. And now they won't help. Nice.

                        I'm shaking my head...

                        Since Alpha is digging in their heels and not helping, and knowing the Alpha Array_Argument doesn't work... let's see what else can be done. Do you care how your List is set up? Do you care if an XBasic function returns results for the List?

                        How about filtering the List client-side, after the List is rendered?

                        Comment


                        • #13
                          Re: Filtering a list using an array or comma-delimited string

                          You have no idea how shaken I am about that. They literally said in their email that it doesn't appear pointing an argument to an XBasic function supports arrays. I would call that a BUG with the software. How else do I get a numeric array if session variables don't support objects, converting it to a JSON string does not work, and XBasic arguments can't use arrays??

                          No worries about further steps from here. I have an alternate component handling my requirements differently to buy myself some time to figure out an actual solution. I'm certain I can figure out how to filter the list using alternate methods, but of course, that deadline is coming soon so I'm just gonna swap it out with a worse method and revisit it when I get more time.

                          Thanks for keeping me sane during this David. Now to avoid getting too angry about that sales pitch as to not get in trouble...

                          Comment


                          • #14
                            Re: Filtering a list using an array or comma-delimited string

                            Here's a really simple solution. It works here... but you'll need to test it in your environment to see if there are any issues. As infuriating as Alpha can be, is it extremely open. This results in letting you get in there and do stuff... but there's so much frustration because there is so little doc.

                            Assume your session var is something like this... it's just a comma delimited string... exactly what you'd use in a normal sql statement.

                            Code:
                            session.ALLOWEDUSERS = "41,43"
                            Now... in your List Control, Data Source tab, Server side beforeQuery Event... put this...

                            Code:
                            xb_ListQuery
                            And... in the UX XBasic functions... this function...

                            Code:
                            function xb_ListQuery as v (e as p)
                            
                            	e.SQL = "SELECT CUSTOMER_ID, FIRSTNAME, LASTNAME, COMPANY FROM tblcustomers WHERE CUSTOMER_ID IN (" + session.ALLOWEDUSERS + ")"
                            
                            end function
                            This will replace your List SQL Statement... not use array_arguments (which only work with other Lists)... and uses a simple IN or NOT IN statement.

                            Comment


                            • #15
                              Re: Filtering a list using an array or comma-delimited string

                              YOU ARE AMAZING!

                              This worked PERFECTLY, David. Thank you so much for taking the time to help out, now I can make it exactly how I wanted. You are the absolute best!

                              Comment

                              Working...
                              X