Alpha Video Training
Results 1 to 5 of 5

Thread: List Control Data Source Filter using SQL IN operator

  1. #1
    Member
    Real Name
    Frank Zwolinski
    Join Date
    Aug 2013
    Location
    Vernon, NJ USA
    Posts
    125

    Default List Control Data Source Filter using SQL IN operator

    My arguments reference controls on the UX.

    This syntax works fine when whatEmployeeID = 1189 and whatStatus = New
    where employeeid = :whatEmployeeID and status = :whatStatus

    This does not work when whatEmployeeID = 1189 and whatStatus = New,Pending
    where employeeid = :whatEmployeeID and status IN (:whatStatus)


    My action javascript "Filter records in a List Control" works fine by mapping the status field to the UX whatStatus control which is set to New,Pending. I then use Search option 201. (combine selections with OR). I need a way to insure my UX only pulls New and Pending when opening the UX.
    Since my app is disconnected, I perform my list refresh in onRestoreFromLocalStorage where I test if the list is dirty or clean BEFORE I execute the refreshListData method.

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

    Default Re: List Control Data Source Filter using SQL IN operator

    I almost understand this... but not quite.

    I need a way to insure my UX only pulls New and Pending when opening the UX.
    Opening the UX for the first time... or being restored from Local Storage?

    Select "New" and "Pending" all the time... or specifically whatever is in your "whatStatus" UX control?

    Your app is disconnected... but you're performing a connected operation... refreshListData. Does this mean you perform a refreshListData when you test for, and have, a connection?

  3. #3
    Member
    Real Name
    Frank Zwolinski
    Join Date
    Aug 2013
    Location
    Vernon, NJ USA
    Posts
    125

    Default Re: List Control Data Source Filter using SQL IN operator

    Here is my code in onRestoreFromLocalStorage.

    function runRefreshLogic()
    {
    var lObj = {dialog.object}.getControl('List_Expenses');
    var listDirty = lObj.listIsDirty();

    if (listDirty == false )
    {
    \\My default refreshed list should only show New and Pending. User can change filter to Approved, Paid, Disapproved or any combination AFTER the list is loaded.
    {dialog.Object}.setValue('SearchStatus','New,Pending');
    {dialog.object}.refreshListData('List_Expenses');
    }
    else
    {
    alert('You have data that was not saved to the server.\n Press Sync to save your data and then refresh.');
    }
    }
    runRefreshLogic();

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

    Default Re: List Control Data Source Filter using SQL IN operator

    There is a special setup of an Argument you can use to specify multiple values... for the IN Clause

    Rename your Argument whatStatus to array_whatStatus

    Your SQL WHERE clause will now be...

    Code:
    WHERE Status IN (:array_whatStatus)
    With the prefix 'array' in there, Alpha processes the values assigned to the argument. They must be CRLF delimited.

    I don't know how your whatStatus Control gets its "filter" values... but you'll need to change things around here a bit. Lots of ways to get this done.

    If your whatStatus control is a simple Textbox... then you could now set a default of...

    Code:
    ="New" + crlf() + "Pending"
    Because the default is first interpreted at the server, this default gets turned into a Javascript Array. Because it's an Array, the SQL IN Clause understands it... and you'll get New and Pending rows.

    However, you also need to let your users change the Status filter. The easiest way would be to change the TextBox control to a DropdownBox Control, change the DropDownBox Properties Height property to 2, Multi-select allowed to Multiple, and change the Field Properties Height to 3in (or whatever you want). Pay specific attention to what properties you're changing. You can set the DropDownBox Static Choices to the filter values you want to allow.

    When rendered, the whatStatus DropDownBox Control will show "New" and "Pending" selected, because that's what your Default is set to.

    You can now select single or multiple values from the DropDownControl and perform a refreshListData(). The "array" syntax of the Argument will interpret your DropDownControl properly and the IN Clause will work.

    Any multi-value control will work in this case I believe, although I've only tested the DropDownBox control.

    You can find this information in the Release Notes... which includes this video...
    http://www.ajaxvideotutorials.com/V1..._IN_Clause.swf
    Last edited by Davidk; 05-26-2017 at 01:23 PM.

  5. #5
    Member
    Real Name
    Frank Zwolinski
    Join Date
    Aug 2013
    Location
    Vernon, NJ USA
    Posts
    125

    Default Re: List Control Data Source Filter using SQL IN operator

    Thank you very much. I use a multi-select list control which returns a friendly view as "New,Pending,Paid" visible to the user and a hidden control whatstatus which will contain the CRLF delimited list: "New" + crlf() + "Pending" + crlf() + "Paid". The hidden control will be source of my array argument. The video was helpful too. Thanks again.

Similar Threads

  1. Replies: 6
    Last Post: 06-24-2016, 10:50 AM
  2. List control with custom data source and parent list
    By sjackson@drake in forum Mobile & Browser Applications
    Replies: 1
    Last Post: 07-01-2015, 09:26 PM
  3. Sharepoint list as data source
    By John_Titor in forum Mobile & Browser Applications
    Replies: 0
    Last Post: 06-24-2015, 08:39 AM
  4. How to use Calendar data control to filter list with date field?
    By dwsteven in forum Mobile & Browser Applications
    Replies: 0
    Last Post: 02-17-2015, 05:04 PM
  5. Action Javascript "Filter Records in a List Control" - data RANGE filter
    By JPMPA in forum Mobile & Browser Applications
    Replies: 4
    Last Post: 08-19-2014, 10:15 AM

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
  •