none
Query with a parameter to return Items that have quantity (exist) in store

    Question

  • Query with a parameter to return Items that have quantity (exist) in store

    tables Items (ItemID) tables

    Inventory (StoreID-ItemID-Quantity)

    no relation exist between tables Items and inventory

     

    Select ItemID from Items
    Where ItemID In
    ( Select ItemID from Inventory Where Quantity>0 )

    I want to make StoreID as parameter in order to use it as a Screen parameter


    Saturday, January 14, 2012 1:16 PM

Answers

All replies

  • First off, WHY is there no rlationship between the Items & Inventory table?

    You're going to have a hard time doing it without a relationship.

    And I don't understand how StoreID has anything to do with your quesy. Can you explain a bit more what you're trying to do? I get teh "principle", just not the details.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.
    Sunday, January 15, 2012 5:41 AM
    Moderator
  • @yann

    I added a picture to my question ,I want to do a screen similar  to the shown picture Let replace Selected Category AutoCompleteList

    with Selected Store Auto Compete Box When User Select a Store It will display Only Items(Products) that exist in selected Store (So I want SoreID as parameter)

    There is no Relation between Inventory and Items actually because inventory is a View

    If it is impossible I will change Inventory from View to table I do it easily in windows form so I should be able to do it in lightswitch


    Tuesday, January 17, 2012 8:37 AM
  • You can achieve what you want without changing your schema.

    You can Create a RIA service for the Inventory and Create a relation between Item and Inventory. This is what I would do. The truth is that is LS when an underlying relationship is not "declaratively" defined in the domain it's hard to "infere" and extract information from it.


    1+1 = 3 for large values of 1
    Tuesday, January 17, 2012 9:14 PM
  • You can create a relationship between a SQL View (which when you attach to LS will be treated as a table, albeit read-only), as long as you provide the required CASTs in the view to coerce LS to correctly infer a Primary Key.

    If you need the view, or the joined tables to be editable, you'll need to go the RIA service route as Kostas mentioned.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.
    Tuesday, January 17, 2012 11:50 PM
    Moderator
  • Yann, can you please elaborate on this LS infering the PK? It's something I haven't managed to solve.

    Actually before suggesting RIA services this was the first thing I tested but the PK was an issue.

    Thanks in advance (I hope :-)).


    1+1 = 3 for large values of 1

    Wednesday, January 18, 2012 7:27 AM
  • @yann

     Will your suggestion work in my case ? I design my database then I attach it to lightswitch .

    @Kostas Christodoulou

    Could you please give more detail I am new to the world of  Ria service

    Could you provide tutorial,links in order to learn from  it,Ria Sevice is magic any thing you can't do in lightswitch use Riaservice

     

    What about using change event  or Selection change of Store AutoCompletBox,Can I write code to filter and fill Items Auto completbox ?


    Wednesday, January 18, 2012 8:05 AM
  • Here you can find a good example and a first tutorial

    http://lightswitchhelpwebsite.com/Blog/tabid/61/EntryId/47/WCF-RIA-Service-Combining-Two-Tables.aspx

     


    1+1 = 3 for large values of 1
    Wednesday, January 18, 2012 6:57 PM
  • It's actually an Entity Framework thing, not specifically LightSwitch.

    I thought I'd posted a link, but it must have been ina different thread, & of course I can't find it now.

    Have a look at this post.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.
    Thursday, January 19, 2012 8:13 AM
    Moderator
  • Thanks Yann, very neat trick. Of course I was also missing the nullable/not-nullable part :-).

    Thanks again


    1+1 = 3 for large values of 1
    Thursday, January 19, 2012 8:16 AM
  • You're welcome.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.
    Thursday, January 19, 2012 11:19 AM
    Moderator
  • Yann, I created a small post summing up what I learned about views from your information. Just informing you because you were the origin :-)

    1+1 = 3 for large values of 1

    Thursday, February 9, 2012 8:54 AM
  • Thanks for letting me know Kostas. I'm really glad I was able to steer you in a helpful direction.

    One suggestion. Your post gives the impression that LS is what is inferring the key, whereas you probably realise it's actually EF (as it's been mentioned in this thread), but readers of your post who haven't seen this thread may not realise that, & think it's something that LS is doing.


    Yann - LightSwitch Central - Click here for FREE Themes, Controls, Types and Commands
     
    If you find a reply helpful, please click "Vote as Helpful", if a reply answers your question, please click "Mark as Answer"
     
    By doing this you'll help people find answers faster.

    Saturday, February 11, 2012 10:40 AM
    Moderator
  • Thanks for the suggestion Yann. The truth is that although I have realised it's EF that does this I didn't make it obvious to the article now that I read back. In fact, as you note I really narrow it down to LS. I will fix this. Thanks again for the remark  and thanks for taking the time to read it and comment on it.

    1+1 = 3 for large values of 1

    Monday, February 13, 2012 7:38 AM