Hi
I have 2 tables:
The first is enrolment that has a list of subjects the learners are enrolled to. There is column named Enrolment [Reference] that can either be the programme or element enrolment depending on the funding stream. The unique field is the enrolment ref and student ref that I concatenate together.
The second is PBI_WBI_Tutors which contains 4 columns a list of the programmes (reference 1), the corresponding element enrolments (Reference 2), the student ref and tutors against them.
In order to link the correct tutor to the enrolment I have to select the correct reference number from the PBI_WBI_tutors table and create a concatenated field that matched the enrolment table. I have created the following formula to do this:
Overall Reference =
IF( LOOKUPVALUE( Enrolment[Reference], PBI_WBL_Tutors[Reference1], PBI_WBL_Tutors[Reference1]) <> BLANK(),
PBI_WBL_Tutors[Reference1] & PBI_WBL_Tutors[StudentReference],
PBI_WBL_Tutors[Reference2] & PBI_WBL_Tutors[StudentReference]).
I want to use this column as the unique key to create a relationship to the enrolments table. I am having numeorus problems.
The formula wont work unless there is an exisitng relationship between the tables. I have created a fake relationship and removed some data to test this and the formula does work. However when I try to change the relationship to the new one I get one of 2 errors
The overall reference (column I have created with the above formula) is not unique so is there a way to remove duplicates from this column?
When manually removing further data from the column I tried to change the fake relationship I have created to the one I want (formula above) and it errors saying there is a circular dependancy.
Is it possible to achieve what I want?
Thanks
