locked
Search files and pass parameters RRS feed

  • Question

  • Hopefully someone can give me a nudge in the right direction.  I'm developing an inquiry only system for legacy software.  Everything is a piece of cake except my search screen.  There is one huge file that contains numerous fields from other master tables.  What I want to do is have a front-end with seven or eight fields that are keys to the other files.  I want to allow a search on each parameter, like the ellipsis or something that will allow the user to search and fill one or all the fields.  When enter or search is hit it will populate the parameters of the search file and process.  I have the main search setup with search parameters but it is too slow as it has to reprocess all those records.  I can't seem to find the right tutorials to get over the hump.  Everyone has been very helpful so thanks in advance for any assistance.

    Vern Miller

    Monday, April 22, 2013 1:16 AM

Answers

  • Vern,

    What you want to do is very easy with LS out-of-the-box.

    Simply create local table properties and queries for the ACB or MWP control lookups.  

    Create PreprocessQuery parms for the top level table FKs and set the parms based on the local table properties PK values. 

    If you require multiple result sets, just create tabs for each result set.

    Views can be used to combine tables that are searched on with drill-down links to specific details.

    Some of my search screens have 40+ values on multiple tabs for the criteria values that users need.

    I often give them buttons to hide/show the selection criteria so that they can see a full screen of row results to work with.  

    Here's a screenshot of a Search screen with 50+ values and the ability to select rows to process workflow actions.  


    Garth Henderson - Vanguard Business Technology

    • Marked as answer by VernMiller Tuesday, April 30, 2013 4:32 PM
    Tuesday, April 23, 2013 12:18 AM

All replies

  • Vern,

    I am working on ways to do similar things, but more for reporting purposes.

    As background, I have been using SQL Server for a while, so T-SQL is easy for me.

    I like to use work tables.  That is a TABLE with a foriegn key that relates to whatever process is currently running.  In other words, create tables as described below to represent the options searched for and the output of the search.

    So, let me lay out what I am thinking, and then if you need any examples, I can throw out a few lines of code for you.

    1. Create a Search Screen Work table for your search screen.  I choose to make my own database so I'm in control of everything, but there is no reason you cannot just use a LightSwitch table
    2. Create a Search Results Work Table for the search results, related to your Seach Screen Work table
    3. Create your seach screen using your Search Screen Table.
    4. Create an SQL trigger that runs the code for your search.  This Trigger Populates the Results Table.  You could have this trigger update a Status Column in your Search Screen Work table depending on the sucess of the update.
    5. Use the related Search Results Table to populate the grid on your screen showing the results of the search.

    The advantage of running the Search code on the server is that you get the speed of the SQL server, which optimized for data searches.

    Depending on how you set up your working tables, you might want to add code to auto-delete certain things.

    This method is related to the Command Table Pattern as shown.

    I entered this comunity asking for a review on using similar ideas for using this type of method.  While this is certainly much broader than what you are asking, if you choose, you can see what I am meaning by quickly scanning what I was asking, and some of my initial thoughts.

    Expert Review Wanted: LightSwitch Power SQL: Dynamic SQL using SQL Server

    As a note, what I am suggesting above would not be dynamic SQL.

    Also, I have not used this method YET (but will soon):  Many users (such as Yann) would probably recomend using a Ria Service:

    Creating A WCF RIA Service Using Entity Framework

    So, hopefully this will get your started, and let us know what your find is your best option!


    Would someone please turn on the LIGHT?

    Monday, April 22, 2013 4:38 PM
  • Vern,

    What you want to do is very easy with LS out-of-the-box.

    Simply create local table properties and queries for the ACB or MWP control lookups.  

    Create PreprocessQuery parms for the top level table FKs and set the parms based on the local table properties PK values. 

    If you require multiple result sets, just create tabs for each result set.

    Views can be used to combine tables that are searched on with drill-down links to specific details.

    Some of my search screens have 40+ values on multiple tabs for the criteria values that users need.

    I often give them buttons to hide/show the selection criteria so that they can see a full screen of row results to work with.  

    Here's a screenshot of a Search screen with 50+ values and the ability to select rows to process workflow actions.  


    Garth Henderson - Vanguard Business Technology

    • Marked as answer by VernMiller Tuesday, April 30, 2013 4:32 PM
    Tuesday, April 23, 2013 12:18 AM
  • Thanks much for your great input.  I've been out of town all day but will dive into the details in the AM.  Thanks again and I too am very comfortable with SQL so this approach looks promising.

    Vern Miller

    Tuesday, April 23, 2013 2:08 AM
  • Thanks Garth and that looks great. The print is too small to see on the example but I think I see where you're heading.  Thanks again and I will get after it tomorrow.

    Vern Miller

    Tuesday, April 23, 2013 2:10 AM
  • You can right click on the picture and save it to your desktop to enlarge it.

    Garth Henderson - Vanguard Business Technology

    Tuesday, April 23, 2013 6:02 PM
  • Thanks.  That's a nice looking approach.

    Vern Miller

    Wednesday, April 24, 2013 2:15 PM
  • Do you know of a resource where I could get a couple hours of one on one lightswitch training? No problem paying someone for their labor, I just can't seem to find anything.  With screen sharing I could get over the hump on a couple of tricky parts of my solution. Thanks again for all your help.

    Vern Miller

    Tuesday, April 30, 2013 4:35 PM