cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shavora
Super User
Super User

AddColumns: lookup column value

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

1 ACCEPTED SOLUTION

Accepted Solutions
shavora
Super User
Super User

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
    )
)

View solution in original post

5 REPLIES 5
Drrickryp
Super User
Super User

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

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
Super User
Super User

@shavora 

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

shavora
Super User
Super User

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
    )
)

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Users online (2,633)