Multi-table UX view

[se]

Apologies - I have spent a decent amount of time trying to find a solution for this but can't find the correct terminology to explain what I am trying to do. 

I will try explain below.

I have an app with multiple tables linked by REF columns. I’ve tried to explain the structure below using example data.

Table 1 - Employee Details

Employee ID (Key)	Employee Name (Label)	Department	Phone Number
4342352	Jane Drask	HR	5444665434
6547567	Toby Glow	IT	277499333

Table 2 - Incident Details (predetermined list of incident types, organised under categories)

Unique ID (Key)	Incident Category	Incident Description (Key & Label)
4342352	HR	Arrives late
6547567	HR	Arrive early
653567	Safety	Misuse of equipment
3454634	Safety	No gloves

Table 3 - Incident log

Timestamp (key)

Employee ID (Ref column to Table 1 - Employee Details)

Incident Category (text column with Valid IF statement =Incident Details[Incident Category]

Incident Description (Ref column to Table 2 - Incident Details table)

Progress (Progress Enum column type)

Completed (Y/No column)

The app currently has two views:

UX 1 - Employee View (Source Table is Table 1 - Employee Details)

Organised into Departments, user selects the Department and then it shows the employees as part of that department. User drills down into employee which then shows all the related incident logs to do with that Employee.

UX 2 - Incident View (Source Table is Table 3 - Incident Log)

Organised into incident descriptions, shows all employees under each incident description who met that critiera.

UX 3 - What i’m trying to do

Similar to UX 1 where first its organised into Departments, however when you drill down it replicates the UX 2 view style where it organises first under Incident Description - this doesn’t appear possible with using Table 1 as the source.

HR Department employees only:

No gloves

Employee name 1

Employee name 2

Arrives late

Employee name 2

Ive tried a work around where i add additional columns to the Incident Log table which are populated by the Department column from Table 1 however this does not meet the purpose as i want to be able to view data based on the current department (eg. moving an employee from HR to IT etc.) rather than at time the row was entered. Hope I have explained this to make enough sense.

Any leads for this one?

[1minManager]

Not sure if I 100% understand... But first create a column Incidents so that it will show up in a table.

Next create a table view of incidents grouped first by Department then by Employee.

Simon@1minManager.com

[se]

Apologies, it’s a bit confusing.

In which table?

Currently the log of incidents (table 3) includes the employee name only (referencing the Employee Detail table) and then the incident category, description etc. This is the form view where everything is logged.

As it’s only referencing one column (employee ID), I can’t use the other columns (Department etc) to sort the data.

This is the same the other way around where if I use the employee detail table for the ux I can’t use the incident log etc to group or sort the data as it doesn’t appear.

[1minManager]

Really need to see some screenshots of

• The the tables and columns you have
• The views you have currently

[se]

The actual app is a lot more confusing but I have replicated it based on above information, is there a way of sharing it so you can view the app internals or just screenshots? 

[se]

PLease see the column structure below

and then the main ux - essentially what im wanting to do is group the data by the Department type before it is then grouped by the incident description.

[1minManager · Accepted solution]

Ok, I think i understand.  So the simple solution might be to create a virtual column at

Incident Log[Department]

with an App Formula of 

[Employee].[Department]

You can then create a view of the Incident Log table, grouped by this colum first then Incident Log[Incident Description]

Simon@1minManager.com

[se]

So simple, so effective! Thankyou