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

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)

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 ?

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Frequent Visitor

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

 

View solution in original post

5 REPLIES 5
Microsoft
Microsoft

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 correctthe 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.

v-xiaochen-msft_0-1608101316692.jpeg

 

 

2\ This is my TableHistory list.

v-xiaochen-msft_1-1608101316692.jpeg

 

 

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:

v-xiaochen-msft_2-1608101316694.jpeg

 

 

Best Regards,

Wearsky

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.

 

 

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

Frequent Visitor

I succeeded with much more simple code & process

I can post the code for anyone interested

Frequent Visitor

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

 

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Users online (26,569)