Hi, I am bending my head around this one. I think I have gotten all backwards somehow...
ClearCollect(
colProject,
AddColumns(
ShowColumns(
Filter(
'Product Certifications',
ProdCand.Candidates in First(ColCandidate).key_candidatesid
),
"key_productcertificationid"
),
"ProjectDivisionID",
LookUp(
'Project Divisions',
'Project Division' in First(
Filter(
'Product Certifications',
ProdCand.Candidates in First(ColCandidate).key_candidatesid
)
).'Project Division'.'Project Division'
).'Project Division'
)
)
I don't have any errors but the column ProjectDivisionID is empty when I run the collection. Only the ShowColumn value.
Product Certification has a lookup column called Project Division to the table called Project Division. And I aim to pass the ID of that row into the collection. These are Dataverse tables btw.
If anyone also could clarify which of these tables is referred to as a child and parent it would be most helpful. 🙏
Solved! Go to Solution.
I must have been very tired and making it much more complicated. I found it finally:
ClearCollect(
colProject,
AddColumns(
ShowColumns(
Filter(
'Product Certifications',
ProdCand.Candidates in First(ColCandidate).key_candidatesid
),
"key_productcertificationid"
),
"ProjectDivisionID",
LookUp(
Candidates,
Candidates in First(ColCandidate).key_candidatesid
).Project.'Project Division',
"ProjectName",
LookUp(
Candidates,
Candidates in First(ColCandidate).key_candidatesid
).Project.Name
)
)
To determine which is the parent and which is the child, you need to define the one to many relationship. Can a division have more than one certification or can a certification have more than one division? If both are true, then you are dealing with a many to many relationship.
ok, so just to be sure I understand what you mean. A division can be chosen by many candidates. So candidates has the lookup column to divisions - so candidates is the child. So, the records with the lookup column is the child.
Can candidates choose more than 1 division? If not, then your conclusion is correct.
No they can't. Ok, thanks... now onto figuring out how to add the collection of the parent ID...
I must have been very tired and making it much more complicated. I found it finally:
ClearCollect(
colProject,
AddColumns(
ShowColumns(
Filter(
'Product Certifications',
ProdCand.Candidates in First(ColCandidate).key_candidatesid
),
"key_productcertificationid"
),
"ProjectDivisionID",
LookUp(
Candidates,
Candidates in First(ColCandidate).key_candidatesid
).Project.'Project Division',
"ProjectName",
LookUp(
Candidates,
Candidates in First(ColCandidate).key_candidatesid
).Project.Name
)
)