during ForAll loop i get error: "Error when trying to retrieve data from the network"

[Jean-Philippevb]

Hi,

I have 3 tables, that are linked in a parent child manner

Table 1:  "Main"

main	Birth date	Death date
Granny
Mopsy

Table 2: "Child", linked to "main"

Child	Birth date	Death date	Linked main
Dudel			Granny
Frutsel			Mopsy
Piew			Mopsy
Wiesel			Granny
Frannel			Granny

Table 3: "Grand Child", linked to "Child"

main	Birth date	Death date	Linked Child
Jan			Piew
An			Piew
Wan			Wiesel
Sam			Frutsel
Dan			Piew
Joan			Frutsel
Sympan			Dudel
Clan			Dudel

In multiple cases, i need to know the data of the "Grand Child" that are linked to a selected the "Main" record.

Filter(

    'Grand Child',

    'Linked Child' in Filter(

        'Child',

        'Linked main' = 'Gallery_main.Selected.main'

    ).'Child'

)

This Works, but i get a Delegation warning on the use of "in" in the first filter set-up. I thought i could use the ForAll function as a workaround for this issue. So when i select a new Item in my 'main'Gallery i run;

Clear( GrandChildLinkedToMain);

ForAll(
    Filter(

        'Child,

         Linked_Main = ThisItem.main

    ),
    Collect(

        GrandChildLinkedToMain,

        Filter(

            'GrandChild',

            ThisRecord.Title = 'Linked Child'

        )

    )
)

Here i'm hoping to get a small collection (named 'GrandChildLinkedToMain') back of all the 'Grandchilds' that are linked to the selected 'main'; 

I don't get an error on the formula itself, but when i run the app, i get an error message:

"Error when trying to retrieve data from the network"

Anyone knows how to get my problem fixed?

[v-jefferni]

Hi @Jean-Philippevb ,

Unless filtered child entries are more than 2000, there should be no worries about the delegation issue. You are comparing 2 tables which means using the "in" operator is a must.

Best regards,

[Jean-Philippevb]

The parent, child, grandchild was more an analogy,

you can directly translate them to transports, shipments and purchase order numbers.

But just as in a family, i will have about 5 child/ parent and thus 25 grand child / parent.

I will have 20 000 Purchase orders /year... so I definitely will go over the 2000 limit.

The problem is that I have no possibility to filter this down to a max 2000 list... So i was really hoping to find a solution with the "ForAll" function

=> Since i have max 10 child / parent, i need to loop 10 times the filter for the grandchild.

I think in database management this is a quite frequently made link. But am i running against the limits of SharePoint? should i look for a different data source like SQL ?

[v-jefferni · Accepted solution]

Hi @Jean-Philippevb ,

OK, then you can try below to get the results:

UnGroup(
    ForAll(
        Filter(
            'Child,
             Linked_Main = Gallery.Selected.main
        ) As CC,
        Filter(
            'GrandChild',
            'Linked Child' = CC.Child
        )
    ),
    "Value"
)

ForAll function returns a single Value column, as the Filter function always give you a table as result. Then use UnGroup to expand tables in Value column.

Best regards,

[Jean-Philippevb]

Wow Jeffer Ni,

You made my day! this works as a charm.

I really which to understand more about the ForAll formula, since i don't understand the full logic behind the steps you took yet...

But the 4 places where i needed it in my app now Work as should without any delegation warning.

Kind regards,

Jean-Philippe

[v-jefferni]

Hi @Jean-Philippevb ,

I made a simple scenario to explain. Firstly, I generated a table with collection:

Then, looping on a static table and filter the collection (using collection will help look into the sub table):

You can see the result table returned by ForAll function. Since the static table has two entries, the result table includes two tables in the Value column, each is the result of Filter function. 

Knowing this, we could use UnGroup to extract sub tables from the Value column because all sub tables have the same structure.

Best regards,

[Jean-Philippevb]

Hi @v-jefferni 

This is clear as water,

Thank you for taking the time to explain this. I found this clearer as any explanation i found on the net so far.

Kind Regards,

Jean-Philippe