How to get values from multiple tables in a single report (data visualisation)

[GlideFather]

Ahoy Now Ninjas!

I am looking for your ideas and suggestions how to get values from more tables into one report. I have heard of database views but we either do it wrong or it's not achievable so I would like to hear from others.

My goal is to have Demand (+ its fields to dot-walk:  Number, Name, Category, etc), Change Request (+ dot-walked fields) and Stakeholders (+ its fields).

For demands with Type: Change I can create a change by the button in related link, it adds the change ID to the parent field and I can use it but if a change is associated from the related list, then it is not stored on the demand form.

I have tried building that report on more tables: dmn_demand, dmn_demand_task, task, dmn_m2m_dmn, change_request but I was never able to have everything with dotwalking.

Is there any hack to get these?

PS: it's preferred to avoid creating custom fields or custom logics

[Ankur Bawiskar]

@GlideFather 

if those 3 fields have something in common or reference field pointing to each other then database view should help you

I could see from screenshots so this is feasible

Stakeholders -> has reference field pointing to Demand

Change Request -> has reference field parent pointing to Demand

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[GlideFather]

Thanks @Ankur Bawiskar, the change associated through Parent field would work, is there a chance for the change added in the related list? CHG009 in my screenshot

[Ankur Bawiskar]

@GlideFather 

sorry I didn't get CHG009 has parent field holding Demand

So that field can be used in DB view join

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[GlideFather · Accepted solution]

Closing this thread - it was achieved by a database view.

• Create a new database view [sys_db_view],
• Add all the required tables in the view Tables related lists,
• Give them prefix and order,
• Then apply conditions/relationships

Once you do this, under the Try it button in the related lists, it redirects you to a list (table called u_demand_reporting in my case) where you can access all the fields.

For example if you search for Number, you will see 3 out of 3 tables, because stakeholder doesn't have any number:

• ✅ dm_number for Demand's number,
• ✅ dt_number for Demand Task's number,
• ✅ chg_number for Change request's number,
• ❌ no number for stakeholder

If you search for Stakeholder's fields (e.g. Level of interest, Approver, Assessment recipient), you will see it only once because the other 3 tables don't have these fields.

It is applicable for OOTB as well as custom fields as per defined tables.

Checkbox for Left join:

 

and if needed, you can also add Table's fields:

The final list can be further grouped or filtered like it was a single table: