Alpha Video Training
Results 1 to 17 of 17

Thread: Filtering a list using an array or comma-delimited string

  1. #1
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

    Default 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. #2
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

    Default 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 at 11:09 PM.

  3. #3
    Member
    Real Name
    Sean OKelly
    Join Date
    Sep 2015
    Location
    Charleston, WV
    Posts
    205

    Default Re: Filtering a list using an array or comma-delimited string

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

  4. #4
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

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

  5. #5
    "Certified" Alphaholic
    Real Name
    Lee Vasic
    Join Date
    May 2010
    Location
    Salt Lake City
    Posts
    1,179

    Default 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)"

  6. #6
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

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

  7. #7
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

    Default Re: Filtering a list using an array or comma-delimited string

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

  8. #8
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

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

  9. #9
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

    Default 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 at 04:21 PM.

  10. #10
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

    Default Re: Filtering a list using an array or comma-delimited string

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

  11. #11
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

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

  12. #12
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

    Default 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?

  13. #13
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

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

  14. #14
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

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

  15. #15
    Member
    Real Name
    Cameron Smith
    Join Date
    Oct 2018
    Posts
    14

    Default 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!

  16. #16
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

    Default Re: Filtering a list using an array or comma-delimited string

    Excellent, Cameron... good to hear it worked out. Cheers.

  17. #17
    "Certified" Alphaholic
    Real Name
    David Kates
    Join Date
    Apr 2008
    Location
    Unionville, ON
    Posts
    7,496

    Default Re: Filtering a list using an array or comma-delimited string

    Today's pre-release, Build 5796, fixes the bug when using an Array_Argument and a session variable with comma-delimited values.

    This now works...

    Code:
    session.ALLOWEDUSERS = "41,43"
    This feature has also been extended to allow for a JSON array. This now works...

    Code:
    session.ALLOWEDUSERS = "[41,43]"
    Refer to... http://aadocuments.s3.amazonaws.com/...easeNotes.Html
    In this section... UX Component - Array arguments - Session Variables

    Here is the full note...
    UX Component - Array arguments - Session Variables - Arguments can be bound to session variables. Array arguments can also be bound to session variables and the session variable must be set to a comma delimited list of values in order for the array argument to be argument to be correctly populated. Now, the session variable whose value is a JSON array can also be used to populate an array argument.

    Also fixed today is an irritant when using the Portable SQL property... see the pre-release notes for this one.

Similar Threads

  1. How to simply populate an array from a comma delimited text file for individual cell manipulation.
    By chortle in forum Alpha Five Version 11 - Desktop Applications
    Replies: 23
    Last Post: 10-03-2016, 01:05 PM
  2. Wonderful .. Data from kids into comma delimited string and stuffed into parent table
    By MarionT in forum Application Server Version 11 - Web/Browser Applications
    Replies: 4
    Last Post: 03-17-2012, 02:52 PM
  3. Convert result of query into comma delimited string
    By WindForce in forum Application Server Version 11 - Web/Browser Applications
    Replies: 6
    Last Post: 03-01-2012, 11:50 PM
  4. how to query a list or a comma delimited list
    By hov333 in forum Alpha Five Version 10 - Desktop Applications
    Replies: 12
    Last Post: 02-29-2012, 03:05 PM
  5. Best commands to use on a comma delimited string
    By Graham Wickens in forum Alpha Five Version 7
    Replies: 2
    Last Post: 01-11-2007, 12:09 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •