Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / How can I sort records...
Microsoft Dynamics GP (Archived)

How can I sort records in a table that were filled using a Range Where statement

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

Is there a way to sort the data in a range when the range where statement is used?

I am using a Range Where statement on a table that works well, returning the records I want, however I'd like to sort the range by a specific field that currently is not an indexed field.

I have set up a virtual key, but there is no option for using the virtual keoy on the range, that I can see. I have seen some reference to being able to combine an index range and a Range Where statement together which I think would work, but I have seen no example code and am not sure how to set this up to work.

*This post is locked for comments

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Thanks Almas!

  • Verified answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,006 User Group Leader on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Just apply the range in your table as you wish to by using where clause.

    and then on fill window command use your virtual key.

    range clear table GL_Account_MSTR;
if 'Segment Number'=1 then
	range table GL_Account_MSTR where physicalname('Account Number':Account_Segment_Pool1  of table GL_Account_MSTR)+" between '"+'Segment ID'+"' and '"+'(L) Segment_ID_To'+"'";
elseif 'Segment Number'=2 then
	range table GL_Account_MSTR where physicalname('Account Number':Account_Segment_Pool2  of table GL_Account_MSTR)+" between '"+'Segment ID'+"' and '"+'(L) Segment_ID_To'+"'";
elseif 'Segment Number'=3 then
	range table GL_Account_MSTR where physicalname('Account Number':Account_Segment_Pool3  of table GL_Account_MSTR)+" between '"+'Segment ID'+"' and '"+'(L) Segment_ID_To'+"'";
elseif 'Segment Number'=4 then
	range table GL_Account_MSTR where physicalname('Account Number':Account_Segment_Pool4  of table GL_Account_MSTR)+" between '"+'Segment ID'+"' and '"+'(L) Segment_ID_To'+"'";
elseif 'Segment Number'=5 then
	range table GL_Account_MSTR where physicalname('Account Number':Account_Segment_Pool5  of table GL_Account_MSTR)+" between '"+'Segment ID'+"' and '"+'(L) Segment_ID_To'+"'";	
end if;


fill window TempScroll by number virtual_key1;

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Thanks Jeff,

    The table is a custom table and a normal range start and end would work, but what I am trying to do is populate a combo box items with an ordered set of data from this custom table. So the range where clause was selecting the records that were to be included in this combo box, but I don't really have a value to bracket a start and end range.

    Maybe pass thru sql would work, or creating a view/temp table that has the records in sorted order already would be the way to accomplish what I need.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Erick,

    If you're working with a table for which the structure cannot be changed (i.e. a GP table) you might want to consider creating a temp table with the same structure and add an index on the field you want to sort by. Then use the range copy statement "range copy table Source_Table to table Source_Table_TEMP". You can then navigate your temp table by the desired index and process the records in order you need. If you want to update Source_Table simply copy the Source_Table_TEMP buffer to Source_Table and change/save Source_Table with whatever changes you need.

    Jeff

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Erick, sorry :-)

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Doesn't work for me. I get a get/change error and it is complaining about something around the order statement.

    Here is my code segment.

    wherestmt = physicalname(RMCCS_DisableDate of table RMCCS_RouteLookup) + "  =  '1/1/1900' " + " order by 2";

    range table RMCCS_RouteLookup where wherewherestmt;

    I did a warning to display the where statement and it showed:

    RMCCS_DisableDate = '1/1/1900' order by 2.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Mike, my apologies. I thought I tested this successfully several years ago, but evidently memory fails. I just tested and received a 45 error, so it looks like there's no joy for you.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Thanks Jeff. I thought I tried that, but got an error. I assumed it was because I added the order by, but it could have been I was missing a space .  I'll try it again and see what happens.

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    ... here is some actual code from one of our apps:

    local text tWhereClause;

    range clear table RM_Customer_MSTR;

    set tWhereClause to physicalname('Customer Class' of table RM_Customer_MSTR) + " = 'SERVICE'" + "order by 2";

    range table RM_Customer_MSTR where tWhereClause;

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: How can I sort records in a table that were filled using a Range Where statement

    Hi Erick,

    In your tWhereClause statement I believe you can append the order by clause. For example, to have a range on the customer master table ordered by customer name:

    local text tWhereClause;
    set tWhereClause to "'Customer Number' = 'AARONFIT0001' order by 2";
    range table RM_Customer_MSTR where tWhereClause;

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

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Vahid Ghafarpour – Community Spotlight

We are excited to recognize Vahid Ghafarpour as our February 2025 Community…

Tip: Become a User Group leader!

Join the ranks of valued community UG leaders

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 292,516 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,321 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans