version 2.5 (self-servicing) VS2005 asp.net 2.0

hiya,

I need to add additional fields from a different table to a typedList.

I've seen the following thread, and know that I need to do something similar:

http://llblgen.com/TinyForum/Messages.aspx?ThreadID=8110&HighLight=1

So, it's not a case of me re-using fields that are in this particular typedList, I want to add fields that are in a different typedList.

eg, let's say the current typedList contains info about a person, eg name , address etc. The fields come from tblPerson.

personId PK personName

I've been asked to display each person in a grid with their skills in the same field as that of their name,

eg "John (builder, baker)"

I already have this in a separate typedList, tlistPersonSkill

My schema is as follows:

tblPersonSkill personId PK skillId PK

tblSkill skillId PK skillName

What do I do, do I try to re-use the tlistPersonSkill within the

' __LLBLGENPRO_USER_CODE_REGION_START InitClass

section?

many thanks,

yogi

Just to clear everything. Could you write the SQL Query that should produce the required resultSet?

hiya Walaa,

My sql skills aren't great.That's why I bought llblGenPro.

What I have done is grabbed the generated sql from the 2 typed Lists.

typedListONE is the one that I bind to the datagrid

typedListTWO is the sql that will generate the result set that I want to APPEND to the "RegisteredName" field of typedList1

typedListONE:

SELECT [dbo].[tblDog].[dogId] AS [DogId], [dbo].[tblDog].[sireId] AS [SireId], [dbo].[tblDog].[damId] AS [DamId], [dbo].[tblDog].[registeredName] AS [RegisteredName], [dbo].[tblDog].[dateOfBirth] AS [DateOfBirth], [dbo].[tblDog].[dateOfDeath] AS [DateOfDeath] FROM [dbo].[tblDog] WHERE ( [dbo].[tblDog].[dogId] = @DogId1)',N'@DogId1 int',@DogId1=7

SELECT DISTINCT [dbo].[tblDog].[dogId], [dbo].[tblDog].[registeredName] AS [RegisteredName], [dbo].[tblDog].[damId] AS [DamId], [dbo].[tblDog].[sireId] AS [SireId], [dbo].[tblDog].[dateOfBirth] AS [DateOfBirth], [LPA_M2].[registeredName] AS [motherName], [LPA_F1].[registeredName] AS [fatherName]

FROM (( [dbo].[tblDog] [LPA_M2]

RIGHT JOIN [dbo].[tblDog] ON
[LPA_M2].[dogId]=[dbo].[tblDog].[damId])

INNER JOIN [dbo].[tblDog] [LPA_F1] ON [LPA_F1].[dogId]=[dbo].[tblDog].[sireId])

WHERE ( ( [dbo].[tblDog].[damId] = @DamId1))',N'@DamId1 int',@DamId1=7

typedListTWO:

SELECT DISTINCT [dbo].[tblDogTitle].[dogId] AS [DogId], [dbo].[tblDogTitle].[titleId] AS [TitleId], [dbo].[tblTitle].[titleName] AS [TitleName]

FROM ( [dbo].[tblTitle]

RIGHT JOIN [dbo].[tblDogTitle] ON
[dbo].[tblTitle].[titleId]=[dbo].[tblDogTitle].[titleId])

WHERE ( ( [dbo].[tblDogTitle].[dogId] = @DogId1))',N'@DogId1 int',@DogId1=7

Does the above make sense?

Please let me know if I should clarify.

many thanks,

yogi

eg "John (builder, baker)"

I already have this in a separate typedList, tlistPersonSkill

My schema is as follows:

tblPersonSkill personId PK skillId PK

tblSkill skillId PK skillName

From the above I understand that you want to have something like a pivoting behaviour. Where you get values from different rows (from the tblSkill) to be displayed in one row (cell).

The best approach I can think of is to build this in code. As such: You may a property to the main entity (tblPersonSkill or tblDog), and call it LongDesc for instance. Then in the Get of this property you loop on the related Collection to get required field of each entity in this collection and append it to the output of the LongDesc property.

Then you should fetch the collection instead of the TypedList.

Also for databinding and stuff you can add properties for the [motherName] and the [fatherName], this can be done automatically from the designer from the the Fields Mapped On Related Fields sub-tab.

hiya Walaa,

Thanks for the reply.

I need the functionality for databinding.I'm already aware of being able to bind to aliases in a typedList.That isn't the issue.

So, as you say..

From the above I understand that you want to have something like a pivoting behaviour. Where you get values from different rows (from the tblSkill) to be displayed in one row (cell).

Correct, however, the typedListOne (tblDog) already binds to the grid.

What I need to do is ADD values from different rows (from the tblSkill) to be displayed in one row (cell)

Therefore, my grid will be as follows

column1 column2 etc tListOne field tblSkillFields etc

So, I need to keep typedListONE and add the fields from typedListTWO (tblSkill) TypedListONE is quite complex.

You may a property to the main entity (tblPersonSkill or tblDog), and call it LongDesc for instance. Then in the Get of this property you loop on the related Collection to get required field of each entity in this collection and append it to the output of the LongDesc property.

Then you should fetch the collection instead of the TypedList.

As I mentioned, typedListONE is quite complex.Why can't I just add a column to it that contains these values?

If I have to discard typedListONE, then that means that I have to somehow re-create it as a collection??

..this seems like duplicated effort.

Obviously, all of this is new to me.Why do I have re-create typedListONE as a a collection?

Thanks,

yogi

As I mentioned, typedListONE is quite complex.Why can't I just add a column to it that contains these values?

If I have to discard typedListONE, then that means that I have to somehow re-create it as a collection??

Ok, in order not to discar that complex TypedList, here what I think you can do. (I think this can be better done in code as follows) 1- Fetch the TypedList as is. 2- A TypedList is a DataTable 3- You can later add an extra Column, to hold the required values. 4- Then you should fetch the other table by using the other TypedList or by using an EntityCollection. 5- You can create a routine that grabs the required data from the other fetched table into the extra column of the first TypedList.

hiya Walaa,

That worked great. Thanks to yourself, Daelmo and Goose.

I have a slight other issue, but that's best left for another post.

cheers,

yogi