You’re offline. This is a read only version of the page.
Announcements
10
Hi,
I have calculated that in September-2020 25.000.000 records are used.
To solve this I need to know which tables use so much records in 1 month
Does anybody have a piece of code like:
While walk trough every table inAOT
{
if(table.createdDate)
{
Select count of (recid) from table, where createdDate >= 01\09\2020 && createdDate <= 30\09\2020
Info(tablename, table.recid)
}
}
I'm working with some older version of Axapta, but maybe soemone knows the code and I can use it on that version
Thanks. This and with some other code I have found, I could fix it:
Share
Report
All responses (
Answers (
static void CountRecsMonth(Args _args)
{
UtilElements _UtilElements;
SysDictTable SysDictTab;
NumberOf TotalField;
tableId tabel;
Common common;
DictTable dictTable;
int x;
;
while select _utilelements order by name
where _utilelements.recordType == utilElementType::Table
{
if(tableName2id(_utilelements.name) != 0 && tableName2id(_utilelements.name) != tabel)
{
SysDictTab = SysDictTable::newTableId(tableName2Id(_utilElements.name));
TotalField = SysDictTab.fieldCnt();
if (TotalField)
{
dictTable = new DictTable(tableName2id(_utilelements.name));
common = dictTable.makeRecord();
common.selectForUpdate(false);
select count(RecId) from common
where common.(fieldName2id(dictTable.id(), 'CreatedDate')) >= 01\09\2020
&& common.(fieldName2id(dictTable.id(), 'CreatedDate')) <= 30\09\2020;
info(strfmt("%1;%2",_utilelements.name,common.RecId));
}
tabel = tableName2id(_utilelements.name);
}
}
}
Hi Satch,
Here are some references on how to use UtilElements
How to select data if you have only TableId
You just need to add check if CreatedDateTime is activated for table
In the end, you should get something like this
select count(RecId) from common
where common.(fieldName2id(dictTable.id(), 'CreatedDateTime')) <= toDate
&& common.(fieldName2id(dictTable.id(), 'CreatedDateTime')) >= fromDate;
That's the issue. I was trying to check if we could use "Common". But, by default common does not table CreatedDateTime object.
Unless and until you query by individual tables, I don't see how you could use createdDateTime.
You can use SysDictTable class. To find total number of records, you can use
SysDictTable::CASRecordcount(UtilIdElements.name);
Hi Satch,
I recommend not to use x++ to get this information, but use SQL server features instead.
You can start with top biggest tables by rows or by size and narrow down list of tables for analysis.
Hi, thanks.
You're right. This is only valid for tables with created date. There's no other option it seems.
But "translating" Utildelements to Table is some grey area for me...:(
How to translate it so that I can do: Select from 'Table'
Where 'Table' is the table from utilElements
Hi Satch,
Not sure if it will be possible to get this count as all tables don't have CreatedDateTime property enabled.
To loop through all the tables you can use UtilIdElements and filter on the basis of field RecordType value UtilElementType::Table.
#1
André Arnaud de Cal...
294,208
Super User 2025 Season 1
#2
Martin Dráb
232,970
Most Valuable Professional
#3
nmaenpaa
101,158
Moderator
