web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

Welcome to the Power Platform Communities
Register Today: Microsoft Business Applications Update- April 15th
Your Invited to Keeping It Real with the Power Platform
News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Apps / Inefficient Code
Power Apps
Answered

Inefficient Code

(0) ShareShare
ReportReport
Posted on by Community Power Pla... Microsoft Employee

Hi, 

 

I have the following tables: REPORT_DETAIL, OBJECT_DETAIL, REPORT_OBJECT_LINK, OBJECT_LEVEL_LINEAGE, EDW_TO_ROOT_OBJECT_MAP. 

 

The model is: model.PNG

 

I have a gallery to display records in OBJECT_DETAIL and I am filtering this gallery based on a dropdown of EDW_Project_Names. When an EDW_Project_Name is selected from the dropdown and the value is NOT "TBD", I want object records to display if:

1) they are directly linked to a Report record in REPORT_DETAIL for which the EDW_Project is the EDW_Project_Name

 

2) they appear anywhere in an OBJECT_LEVEL_LINEAGE record for which the Master_Object_Id of the lineage is associated to an object that is directly tied to a Report record for which the EDW_Project is the EDW_Project_Name

 

3) It is an EDW_Object_Id associated mapped to a Root_Source_Object_Id where the Root_Source_Object_Id is the Root_Object_Id for a record in OBJECT_LEVEL_LINEAGE for which the Master_Object_Id of the lineage is associated to an object that is directly tied to a Report record for which the EDW_Project is the EDW_Project_Name. 

 

Additionally, I have a selection of radio buttons that, depending on which is selected, the data source changes (ie OBJECT_DETAIL vs OBJECT_DETAIL_ROOTS). 

 

I know that I can create a SQL Server stored proc and pass in the parameter of Edw_Project_Name, but this would involve Power Automate and be a Premium feature. I am trying to avoid using that feature. 

 

I have written the below code and it works, but is EXTREMELY slow and I'm sure very inefficient, as there are a lot of repetitive chunks. I am looking for suggestions on how to optimize this code.

 

If the value is "TBD", then I display all Object records from OBJECT_DETAIL that are NOT tied to any Reports or Objects through any of the above relations. This logic is achieved with a SQL view since it is a static query and does not require any parameters or stored proc flows.

 

I would also like to have alternate row coloring, which is why everything is wrapped in the ForAll with the RowNo patch. 

