Skip to main content

Notifications

Finance | Project Operations, Human Resources, ...
Answered

How to add 2 values in Query range in Query range

(0) ShareShare
ReportReport
Posted on by 355

Hi 

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

pastedimage1677657875289v1.png

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

  • Suggested answer
    D365FO Avatar Profile Picture
    D365FO Avatar 355 on at
    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 Profile Picture
    Mohit Rampal 12,547 Super User 2024 Season 1 on at
    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 Profile Picture
    Martin Dráb 230,370 Most Valuable Professional on at
    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 Profile Picture
    D365FO Avatar 355 on at
    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 Profile Picture
    Martin Dráb 230,370 Most Valuable Professional on at
    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 Profile Picture
    D365FO Avatar 355 on at
    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 Profile Picture
    Martin Dráb 230,370 Most Valuable Professional on at
    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 Profile Picture
    GirishS 27,832 Super User 2024 Season 1 on at
    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.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Announcing Category Subscriptions!

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Verified Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,359 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,370 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Product updates

Dynamics 365 release plans