cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
LinnZawWin
Level: Powered On

How to Collect lookup GUID value from CDS lookup Field or Join two CDS Data Sources

How to join two CDS data source via lookup GUID value to get the GUID value of the lookup value of parent record? (in short, grandparent record GUID)


E.g. I have 3 entities.
1. Assessment Section
2. Assessment Question
3. Assessment Details

Assessment Details has a lookup field for entity Assessment Question.
Assessment Question has a lookup field for entity Assessment Section.

What I want is the GUID value for "Assessment Section" lookup field on "Assessment Question" entity to be added to the collection together with "Assessment Details" data

 

I tried to AddColumn using the following formula but

  • zzlocalassessmentsectionid (getting a lookup value of lookup record returns an empty data even though "zzTextField" returns the data though since it is a text field)
  • zzlookupassessmentsectionid (lookup to the Assessment Question returns value for the other text fields but not the lookup field. If I set the same Lookup function to a label text value with hardcoded "Assessment Question" GUID, it is working fine)

*Screenshot attached*

 

 

ClearCollect(colTest,
    AddColumns
    (
        'Assessment Details',
        "zzTextField",
        assessmentquestionid.'Question Text',
        "zzlocalassessmentsectionid",
        assessmentquestionid.'Assessment Section',
        "zzlookupassessmentsectionid",
        LookUp
        (
            'Assessment Questions', 
            'Assessment Question' = assessmentquestionid.'Assessment Question', 
            'Assessment Section'.'Assessment Section'
        )
    )
)

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-siky-msft
Microsoft

Re: How to Collect lookup GUID value from CDS lookup Field or Join two CDS Data Sources

Hi  @LinnZawWin ,

 

What is the assessmentquestionid? the lookup field of 'Assessment Details'?

I'm sure this is not a easy way, but it works on my test. We can't get the Lookup value of lookup field in batches, so we have to first get the GUID field Name of Assessment Question, then patch the GUID2(the Assessment Section lookupfield of  Assessment Questions ) to collection. Please try the following formula, If this doesn't work, please post back more details information.

Note: I assume that GUID field Name of three entity is the same with entity name.

 

ClearCollect(colTest,
    AddColumns
    (
        'Assessment Details',
        "zzTextField",
        assessmentquestionid.'Question Text',
        "GUID1"
        assessmentquestionid.'Assessment Question', /*Get GUID field Name of Assessment Question lookup field*/
        "GUID2",
        ""
    )
);
ClearCollect(coltest3,colTest);
ClearCollect(coltest4,'Assessment Question');
ForAll(
    coltest4,
    ForAll(
        coltest3,
        If(
            'Assessment Question' = GUID1,
            Patch(
                colTest,
                LookUp(
                    colTest,
                    'Assessment Details' = coltest3[@'Assessment Details']
                ),
                {
                    GUID2: LookUp(
                        'Assessment Question',
                        'Assessment Question' = coltest4[@'Assessment Question'],
                        'Assessment Section'.'Assessment Section' /* LookUpfied.GUID */
                    )
                }
            )
        )
    )
)

 

Snipaste_2019-11-12_14-31-40.png

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

View solution in original post

LinnZawWin
Level: Powered On

Re: How to Collect lookup GUID value from CDS lookup Field or Join two CDS Data Sources

@v-siky-msft Thanks for your response. It's a shame that we can't get the Lookup value of the lookup field (not even GUID).

Your sample code is also useful for me. It inspired me to come up with the idea to patch the collection instead.

And here is the modified version to update all the rows of the collection and populate the Lookup GUID value of the parent record.

UpdateIf(
    colTest, 
    true, 
    {
        assessmentsectionid: LookUp
        (
            'Assessment Questions', 
            'Assessment Question' = dxc_assessmentquestionid,
            'Assessment Section'.'Assessment Section'
        )
    }
);

 

View solution in original post

2 REPLIES 2
Microsoft v-siky-msft
Microsoft

Re: How to Collect lookup GUID value from CDS lookup Field or Join two CDS Data Sources

Hi  @LinnZawWin ,

 

What is the assessmentquestionid? the lookup field of 'Assessment Details'?

I'm sure this is not a easy way, but it works on my test. We can't get the Lookup value of lookup field in batches, so we have to first get the GUID field Name of Assessment Question, then patch the GUID2(the Assessment Section lookupfield of  Assessment Questions ) to collection. Please try the following formula, If this doesn't work, please post back more details information.

Note: I assume that GUID field Name of three entity is the same with entity name.

 

ClearCollect(colTest,
    AddColumns
    (
        'Assessment Details',
        "zzTextField",
        assessmentquestionid.'Question Text',
        "GUID1"
        assessmentquestionid.'Assessment Question', /*Get GUID field Name of Assessment Question lookup field*/
        "GUID2",
        ""
    )
);
ClearCollect(coltest3,colTest);
ClearCollect(coltest4,'Assessment Question');
ForAll(
    coltest4,
    ForAll(
        coltest3,
        If(
            'Assessment Question' = GUID1,
            Patch(
                colTest,
                LookUp(
                    colTest,
                    'Assessment Details' = coltest3[@'Assessment Details']
                ),
                {
                    GUID2: LookUp(
                        'Assessment Question',
                        'Assessment Question' = coltest4[@'Assessment Question'],
                        'Assessment Section'.'Assessment Section' /* LookUpfied.GUID */
                    )
                }
            )
        )
    )
)

 

Snipaste_2019-11-12_14-31-40.png

Best regards,

Sik

If my post is helpful for you, please click on “Accept as Solution” to help other members find it more quickly.

View solution in original post

LinnZawWin
Level: Powered On

Re: How to Collect lookup GUID value from CDS lookup Field or Join two CDS Data Sources

@v-siky-msft Thanks for your response. It's a shame that we can't get the Lookup value of the lookup field (not even GUID).

Your sample code is also useful for me. It inspired me to come up with the idea to patch the collection instead.

And here is the modified version to update all the rows of the collection and populate the Lookup GUID value of the parent record.

UpdateIf(
    colTest, 
    true, 
    {
        assessmentsectionid: LookUp
        (
            'Assessment Questions', 
            'Assessment Question' = dxc_assessmentquestionid,
            'Assessment Section'.'Assessment Section'
        )
    }
);

 

View solution in original post

Helpful resources

Announcements
Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Microsoft Business Applications Virtual Launch

Join us for the Microsoft Business Applications Virtual Launch Event on Thursday, April 2, 2020, at 8:00 AM PST.

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (5,693)