I am working on a document management module for DotNetNuke.

I was looking at sharepoint and I like the way that they allow a users to customized the number of columns that are displayed in a document library. I would like to duplicate the same type of behavior and was wondering if anyone has done something like this they would be willing to share.

I came across this

http://ycmi.med.yale.edu/nadkarni/Introduction%20to%20EAV%20systems.htm

but haven't had a chance to look at it fully

Bert

I am working on a way for users to create custom text, number, date, and picklist fields for various functional areas within my system. Once a custom field is defined it can be assigned to any functional area, and data can be captured in the custom field.

Is this what you mean?

Devildog74 wrote:

I am working on a way for users to create custom text, number, date, and picklist fields for various functional areas within my system. Once a custom field is defined it can be assigned to any functional area, and data can be captured in the custom field.

Is this what you mean?

yes... I wasnt planning on allowing the user to assign the custom fields to other areas but only to the current module.

For example, user adds new doucment module to a tab. This user can then add additional columns, tehy woudl submit

1. Type wants to track Single line of text Multiple lines of text Choice (menu to choose from) Number Currency Date and Time

2. Column Name

3. Is Required

and based on type they would have other options.

Bert

Before I begin, I got my idea from the way that Axosoft OnTime supports custom fields and picklists for defects and fetures. Ontime is free and you can see their DB schema and what it looks like after you make your changes. They do some things a bit differently than I will describe below, but the schema is pretty much the same.

Here is my architechture:

Table Specs:

Create Table CustomField CustomFieldId int identity primary key, FieldType int not null --Determines the data type / picklist, 1=Date, 2=Number, 3=Picklist, RecordType int not null --Determines the functional area 1=Organization, 2=Student, 3=Manager, etc., PickListId int not null default(0) -- is the PK of the picklist that is used for the custom field, StringSize int default 8, Format varchar(20) --is the .NET format, i.e. {0:0.00} for money, etc, DisplayOrder int, FieldLabel varchar(50) -- is the user defined name of the field.

Create Table OrganizationCustomFields OrganizationId int identity primary key

Thats it for tables. I have a XXXCustomFields table for each table that they can create custom fields for. Creation of new custom field entities cause an alter table to be executed on OrganizationCustomFields which creates the new field in the table using the appropriate TableFieldName and data type. Deletion of CustomFieldEntities also cause the field to be removed from the OrganizationCustomFields table using similar alter table logic.

Sample data CustomField: Row1: 1, Custom_1, 1,1,0,8,{0 },0,Date Created Row2: 2, Custom_2, 2,1,0,8,{0:0},0,Your Organization Id

So, after creating the 2 custom fields above, the OrganizationCustomField table looks like this: OrganizationId int identity, Custom_1 smalldatetime, Custom_2 int

After a user interacts with the UI, to add some data for an organization, the data in the OrganizationCustomField table will look like this:

1, 10/12/2004, 111222 2, 10/13/2004, 222111

My structure is a bit more advanced, because I need to factor in culture as well, because the dynamic data is localizable.

On the UI side of things, there a few things that need to be done to make your life easier.
Create some controller methods to fetch custom field labels. Create some controller methods to fetch custom field values.

In my scenario I have an organization entity, that has organization custom field data, as well as the relating custom fields collection for the given record type, i.e. Organization. So I can take these 3 objects and build controls for data collection and conrols for display purposes.

Because my XXXCustomFields entities are always changing, I have a stored proc that just says select * from XXXCustomFields where XXXId = @KeyValue. These types of stored procedures do code generate well.

Also keep in mind, that in my scenario, I dont make any business descisions whatsoever on the custom field data. It is just a mechanism that allows users to customize the data they want to select and display.

Hope this helps.

I appreciate you feedback and your sample code. The problem with altering the table is that each document module might support different custom columns.

For example

DocLibrary1 – has 3 custom fields DocLibrary2 – has 5 custom fields

For this to work correctly the table will need all 8 columns. When a module is deleted the columns would no longer be needed.

In the end I might do this, but what would be best would be an EAV system such as the one outlined in the link posted above. Just need to find the time to read and understand that long website. I was hoping that maybe someone had implemented an EAV system using LLBLGen.

Next question is how to build the search against this also…hum I thin k I will scratch my head with this one of a bit..

Thanks!

bert

Has anyone ever attempted to implement an EAV system in LLBLGen?