web
You’re offline. This is a read only version of the page.
close
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

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

I have the same question (0)
  • Suggested answer
    Community Member Profile Picture
    Community Member on at

    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;

  • Community Member Profile Picture
    Community Member on at

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

  • Community Member Profile Picture
    Community Member on at

    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 on at

    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 on at

    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 on at

    Erick, sorry :-)

  • Community Member Profile Picture
    Community Member on at

    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 on at

    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.

  • Verified answer
    Almas Mahfooz Profile Picture
    Almas Mahfooz 11,009 User Group Leader on at

    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 on at

    Thanks Almas!

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

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

#1
mtabor Profile Picture

mtabor 1

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans