locked
Cascading Details Picker Lightswitch HTML RRS feed

  • Question

  • Hello,

    I have worked on this for so long, and am convinced that I am doing everything correctly. I very much need this to work, and for more queries than one, but I am starting here. I am following this walk-through by Beth Massi, and I need to know what is going wrong, or if this is a 'bug' of LS.

    Also, just a question: Do I really have to have a direct relationship between every table in SQL if I want to filter results from one entity to the next? That seems like a lot of anticipation in the SQL design phase, to account for any possible data filtering you will ever need and to make those relationships at the start...?

    Here is my process, from the beginning...

    Here is the table and its relationships. Relative to Beth's table, States = Buildings, Cities = Collections, and Customers = Devices. So, this would be my 'Cities' table...

    Next, I create the first query in the walkthrough...

    Now, I create my 'CitiesByState' query. For me, it is CollectionsByBuilding...

    Next, I add the Buildings local property and name it SelectedBuildings, just like Beth added the 'SelectedState'...

    I then drag SelectedBuilding onto the screen designer...

    Then I add the SortedBuildings query to the screen, just like she did the SortedStates...

    I then set the Choices of the SelectedState auto-complete box to SortedBuildings...

    I then add the CollectionByBuilding query, like she did the CitiesByState query...

    At this point, I get a little lost. Beth says to, "Next, add the CitiesByState query to the screen and set that as the Choices property of the Cities auto-complete box. Again, click “Add Data Item” and choose the CitiesByState query."

    But, at this point, I do not have a Cities (for me, it would be "Collections", not Cities)auto-complete box. What is my next move at this point?? I think this is where it fails but no matter what combination I try, I get this error when trying to set up the parameter binding...

    I know this is a lot, but this is a huge piece of this project that needs to be working, and I really appreciate anyone that has a look.

    Thank you so much in advance.


    • Edited by CreedCor Thursday, March 3, 2016 7:13 PM
    Thursday, March 3, 2016 5:31 PM

Answers

  • You can create a new query DeviceTypesByBuilding(int buildingId) as you should be able to easily set up a filter based on the passed BuildingId

    Similarly, you should be able to create another new query DevicesInBuildingByBuildingAndDeviceType(int buildingId, int deviceTypeId) as you will have those two parameters after executing the above query first after selecting the Building and Device Type.


    Regards, Xander. My Blog

    • Marked as answer by CreedCor Wednesday, March 9, 2016 8:55 PM
    Monday, March 7, 2016 8:35 PM

