how to add multiples enum values to filter in QBDS range.

(0) Share

Report

Posted on by @rp@n

Hi Team,

Kindly let me know how to add multiples type from retailTransactionTable in qbds range.

The typw will be Income expense, float entry, Remove tender

Query                   query   = new Query();
        QueryRun                queryRun;
        QueryBuildDataSource    qbds;

        qbds = query.addDataSource(tableNum(RetailTransactionTable));
        qbds.addRange(fieldNum(RetailTransactionTable, TransDate)).value(SysQuery::range(fromDate, toDate));
        qbds.addRange(fieldNum(RetailTransactionTable, store)).value(con2Str(stores));
        // TO DO
        // LIKE to put range based on type like Income expense, float entry, Remove tender***********

        queryRun    = new queryrun(query);

        while (queryRun.next())
        {
            retailTransactionTable     = queryRun.get(tablenum(RetailTransactionTable));
        }
}        }

Kindly send me the syntax pls.

Pls give me more shed on this.

thanks!

I have the same question (0)

All responses (12)

Answers (2)

Suggested answer

Mohit Rampal 12,565 Moderator on at

Like (0)

Report
Hi, You can add range on the Transaction Type Field

QueryBuildRange qbr = qbds.addRange(fieldNum(RetailTransactionTable, TransactionType)); qbr.value(strFmt('((TransactionType == %1) || (TransactionType == %2))', any2int(TransactionType::Income), any2int(TransactionType::Expense)));

vimalaxapta.blogspot.com/.../how-to-add-set-multiple-enum-values-in.html

https://erpdax.wordpress.com/tag/query-range/

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report
Hi Mohit,

I tried the above expression but report is totally hanged.

I don't wants to considered other elements in my report unnecessary in loop. just to be considered only those 3 transactionType in my report.

I have debugged the query return the below ouput. It seems correct only I guess

{Query object 890528b0: SELECT * FROM RetailTransactionTable(RetailTransactionTable_1) ORDER BY RetailTransactionTable.type ASC WHERE ((transDate>={ts '2022-12-20 00:00:00.000'} AND transDate<={ts '2022-12-20 00:00:00.000'})) AND ((store = N'104096')) AND ((((TransactionType == Income expense) || (TransactionType == Float entry) || (TransactionType == Remove tender))))}

The Type = 0 for RetailTransactionType::LogOff

got the below message

Here the transaction type unnecessary taking the element 0 that' s I don't want to enter in the loop.

I just looking for only above mentioned 3 transactions type which I needed only to considered in my report,

Kindly let me know what to modify?

Pls give me more shed on this.

thanks!

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report
Hi Mohit,

Here is my complete code