Categories:
Building Power Apps
I have the same question (0)
  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    This is the current code I am using:


    With(
    {
    Objects: If(
    // EDW Project is TBD
    ProjectPicker_1.Selected.Result = "TBD",
    ForAll(
    Sort(
    Search(
    OBJECT_DETAIL_UNATTACHED,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    // else EDW Project is NOT TBD
    If(
    Roots,
    ForAll(
    Sort(
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL_ROOTS,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    If(
    RootsNoEDW,
    ForAll(
    Sort(
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL_ROOTS_MISSING_EDW_EQUIVALENT,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    If(
    NoLineages,
    ForAll(
    Sort(
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL_MISSING_LINEAGES,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    ),
    ForAll(
    Sort(
    // search for OBJECT_DETAIL records matching the search bar text on Object_Combined_Name and in EDW project
    Search(
    /*-------------------- begin code block to filter objects by edw project -----------------*/
    Filter(
    OBJECT_DETAIL,
    Object_Id in
    // directly linked to a report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id Or// in any part of an object lineage for an object that is directly linked to a report in EDW project
    Object_Id in// Level 1
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // make sure Master Object directly linked to report in EDW project
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level1_Object_Id Or Object_Id in// Level 2
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level2_Object_Id Or Object_Id in// Level 3
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level3_Object_Id Or Object_Id in// Level 4
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level4_Object_Id Or Object_Id in// Level 5
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level5_Object_Id Or Object_Id in// Level 6
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Level6_Object_Id Or Object_Id in// Root
    // object in a lineage that is linked to a report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id Or//Object is EDW object mapped to a root object in a lineage for an object directly linked to report in EDW project
    Object_Id in// EDW Mapping
    Filter(
    EDW_TO_ROOT_OBJECT_MAP,
    Root_Source_Object_Id in
    // lineage where master object directly linked to report
    Filter(
    OBJECT_LEVEL_LINEAGE,
    Master_Object_Id in
    // directly linked to a report
    Filter(
    REPORT_OBJECT_LINK,
    Report_Id in Filter(
    REPORT_DETAIL,
    // delegation warning should be okay since there shouldn't be > 2,000 reports for a single project
    EDW_Project = ProjectPicker_1.Selected.Result
    ).Report_Id
    ).Object_Id
    ).Root_Source_Object_Id
    ).EDW_Object_Id
    )
    /*-------------------- end code block to filter objects by edw project -----------------*/,
    box_Source_Object_Search.Text,
    "Object_Combined_Name"
    ),
    Object_Combined_Name
    ),
    {
    Object_Id: Object_Id,
    Object_Name: Object_Name,
    Object_Type: Object_Type,
    Server: Server,
    Database: Database,
    Schema: Schema,
    Object_Combined_Name: Object_Combined_Name,
    Obsolete_Code: Obsolete_Code,
    Reported_Code: Reported_Code,
    Notes: Notes,
    Created_Person_Id: Created_Person_Id,
    Created_Ts: Created_Ts,
    Last_Updated_Person_Id: Last_Updated_Person_Id,
    Last_Updated_Ts: Last_Updated_Ts
    }
    )
    )
    )
    )
    )
    },
    // iterate through each generated number
    ForAll(
    // genereates a sequence of numbers in the range of the number of Objects
    Sequence(CountRows(Objects)),
    // add a RowNo to each Object record - this allows for dynamic, alternate colors in the gallery based on RowNo
    Patch(
    Last(
    FirstN(
    Objects,
    Value
    )
    ),
    {RowNo: Value}
    )
    )
    )

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Oh boy, I will do my best here, could you please share the same in a file formatted please?

     

    Some additional questions, 

    --where is this hosted? looks like SQL server, if so the power app would be considered Premium

    --if hosted in SQL DB, would you be able to create additional views if required? (not that I'm considering this, but I need to know) 

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @Anonymous 

    Just wanted to let you know that I am reviewing your formula.  It is *extremely* overcomplicated and can be reduced to just a few lines.  But, I need to review it thoroughly to grasp what you are doing. 

    You are repeating so many functions over and over on the same data - this is not only a nightmare to maintain, but will be highly inefficient. 

    Give me some "digesting" time on it. 

  • RandyHayes Profile Picture
    RandyHayes 76,299 Super User 2024 Season 1 on at

    @Anonymous 

    Can you provide more details on the following:

    - OBJECT_DETAILS_ROOTS (Is this the Object_Details table?  Not seeing it in your model)

    - Roots

    - RootsNoEDW

    - NoLIneages

    - The Items property of ProjectPicker_1

     

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @RandyHayes. Thanks for giving this your consideration. 

     

    OBJECT_DETAILS_ROOTS is a view of the OBJECT_DETAIL table that only selects records where the Object_Ids appear at the Root level of an Object Lineage in the OBJECT_LEVEL_LINEAGE table. It's essentially:

    SELECT *
    FROM OBJECT_DETAIL d WITH(NOLOCK)
    WHERE d.Object_Id IN (
    SELECT OBJECT_Id
    FROM OBJECT_DETAIL d WITH(NOLOCK)
    INTERSECT
    SELECT l.Root_Source_Object_Id
    FROM OBJECT_LEVEL_LINEAGE l WITH(NOLOCK)
    )

     

    Roots, RootsNoEDW, and NoLineages are all booleans triggered true/false depending on the radio button selection by the user. Similar to how OBJECT_DETAIL_ROOTS was created, OBJECT_DETAIL_ROOTS_MISSING_EDW_EQUIVALENTS becomes the data source if RootsNoEDW is true to show Object records from OBJECT_DETAIL_ROOTS where there are no Root_Source_Object_Id matches in EDW_TO_ROOT_OBJECT_MAP. This view query is:

    SELECT *
    FROM OBJECT_DETAIL WITH(NOLOCK)
    -- GET ROOT OBJECTS ONLY
    WHERE OBJECT_ID IN (
    SELECT OBJECT_ID
    FROM OBJECT_DETAIL WITH(NOLOCK)
    INTERSECT
    SELECT Root_Source_Object_Id
    FROM OBJECT_LEVEL_LINEAGE WITH(NOLOCK))
    AND
    -- GET OBJECTS WITHOUT EDW EQUIVALENTS
    OBJECT_ID IN (
    SELECT OBJECT_ID
    FROM OBJECT_DETAIL WITH(NOLOCK)
    EXCEPT
    SELECT Root_Source_Object_Id
    FROM EDW_TO_ROOT_OBJECT_MAP WITH(NOLOCK)
    )

     

    NoLineages is similar to the above but selects a subset from OBJECT_DETAIL where there are no related records in OBJECT_LEVEL_LINEAGE. the view query is:

    select *
    from OBJECT_DETAIL with(nolock)
    where object_id in (
    select Object_Id
    from object_detail with(nolock)
    except
    select Master_Object_Id
    from OBJECT_LEVEL_LINEAGE with(nolock)
    )

     

    The Items property of ProjectPicker_1 is EDW_PROJECT.EDW_Project_Name.

  • Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    Hi @Anonymous. Thanks for looking at this. I've saved the code as a .docx and attached it. 

    This is hosted on SQL Server. It is a Premium Power App but I am trying to avoid using Power Automate and stored proc flows if at all possible.

    I am able to crate additional views if required. 

    Thanks.

    InefficientCode.docx
  • Verified answer
    Community Power Platform Member Profile Picture
    Community Power Pla... Microsoft Employee on at

    I ended up just using SQL Server stored procs and Power Apps Flows to achieve what I was trying to do. Way more efficient and run times reduced from ~11,000 ms to ~700 ms. 

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Welcome to the Power Platform Communities
Register Today: Microsoft Business Applications Update- April 15th
Your Invited to Keeping It Real with the Power Platform

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Users!

Kudos to our 2025 Community Spotlight Honorees

Congratulations to our 2025 community superstars!

Congratulations to the March Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Power Apps

#1
11manish Profile Picture

11manish 534

#2
WarrenBelz Profile Picture

WarrenBelz 416 Most Valuable Professional

#3
Valantis Profile Picture

Valantis 306

Last 30 days Overall leaderboard

Featured topics

Auto-Populate field Power Pages
Gallery Tabs - Show or Hide Tabs based on Field Value
How to patch multiple records at once
Enable Copilot for end users in model-driven apps
Question for everyone : How are you using Create Text GPT in AI Builder?
Community content - sample components, blogs etc. - Link to this page (http://aka.ms/PCFdemos)
Welcome to the Power Apps forum!

Product updates

Microsoft Power Platform Community release plans