cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
geneZebra
Level 8

how to do a join without a delegation warning.

@Jeff_Thorpe (or anyone?)

 

Any idea on to resolve this delagation warning?

 

ClearCollect(_coBudgets, Filter(EttTravelAppBudgets, _User.Department = Department));
ClearCollect(_coTrips, Filter(EttTravelAppTrips, AirBudgetID = LookUp(_coBudgets, ID= AirBudgetID,ID)));

 

I get a warning in the filter.

I am looking to build a collection called _coTrips where an AirBudgetID belongs to a matching budget. I had this expression a few ways but still get the warning. I am willing to modify the underlying sharepoint list but I do not think that would help.

 

Thanks,

Gene

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
geneZebra
Level 8

Re: how to do a join without a delegation warning.

Ok, I think I got it but still do not think this is the best way but will live it if I have to. note I could not find a way other than using AddColumns to make the foreign key lookups work. There really should be a way to reference the record being used in the loop interation.

 

Clear(_coTrips);
ForAll(AddColumns(EttTravelAppTrips,"_TripID", ID) ,
    If (Value(LookUp(_coBudgets, AirBudgetID = _BudgetID || OtherExpBudgetID = _BudgetID).ID)>0
        && !(_TripID in _coTrips.ID),
    Collect(_coTrips, LookUp(EttTravelAppTrips, ID = _TripID))
    )
);

View solution in original post

4 REPLIES 4
Dual Super User
Dual Super User

Re: how to do a join without a delegation warning.

Hi @geneZebra 

 

Set(lookupID,LookUp(_coBudgets, ID= AirBudgetID,ID));ClearCollect(_coTrips, Filter(EttTravelAppTrips, AirBudgetID = lookupID));

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

geneZebra
Level 8

Re: how to do a join without a delegation warning.

@RezaDorrani 

My problem is I need to set up the Trips collection to have all trips that reference anything in the budgets collection/table. I guess I would need to put in a for loop to repeat the lookup for each ID in the budget table that matched the foreign key (AirBudgetID) in the trips table.

Thinking something like the following but in my example, I am looking at entry in the trips table. This could work but I can't find a way to add the full record to the collection (the "Value" name is not working.

 

ForAll(EttTravelAppTrips,

     If ( AirBudgetID = LookUp(_coBudgets, ID = AirBudgetID, ID), Collect(_coTrips, Value))

);

 

 

I was hoping there was a filter formula for this and I can't seem to be able to reference the Value used in the loop so it can be added to the collection. I am still playing with this but think I am getitng close.

 

geneZebra
Level 8

Re: how to do a join without a delegation warning.

I could not get it exaclty but this comes close. 

 

Clear(_coTrips);
ForAll(AddColumns(_coBudgets, "_BudgetID", ID),
   Collect(_coTrips, Filter(EttTravelAppTrips, _BudgetID = AirBudgetID || _BudgetID = OtherExpBudgetID ));

);

In this approach, I could not refer to the "ID" value in the Budgets table to use in the filter since both tables have a column named ID. To get around this, I used add cols to put in a unique name for the budget ID. Once I had that, I can now perform a filter function.  I still would not having to add a temp column but at least I am getting closer. Now I have to work on not adding duplicate entries into the collection (and hope I can do this without a delation error).  

 

If anyone can provide any insiight on how to refer to the complete record being referenced by the ForAll Loop interation, please share. I just find it hard to believe I need to do an add columns to avoid a name conflict. This is not directly related to my delegation problem but not being able to refer to the record makes the task harder to resolve.

 

This is still not ideal but at least I am getting closer. In my example, I can have duplicates in the trip if both foreign keys refer to the same budget id. Plus I need to do caluculations based on similar join operations so this could get complicated. 

 

 

 

Highlighted
geneZebra
Level 8

Re: how to do a join without a delegation warning.

Ok, I think I got it but still do not think this is the best way but will live it if I have to. note I could not find a way other than using AddColumns to make the foreign key lookups work. There really should be a way to reference the record being used in the loop interation.

 

Clear(_coTrips);
ForAll(AddColumns(EttTravelAppTrips,"_TripID", ID) ,
    If (Value(LookUp(_coBudgets, AirBudgetID = _BudgetID || OtherExpBudgetID = _BudgetID).ID)>0
        && !(_TripID in _coTrips.ID),
    Collect(_coTrips, LookUp(EttTravelAppTrips, ID = _TripID))
    )
);

View solution in original post

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (4,937)