How to add 2 values in Query range in Query range

(0) Share

Report

Posted on by D365FO Avatar

668

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

I have the same question (0)

All responses (8)

Answers (1)

GirishS 27,827 Moderator on at

Like (0)

Report

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.

Was this reply helpful? Yes No
Martin Dráb 237,817 Most Valuable Professional on at

Like (0)

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

Was this reply helpful? Yes No
D365FO Avatar 668 on at

Like (0)

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

Was this reply helpful? Yes No
Martin Dráb 237,817 Most Valuable Professional on at

Like (0)

Report

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.

Was this reply helpful? Yes No
D365FO Avatar 668 on at

Like (0)

Report

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

Was this reply helpful? Yes No
Verified answer

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

Like (0)

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

Was this reply helpful? Yes No
Suggested answer

Mohit Rampal 12,565 Moderator on at

Like (0)

Report

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

Was this reply helpful? Yes No
Suggested answer

D365FO Avatar 668 on at

Like (0)

Report

I have implemented this by using this below line.

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

Thanks Martin , for your support.

Was this reply helpful? Yes No