AddColumns: lookup column value

[shavora]

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. 🙏

[Drrickryp]

@shavora 

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. 

[shavora]

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.

[Drrickryp]

@shavora 

Can candidates choose more than 1 division? If not, then your conclusion is correct.  

[shavora]

No they can't. Ok, thanks... now onto figuring out how to add the collection of the parent ID...

[shavora · Accepted 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
    )
)