Filter and Patch a Share Point List

[sajarac]

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,

 

[datamaster]

@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]

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)

[datamaster · Accepted solution]

@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]

@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]

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]

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]

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

[sajarac]

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]

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.