ForAll (ForEach) record of a datasource assigned to a gallery -> Check via a common primary key to see if it exist already on a SharePoint List (as table)

[Ritoxik]

I was hoping to do something following the logic here under, but this is not allowed

Also it is really a pity that we have function as First() and Last() to get records in a dataset but we do not have Next() or Previous(), and I see people asking for this since 2017...

Here under the idea is to build a Primary variable for each item (on one datasource assigned to Gallery)

and increment a counter for what exist already and what does not exist in another Table (SharePoint List)

Set(cExist,0);
Set(cNotExist,0);

ForAll(DataSource1,
    Set(MyKEY,
         Concatenate(Text
              (Today(),"[$-en-US]yyyy-mm"),"_",
               DataSource.CountryID,"_",
               DataSource.QuestionID)
         );
     If(CountRows(Filter(TableHistory, HistoryKEY = MyKEY)) > 0, Set(cExist,cExist+1), Set(cNotExist,cNotExist+1))
 )

How can we achieve something similar in PowerApps ?

[v-xiaochen-msft]

Hi @Ritoxik ,

Could you tell me:

1. Is CountryID a “single line of text” column?
2. Is QuestionID a “single line of text” column?

If my assumptions are correct，the point is to put your variables in the collection. Because the set() function cannot be used in the forall() function.

I've made a test for your reference:

1\ This is my DataSource1 list.

2\ This is my TableHistory list.

3\ Add a button control and set its onselect property to:

ClearCollect(

    Test,

    {Thevar: ""}

);

ClearCollect(

    Test2,

    {cExist: 0}

);

ClearCollect(

    Test3,

    {cNotExist: 0}

);

Clear(Test);

ForAll(

    DataSource1,

    Patch(

        Test,

        Defaults(Test),

        {

            Thevar: Concatenate(

                Text(

                    Today(),

                    "[$-en-US]yyyy-mm"

                ),

                "_",

                CountryID,

                "_",

                QuestionID

            )

        }

    );

    If(

        CountRows(

            Filter(

                TableHistory,

                HistoryKEY = Last(Test).Thevar

            )

        ) > 0,

        Patch(

            Test2,

            First(Test2),

            {cExist: First(Test2).cExist + 1}

        ),

        Patch(

            Test3,

            First(Test3),

            {cNotExist: First(Test3).cNotExist + 1}

        )

    )

);

Set(

    cExist,

    First(Test2).cExist

);

Set(

    cNotExist,

    First(Test3).cNotExist

)

The result is as follows:

Best Regards,

Wearsky

[Ritoxik]

Thank you Xiaochen:

1. Is CountryID a “single line of text” column? =>  Yes, this is just Text
2. Is QuestionID a “single line of text” column? => Yes, this is just Text

Thanks Xiaochen

I will give a try to your proposal

Thus, I understood that the usage of ForAll goes with Patch

I did not need to 'Patch' any thing, your idea is to use Patch anyway with the usage of a test collection, that should be OK.

At the end I need to test the cNotExit, if this is lower than 1 (nothing that still need to be created)

Then I would move/navigate to the next screen

* Note: I also imagined a better and simpler way to achieve the same:

I could remove from the gallery collection the record as soon as we create the counter part in the other table

then we are left in the gallery only with elemnt that still need to be created in other table, at the end with the last item in the gallery, I could then navigate to next screen when this last record is also created.

I will try to implement both solution, yours and the one just proposed

Thanks, Have a great day ahead.

[Ritoxik]

I'm trying to make it more simple:

I want to 'delete' records in table2 

if they are found in table1

PowerApps complains that Table2 is not a Collection

Here is the code;

ForAll(Table1,
Remove(Table2,First(Filter(Table2,Table2_ID=Table1_ID)))
);

[Ritoxik]

I succeeded with much more simple code & process

I can post the code for anyone interested

[Ritoxik · Accepted solution]

This is the code that finally match what I need to achieve:

1st build a collection (ColQuestSrc1)

Set a Variable to make a selection (MyKEY1)

Build a Table Data Source using the selection Key(MyKEY1)

A ForAll loop with a "Remove" for what already exist

If all is Removed set a Variable that nothing is left (bNoQuestionS1)

ClearCollect(ColQuestSrc1,
Sort(Filter(Server_5S_Questions, QuestionSectionID = "S1")
, QuestionID
, Ascending
)
);
Set(MyKEY1,
Concatenate(Text
(Today(),"[$-en-US]yyyy-mm"),"_",
DropdownSiteCode_1.SelectedText.Result)
);
Set(RatingDataSrc1,
Sort(Filter(Server_5S_Rating, RatingMonthSite = MyKEY1)
, RatingKEY
, Ascending
)
);
ForAll(RatingDataSrc1,
Remove(ColQuestSrc1,First(Filter(ColQuestSrc1,QuestionID=RatingQuestionID)))
);
If(IsEmpty(ColQuestSrc1)
, Set(bNoQuestionS1,true)
, Set(bNoQuestionS1,false)
);