Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Using RANGE for Date f...
Microsoft Dynamics GP (Archived)

Using RANGE for Date field

(0) ShareShare
ReportReport
Posted on by Community Member Microsoft Employee

Hi,

I have a scenario to filter and show records (in scrolling window) from one table. The filter is based on three criteria

1. Customer ID

2. RMD Type ID

3. Document Date (from and to)

For criterion 1 and 2 I am using reject record and it is working fine.

However, for 3rd criteria, I tried using Range clause with following code:

{Clear any previous range for the table.}

range clear table HBM060;

{Set the start of the range.}

'Document Date' of table HBM060 = '(L) DateFrom' of window Window1 {setdate( '(L) DateFrom' of window Window1, 0)};

range start table HBM060 by number 1;

{Set the end of the range.}

'Document Date' of table HBM060 = '(L) DateTo' of window Window1 {setdate('Document Date' of table HBM060, '(L) DateTo' of window Window1)};

range end table HBM060 by number 1;

But it is having following issues:

- Sometimes showing incorrect results (not within the date range)

- Document Date is a column of scrolling window, so one the above script runs it changes the document date to "DateFrom" field's selected date for all of the displayed records.

I want to know if I am going in a right direction OR is there any better solution to this requirement. Please let me know if the above code requires any correction.

Regards,

Waliullah

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using RANGE for Date field

    Hi Naga,

    I tried your solution and it works, thanks.

    However, there is another fitler criteria that requires wild card search (using % sign). So, I use below code,

    range table HBM060 where physicalname('DocType' of table HBM060) + " = " + SQL_FormatStrings(sRMDType) + " and " + physicalname('Customer Number' of table HBM060) +" = " + SQL_FormatStrings('Customer Number' of window Window1)  + " and " + physicalname('ProgramName' of table HBM060) +" like %" + SQL_FormatStrings('(L) Program' of window Window1)  + "%";

    but it didn't work and when I check sql profiler it produces %" + SQL_FormatStrings('(L) Program' of window Window1)  + "%" as %'program name'%. How do I correct that.

    Regards,

    Waliullah

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using RANGE for Date field

    Thanks Naga, I will now try and will let you know.

    Regards,

    Waliullah

  • Verified answer
    Naga Kiran Profile Picture
    Naga Kiran on at
    RE: Using RANGE for Date field

    Hi Waliullah,

    Yes my suggestion is based on the approach you are following and I agree that its not best for performance if you have more records in you table.

    Before the fill window statement set proper range to the fields you mentioned in your query which will avoid the usage of reject record. The code should like below.

    Method 1: (if 3 fields in your query form a Key in your table)

    set Customer ID table HBM060 to Customer ID of window Window1 ;

    set  RMD Type ID table HBM060 to  RMD Type ID of window Window1 ;

    set  'Document Date' of table HBM060 to '(L) DateFrom' of window Window1;

    range start table HBM060 by number <your key number>;

    set  'Document Date' of table HBM060 to '(L) DateTo' of window Window1;

    range end table HBM060 by number <your table key number>;

    fill window <your scroll window> by number <your table key number>;

    Method 2: (if you don't have a Key with the 3 fields in your query)

    range clear table HBM060 ;

    range table HBM060 where physicalname(Customer ID table HBM060) +" = " + SQL_FormatStrings(Customer ID of window Window1) + " and " + physicalname(RMD Type ID table HBM060) + " = " + SQL_FormatStrings(RMD Type ID of window Window1) + " and " + physicalname('Document Date' of table HBM060) + " < " + sqlDate('(L) DateFrom' of window Window1) + " and "+ physicalname('Document Date' of table HBM060) + " > " + sqlDate('(L) DateTo' of window Window1);

    fill window <your scroll window>;

    Hope this helps.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Using RANGE for Date field

    Hi Naga,

    Thanks for your help, it works successfully.

    Only problem is it reduces the performance very much and for a single record search I need to wait for atleast 8-9 seconds.

    Regards,

    Waliullah

  • Suggested answer
    Naga Kiran Profile Picture
    Naga Kiran on at
    RE: Using RANGE for Date field

    Hi Waliullah,

    if you want to reject the record based on document date that is coming from scroll table HBM060 (which I assume is your scroll table) why don't you write an IF condition instead of setting range. You should not disturb the range of the scroll table as you are doing here.

    In Scrolling window's Line Fill event add the IF condition below to reject records that don't fall in the selected date range:

    if ('Document Date' of table HBM060 < '(L) DateFrom' of window Window1 or 'Document Date' of table HBM060 > '(L) DateTo' of window Window1) then

         reject record;

    end if;

    Hope this helps.

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

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 Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,214 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans