Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
CustTable custTable;
QueryBuildRange range;
//int cnt, i;
;
qbd = query.addDataSource(tablenum(CustTable));
queryRun = new QueryRun(query);
queryRun.prompt(); // To Prompt the dialog
while (queryRun.next())
{
custTable = queryRun.get(tablenum(CustTable));
info(strfmt(“Customer %1, Name %2”,custTable.AccountNum, custTable.name()));
}
Before reaching to while.next() line i must get the variable values
Thanks
QueryBuildRange accountRange;
accountRange = qbd.findRange(fieldNum(CustTable, AccountNum));
if (accountRange)
{
info(accountRange.value());
}
Please ask questions about development in the Developer forum, otherwise I have to move them to the correct place.
Thanks for ur reply Martin, But its not working here is my code
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
QueryBuildRange range;
;
qbd = query.addDataSource(tablenum(HcmWorker));
queryRun = new QueryRun(query);
queryRun.prompt(); // To Prompt the dialog
range = qbd.addRange(fieldNum(HcmWorker, PersonnelNumber));
if (range)
{
info(range.value());
}
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
QueryBuildRange range;
;
qbd = query.addDataSource(tablenum(HcmWorker));
queryRun = new QueryRun(query);
queryRun.prompt(); // To Prompt the dialog
range = qbd.findRange(fieldNum(HcmWorker, PersonnelNumber));
if (range)
{
info(range.value());
}
================================
This is not working at all please help me
“Is not working” is not a description I can work with. Please explain your problem.
I 've a new table with one field
DemoTable and field name is Personnel Number
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
HcmWorker hcmWorker;
DemoTable demoTable;
;
qbd = query.addDataSource(tablenum(HcmWorker));
queryRun = new QueryRun(query);
queryRun.prompt(); // To Prompt the dialog
delete_from demoTable;
while (queryRun.next())
{
hcmWorker = queryRun.get(tablenum(HcmWorker));
demoTable.clear();
demoTable.PersonnelNumber = hcmWorker.PersonnelNumber;
demoTable.doInsert();
info(strfmt(“Personnel Number %1”,hcmWorker.PersonnelNumber));
}
==============================
Assume that at first time i dnt selected any range values in query window.
In this case it wil insert all hcmworker datas in demotable
NOw again i’m run this job in this case i selected only one range value for Personnel number in Query Window
before reaching to while(queryRun.next()) I must delete only selected employee record from demottable.
How to achieve this.
Thanks
Inorder to get the range value from query,try the below code
this.queryRun().query().dataSourceTable(tableNum(“YourTableName”)).findRange(fieldNum(“YourTableName”,“FieldName”)).value()
No its now working. Please not this is in job, we are not using this in form
It would be easier to spot your problem if you actually explained what doesn’t work instead of just repeating your requirement. Please pay more attention to the information you provide, in your own interest.
First of all, your code refers to the original query, not to the modified one. And you have to use query filters in AX 2012 (I didn’t notice it’s about AX 2012 until you used HcmWorker in your code).
This is the correct implementation:
Query query = new Query();
QueryRun queryRun;
QueryBuildDataSource qbd;
QueryFilter filter;
qbd = query.addDataSource(tablenum(HcmWorker));
queryRun = new QueryRun(query);
queryRun.prompt();
query = queryRun.query();
qbd = queryRun.query().dataSourceTable(tablenum(HcmWorker));
filter = query.findQueryFilter(qbd, fieldStr(HcmWorker, PersonnelNumber));
if (filter)
{
info(filter.value());
}
1 Like
there is no usage of filter variable in ur above code.
Hi Martine,
How to get range value when same table is used multiple time in query.
Like DimensionAttributeLevelValueView.DisplayValue for Main Account ,
DimensionAttributeLevelValueView.DisplayValue for Businesarea
Example query : SELECT * FROM GeneralJournalEntry(GeneralJournalEntry) ORDER BY GeneralJournalEntry.AccountingDate ASC WHERE ((SubledgerVoucherDataAreaId = N’200’)) AND
((AccountingDate>={ts ‘2017-04-04 00:00:00.000’} AND AccountingDate<={ts ‘2017-04-04 00:00:00.000’}))
‘JOIN * FROM GeneralJournalAccountEntry(GeneralJournalAccountEntry) ORDER BY GeneralJournalAccountEntry.GeneralJournalEntry ASC
ON GeneralJournalEntry.RecId = GeneralJournalAccountEntry.GeneralJournalEntry
JOIN DisplayValue, AttributeValueRecId FROM DimensionAttributeLevelValueView(DimAttCol_GeneralJuntEntry_LedgerDimension_5637144583)
ON GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId AND ((DimensionAttribute = 5637144583))
’ AND ((DisplayValue = N’2550053’))
JOIN DisplayValue, AttributeValueRecId FROM DimensionAttributeLevelValueView(DimAttCol_GeneralJuntEntry_LedgerDimension_5637145326)
ON GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId AND ((DimensionAttribute = 5637145326)) AND
((DisplayValue = N’201’))
Please help.
Thanks
First of all, let me format your query so we can read it. Please use Insert > Insert code in the rich formatting view for this purpose.
SELECT * FROM GeneralJournalEntry(GeneralJournalEntry)
ORDER BY GeneralJournalEntry.AccountingDate ASC
WHERE ((SubledgerVoucherDataAreaId = N'200'))
AND ((AccountingDate>={ts '2017-04-04 00:00:00.000'}
AND AccountingDate<={ts '2017-04-04 00:00:00.000'}))
JOIN * FROM GeneralJournalAccountEntry(GeneralJournalAccountEntry)
ORDER BY GeneralJournalAccountEntry.GeneralJournalEntry ASC
ON GeneralJournalEntry.RecId = GeneralJournalAccountEntry.GeneralJournalEntry
JOIN DisplayValue, AttributeValueRecId FROM DimensionAttributeLevelValueView(DimAttCol_GeneralJuntEntry_LedgerDimension_5637144583)
ON GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId
AND ((DimensionAttribute = 5637144583))
AND ((DisplayValue = N'2550053'))
JOIN DisplayValue, AttributeValueRecId FROM DimensionAttributeLevelValueView(DimAttCol_GeneralJuntEntry_LedgerDimension_5637145326)
ON GeneralJournalAccountEntry.LedgerDimension = DimensionAttributeLevelValueView.ValueCombinationRecId
AND ((DimensionAttribute = 5637145326))
AND((DisplayValue = N'201'))
Now I see that you have DimensionAttributeLevelValueView twice there, so it seems that your problem isn’t about ranges - you don’t know how to get these two datasources.
If you’re finding a datasource in a query by table ID, you can use the second argument of dataSourceTable(), occurrence, to specify the index. Or you could iterate all datasource and pick just those for the table you’re interested in.