Okay I have been struggling with this for months now. I have it hacked together in a dirty, dirty manner right now and I want to get it right! What I have is a table called StringMap is contains strings for various things throughout my database. It has a composite key and looks like this:

StringMap

k ObjectType int k AttributeName nvarchar k AttributeValue int k LangId int Value nvarchar DisplayOrder int

A sample record my look like this: ObjectType = 1 AttributeName = "StatusCode" AttributeValue = "1" LangId = "1033" Value = "Cannot Contact" DisplayOrder = 2

So I have another table with SalesSubject (customers) values may look like

SalesSubject

other fields . . . . . . . . OjbectTypeCode = 1 StatusCode = 1 QualityCode = 4 . . . . . . . .

Now what I want to do is get the Value(Cannot Contact) of the StringMap Table (string representation of StatusCode 1 or QualityCode 4....whatever). I would like to use the Fields based on related entities but I cannot figure out how to relate the tables (through sql or llblgen) as the relation contains a predicate expression with constant strings like: where AttributeName = "StatusCode" and LangId="1033"... I have a bunch of values in this StringMap and use them all over the place so I would like to get this right (like I said it is really hacked together right now). Is this even possible? How the heck do I do this?

I'm not sure exactly what you need. So how are the fields used in SalesSubject to relate to StringMap. What is it that they are trying to accomplish?

The StringMap is like a big generic multilingual lookup table. For instance say an Employee has a EmploymentStateCode = 2. Obviously this doesn't mean much to the average user. So one would want to look up the human readable string value of EmploymentStateCode 2. This would be done by a query to the StringMap table saying We are dealing with an employee record = ObjectTypeCode of say 3 we are also looking for the EmploymentStateCodes so AttributeName = "EmploymentState" where the attribute value = 2. The StringMap record that we are looking for would look like this:

ObjectTypeCode=3(employee object) AttributeName = "EmploymentState" AttributeValue = 2 LangId = 1033 (english) -->Value = "Terminated"<--

Make sense? For clarification the EmployeeRecord has the ObjectTypeCode=3, EmploymentStateCode = 2, LangId 1033. I run into trouble with the string literal "EmploymentState" How can I create an relationship based on this constant string?

I think the problem is that you are using One Table instead of Two..

I'll explain (As I understand it).

To have a dynamic object attributes (defined by the user/admin), you use a table to define those attributes and their display names (AttributeValue/AttributeName)...right?

Thus for Employees, we can have "Salary, EmpNumber, ....etc" for Orders, we can have "OrderDate, OrderNumber...etc"

This is fine to be stored in one table.

But yet you need another table to store the individual values for each Entity. I mean the values for those attributes for Each Employee record.

So for Sam the AttributeName = "EmploymentState" holds the value "Terminated" But for Walaa it holds the value "Promoted"

And then you have 2 options: 1- You can have one Attributes Table and one Values Table for each Object in your system. Without the need of the "OjbectTypeCode" to differentiate them.

2- You may have only one Attributes Table and one Values Table for all you objects. (As you did)In that case you should use a discriminator field "OjbectTypeCode". And either create database views for each database object using OjbectTypeCode as a filter, and use LLBLGen Pro to map these views as Entities and define their relations with the other entities. Or use LLBLGen Pro to define hierarchy of type TargetPerEntityHierarchy using the OjbectTypeCode as the discriminator.

I am still confused why would I need StringMap Sub-Entities based on ObjectTypeCode when I have that information in the Employee table. The whole ObjectTypeCode is really not much of an issue. You could think of this value as an Employee State rather than an object type. The information is in the employee table, so I can do a simple join of that data. The problem is saying for Employee.StatusCodeId = 3 give me the StringMap.Value = "Executive"

Basically if I try to create a Cosutom entity relation here is what I am looking at:

SalesSubject (FK) - Stringmap (PK) StringMap Employee

Primary Key field name Foreign Key field Name

ObjectTypeCode ObjectTypeId AttributeName (would like to put in string literal like) "statuscode" AttributeValue StatusCode LangId LangID

Even if I crate a subentity with discriminator AttributeName = "statuscode" called StatusStringMap I cannot map the entity.

think that I am just going to create separate tables for each type of code. It will be alot less of a pain!