Getting MAX value from column in IDO via Form Script

Before going live on CSI, we had an Infor consultant write a script for us to calculate the UPC code for new finished goods.

This should essentially do a look up in the SLItems IDO for the largest value in the itemUf_jcm_UPCkey column and add 1 to it to calculate the next UPC Key -- which is then combined with our unique UPC Prefix to generate the new UPC code.

The problem is that the script is not getting the maximum value from the itemUf_jcm_UPCkey field. Instead, it appears they are just sorting the field and getting the first column value from descending order. Below is the script - can anyone offer advice on changing this to get the largest value?

Sub QueryItem()
'ICS001 - Provided code for this Sub 05.31.2017.
'
Dim vNextUpc As Integer
Dim oUpdReq As New Mongoose.IDO.Protocol.UpdateCollectionRequestData("SLItems")
Dim oUpdRes As New Mongoose.IDO.Protocol.UpdateCollectionResponseData
Dim oResp As LoadCollectionResponseData

Dim sProps As String = "itmUf_jcm_UPCKey"
Dim sFilter As String = "1=1" 'DH "itmUf_jcm_UPCKey is not null"

Try
oResp = IDOClient.LoadCollection("SLItems", sProps, sFilter, "itmUf_jcm_UPCKey desc", 1)

If oResp.Items.Count > 0 Then 'if item record exists
'Application.ShowMessage(oResp.Item(0, "itmUf_jcm_UPCKey").ToString)
If oResp.Item(0, "itmUf_jcm_UPCKey").ToString = "" Then
vNextUpc = 26000
ThisForm.Components("UPCKeyEdit").Value = vNextUpc.ToString
'Application.ShowMessage(ThisForm.Components("UPCKeyEdit").Value)
Else
Dim vUPC As String
Dim vUPC_Num As Integer
vUPC = oResp.Item(0, "itmUf_jcm_UPCKey").ToString
vUPC_Num = CInt(vUPC) + 1

If vUPC_Num < 10 Then
ThisForm.Components("UPCKeyEdit").Value = "0000" & vUPC_Num.ToString 'oResp.Item(0,"itmUf_jcm_UPCKey").ToString
Else
If vUPC_Num > 9 And vUPC_Num < 100 Then
ThisForm.Components("UPCKeyEdit").Value = "000" & vUPC_Num.ToString
Else
If vUPC_Num > 99 And vUPC_Num < 1000 Then
ThisForm.Components("UPCKeyEdit").Value = "00" & vUPC_Num.ToString
Else
If vUPC_Num > 999 And vUPC_Num < 10000 Then
ThisForm.Components("UPCKeyEdit").Value = "0" & vUPC_Num.ToString
Else
If vUPC_Num > 9999 And vUPC_Num < 99999 Then
ThisForm.Components("UPCKeyEdit").Value = vUPC_Num.ToString
Else
Application.ShowMessage("Next Up number has exceeded 99999. Time to replace the upc prefix. Update Variable JCM_UPCPrefix with new Prefix")
ThisForm.Components("UPCKeyEdit").Value = ""
End If
End If
End If
End If
End If
'application.ShowMessage(ThisForm.Components("UPCKeyEdit").Value)
End If

ThisForm.Components("UPCEdit").Value = ThisForm.Variables("JCM_UPCPrefix").Value & ThisForm.Components("UPCKeyEdit").Value
UpdateUPCCode()
Else

'default value for the nextupckey = 1
Dim vUPC As String
Dim vUPC_Num As Integer

vUPC = oResp.Item(0, "itmUf_jcm_UPCKey").ToString
vUPC_Num = CInt(vUPC) + 1

ThisForm.Components("UPCEdit").Value = ThisForm.Variables("JCM_UPCPrefix").Value & ThisForm.Components("UPCKeyEdit").Value
UpdateUPCCode()
End If
Catch ex As Exception
Application.ShowMessage("exception : " + ex.Message)
End Try

End Sub

Find more posts tagged with

Syteline Development

Accepted answers

All comments

Legacy Contributor

The best implementation would be to extend and replace SLItems. Then add a derived property with a value of MAX(itmUf_jcm_UPCkey) + 1. Which would always be the next value to use for the next UPC code.

That is some of the ugliest code I have ever seen. Replacing all that, you could simply have another derived property that has a value of:

'UPCPrefix' + RIGHT('00000' + CAST((MAX(itmUf_jcm_UPCkey) + 1) AS VARCHAR(5)),5)

You would have to double-check that, but I believe that is the correct syntax without testing it.

But that would give you a property that always has the next value to use for the UPC code. You could use reporting or an AES event to email someone when the value reaches a certain threshold where you need to order the new prefix. Then just update the derived property for the new prefix to take effect. You could also setup a prefix table that you can just add to with an effective date, and pull the latest prefix in based on the effective date.

BradleyBrown

One potential issue with this method is a race condition that could occur between multiple processes using the same data, if that is possible in this scenario. If user A is looking at the same data as user B they both will have the same value in this derived prop. If the data isn't refreshed exactly when the new UPC code is created then it could be possible for both users to create the same new UPC code. Perhaps a bit far fetched, but it could happen.

One other possibility could be to use the next keys system to manage the UPC codes. A bit more setup but still cleaner than that code sample.

Legacy Contributor

No more likely than two users hitting Next Keys at the same and potentially grabbing the same value. That is what table constraints are for, so you can't insert the same value twice. Using try/catch blocks and catching that condition would allow you to retry the transaction and get the next value, if you are really anal about it. Storing next keys in a derived property is used all over CSI. Accounting Period Control Numbers is one example.

Legacy Contributor

With the current code, I already have issues with duplicates being created. My plan is to set the UPC column to require a unique value to prevent this from happening.

Legacy Contributor

So I am attempting to do as TimBoyden suggested and use a Derived Property to get the max value. If I run this code in SQL, I get exactly what i am looking for:

(Select RIGHT(max(itm.Uf_jcm_UPC),5) as maxvalue From item_mst itm Where itm.UF_jcm_UPC <> 'N/A')

If I then try to use this to get my derived property, I get error: Invalid column name 'itmUf_jcm_UPCkey'. This error only shows on the form when the derived property is added. Also, I am not using itmUf_jcm_UPCkey in my select statement, so I don't know where this is even coming from.

(Select RIGHT(MAX(itm.Uf_jcm_UPC),5) From item itm where itm.Uf_jcm_UPC <> 'N/A')

Anyone have some thoughts on what I can try before I just quit my job and try to make it as a chain saw artist?

Legacy Contributor

What did you name the derived property? Did you run the Unload Farm IDO Metadata form after making the change? Check User Preferences - Unload IDO Metadata with Form setting is checked. Try a few Ctrl+U's. That is the troubleshooting steps I would typically check.

If you named the derived property itmUf_jcm_UPCkey, that could be conflicting with how Mongoose detects UET properties.

Could also be when you extended and replaced SLItems, it tried to create an IDO Property for the UET field. If so, try deleting that IDO Property because you don't need it.

Copyright © 2025 Infor. All rights reserved.