How to make the fields visible on table created with database view

[sath]

Hi,

We have created a database view with story(rm_story) and metric instance(metric_instance) tables and generated a report on it.

When the story state is blocked, substate and blocked reason fields will be visible on the form and those fields are populated with values on the report as well. Once the story is moved to any other state from blocked, substate and blocked reason values returns empty. I believe this is happening because the report is on database view and it will display only the fields visible on the form at the time it is retrieving data. What other options do we have to get those fields with values displayed all the time on the table/report?

[Maik Skoddow · Accepted solution]

Hi @sath 

Database views do NOT respect visibility in any forms, as they combine the ENTIRE tables with ALL it their containing columns!

Therefore, after adding tables, you must define which fields from each table should be visible in the database view.

This is done by adding entries to the View Fields related list under each View Table record. If you do not specify any fields in the View Fields list for a table, all fields from that table will be included by default. However, if you specify even one field in View Fields for a table, you must explicitly add all fields you want from that table; otherwise, only the specified fields will be visible. This step is crucial because if you add no fields after specifying some, the list may appear blank (no data visible).

Maik

[sath]

Hi @Maik Skoddow , @Ankur Bawiskar 

We have not added any fields on 'View Fields' related list since we want all the fields to be displayed. Here's the database view configuration:

When a story state is set to blocked and substate & blocked reason are filled with values, database view fields are populated like below.

Also, substate and blocked reason should have been filled for only blocked state row, instead it is populated for draft state row too which should not be the case.

After the story is moved from blocked to any other state, substate and blocked reason returns empty.

Can you please point out the issue here?

[Maik Skoddow]

Hi @sath 

there is no issue, as it is working as expected! A database view is a snapshot of LIVE data and NOT a recording like in performance analytics. In case the status of your story is changed, ALL records in your database view related to that story are displayed with the current story values. This is the nature of a database view! If you are still struggling to understand, I recommend getting familiar with database views and especially how the different JOIN types are working.

Maik

[sath]

Hi Maik,

Got it, thank you. Is there any other way to get to this requirement? We need to generate a report showing when a story was blocked, including its state, substate, blocked reason, start time, end time, duration, assignment group, assigned to.

if a story was blocked twice, would need to know why it was blocked the first time as well as the second time.