First a thank you out to @WarrenBelz I appreciated your blog and code on this subject. (Manageing Delegation with the with() Statment )
I have done what most people do, is I have tried to form the collection off the data, instead of planning the data for the collection.
I have a collection of over 100k records and grow daily, and only want the last 15k records for the collection (last day).
Here is the code I modified from your blog:
Clear(colAllList);
With(
{wSets:
With(
{wLimits:
With(
{wLimit:
Sort(
'2-Finviz_Stockss',
Value(FinViz_Primary_Key),
Descending
)
},
RoundDown(
First(wLimit).FinViz_Primary_Key / 2000,
0
) + 1
)
},
AddColumns(
RenameColumns(
Sequence(
wLimits,
0,
2000
),
"Value",
"LowID"
),
"HighID",
LowID + 2000
)
)
},
ForAll(
wSets As MaxMin,
Collect(
colAllList,
Filter(
'2-Finviz_Stockss', Value(FinViz_Primary_Key) > MaxMin.LowID && Value(FinViz_Primary_Key) <= MaxMin.HighID
),CountRows(colAllList)
)
)
);
ClearCollect(Todays_Filtered_Data, Filter(colAllList, crb36_import_date = Today())
)
My primary key is an auto generated number, and is unique, unfortunately it is stored as Text, so i used the Value statement to get the value to use in the Max and Min.
I was first trying to get the collection of all records, to ensure i had working code, before filtered it down to just the last 15k.
the last line, is creating a collection of just records imported today.
here is where it is strange. I used the countrows to see how many rows i retrieved, it is (124)
and the last collection is blank.
I have verified in the table that there is 15k rows of data with today's import_date.
This is a hard subject to get my head around. if there is a better way to propagate the data, i can change the schema, this is still in development.
As always, any ideas and suggestions are appreciated.
Guy
