Collection that shows all records

[todaytoo]

I have 3 dataverse tables that we are using to track project related details. I need to be able to search by ID or Name in each of the tables and filter a gallery based on the records. The code below pulls in all the data that is related, so as long as the Story has a Feature and the Feature has a Project the below works. When the Feature does not have a Story and when the Project does not have a Feature or Story, the search does not work as the collection is only pulling the related rows. How can I create a collection that joins all of the matching data but also includes the non-matching records as well?

Dataverse Tables

1. Project
2. Feature
3. Story

• Projects might not have feature or story
• Feature might not have story but will always have project
• Story will always have feature and project

ClearCollect(colSearch,

AddColumns(

    STORY_TABLES,

    "_featureID",

     LookUp(FEATURE_TABLES,Text(FeatureName) = Text(FeatureLookUp.FeatureName)).'Delivery Feature ID',

     "_featureName",

     LookUp(FEATURE_TABLES,Text(FeatureName) = Text(FeatureLookUp.FeatureName)).'Feature Name',

     "_projectName",

     LookUp(PROJECT_TABLES,Text(ProjectNameLookUp) = Text(ProjectLookUp.ProjectNameLookUp)).'Project Name',

     "_projectID",

     LookUp(PROJECT_TABLES,Text(ProjectNameLookUp) = Text(ProjectLookUp.ProjectNameLookUp)).'Project ID'))

What I get from the collection:

Project ID	Project Name	Feature ID	Feature Name	Story ID	Story Name
1	Project 1	11	Feature 1	1111	Story 1111
2	Project 2	22	Feature 2	2221	Story 2221
2	Project 2	22	Feature 2	2222	Story 2222
4	Project 4	44	Feature 4	4444	Story 4444

What I am needing:

Project ID	Project Name	Feature ID	Feature Name	Story ID	Story Name
1	Project 1	11	Feature 1	1111	Story 1111
2	Project 2	22	Feature 2	2221	Story 2221
2	Project 2	22	Feature 2	2222	Story 2222
3	Project 3
4	Project 4	44	Feature 4	4444	Story 4444
5	Project 5	55	Feature 5

[v-xiaochen-msft · Accepted solution]

Hi @todaytoo ,

According to your description, you want to show the all Project in your collection. And you have three table in Dataverse .

I test it in my side , here are the steps you can refer to :
(1)This is my test data:

The "FeatureLookUp" and "ProjectLookUp" in the 'Story' Table  are LookUp type columns.

The "ProjectLookUp" in the 'Feature_Table' is LookUp type columns.

For your need , I think you need to get the corresponding data in the story first, just like you are doing now, and then we have to get the value that does not exist in the Feature table, use the Collect function to chase to the Collection we got earlier, and then in the same way to get the value that does not exist in the Project table, and use the Collect function to track to the Collection we obtained earlier.

(2)Then i use this code to get the table you need(You need to change the table names and column names I have here to the corresponding names on your side):
ClearCollect(

    test,

    ShowColumns(

        AddColumns(

            Stories,

            "FeatureId",

            FeatureLookup.FeatureID,

            "ProjectId",

            ProjectLookUp.ProjectID,

            "FeatureName",

            FeatureLookup.FeatureName,

            "ProjectName",

            ProjectLookUp.ProjectName

        ),

        "cr667_stortyid",

        "cr667_storyname",

        "FeatureId",

        "FeatureName",

        "ProjectId",

        "ProjectName"

    )

);

ClearCollect(

    test_feature,

    test.FeatureId

);

ClearCollect(

    test_features,

    RenameColumns(

        ShowColumns(

            AddColumns(

                Feature_Tables,

                "ProjectId",

                ProjectLookUp.ProjectID,

                "ProjectName",

                ProjectLookUp.ProjectName

            ),

            "cr667_featureid",

            "cr667_featurename",

            "ProjectName",

            "ProjectId"

        ),

        "cr667_featureid",

        "FeatureId",

        "cr667_featurename",

        "FeatureName"

    )

);

ForAll(

    test_features,

    If(

        !(ThisRecord.FeatureId in test_feature),

        Collect(

            test,

            ThisRecord

        )

    )

);

ClearCollect(

    Projects,

    RenameColumns(

        ShowColumns(

            ProjectTests,

            "cr667_id",

            "cr667_name"

        ),

        "cr667_id",

        "ProjectId",

        "cr667_name",

        "ProjectName"

    )

);

ForAll(

    Projects,

    If(

        !(ThisRecord.ProjectId in test.ProjectId),

        Collect(

            test,

            ThisRecord

        )

    )

)

Then we can get the 'test' Table like this :

Best Regards,

Wearsky

[todaytoo]

Thank you so much! Exactly what I needed. I really appreciate it!