Inefficient Code

[Anonymous]

Hi, 

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

The model is: 

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. 

[Anonymous]

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

[Anonymous]

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]

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

@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

[Anonymous]

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.

[Anonymous]

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.

[Anonymous · Accepted solution]

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.