cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Kudo Kingpin
Kudo Kingpin

Filter and Patch a Share Point List

Hi there,

 

I hope someone could help me here:

 

This is my situation:

An the beginning of the year I have to produce a list of employees with their savings, every month somebody give me the month end interest and I have to apply that interest to all of the employees that fit into that year. So I need to filter my saherpoint list with the year i.e 2019 and then pacth the month once I have the interest for that month.

 

In the sample bellow you can see that I have two months Jan and Feb with their interest value, so I need to get March value and hit the Patch this month to have a formula that multiply "interest + Savings" and repeat this for every employee in that list.

 

Could someone point me in the right direction?

 

Thank you in advance,

 

Regards,

 

Capture.PNG 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Resolver II
Resolver II

Re: Filter and Patch a Saher Point List

@sajarac  try to first bring into a collection (ColMySPLTable) your records you need changing then you use this collection in the ForAll to update your records. In sample below I am assuming you have an ID field in your table.

 

ClearCollect(ColMySPLTable,Filter(MYSPLTable,Year=Cboyear.Selected.Year));

ForAll(
ColMySPLTable,
UpdateIf(
MySPLTable,
ID = ID,
{MarchInterest: Savings * March_Interest.Text}

)
)

------------------------------------------------------------------------------OfficePowerUser.com------------------------------------------------------------------------
If this post helps answer your question, please click on
“Accept as Solution” to help other members. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Highlighted
Resolver II
Resolver II

Re: Filter and Patch a Saher Point List

Perhaps it doesn't like the ID=ID. So let's try this.

ClearCollect(
ColMySPLTable,
AddColumns(
Filter(
MySPLTable,
Year=Cboyear.Selected.Year
),
"TheID",
ID
)
);
ForAll(
ColMySPLTable,
UpdateIf(
MySPLTable,
ID = TheID,
{MarchInterest: Savings * March_Interest.Text}
)
)

View solution in original post

14 REPLIES 14
Highlighted
Resolver II
Resolver II

Re: Filter and Patch a Saher Point List

@sajarac  you should be able to do this using a ForAll.

 

 

-----------------------------------------------------OfficePowerUser.com---------------------------------------------------
If this post helps answer your question, please click on
“Accept as Solution” to help other members. If you thought this post was helpful, please give it a Thumbs Up.

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filter and Patch a Saher Point List

Hi there,

 

Thank you very much for your prompt response, not familiar with the "ForAll" formula. Do you think that something like this will work?

 

btnpatch

ForAll((MySPLTable,Year=Cboyear.Selected.Year),Savings*March_Interest)

 

Picture1.png

Highlighted
Resolver II
Resolver II

Re: Filter and Patch a Saher Point List

@sajarac  try to first bring into a collection (ColMySPLTable) your records you need changing then you use this collection in the ForAll to update your records. In sample below I am assuming you have an ID field in your table.

 

ClearCollect(ColMySPLTable,Filter(MYSPLTable,Year=Cboyear.Selected.Year));

ForAll(
ColMySPLTable,
UpdateIf(
MySPLTable,
ID = ID,
{MarchInterest: Savings * March_Interest.Text}

)
)

------------------------------------------------------------------------------OfficePowerUser.com------------------------------------------------------------------------
If this post helps answer your question, please click on
“Accept as Solution” to help other members. If you thought this post was helpful, please give it a Thumbs Up.

View solution in original post

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filter and Patch a Saher Point List

@datamaster , thanks again for you help.

 

When you said bring to a collection is like this?

 

My Share Pint List "TblSavings", I need to add a gallery adn the items in that gallery = TblSavings?

 

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filter and Patch a Saher Point List

I Just found this article very handy and is additional to your explanation:

 

To create a Collection variable (myCollection) that links to and holds the contents of a SharePoint list (mySPList) do the following steps:

  1. In your application, add a datasource to your application that connects to your SharePoint list (e.g. mySPList)
  2. Create a temporary page with a button on it.
  3. Change the OnSelect action of that button to be: ClearCollect(myCollection,mySPList)
  4. Run the app and click that button
  5. Stop the app and go back to your OnStart event for your main application and again put in: ClearCollect(myCollection,mySPList)

Steps 1-4 create a Collection that includes the fields/data from your SharePoint list and allows it to be referenced ANYWHERE in your application properly. Step 5 (which is what you probably tried first) allows you to have your list updated at start but does not actually create the list for your application at development time.

You can easily test this now by creating another temporary Data Table and changing the Items property for that table to be the name of your Collection (e.g. myCollection). It will now display the contents of your SharePoint List.

This is very handy for having your application access semi-static content that is not updated by the application (e.g. context-sensitive help fields). It drastically improves the speed of this for your applications where you want to populate a text or HTML field based on Lookups (e.g. LookUp(HTMLHelp,Parent.DisplayName=FieldName,HTML)

 

 

I did it, and now I am able to bring my collection back, now once I put the code for my "Apply" button, it show unexpected characters. Characters are used in the formula in an unexpected way. 

Highlighted
Resolver II
Resolver II

Re: Filter and Patch a Saher Point List

ClearCollect(colTblSavings,TblSavings)

 

colTblSavings is your collection and TBLSavings is your data source. The structure of both are identical and then you can do a ForAll on the collection colTBLSavings to update your "TBLSavings" record source.

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filter and Patch a Saher Point List

I can get it, maybe is because in my data source SPL, I have the ID not showing in the current view?

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filter and Patch a Saher Point List

Finally I found why it was not working. I changed the formula to end with " ; " insted of " : " and now is working fine.

 

ClearCollect(ColMySPLTable,Filter(MYSPLTable,Year=Cboyear.Selected.Year)):

 

Thank you very much for all your help!!

 

🙂

 

 

Highlighted
Kudo Kingpin
Kudo Kingpin

Re: Filter and Patch a Saher Point List

Hi there,

 

Sorry to bother you again. I was happy with the solution that you provided, but I have been testing and found an error and I hope you can help me here?

 

The solution to patch the records selected form my dropdown box works fine it patch, but in fact it is patching all of the records in that column, not the records selected with the Dropdown.

 

This is the formula:

 

ClearCollect(myCollection,Filter(MSP,Year=Dropyear_1.Selected.Year));

ForAll(
myCollection,
UpdateIf(
MSP,
ID = ID,
{Quarter3: (LabelQ3_1.Text/100*MSPMatching/4)}

)
)

 

Any help will be very appreciated.

 

Regards.

 

 

Helpful resources

Announcements
secondImage

Demo-Extravaganza 2020

Check out these cool Power Apps & vote on your favorite!

secondImage

Community Highlights

Check out whats happening in Power Apps

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (9,951)