Dynamics GP advanced lookup not sorting properly.

(0) Share

Report

Posted on by Community Member Microsoft Employee

Issue I'm running into.

If you go into the COA maintenance window, click the magnifying glass you can see they have the sort defaulted to by Property which is there fourth segment. But nothing shows up in the window.

I added the for options below the sort items that have the word "by"them. I added the new options under advanced lookups those the options that do not have the word by in front of them are listed below.

If you select property from the advanced look up option on the bottom part of the window, the window does fill sorting by property and then by DEX_ROW_ID, that seems weird, we want it to sort by Property (Segment 4) then by main account or Segment 3.

Dex Row Id makes no sense.

If I do a custom sort and enter segment 4, then segment 3 it works fine as long as your in the window but once you close the window you lose the custom sort. Is there a way to keep the custom sort option, too many mouse clicks Here is what I see in the DEXSQLLog file.

SELECT TOP 25 ACTINDX,ACTNUMBR_1,ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4,ACTALIAS,MNACSGMT,ACCTTYPE,ACTDESCR,PSTNGTYP,ACCATNUM,ACTIVE,TPCLBLNC,DECPLACS,FXDORVAR,BALFRCLC,DSPLKUPS,CNVRMTHD,HSTRCLRT,NOTEINDX,CREATDDT,MODIFDT,USERDEF1,USERDEF2,PostSlsIn,PostIvIn,PostPurchIn,PostPRIn,ADJINFL,INFLAREV,INFLAEQU,ACCTENTR,USRDEFS1,USRDEFS2,Clear_Balance,DEX_ROW_TS,DEX_ROW_ID FROM UREF.dbo.GL00100 WITH ( NOLOCK) WHERE (ACCTTYPE IN(1, 0, 0, 0)) AND ACTNUMBR_4 = '00000' ORDER BY ACTNUMBR_4 ASC ,DEX_ROW_ID ASC

Here is thre screen shot when you go into the sort window, you can see it is blank, the dex sql log attached is from this process.

Anybody else run into this? Thoughts on how or why dexterity would choose dex_row_id to sort on, and is this something that is easily changed through some sort of customization?

Thank You.

*This post is locked for comments

All responses (8)

Answers (0)

Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

Sounds good, confusing to me, let me know what you might find out if anything, I'm going to look at writing a customization to work around the issue. I just wanted to make sure it was not a setup thing or customer specific. I'm going to do more testing on my own install, plus I'm assuming the functionality is the same on GP 2015 but not sure.

Thanks
Suggested answer

soma 24,406 on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

I am also tried the same and got the same result for sorting option with including the column Active and all other sorting it is not including the DEX_ROW_ID. It's happening only for column sorting Active.

But, I couldn't understand why there are using DEX_ROW_ID in sorting process with Active column only.

If I will get any information regarding the same and let you know.

Hope this helps!!!
Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

That is the issue, bummer that we can set a default for the account look up.

I'm still struggling why Microsoft would code for the second sort option to be by DEX_ROW_ID. Unless I'm missing something that really makes no sense, an end user has no idea that there is even a dex_row_id and to do a secondary sort on that is crazy if you ask me. Seems odd and weird and how would this benefit a customer?
Suggested answer

soma 24,406 on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

Bison, you can set your custom sort lookup window as default. But, this option available only for few lookups, not accounts lookups. So, you can not do it for accounts lookup.

Please have a look on the below.

Hope this helps!!!
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

Bison, I can understand your points. But you can give a try with dexterity customizations for it.
Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

Thanks for the information, I realize that the custom sort does work, but each time you leave the window and re-enter you have to add the columns into the custom sort window, many mouse clicks each time and painful if you do this multiple times over and over again. Do you know if there is a way to save your custom sort option or modify the behavior possibly with some sort of customization.

Thanks
Suggested answer

Community Member Microsoft Employee on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

Bison, I believe Advanced Lookup can sort by one field at a time which is defined using Advanced Lookup Setup window. But you can use custom sort option to include more than one field for sorting purpose. The below is the screenshot for custom sort option window and the sql statement log.

SELECT TOP 25 ACTINDX,ACTNUMBR_1,ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4,ACTNUMBR_5,ACTALIAS,MNACSGMT,ACCTTYPE,ACTDESCR,PSTNGTYP,ACCATNUM,ACTIVE,TPCLBLNC,DECPLACS,FXDORVAR,BALFRCLC,DSPLKUPS,CNVRMTHD,HSTRCLRT,NOTEINDX,CREATDDT,MODIFDT,USERDEF1,USERDEF2,PostSlsIn,PostIvIn,PostPurchIn,PostPRIn,ADJINFL,INFLAREV,INFLAEQU,ACCTENTR,USRDEFS1,USRDEFS2,Clear_Balance,DEX_ROW_TS,DEX_ROW_ID FROM TWO.dbo.GL00100 WITH ( NOLOCK) WHERE (ACCTTYPE IN(1, 0, 0, 0)) AND (ACTIVE = 1 AND ACCATNUM = 3 AND DEX_ROW_ID > 9 OR ACTIVE = 1 AND ACCATNUM > 3 OR ACTIVE > 1) ORDER BY ACTIVE ASC ,ACCATNUM ASC ,DEX_ROW_ID ASC
soma 24,406 on at

Like (0)

Report

RE: Dynamics GP advanced lookup not sorting properly.

we are not able to see your screen shots. Please attach again with properly.