Min/max bug in sql alternative, right?

sundfar

The "Completely shipped"-field i Sales Header does not work correctly. Our customer are running version 5.0 with SQL and I have learned that in 5.0 min/max doesn't work with SQL. "Completely shipped"-field is a flowfield with a min calcformula in it.

I checked this functionality in a 5.0 SP1 installation, and there seems to be a bug there as well.

Can anyone confirm if the min/max error is still there in both 5.0 and 5.0SP1?

Any feedback would help, Gunnar.

David_Singleton

This is not so much a bug, more that it acts differently in Native to SQL. Generally the issue will relate to having a new key added to the table that SQL now thinks is a better match than the one Native was using.

This can be a big issue in warehouse shipping. Best is to look for the code that uses the field (I think there are only a couple of places) and replace it with code where you can force the correct sorting instead of the flow field. The other option is to create a new flow field as "EXISTS" instead of min/max. This is a little bit slower but also works.

Which country version are you using?

sundfar

Thanks for the suggestions.
Would like to comment that I have tried to rewrite it to use EXIST, with only partly successful result. My filter criteria includes line.completely shipped=false, and then I reverse sign the whole thing. If there are lines in the order, it works, if there are no lines in the order, the flowfield returns TRUE, which is not acceptable.

Your suggestion to: "Best is to look for the code that uses the field (I think there are only a couple of places) and replace it with code where you can force the correct sorting instead of the flow field." sounds interesting but I don't have years of experience in NAV and don't know how go about doing this.

Regards, Gunnar

David_Singleton

Are you a partner (NSC) or a user. If a user get your partner to do this. If you are a partner, then get one of your senior developers to look at this. Is not a huge mod for someone that knows what they are doing.

stryk

The incorrect execution of FlowFields using "Min" or "Max" with SQL Server indeed has been a bug - which has been fixed looooong time ago (May 2007):
https://mbs.microsoft.com/knowledgebase/kbdisplay.aspx?wtntzsmnwukntmmylsvqusptntnsmqpytpwqppqtxvsstnxt (KB 936719, Hotfix Build 24652)
Check Waldo's BLOG for further updates: http://dynamicsuser.net/blogs/waldo/archive/2009/06/12/platform-updates-overview-3-70-b-nav2009-sp1-ctp2.aspx

sundfar

Brilliant, stryk. This solved my issues.
Thanks!

David_Singleton

Thanks Joerg,

I only ever had this issue once, and it was a long time ago, (first release of 4.00sp3) code was the only way to fix it then. Glad to know it was considered a bug and now fixed.

Gunnar, sorry for heading you in the wrong direction. :oops: