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!

All responses (12)

Answers (2)

Suggested answer

Mohit Rampal 12,554 Super User 2024 Season 1 on at

Like (0)

Report
RE: how to add multiples enum values to filter in QBDS range.

I have written this code for reference and didn't compiled the code.

qbds.addRange(fieldNum(RetailTransactionTable, Type)).value(strFmt('((%1.%2 == %4) && (%1.%3 == %5)))', query.dataSourceTable(tableNum(RetailTransactionTable)).name(), fieldStr(RetailTransactionTable, Type), fieldStr(RetailTransactionTable, Description), any2int(RetailTrancationType::StratingAmount), "Bank-In"));

vmendivilblog.wordpress.com/.../
@rp@n on at

Like (0)

Report

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

Hi team,

In above query, I like to add retailTransaction.description == "Bank-in" with AND condition in the above yellow marked range for startingAmount only. Kindly let me know how will achieve this.

For example - Similar like yellow marked if condition

This is only applicable for transactionType startingAmount , other ranges should work as it is.

Pls give me more shed on this.

thanks!
@rp@n on at

Like (0)

Report
RE: how to add multiples enum values to filter in QBDS range.

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!
Verified answer

Mohit Rampal 12,554 Super User 2024 Season 1 on at

Like (0)

Report
RE: how to add multiples enum values to filter in QBDS range.

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"));
@rp@n on at

Like (0)

Report

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

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!

@rp@n on at

Like (1)

Report

RE: how to add multiples type in qbds range.

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 !

Mohit Rampal 12,554 Super User 2024 Season 1 on at

Like (0)

Report

RE: how to add multiples type in qbds range.

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

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

Martin Dráb 230,842 Most Valuable Professional on at

Like (0)

Report
RE: how to add multiples type in qbds range.

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));
Mohit Rampal 12,554 Super User 2024 Season 1 on at

Like (0)

Report

RE: how to add multiples type in qbds range.

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

@rp@n on at

Like (0)

Report

RE: how to add multiples type in qbds range.

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!