CODEWITHSUNDEEP
foreign-keysvisual-studio-lightswitchlightswitch-2013
genericuser
genericuser

Reputation: 1450

Filter FK field values in the drop down Lightswitch

I want to add a FK relationship to the field in the table and filter the values that are being displayed in the dropdown when edited.

For eg., The "Field1" in table "Lookup" contains values A1, A2, A3, B1, B2, B3. I want to add that as FK in table "Mapping" where I want to show values in A1, A2, A3 in one and B1, B2, B3 as another FK to the same table.

Can you please help. The screen should be something like this. When I add FK relationship each one shows all the dropdown when in edit mode. I am new to lightswitch.

enter image description here

Upvotes: 0

Views: 453

Answers (1)

Chris Cook
Chris Cook

Reputation: 2841

As covered by SwedishProgrammer, you can easily implement this by using additional queries against your lookup table. The following provides a step-by-step guide to implementing this (using your example of A and B prefixed lists):

  1. Add a new query by either right mouse clicking on the lookup table in the solution explorer and selecting 'Add Query' or by choosing the 'Query' button at the top of the table designer screen (to select this button you'll need to ensure that the 'Server' perspective is selected at the bottom of the table designer screen).

  2. Name the query (e.g. ALookups) and specify the required filter either in the query designer or by implementing the query's _PreprocessQuery general method (selected by using the Write Code\General Methods\_PreprocessQuery option at the top of the query designer). The following illustrates both of these techniques using a slightly different filtering approach for each (contains and starts with):

Add query

partial void ALookups_PreprocessQuery(ref IQueryable<Lookup> query)
{
    query = query.Where(q => q.Value.StartsWith("A"));
}

  1. Repeat steps one and two to create a second BLookups query checking for the letter "B".

  2. On the basis that you're using a Silverlight client (implied by your screen-shot) on your 'Editable Grid Screen' choose the 'Add Data Item...' button to add the ALookups query as follows:

Add Data Item

  1. Repeat step four to also add the BLookups query.

  2. Select your AutoCompleteBox for the Lookup A entry and, in the Properties pane, change the 'Choices' property from 'Auto' to the 'ALookups' query (the data item added in step 4) as follows:

Change the AutoCompleteBox choices

  1. Repeat step six to change the Lookup B entry to use the 'BLookups' query for its entry choices.

  2. This will provide separately filtered entries in the editable entry grid e.g.:

The resulting editable entry grid The resulting editable entry grid

You'll also need to bear in mind that the above steps will only change the choices in the grid entries. If you'd also like to change the choices in the Add and Edit dialog, you'll need to implement a similar approach as part of the solution detailed in the following blog post by the LightSwitch team:

Creating a Custom Add or Edit Dialog (Sheel Shah)

Finally, if you need to implement this on a LightSwitch HTML Client, the approach is essentially identical and is covered in a roundabout way in the following stackoverflow post:

Sorting data in Foreign key drop down lightswitch

Upvotes: 2

Related Questions