Query issue - Select max JobNum

Epicor ERP 10
1

Hello all,

I am trying to create a query that shows the completion date of the last job on a part. I used a sub-query to aggregate max JobNum per Part and went from there.

My problem lies in how Epicor is interpreting JobNum. Example: my query brings back this job num (99877) as max job num for this part:

1

When I look at Part Advisor, I can clearly see that the newest JobNum is 129852:

3
3.png702×315 11.9 KB

What is causing Epicor to see this 5 digit job number (99877) as larger than the 6 digit job number? I tried to set datatype as 6 digit int but it hasn’t helped.

4
4.png703×409 26.2 KB

For good measure, here is my query:

2

Thanks,

Alice

2

I think the problem here is that the JobNum field itself is a string.

Strings are sorted like this:
1
11
111
2
22
222

3 Likes
3

Its sorts by ALPHA, JobNum is a string. If you can ensure you always will have a number, you can cast it to an integer in a calc field and work that way.

4

Instead of using MAX(jobnum), could you instead get the job from the max CreateDate? This should save also accomplish the purpose and won’t break if someone manually types in a jobnum string in job entry.

2 Likes
5

I have to agree - this is a much better solution. Kudos.

6

Awesome. Thank you so much.