cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
sajarac
Level 8

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
datamaster
Level 8

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.

datamaster
Level 8

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

14 REPLIES 14
datamaster
Level 8

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.

sajarac
Level 8

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

datamaster
Level 8

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.

sajarac
Level 8

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?

 

 

sajarac
Level 8

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. 

datamaster
Level 8

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.

sajarac
Level 8

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?

 

sajarac
Level 8

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!!

 

:-)

 

 

sajarac
Level 8

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
firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

SecondImage

Power Platform Summit North America

Register by September 5 to save $200

Top Kudoed Authors
Users Online
Currently online: 123 members 4,677 guests
Please welcome our newest community members: