Create a collection based on last entry for each item in SP List, then patch to list to create new entries......

[BrendanZ1]

Hi all,

Once again apologies if the title of the thread is misleading, but I am hoping to get some guidance on the following scenario:

I am building a stock take application and I want to generate the items requiring a count based off previous entries in my SP List.

Within my app, currently you can manually add stock items to the SP List. These items can then be edited as part of counting stock.
Rather than manually entering almost 100 items each month, I want to do this by looking at the last counts for each item, and if it has a total count greater than "1", then create a new entry for that item in the list.

As an example, I have counted approx 95 items on the 31/7/20.

Now I am planning to do my next stock take at the end of August and I want to pre-fill my list. I was thinking of using a collection and then patching this? So create collection as per my suggestion above, then patch to create new entries. Then I can just go ahead and start counting.....

SPList is - StockTake and has the following columns (CountDate, StockCode, StockDescription, TotalCount and CountComplete)

TotalCount is a number column (using powerapps to do the calculating)
CountComplete is a Yes/No column

If more information is needed please let me know. 

[BrendanZ1]

Just bumping this hoping someone can help me out.

Since posting, I have managed to create a collection that populates a gallery with the following code;

ClearCollect(colStockTake, Filter(StockTake_1, Total>0))

This worked for the first time as my SP List only had one stock take count in there.
Once I entered a second round of stock take and then used this function, of course it pulled all items as they all had a total count greater than 0.

What I am hoping to do, is create the collection so that it looks at the latest entry for each item, and if it has a value greater than 0, then put it in the collection. The best way I believe to tackle this, is to use the StockCode. So for example I have the following in my list:

DateCounted	StockCode	Total
30/7/20	FR-234	10
30/7/20	GHFR11	12
30/7/20	BRS34-2	15
31/8/20	FR-234	6
31/8/20	GHFR11	12
31/8/20	BRS43-2	10

Or maybe if I can collect based on the latest date and if the item has a count greater than 0?

[BrendanZ1 · Accepted solution]

Sometimes it helps to theorise an idea with words - I found a solution in another thread based on using the date column to narrow down the collection date. So I have kept my collect code the same, but used the gallery to filter my list to only show items that were from the latest count.

Here is the thread just in case anyone needs this info:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Filter-Sharepoint-list-by-latest-date/td-p/1...