I have a question concerning the way dynamic data filters records in foreign key fields in edit mode. Here is my application as an example:
I have a table called people which has a one to many relationship to a table called addresses (to keep historical address records) as well as a one-to-one foreign key relationship to determine which of those multiple addresses is the current active address.Dynamic data displays the one-to-one as a drop down list in edit mode but the list is populated with every address in the table to choose from.Is there a way to filter it so that the drop down list is populated with only the possible addresses that already belong to that person rather than every address in the table (including those that pertain to other people)?
Scott, so how would you filter out the values in the drop down using a filter? As far as I understand filters used to filter data in the table, not to filter possible values of a drop down list when editing a particular record.
I also have a similar question on filtering of values listed in the Foreign-key drop down list.
I checked your sample and am not sure whether exactly the filtering took place.
In addition, what I want to do is a simple filtering not cascading effects.
For example, I have two foreign-key (F1 and F2) drop down list in a list screen.
They both refer to a same master table, let's say, Categories in Northwind DB.
In F1 drop down list, I would like to only display a list of products whose category is Seafood and likewise I would like to have a product list in F2 drop down whose category fall under a "Beverage".
Would you be able to show me how it can be accomplished?
In F1 drop down list, I would like to only display a list of products whose category is Seafood and likewise I would like to have a product list in F2 drop down whose category fall under a "Beverage".
How flexible do you want this to be? Do you want to be able to set this via metadata?
Dynamic Data
See my blog C# Bits | Twitter @sjnaughton Always seeking an elegant solution.
Hi Gijigae, having thought about your requirements which are non standard I would just create a custom field template for each variant, (I could never see a situation where I use one table to store data for different relationships).
So alll you need to do is copy the default ForeignKey both template rename appropriatly and then add code to replace the PopulateListControl method:
public new void PopulateListControl(ListControl listControl)
{
const string STR_CategoryId = "CategoryId";
var uiHint = MetadataAttributes.OfType<UIHintAttribute>().FirstOrDefault();
if (uiHint.ControlParameters.Count == 0 || !uiHint.ControlParameters.Keys.Contains(STR_CategoryId))
throw new InvalidOperationException(String.Format("Parameter field is missing from UIHint on {0}", Column.Name));
var categoryId = (int)uiHint.ControlParameters[STR_CategoryId];
var OC = new Models.NorthwindDataContext();
var values = from p in OC.Products
where p.CategoryID == categoryId
select p;
foreach (var p in values)
{
listControl.Items.Add(new ListItem()
{
Text = p.ProductName,
Value = p.ProductID.ToString()
});
}
}
When you add your UIHint you can the add extra info the field template requires:
Thank you. Is there any way of making the reference to the tablename in more generic form?
var OC = new Models.NorthwindDataContext();
var values = from p in OC.Products // How to make this line to match current tablename?
where p.CategoryID == categoryId
select p;
What I mean is changing the code to be valid not just for OC.Products but OC.Table where Table would be the current table name, so you could have OC.Products, OC. Catalog, OC.Patio, OC.Metals, OC.Hadware, OC.Software, etc.
I am assuming that the where condition could be built on the fly as well. It might as well be that such condition could be the same for every table. In the event that they were different you could create some kind of switch statement as to make finer
adjustments to your code, is that possible too?
I apologize but my previous consideration is wrong. I have realized that when working with filters the reference to the table must be made to every filter-table not to the current table. Moreover and taking in consideration that the hierarchy among the filters
must accomplish the same filtering for every combo box then it makes things much, much harder to understand.
But even like this, it could be good to know how to make reference to the table name as a little tutorial on ASP.Net
I must add this: I made changes to every data source in my pages as to have available my filtering conditions for any table. My filtering conditions must accomplish two different criteria: the authorization level of logged user and the type of business he
or she is allowed to see.
Without hierachichal filtering the datasources work just fine but what is called cascading follows their own rules so the filtering is bypassed by this feature and it makes its own population of the combo boces so I must solve the issue that I have to reenter
my filtering conditions to the hierarchichal filters again. It sounds redundant but the feature, as provided, helps a lot so I want to use it but filtering accordingly
szDevGuy
0 Points
1 Post
Filtering foreign key drop down list question.
Dec 22, 2008 06:02 PM|LINK
I have a question concerning the way dynamic data filters records in foreign key fields in edit mode. Here is my application as an example:
I have a table called people which has a one to many relationship to a table called addresses (to keep historical address records) as well as a one-to-one foreign key relationship to determine which of those multiple addresses is the current active address. Dynamic data displays the one-to-one as a drop down list in edit mode but the list is populated with every address in the table to choose from. Is there a way to filter it so that the drop down list is populated with only the possible addresses that already belong to that person rather than every address in the table (including those that pertain to other people)?
Dynamic Data foreign key filter
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Filtering foreign key drop down list question.
Dec 22, 2008 09:20 PM|LINK
I think your looking at a custom filter to that but to get a custom filter you will need to add Filtering from DD Futures:
Hope this helps [:D]
Dynamic Data Futures Filters
Always seeking an elegant solution.
rroyter
Member
6 Points
11 Posts
Re: Filtering foreign key drop down list question.
Mar 25, 2009 02:37 AM|LINK
Scott, so how would you filter out the values in the drop down using a filter? As far as I understand filters used to filter data in the table, not to filter possible values of a drop down list when editing a particular record.
Thanks,
Roman
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Filtering foreign key drop down list question.
Mar 27, 2009 08:37 AM|LINK
Hi Roman here's my example on Cascading Filters – for Dynamic Data v1.0
Also see Cascading or Dependant Field Templates for ASP.Net 4.0 Preview
Steve [:D]
Dynamic Data Cascad FieldTemplate Cascading Filters
Always seeking an elegant solution.
gijigae
Member
26 Points
56 Posts
Re: Filtering foreign key drop down list question.
Nov 07, 2010 02:04 PM|LINK
Dear Steve,
I also have a similar question on filtering of values listed in the Foreign-key drop down list.
I checked your sample and am not sure whether exactly the filtering took place.
In addition, what I want to do is a simple filtering not cascading effects.
For example, I have two foreign-key (F1 and F2) drop down list in a list screen.
They both refer to a same master table, let's say, Categories in Northwind DB.
In F1 drop down list, I would like to only display a list of products whose category is Seafood and likewise I would like to have a product list in F2 drop down whose category fall under a "Beverage".
Would you be able to show me how it can be accomplished?
Best regards,
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Filtering foreign key drop down list question.
Nov 07, 2010 03:08 PM|LINK
Hi Gijigae,
How flexible do you want this to be? Do you want to be able to set this via metadata?
Dynamic Data
Always seeking an elegant solution.
gijigae
Member
26 Points
56 Posts
Re: Filtering foreign key drop down list question.
Nov 08, 2010 03:07 PM|LINK
Hello Steve,
I love the idea of setting it via metadata. That will give us great flexibility.
Best regards,
sjnaughton
All-Star
27308 Points
5458 Posts
MVP
Re: Filtering foreign key drop down list question.
Nov 09, 2010 10:31 AM|LINK
Hi Gijigae, having thought about your requirements which are non standard I would just create a custom field template for each variant, (I could never see a situation where I use one table to store data for different relationships).
So alll you need to do is copy the default ForeignKey both template rename appropriatly and then add code to replace the PopulateListControl method:
public new void PopulateListControl(ListControl listControl) { const string STR_CategoryId = "CategoryId"; var uiHint = MetadataAttributes.OfType<UIHintAttribute>().FirstOrDefault(); if (uiHint.ControlParameters.Count == 0 || !uiHint.ControlParameters.Keys.Contains(STR_CategoryId)) throw new InvalidOperationException(String.Format("Parameter field is missing from UIHint on {0}", Column.Name)); var categoryId = (int)uiHint.ControlParameters[STR_CategoryId]; var OC = new Models.NorthwindDataContext(); var values = from p in OC.Products where p.CategoryID == categoryId select p; foreach (var p in values) { listControl.Items.Add(new ListItem() { Text = p.ProductName, Value = p.ProductID.ToString() }); } }When you add your UIHint you can the add extra info the field template requires:
[UIHint("ByCategory", null, "CategoryId", 4)] public Product Product { get; set; }Here I am adding a paramter "CategoryId" with a value of 4
Dynamic Data Custom Field Templates
Always seeking an elegant solution.
gijigae
Member
26 Points
56 Posts
Re: Filtering foreign key drop down list question.
Nov 09, 2010 03:48 PM|LINK
Hello Steve,
Thanks again for your concise and kind reply!
It worked very well.
Would it be possible to specify the "CategoryID" at run time?
I would like to filter some of the drop-down list based on who logged in.
Many thanks,
PeterSkeller...
Member
245 Points
155 Posts
Re: Filtering foreign key drop down list question.
Nov 16, 2010 08:41 PM|LINK
Thank you. Is there any way of making the reference to the tablename in more generic form?var OC = new Models.NorthwindDataContext();var values = from p in OC.Products // How to make this line to match current tablename?where p.CategoryID == categoryIdselect p;What I mean is changing the code to be valid not just for OC.Products but OC.Table where Table would be the current table name, so you could have OC.Products, OC. Catalog, OC.Patio, OC.Metals, OC.Hadware, OC.Software, etc.I am assuming that the where condition could be built on the fly as well. It might as well be that such condition could be the same for every table. In the event that they were different you could create some kind of switch statement as to make finer adjustments to your code, is that possible too?I apologize but my previous consideration is wrong. I have realized that when working with filters the reference to the table must be made to every filter-table not to the current table. Moreover and taking in consideration that the hierarchy among the filters must accomplish the same filtering for every combo box then it makes things much, much harder to understand.
But even like this, it could be good to know how to make reference to the table name as a little tutorial on ASP.Net
I must add this: I made changes to every data source in my pages as to have available my filtering conditions for any table. My filtering conditions must accomplish two different criteria: the authorization level of logged user and the type of business he or she is allowed to see.
Without hierachichal filtering the datasources work just fine but what is called cascading follows their own rules so the filtering is bypassed by this feature and it makes its own population of the combo boces so I must solve the issue that I have to reenter my filtering conditions to the hierarchichal filters again. It sounds redundant but the feature, as provided, helps a lot so I want to use it but filtering accordingly