How to use Ref Row for below situation.
“Tbl_Title” - Here “TitleName” is unique
“Tbl_Course” here “CourseName” is unique
“Tbl_Employees” here Empid is unique
“Tbl_Mapping” here both Titlename & Coursename are unique
Question is in Table “Tbl_Employee”, how to do use “Ref Row” to show related training by referring their Titles from “Tbl_Mapping”.
Thanks in Advance.
John
If not already, set TitleName in the Employees table to a Ref type column. Also. make sure TitleName in your Mapping table is a Ref.
This will give you by default a [Related Tbl_Mappings] column in the “Tbl_Title” column - i.e. a list of course for each Title. You would have access to the list of courses from Employee with an expression like this:
[TitleName].[Related Tbl_Mappings]
Now, if you want to see the list of courses within the Employee record, then add a Virtual Column of List type Ref to Mapping table, maybe named “Required Courses” and set its App Formula to the expression above. (A Virtual Column in case any course changes are made, the Employee will automatically get those updates)
This new VC List/Ref column will allow you to display the column as a table within the Employee row.
Hi John, if I understand your request properly, one way is to use a List/Ref column with a formula as..
SELECT(Tbl_Mapping[KeyColumn],[TitleName]=[_THISROW].[TitleName])
Thank you so much Mr. Aleksi.
You’re welcome!
The formula has no error. But it does not produce the desired result.
The KeyColumn in Tbl_Mapping is combination of Titlename & Coursename.
Thanks lot @WillowMobileSys
Finally I got the results by your guidelines above.
So far, we achieved to show that Minimum Trainings to be attended, Actual trainings attended. Now the next step is “Gap Analysis” where I want to show the difference between these two and show as a table in Employees Table.
Can you please advise how to achieve this? the below picture shows the an individual’s trainings attended and minimum requirement.
Thanks in advance.
You can use List subtraction to get the difference. I’ll assume these are Virtual Column lists of Course Refs. The expression would be similar to:
[Minimum Trainings] - [Actual Attended]
Bottom line is you need to get to a List of Course Refs that are required and subtract a List of Course Refs of the trainings attended - however you need to adjust the expressions to get to those lists.
Sorry! I understand the logic. My Data Structure is different. That is I use Training Master table in middle to go for Individual Training Records.
Course Name is derived from Tbl_Courses, and Ref_rows used in Tbl_TrainingMaster.
I understand, “Tbl_TrainingMap” (Title vs Course) is not connected either in TrainingMaster and Training Data.
Is there any workaround there to get the Gaps. I somehow managed to get the list. but its not showing in the application.
Please help me.
So this is the actual problem then? You have the correct Gap list but having trouble getting it to show in the app?
It seems you have the Gap column as a List and if it is defined as a Ref then all you should need to do is add it as a column to the Detail View - if that is the view you are trying to show it in.
If you are trying to show it in a different way, then please explain.
