Screen to display records having one to many relation and search also

Screen to display records having one to many relation and search also

  

hi guys,

I am developing a screen where I have to display the list of user records and each User record  has multiple record in another entity,For Example  like John Owns Many Vehicles.User Jon Owns multiple vehicles like car, bike, bicycle. both the entity is linked with UserID.  

I need to search for a  Record based on what the user owns like Car or Bike.I would like to display all the users who have a bike or a car 

Thanks for help

ali amin wrote:

hi guys,

I am developing a screen where I have to display the list of user records and each User record  has multiple record in another entity,For Example  like John Owns Many Vehicles.User Jon Owns multiple vehicles like car, bike, bicycle. both the entity is linked with UserID.  

I need to search for a  Record based on what the user owns like Car or Bike.I would like to display all the users who have a bike or a car 

Thanks for help


Hello,

What you could do for example its to add a ComboBox for the user to select if he wants to search for a Car or Bike or other options you might have and then if you have an aggregate, which im assuming you do, you need to add a filter which would be something like this:

User.Vehicle = VariableAssociatedToCombobox


VC


Hi Ali,

In the aggregate, Join Both Tables 



in Fileds, Select User name and apply group by



Then Hide other fields,


In Filter u can pass your search string to filter it from 

I hope it helps


thanks for the help guys

But grouping them will not able to display other field

here what i trying to do

I am able to display the user related vehicle by using web blocks but unable to perform search by user vehicle.



Solution

Hello Ali,

To be able to search by "vehicles" using an aggregate, you will have to join the tables and group by the fields you want to show.

See, you can group by more than one field, like you can group by Name, Email, MobileNo, etc. This way, you can join them, do the search, group by the fields to show and voilá. It's done.

The real question here is: Do you want to show the information of the "vehicles" for each user? If you don't need to show, than you don't need, as you will have in the groups all the information you need to show. You don't need to show information in order to search by it.

But, if you wants to show, you have some options. 

1. You simply use a web block, pass the user id, and do an aggregate to retrieve the info for that user and show it. This is by far the easiest way, but it's very "low performance", as you will have multiple access to the database, one for each user.

2. Another possibility is to use an SQL instead of an aggregate and add a field in the select that will be a subquery returning all the vehicles for the user using some technique to "append" each record in a line like you can see here.

3. You can also do the join without the group by and iterate the result storing the main info in a local list using a structure to hold data, and than concatenate as text the info about the vehicles and storing in a text field. Than you can use this info directly into the Record list.

Probably there are other ways.

Cheers,
Eduardo Jauch

Solution

Eduardo Jauch wrote:

Hello Ali,

To be able to search by "vehicles" using an aggregate, you will have to join the tables and group by the fields you want to show.

See, you can group by more than one field, like you can group by Name, Email, MobileNo, etc. This way, you can join them, do the search, group by the fields to show and voilá. It's done.

The real question here is: Do you want to show the information of the "vehicles" for each user? If you don't need to show, than you don't need, as you will have in the groups all the information you need to show. You don't need to show information in order to search by it.

But, if you wants to show, you have some options. 

1. You simply use a web block, pass the user id, and do an aggregate to retrieve the info for that user and show it. This is by far the easiest way, but it's very "low performance", as you will have multiple access to the database, one for each user.

2. Another possibility is to use an SQL instead of an aggregate and add a field in the select that will be a subquery returning all the vehicles for the user using some technique to "append" each record in a line like you can see here.

3. You can also do the join without the group by and iterate the result storing the main info in a local list using a structure to hold data, and than concatenate as text the info about the vehicles and storing in a text field. Than you can use this info directly into the Record list.

Probably there are other ways.

Cheers,
Eduardo Jauch


Thanks Eduardo, it's really helpful

can u just elaborate 3 point for displaying.

yes i understand storing the main info in local list but how to concat the list of vehicle with main info. can u explain it would be helpful



Hi Ali,

You can use SQL instead of an aggregate, and if you are using SQL Server, you can add a field in the SELECT clause like this:

    STUFF(
    (SELECT
    ',' + VehicleName
    FROM {Vehicle} v
    WHERE v.UserId = {User}.[Id]
    FOR XML PATH('')), 1, 1, ''
    ) AS Vehicles

If you want to know an example and how this works, you can se the example and this answer in Stack Overflow.

EDIT:

if you prefer to use the "full list" option.
You have to order the aggregate by user. Then, you iterate through the list. Every time you find a NEW user, you create a new record (using the structure that has the extra field) for the user, add  the info of the user and add the first fehicle (as string) to the extra field. Every new iteration, if it is the same user as before, you simply concatenate the vehicle name as string to the end of the actual extra field in the list.  

Cheers,
Eduardo Jauch

Eduardo Jauch wrote:

Hi Ali,

You can use SQL instead of an aggregate, and if you are using SQL Server, you can add a field in the SELECT clause like this:

    STUFF(
    (SELECT
    ',' + VehicleName
    FROM {Vehicle} v
    WHERE v.UserId = {User}.[Id]
    FOR XML PATH('')), 1, 1, ''
    ) AS Vehicles

If you want to know an example and how this works, you can se the example and this answer in Stack Overflow.

EDIT:

if you prefer to use the "full list" option.
You have to order the aggregate by user. Then, you iterate through the list. Every time you find a NEW user, you create a new record (using the structure that has the extra field) for the user, add  the info of the user and add the first fehicle (as string) to the extra field. Every new iteration, if it is the same user as before, you simply concatenate the vehicle name as string to the end of the actual extra field in the list.  

Cheers,
Eduardo Jauch

Thanks Eduardo