How to get the selected query range value in Variable

Finance and Operations/AX Developer Forum
1

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()));
}

7607.Query.jpg
7607.Query.jpg875×456 78.5 KB

Before reaching to while.next() line i must get the variable values

Thanks

2 
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.

3

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());

}

4

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

5

“Is not working” is not a description I can work with. Please explain your problem.

6

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

7

Inorder to get the range value from query,try the below code

this.queryRun().query().dataSourceTable(tableNum(“YourTableName”)).findRange(fieldNum(“YourTableName”,“FieldName”)).value()

8

No its now working. Please not this is in job, we are not using this in form

9

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
10

there is no usage of filter variable in ur above code.

11

Fixed. Sorry about that.

12

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

13

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.