How to add 2 values in Query range in Query range

(0) Share

Report

Posted on by D365FO Avatar

355

This is a method for filtering finish status records in project table, When Show project is Active - All records will come except finished one. When Show project is All - All projects will come.

public static void applyProjectStateFilter(FormDataSource _formDataSource, int selection)
    {
        QueryBuildRange qbr = SysQuery::findOrCreateRange(_formDataSource.query().dataSourceTable(tableNum(ProjTable)), fieldNum(ProjTable, Status));

        if (ProjProjectsListCustomFilterHelper::checkProjActiveAll(selection))
        {
            qbr.value(SysQuery::valueUnlimited());
        }
        else
        {
            // Active = all non-completed projects
            qbr.value(SysQuery::valueNot(ProjStatus::Completed));
        }

		_formDataSource.executeQuery();
    }

so as per the code, I want to add one more value , so that with Active status- all records should come except finish and user1 status.

qbr.value(SysQuery::valueNot(ProjStatus::User1));

public static void applyProjectStateFilter(FormDataSource _formDataSource, int selection)
    {
        QueryBuildRange qbr = SysQuery::findOrCreateRange(_formDataSource.query().dataSourceTable(tableNum(ProjTable)), fieldNum(ProjTable, Status));

        if (ProjProjectsListCustomFilterHelper::checkProjActiveAll(selection))
        {
            qbr.value(SysQuery::valueUnlimited());
        }
        else
        {
            // Active = all non-completed projects
            qbr.value(SysQuery::valueNot(ProjStatus::Completed));
            qbr.value(SysQuery::valueNot(ProjStatus::User1));

            
        }

		_formDataSource.executeQuery();
    }

All responses (8)

Answers (1)

Suggested answer

D365FO Avatar 355 on at

Like (0)

Report

RE: How to add 2 values in Query range in Query range

I have implemented this by using this below line.

qbr.value(SysQuery::valueNot(ProjStatus::Completed) + ',' + SysQuery::valueNot(ProjStatus::User1));

Thanks Martin , for your support.
Suggested answer

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

Like (0)

Report

RE: How to add 2 values in Query range in Query range

Hi, As Martin mentioned you need AND in the query not OR.

Check out this article.

https://wp.me/p8Z27u-U

Also, you can debug the code and find the query being generated from your code either by passing into a string variable or using info(query.toString()).
Verified answer

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

Like (0)

Report
RE: How to add 2 values in Query range in Query range

If you add two ranges for the field, both ranges are applied (with OR condition). For example:

qbds.addRange(fieldNum(ProjTable, Status)).value(queryValue(ProjStatus::Completed)); qbds.addRange(fieldNum(ProjTable, Status)).value(queryValue(ProjStatus::User1));

Regarding your code, it's a completely different thing. You're trying to build a SQL expression, not using two values separated with comma.
D365FO Avatar 355 on at

Like (0)

Report

RE: How to add 2 values in Query range in Query range

As you suggested -

"You can add two ranges for the same field, each with its own value. Or use a single range with comma-separated values."

For your first suggestion, If I do so than will it not only let the last one ?

For second suggestion, I have use single range with comma separated value like below-

qbr.value(strFmt('((Status != "%1") && (Status != "%2"))',

queryValue(ProjStatus::Completed),

queryValue(ProjStatus::User1)));

Could you please help me to get it more better
Martin Dráb 230,370 Most Valuable Professional on at

Like (0)

Report

RE: How to add 2 values in Query range in Query range

I recommend those two solutions I suggested. Your approach is error-prone, as you found.

Of course that queryFiltersChanged() won't work correctly if you change the value to something that it doesn't expect. You'll need either need to change this logic too, or re-consider your approach.
D365FO Avatar 355 on at

Like (0)

Report
RE: How to add 2 values in Query range in Query range

Yes, I have added this code, which overwrite the first one. If I try to do like this -

qbr.value(strFmt('((Status != "%1") && (Status != "%2"))',

queryValue(ProjStatus::Completed),

queryValue(ProjStatus::User1)));

Than also it is not working as in form projProjectsListPage, method queryFiltersChanged the value is not matching and it is only showing ALL.

protected internal void queryFiltersChanged() { #define.companyId(1) #define.isSelected(2) super(); Query savedQuery = projTable_ds.queryRun().query(); QueryBuildDataSource qbdsProject = savedQuery.dataSourceTable(tableNum(ProjTable)); QueryBuildRange qbrStatus = qbdsProject.findRange(fieldNum(ProjTable, Status)); if (qbrStatus) { if (qbrStatus.enabled() && qbrStatus.value() == SysQuery::valueNot(ProjStatus::Completed)) { CtrlActiveAll.selection(enum2int(ProjActiveAll::Active)); } else { CtrlActiveAll.selection(enum2int(ProjActiveAll::All)); } }
Martin Dráb 230,370 Most Valuable Professional on at

Like (0)

Report
RE: How to add 2 values in Query range in Query range

Let's focus on this part:

qbr.value(SysQuery::valueNot(ProjStatus::Completed)); qbr.value(SysQuery::valueNot(ProjStatus::User1));

Your second line overwrites the value set by the first line, therefore the first line has no effect at all. You can add two ranges for the same field, each with its own value. Or use a single range with comma-separated values.
GirishS 27,832 Super User 2024 Season 1 on at

Like (0)

Report

RE: How to add 2 values in Query range in Query range

Hi D365FO Avatar,

You have already written code for adding range.

You can write COC for the class method applyProjectStateFilter and add the same code after next call. On the else part you can add Completed and User1 as a range. After adding check whether its working for your scenario.

Thanks,

Girish S.