Oracle reference item number won't populate on Items form

Kurt Wireman

Hello, I've tried a few methods to automatically populate the Oracle Item Reference field on our Items form. Currently, I have a custom sub written that removes hyphens and spaces from the Syteline item number and adds the letter 'P' at the beginning of it before exporting the Oracle item number to a flat file.

(Yes it's strange we have two different item numbers for the same product, but it's not an option to make do this differently. It currently does work, but the Edit box needs to be double-clicked before the Oracle number will be generated. Please see screenshot below:

But I'm looking to make this as simple for the user as possible and would like to auto generate this Oracle number when an Item number is available (at the top of the form). Because if this Oracle number is generated, clicking the 'Export to Oracle' button will not make the necessary data insertions in our new SQL table.
Also, here's my current sub for concatenating the Syteline Item number: 

Sub SterAddStrToItem()
dim strItemNoConCat as String
dim strItemWithConCat as String

If NOT ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("uf_item_xref_to_oracle") > " "
strItemNoConCat = ThisForm.PrimaryIDOCollection.GetCurrentObjectProperty("Item")
strItemWithConCat = "P" & Replace(Replace(strItemNoConCat,"-","")," ","")
ThisForm.Variables("strItemWithConCatVar").value = strItemWithConCat
ThisForm.PrimaryIDOCollection.SetCurrentObjectPropertyPlusModifyRefresh("uf_item_xref_to_oracle",strItemWithConCat)
End If

End Sub

To date, I've tried assigning this SterAddStrToItem event as a primary event on Oracle Item Reference, I've made it a 'Data Change' event to no avail. I've tried using either edit boxes, combo boxes as well. I don't know if a validator of some sort will help? Any suggestions will be helpful and thank you in advance for any help or ideas to try. I appreciate it!

Ryan Pukanski

Hi,

I have had struggles with this as well - maybe someone else can advise. However I will provide a suggestion that I have used as a workaround.

If you are premise you could try having the oracle item number be a derived field. That way CSI/SQL layer can do the work and you don't need to code on the form. You can extend the SLItems IDO and add a derived field where the expression is something like

CASE WHEN uf_item_xref_to_oracle = ""

THEN REPLACE(Item,'-','')

ELSE ''

END

Then you can bind that derived field to your component on the form.