AmicisPayInTable payInTable; RetailTransactionTable retailTransactionTable; RetailTransactionPaymentTrans retailTransactionPaymentTrans; RetailTransactionInfocodeTrans retailTransactionInfoCodeTrans; RetailTransactionIncomeExpenseTrans retailTransactionIncomeExpenseTrans; RetailIncomeExpenseAccountTable retailIncomeExpenseAccountTable; RetailStoreId wareHouse; TransDate payDate; Amount amount; RetailInfocodeId driverId; Name driverName; RetailInfocodeInformation bagNumber; RetailTransactionType type; Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr; qbds = query.addDataSource(tableNum(RetailTransactionTable)); qbds.addRange(fieldNum(RetailTransactionTable, TransDate)).value(SysQuery::range(fromDate, toDate)); qbds.addRange(fieldNum(RetailTransactionTable, store)).value(con2Str(stores)); qbr = qbds.addRange(fieldNum(RetailTransactionTable,Type)); qbr.value(strFmt('((TransactionType == %1) || (TransactionType == %2) || (TransactionType == %3))', (RetailTransactionType::IncomeExpense), (RetailTransactionType::FloatEntry), (RetailTransactionType::RemoveTender))); qbds.addSortField(fieldNum(RetailTransactionTable, Type),SortOrder::Ascending); queryRun = new queryrun(query); while (queryRun.next()) { retailTransactionTable = queryRun.get(tablenum(RetailTransactionTable)); type = retailTransactionTable.type; wareHouse = retailTransactionTable.store; payDate = retailTransactionTable.transDate; select * from retailTransactionIncomeExpenseTrans where retailTransactionIncomeExpenseTrans.terminal == retailTransactionTable.terminal && retailTransactionIncomeExpenseTrans.store == retailTransactionTable.store && retailTransactionIncomeExpenseTrans.transactionId == retailTransactionTable.transactionId; // In the case of paid in - amount should be visible as positive. In the case of the paid out- amount should be visible as negative. if (retailTransactionIncomeExpenseTrans) { // to do amount = retailTransactionIncomeExpenseTrans.amount; } select * from retailTransactionInfoCodeTrans where retailTransactionInfoCodeTrans.terminal == retailTransactionTable.terminal && retailTransactionInfoCodeTrans.store == retailTransactionTable.store && retailTransactionInfoCodeTrans.transactionId == retailTransactionTable.transactionId; if (retailTransactionInfoCodeTrans) { driverId = retailTransactionInfoCodeTrans.infocodeId; //driverName = bagNumber = retailTransactionInfoCodeTrans.information; } // Tmp table data insert payInTable.clear(); payInTable.Store = wareHouse; payInTable.PayDate = payDate; payInTable.Amount = amount; payInTable.insert(); }

Pls give me more shed on this.

thanks!

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Hi, if you check the code I provided has any2int in qbr.value in my enum values. Please apply it and try again.

Was this reply helpful? Yes No
Verified answer

Martin Dráb 237,912 Most Valuable Professional on at

Like (0)

Report
You should avoid the extended query syntax unless necessary, because it's difficult to write and debug. It's not needed in your case; simply add three ranges for the same field:

qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(queryValue(RetailTransactionType::IncomeExpense)); qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(queryValue(RetailTransactionType::FloatEntry)); qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(queryValue(RetailTransactionType::RemoveTender));

Was this reply helpful? Yes No
Mohit Rampal 12,565 Moderator on at

Like (0)

Report

Martin's code should work, also you can check this old thread.

community.dynamics.com/.../how-to-use-multiple-enum-values-as-query-filter

Was this reply helpful? Yes No
@rp@n 30 on at

Like (1)

Report
Hi Martin & Mohit

Thanks for your support. I applied Martin's suggestions and it is working as expected

AmicisPayInTable payInTable; RetailTransactionTable retailTransactionTable; RetailTransactionPaymentTrans retailTransactionPaymentTrans; RetailTransactionInfocodeTrans retailTransactionInfoCodeTrans; RetailTransactionIncomeExpenseTrans retailTransactionIncomeExpenseTrans; RetailIncomeExpenseAccountTable retailIncomeExpenseAccountTable; RetailStoreId wareHouse; TransDate payDate; Amount amount; RetailInfocodeId driverId; Name driverName; RetailInfocodeInformation bagNumber; RetailTransactionType type; Query query = new Query(); QueryRun queryRun; QueryBuildDataSource qbds; QueryBuildRange qbr; qbds = query.addDataSource(tableNum(RetailTransactionTable)); qbds.addRange(fieldNum(RetailTransactionTable, TransDate)).value(SysQuery::range(fromDate, toDate)); qbds.addRange(fieldNum(RetailTransactionTable, store)).value(con2Str(stores)); qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(queryValue(RetailTransactionType::IncomeExpense)); qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(queryValue(RetailTransactionType::FloatEntry)); qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(queryValue(RetailTransactionType::RemoveTender)); qbds.addSortField(fieldNum(RetailTransactionTable, Type),SortOrder::Ascending); queryRun = new queryrun(query); while (queryRun.next()) { retailTransactionTable = queryRun.get(tablenum(RetailTransactionTable)); type = retailTransactionTable.type; wareHouse = retailTransactionTable.store; payDate = retailTransactionTable.transDate; select * from retailTransactionIncomeExpenseTrans where retailTransactionIncomeExpenseTrans.terminal == retailTransactionTable.terminal && retailTransactionIncomeExpenseTrans.store == retailTransactionTable.store && retailTransactionIncomeExpenseTrans.transactionId == retailTransactionTable.transactionId; // In the case of paid in - amount should be visible as positive. In the case of the paid out- amount should be visible as negative. if (retailTransactionIncomeExpenseTrans) { // to do amount = retailTransactionIncomeExpenseTrans.amount; } select * from retailTransactionInfoCodeTrans where retailTransactionInfoCodeTrans.terminal == retailTransactionTable.terminal && retailTransactionInfoCodeTrans.store == retailTransactionTable.store && retailTransactionInfoCodeTrans.transactionId == retailTransactionTable.transactionId; if (retailTransactionInfoCodeTrans) { driverId = retailTransactionInfoCodeTrans.infocodeId; //driverName = bagNumber = retailTransactionInfoCodeTrans.information; } payInTable.clear(); payInTable.Store = wareHouse; payInTable.PayDate = payDate; payInTable.Amount = amount; payInTable.insert(); }

Debugger Output - Query returned

{Query object 874f5570: SELECT * FROM RetailTransactionTable(RetailTransactionTable_1) ORDER BY RetailTransactionTable.type ASC WHERE ((transDate>={ts '2022-12-20 00:00:00.000'} AND transDate<={ts '2022-12-20 00:00:00.000'})) AND ((store = N'104096')) AND ((type = 18) OR (type = 5) OR (type = 4))}

Thanks !

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report

Hi Mohit & Martin

I have one another table RetailTransactionPyementTrans. In this table, one field is there tender type.

The requirement is , if the value is 9 in tender type than that transaction should not considered in report.

How will i add this table to qbds with range.

pls give me more she don this.

thanks!

Was this reply helpful? Yes No
Verified answer

Mohit Rampal 12,565 Moderator on at

Like (0)

Report
You didn't provided much information on RetailTransactionPyementTranstable, I am assuming it has relation with RetailTransactionTable table and TenderType is a string field. You can declare one more variable qbds1 next to qbds in your code.

qbds1 = qbds.addDataSource(tableNum(RetailTransactionPyementTrans)); qbds1.relations(true); qbds1.addRange(fieldNum(RetailTransactionPyementTrans, TenderType)).value(SysQuery::valueNot("9"));

Was this reply helpful? Yes No
@rp@n 30 on at

Like (0)

Report
Thanks Mohit for the reply.

I have debugged and it seems correct as the below output. It made relationsautomatically and build the range accordingly

{Query object 897960a0: SELECT * FROM RetailTransactionTable(RetailTransactionTable_1) ORDER BY RetailTransactionTable.type ASC WHERE ((transDate>={ts '2022-12-20 00:00:00.000'} AND transDate<={ts '2022-12-20 00:00:00.000'})) AND ((store = N'104096')) AND ((type = 18) OR (type = 5) OR (type = 4)) JOIN * FROM RetailTransactionPaymentTrans(RetailTransactionPaymentTrans_1) ON RetailTransactionTable.Channel = RetailTransactionPaymentTrans.Channel AND RetailTransactionTable.store = RetailTransactionPaymentTrans.store AND RetailTransactionTable.terminal = RetailTransactionPaymentTrans.terminal AND RetailTransactionTable.transactionId = RetailTransactionPaymentTrans.transactionId AND ((NOT (tenderType = N'9')))}

thanks!

Was this reply helpful? Yes No