Database view, many to many result on same row

[simonbergstedt]

Hi,

I'm struggling with a request regarding creating a report where an asset manager wants a list of all assets with (among other things) the related contracts. Now I have created the a report that gets him the fields that he wants but now he says that he wants multiple contracts that is related to the assets on the same row in the report. Atm I'm getting them on separate rows which basicly means that the rows are identical apart from the contract bit.

Anyone knows is this is at all possible to achive using reports, database views or would you need to something more developerish?

Tables used are alm_asset and clm_m2m_contract_asset.

[Kalaiarasan Pus]

I do not think this is possible using database views or report. Wouldn't doing a group-by of records on some field serve the purpose of grouping the data?

[Gaurav Bajaj]

Hi Simon,

As per my understanding, you dont want multiple rows for same asset like in example below

If yes, in that case, you need some customization to be done.

1) Create a new reference list field of contract type on asset table.

2) Write a BR on clm_m2m_contract_asset table which will populate this ref list field every time a contract is attached to the asset.

3) This way, you will have contact details on your asset table itself and you can export the data directly from an asset with a single row for each asset.

Thanks

Gaurav

[simonbergstedt]

Thank you, I was thinking along those lines but wished I wouldn't have to resolve to that But now I have a follow up question. It looks like I don't have the ability to dotwalk when using a reference list and it's "only" comma separated in the report? I have a feeling the person that wants this list will want to see not only the contract numbers but the model of those contract numbers, end date, etc in the same report on a single row.

[Gaurav Bajaj]

Hi Simon,

That's the point I forgot to add up in solution, this will present you the only list of comma separated contract numbers.

I am afraid that beyond this point, you might have to think about implementing the solution in a dynamic block ( where you will have to render all the desired fields from asset and contract table in an HTML format).

Obviously, this will be a tedious job but that's all I can come up with.

I guess you can consider the option of grouping the asset record in the database view as kalai suggested.

Thanks

Gaurav