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?
Solved! Go to 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
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
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.
Really need to see some screenshots of
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?
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.
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
So simple, so effective! Thankyou
User | Count |
---|---|
42 | |
25 | |
25 | |
17 | |
13 |