cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mwojcik
Frequent Visitor

ForAll / Patch with lookup fields

Hello everyone,

 

I'm comming to you with question about one thing in Canvas. I've got formula, that should update one column in database based on lookup fields.

 

 

 

ForAll(ObjectFromModel.RelatedTable As fData; 
        Patch(
            Table; 
            fData;
            {
            ColumnToUpdate:
                  RoundUp(With(
                    {
                    r: 6371;
                    p: (Pi() / 180);
                    latA: ObjectFromModel.Lat;
                    lonA: ObjectFromModel.Lon;
                    latB: fData.LookupObject.Lat;
                    lonB: fData.LookupObject.Long
                    };
                    (2*r)*Asin(Sqrt(0,5 - Cos((latA - latB) * p)/2 + Cos(latB * p)*Cos(latA*p)*(1 - Cos((lonA - lonB) * p)) / 2))
                ); 2)
            }
        );;
)

 

 

I'm getting correctly ObjectFromModel lat and lon, but can't get lat/lon from fData.LookupObject. Could anyone check my code? If it will help structure that I'm working. Tables:
1. MainObjects (id, name, lat, lon)
2. ObjectsInNeighbour (id, name, lat, lon)
3. Mainobjects_ObjectsInNeigbour - table with lookups to MainObjects and ObjectsInNeigbhour and additional distance field

Canvas app is embedded in model-driven app, so in first step I'm getting specific Object from MainObjects and his relations in Mainobjects_ObjectsInNeigbour(as ObjectFromModel.RelatedTable).

 

How it's work currently? It takes fData and update ColumnToUpdate (distance field), but it calculate this wrong and saved for every fData same number. So I assume that I'm taking correctly latA, lonA, but I have problems with latB and lonB?

1 ACCEPTED SOLUTION

Accepted Solutions
mwojcik
Frequent Visitor

Ok, got it.

 

ClearCollect(collection; RenameColumns(Table; originalColumn; changedColumn));;
ForAll(collection;
    Patch(
        [@Table]; 
        LookUp([@Table]; originalColumn = changedColumn);
        {
        field: ThisRecord.Column (value)
    });;
);;

Just needed to make better Lookup. When I use "As" it works to lookup, but not into patch action.

 

 

 

View solution in original post

2 REPLIES 2
mwojcik
Frequent Visitor

After weekend I thought I may go a bit different way and first save to collection all lookup objects that are in my many-to-many table. But... I'm not sure how to do it best...

 

Many-to-many table: GUID of entity, Lookup to object1, Lookup to object2

And I want to have in collection every object2 which is in relation to specific object1

mwojcik
Frequent Visitor

Ok, got it.

 

ClearCollect(collection; RenameColumns(Table; originalColumn; changedColumn));;
ForAll(collection;
    Patch(
        [@Table]; 
        LookUp([@Table]; originalColumn = changedColumn);
        {
        field: ThisRecord.Column (value)
    });;
);;

Just needed to make better Lookup. When I use "As" it works to lookup, but not into patch action.

 

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,711)