locked
Filter on 'starts with' RRS feed

  • Question

  • I have a table that has thousands of records in it and I want to allow the users to look up records in this table by typing a variable number of characters that will match the FIRST few characters of the Description field. I've used the 'contains' operator in the server-side queries, but that often returns many more records than I would like. I'm using the HTML client, no Silverlight.

    How can I accomplish this?


    db_csg

    Friday, December 20, 2013 5:06 PM

Answers

  • Try creating a custom query with an optional start-with search text parameter.

    Do not use this parameter in the custom query filter.

    Complete the rest of the query filter and order by as you wish.

    Add the filter logic to the pre-process event.

    namespace LightSwitchApplication
    {
        public partial class ApplicationDataService
        {
            partial void ContactSearch_PreprocessQuery(string SearchText, ref IQueryable<Contact> query)
            {
                if (!String.IsNullOrWhiteSpace(SearchText))
                    query = query.Where(c => c.Name.StartsWith(SearchText));
            }
        }
    }
    

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Friday, December 20, 2013 5:57 PM

All replies

  • Try creating a custom query with an optional start-with search text parameter.

    Do not use this parameter in the custom query filter.

    Complete the rest of the query filter and order by as you wish.

    Add the filter logic to the pre-process event.

    namespace LightSwitchApplication
    {
        public partial class ApplicationDataService
        {
            partial void ContactSearch_PreprocessQuery(string SearchText, ref IQueryable<Contact> query)
            {
                if (!String.IsNullOrWhiteSpace(SearchText))
                    query = query.Where(c => c.Name.StartsWith(SearchText));
            }
        }
    }
    

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Friday, December 20, 2013 5:57 PM
  • Thanks for your quick response. I'll give it a try and respond back.


    db_csg

    Friday, December 20, 2013 6:04 PM
  • When I key in the 'Partial' keyword I get a run-time error 'No defining declaration found for implementing declaration of partial method'. What does that mean and why am I getting that error?

    db_csg

    Thursday, December 26, 2013 10:36 PM
  • Vb or csharp? LightSwitch should be able to create everything apart from the "if" statement if you follow the "write code" menu. Cheers Dave

    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Thursday, December 26, 2013 10:50 PM
  • I'm using C#. I didn't have any problem with the If statement, but I get the error if I include the 'partial' keyword in the declaration. If I remove it, the error goes away, but I'm wondering if that's an indication of another problem?


    db_csg

    Thursday, December 26, 2013 10:56 PM
  • Are you putting the code in the correct place? If you follow write code menu from the custom query designer it can create the preprocess stub for you and open the code editor. Cheers Dave

    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Thursday, December 26, 2013 11:02 PM
  • I'm using C#, VS2012 with only the HTML client. I created a server-side query, then clicked the Write Code button which shows the source code file but did not create any stubs. Is the correct place? If so, why am I getting that error?


    db_csg

    Friday, December 27, 2013 2:54 PM
  • Click the down arrow button next to the Write Code button and select the method. The down arrow button is a separate button. Clicking the Write Code button itself will only generate the code file. Clicking the method will generate the method and the code file if it already does not exist.

    Justin Anderson, LightSwitch Development Team

    Saturday, December 28, 2013 12:24 AM
    Moderator
  • Ok, thanks for the tip. When I did that, it removed the SearchText string parameter from the method that Dave mentioned above, which in turn, generated errors because the code uses that parameter. Could that have something to do with the error that I'm getting? Maybe the 'partial' event handler doesn't allow for additional parameters?


    db_csg

    Saturday, December 28, 2013 8:31 PM
  • You will need to create the parameter in your custom query:

    'Try creating a custom query with an optional start-with search text parameter.

    Do not use this parameter in the custom query filter.'

    Dave


    Dave Baker | AIDE for LightSwitch | Xpert360 blog | twitter : @xpert360 | Xpert360 website | Opinions are my own. For better forums, remember to mark posts as helpful/answer.

    Saturday, December 28, 2013 8:38 PM
  • Ok, now I get it. I didn't understand exactly what you meant when you originally explained it. So I created the query parameter first and then it was available for me to use in code. So that part is working pretty good now. However, the way it's currently working is that all of the records get populated initially into a list on the dialog. The parameter, at the top of the screen allows me to key in my filter criteria which then repopulates the list with just the filtered items. I would like it to not populate the list initially, rather waiting for me to key in criteria first (in other words, its a required filter). I was able to do this on another screen when I was applying the parameter directly to the query within the query definition (rather than via code). Is it possible to get it to work this way via code as well? The parameter in both cases is not marked as optional. Thanks.


    db_csg

    Saturday, December 28, 2013 9:41 PM