Hello,
I want to get records in collection(gallery) to view by Site.
The values/contents of my tables are specified Sites and there User with application they belong.
Table1(Site Details with User) having 3 columns which have following data:-
| SiteID | UserID | Application |
| 001 | User001 | 1 |
| 001 | User001 | 2 |
| 001 | User002 | 1 |
| 001 | User002 | 2 |
| 002 | User001 | 1 |
| 002 | User003 | 2 |
| 003 | User003 | 2 |
| 003 | User004 | 2 |
Table2 - 'Site Details' having SiteID and Name, such as:-
| Site ID | Site Name |
| 001 | Site1 |
| 002 | Site2 |
| 003 | Site3 |
Table 3 - 'User Role Table' -having records UserID and Role and Application
| UserID | Role | Application |
| User001 | Submitter | 1 |
| User001 | Engineer | 2 |
| User002 | First Approver | 1 |
| User002 | Second Approver | 1 |
| User003 | Initiator | 2 |
| User004 | Area Approver | 2 |
* any user can have multiple access on same/different applications.
Table 4- User Role Details- having Role name and application
| Role Name | Application |
| Submitter | 1 |
| First Approver | 1 |
| Second Approver | 1 |
| Engineer | 2 |
| Initiator | 2 |
| Area Approver | 2 |
Now, my end result what I want is-
| Site Code | Site Name | User Name | User Role | Application |
| 001 | Site 1 | User001 | Submitter | 1 |
| 001 | Site 1 | User001 | Engineer | 2 |
| 001 | Site 1 | User002 | First Approver | 1 |
| 001 | Site 1 | User002 | Second Approver | 1 |
| 002 | Site 2 | User001 | Submitter | 1 |
| 002 | Site 2 | User003 | Initiator | 2 |
My issue here is that, how can I get User Role as that was not directly associated with Site table.
Please let me know if any other information needed.
@ericonline @WarrenBelz @RandyHayes
Thanks,