All replies

  • It looks like the parameter binding should be set to 'SelectedBuilding.IntBuildingID' instead of just 'SelectedBuilding'.

    The parameter is of type Int and you are trying to assign the Building entity to it which cannot be done. You need to assign an Integer type parameter to it.

    Once you select 'SelectedBuilding' in the parameter binding field, you can press the full stop (dot) key and it will use intellisense to show you the next available options to choose from.


    Regards, Xander. My Blog

    Thursday, March 3, 2016 8:26 PM
  • It looks like the parameter binding should be set to 'SelectedBuilding.IntBuildingID' instead of just 'SelectedBuilding'.

    The parameter is of type Int and you are trying to assign the Building entity to it which cannot be done. You need to assign an Integer type parameter to it.

    Once you select 'SelectedBuilding' in the parameter binding field, you can press the full stop (dot) key and it will use intellisense to show you the next available options to choose from.


    Regards, Xander. My Blog

    Well, that surely makes it simple. Amazing the things I can overlook. I guess, as they say, it is easy when you know how. Thank you!

    I am also correct in saying that we can only do thing kind of filtering over directly connected tables? 

    Thursday, March 3, 2016 8:55 PM
  • I am also correct in saying that we can only do thing kind of filtering over directly connected tables? 

    Not sure I quite understand that question... but you can only do this point-and-click type filtering with tables/queries directly added to the screen designer at design time.

    You can also write javascript code to call back-end queries directly. See this HTML API Reference and search for ".execute()".


    Regards, Xander. My Blog

    Thursday, March 3, 2016 9:04 PM
  • Thank you again.

    I am sorry, what I mean to ask is, is it only possibly to do this kind of filtering between data that have one-to-many relationships defined in the data source itself?

    For this entire example, I had to give my Buildings table a one-to-many relationship with the Collections table in the database for any of this to work.

    I am looking to do another cascading filter like this, but this time for Building > Device Type > Device, instead of Building > Collection > Device.

    Right now a Building can have many Devices, and a Device Type can have many Devices, but Building and Device Type are only 'connected' through the Device table. They do not have a relationship themselves. So I cannot use Devices's relationship to them both to connect Building and Device Type. To be honest that makes sense, I am just dealing with the database as it was built and given to me.

    So long question short, it looks like the one-to-many relationships have to be established in the datasource for any of this to work in LS.

    Thursday, March 3, 2016 10:28 PM
  • Short answer is yes, you can only do it when relationships are defined. 

    I take it that you are using a native LS data source over the back-end database. Although I've never used this myself, I believe that you can define/create your own relationships inside the LS entity designer. So it may make sense for you to define your own relationships in the designer in cases where the underlying database does not have built-in relationships.


    Regards, Xander. My Blog

    Thursday, March 3, 2016 11:58 PM
  • Short answer is yes, you can only do it when relationships are defined. 

    I take it that you are using a native LS data source over the back-end database. Although I've never used this myself, I believe that you can define/create your own relationships inside the LS entity designer. So it may make sense for you to define your own relationships in the designer in cases where the underlying database does not have built-in relationships.


    Regards, Xander. My Blog

    Hi Nova,

    I am not sure about the 'native LS data source' part. When I created the project I connected to an external SQL datasource which has all the tables. I just build querys, screens etc off of the tables that are in SQL.

    i always end up with errors when I try to Add Relationships in LS. For instance, I just added a one-to-many relationship between DeviceType and Building, which looked like it worked with no problem...


    until runtime...


    so, I digress.

    Friday, March 4, 2016 2:34 PM
  • So, after talking with my data design guy, I think he is right in saying that a DeviceType does not need to belong to a Building. In fact, it could complicate things.

    Without giving these 2 tables a direct one-to-many relationship, (where a Building can have many DeviceTypes), surely there is way to do this with LS without having to restructure all the data?

    Right now, the Device table is the center of the database. All tables have a relationship to it.

    A Building can have many Devices. A DeviceType can also have many Devices, but Device is the only relationship that both Building and DeviceType have.

    I started looking at this, and WCF_RIA services may help with it, but it looks like writing code for each object you would want to link. Simply put, it is beyond me at the moment.

    Is there a way to call SQL queries and return them on the screen? Or to at least spit them out? It would be kind of cumbersome to have a button for every query we would want to run, and not be able to set any parameters 'on the fly', but I am not seeing a way to do this otherwise.

    I guess I will start looking into what can be done with SQL queries in LS. This is a basic inventory system, so having a couple of parameters to filter criteria and return a list of results is going to have to work out somehow.

    Thanks a lot, Nova!

    Friday, March 4, 2016 8:19 PM
  • From a data modeling point of view it makes sense to me that you would not create a direct link between Building and DeviceType since DeviceType describes a type of Device and should therefore only be linked to Device. It is also bett to get the database relationships correct instead of having to do it in the LS entity designer. Note that you can also create database views and have those exposed in LS - this often provides a nice way to write complex queries.

    EDIT: note though that a database view will return a different entity type to you main entities that you might want to edit, so unless you want to make editable views (which is possible) they may work better for display-only purposes in grids and so on.


    Regards, Xander. My Blog


    • Edited by novascape Friday, March 4, 2016 9:56 PM
    Friday, March 4, 2016 9:45 PM
  • I assume you want to have a filtered list of DeviceTypes for a specific Building and they should be filtered based on the Devices linked to that Building, is that correct? Since there is a relationship between Building and DeviceType, via the Device, you should be able to write a query filter in LS that filters by BuildingID and returns the applicable DeviceTypes. Remember that you can use the full stop (.) to invoke intellisense when specifying the query filter.

    Regards, Xander. My Blog


    • Edited by novascape Friday, March 4, 2016 9:52 PM
    Friday, March 4, 2016 9:51 PM
  • I assume you want to have a filtered list of DeviceTypes for a specific Building and they should be filtered based on the Devices linked to that Building, is that correct? Since there is a relationship between Building and DeviceType, via the Device, you should be able to write a query filter in LS that filters by BuildingID and returns the applicable DeviceTypes. Remember that you can use the full stop (.) to invoke intellisense when specifying the query filter.

    Regards, Xander. My Blog


    Hello Nova,

    The request is to be able to filter Building by DeviceType and show a list of Devices that match criteria.

    Choose Building > Choose DeviceType > Display a list of matching Devices. Easily done with a direct relationship between Building and DeviceType. 

    I can create queries to manage this, but there are far too many DeviceTypes to write a query for each.

    What I may be able to do is create a query on my Building table for Building A. Create a screen for it and then add local property for DeviceType as a Details Picker and add Device off its relationship. Doing that, I will need a screen for each Building. I will try a few things when I am back in the office.

    Monday, March 7, 2016 8:25 PM
  • You can create a new query DeviceTypesByBuilding(int buildingId) as you should be able to easily set up a filter based on the passed BuildingId

    Similarly, you should be able to create another new query DevicesInBuildingByBuildingAndDeviceType(int buildingId, int deviceTypeId) as you will have those two parameters after executing the above query first after selecting the Building and Device Type.


    Regards, Xander. My Blog

    • Marked as answer by CreedCor Wednesday, March 9, 2016 8:55 PM
    Monday, March 7, 2016 8:35 PM
  • DeviceTypesByBuilding(int? buildingId) - create this query and name it 'DeviceTypesByBuilding' on the DeviceType entity , add the buildingId (optional) parameter, click on the Write Code -> DeviceTypesByBuilding_Preprocess_Query and add the following code (C# air code not compile tested):

    partial void DeviceTypesByBuilding_Preprocess_Query(int? buildingId, ref IQueryable<DeviceType> query)
    {
        if (buildingId > 0)
        {
    	// filter DeviceTypes by the Building that they are linked to via the Devices
    	query = query.Where(q => q.Devices.Any(p => p.Building.Id == buildingId));
        }
        // return unique DeviceTypes (avoid duplicates)
        query = query.GroupBy(p => p.Id);
    }

    DevicesInBuildingByBuildingAndDeviceType(int? buildingId, int? deviceTypeId) - create this query and name it 'DevicesInBuildingByBuildingAndDeviceType' on the Devices table in the same way as above with the following pre-processor code:

    DevicesInBuildingByBuildingAndDeviceType_Preprocess_Query(int? buildingId, int? deviceTypeId, ref IQueryable<Device> query)
    {
        if (buildingId > 0)
        {
    	// filter Devices by the Building
    	query = query.Where(q => q.BuildingId == buildingId);
        }
        if (deviceTypeId > 0)
        {
    	// filter Devices also by DeviceType
    	query = query.Where(q => q.DeviceTypeId == deviceTypeId);
        }
    }


    Regards, Xander. My Blog


    • Edited by novascape Wednesday, March 9, 2016 9:43 PM fixed code
    Wednesday, March 9, 2016 9:42 PM
  • Hello Nova,

    Reading over your last suggestion, I simply created a query in the query designer called DeviceTypeByBuilding, and used these parameters...

    It was so simple that I felt like I should really strip myself back and either learn some essential LS basics, or move on and start at ground zero with MVC or other Web Forms. This filter was so incredibly simple once I realized I could set it up off of the Device table. It is working now for what I need and I was just making it far too complicated, but your suggestion of 'connecting' Building and DeviceType via the Device Table really helped me. Thank you very much, once again.

    Wednesday, March 9, 2016 9:49 PM
  • Yeah, the query designer is quite powerful and you can create most queries with simple point and click.

    I'm just not sure whether you can do the Group By in the designer to ensure that you get a unique list of Device Types - for that you might have to write a line of code in the Preprocess_Query() method to group by as per my example above.


    Regards, Xander. My Blog

    Wednesday, March 9, 2016 9:55 PM
  • Right, with this method I do not get a list of DeviceTypes that are only in a given Building, but that is ok.

    It is very important with my Collections, though, but giving them a direct one-to-many relationship actually makes sense.

    Thank you!!

    Thursday, March 10, 2016 3:51 PM
  • Hi Nova,

    In general DB and query terms, do you think it would be possible to create a lightswitch query and subsequent screen that will display a list of all items that meet ANY criteria entered on the screen?

    For instance if I had a drop down for Building, Status, Content Area, Device Type, and Warranty...all of these drop downs on a screen, and the list basically show any item that meets WHICHEVER criteria is selected?

    Example:

    User chooses a Content Area and a Warranty type = the list displays any item that matches. User then adds a Building choice, and the list adjusts. User then takes away their Building choice, and adds a Status choice = query now shows items that match the Content Area, Warranty Type and Status criteria.

    All of these items would have to be Parameter Bound as they are different tables that all share a relationship with a central table.

    Can LS be that flexible?

    Tuesday, March 29, 2016 8:20 PM
  • LS is quite powerful when it comes to writing queries with parameters so I would say the answer is yes.

    I would personally however create the query with all the parameters being optional and then use the MyQuery_PreProcess_Query(int? param1, int? param2, int? param3, etc) to write the where clauses as per my two examples above. That gives you more power and flexibility through LINQ than using the visual designer only from experience.



    Regards, Xander. My Blog

    Tuesday, March 29, 2016 8:27 PM