Search on Different Font/Format of Text within UDF

SOLVED

I am attempting to search for certain phrases and maybe date formats within an UDF on the SO Header in order to populate separate UDF's.  I've had trouble before with the scripts not picking up the phrase if it is all capitalized and vise versa.  Is there a way to account for this or do you have to script for each possible variation. 

Example: I want to search for "New Hire" in the UDF_SHIPPINGMESSAGE and if found check off my UDF_NEWHIRE check box.  If a user types "NEW HIRE" then the script doesn't recognize the phrase.

Part two of the question: Date formats, is there a way to get the date from the UDF_SHIPPINGMESSAGE to populate a UDF Date field and account for all the different ways a user can enter a date format?

The data populating the UDF"s I am searching in is coming from an eCommerce free text field, that is why I can't standardize it.

  • 0

    Hi  

    For the UDF_ShippingMessage new hire part you should be able to use the UCase() function in your script.  Retrieve the value and then convert to uppercase, then you know you can check for "NEW  HIRE" regardless of how it was entered.  With the date portion it might be more difficult and you're likely going to have to search for multiple things, and I don't think VB has many mask search functions but I may be wrong.

    Do you have any control of the eCommerce site?  If you do, perhaps you could at least have some sort of help on the screen that indicates the format you want the date typed in?  If you don't have any control of the site, then you'll likely have to parse the string for separators such as "/" "-" and/or look for key words like "Jan", "Feb".  The other complication is if you have interntional folks typing in this date as well, because us Canadians usually use DD/MM/YY vs MM/DD/YY. 

    E

  • 0 in reply to jepritch

    For the UCase() function, would this be how you use in a script? I assume not because in my test it didnt work, the udf contained "NEW HIRE" but failed to check off the check box.

    rVal = 0
    sNewHire = ""
    searchString = "New Hire"
    
    rVal = oBusObj.GetValue("UDF_SHIPPINGMESSAGE$", sNewHire)
    
    If InStr(sNewHire, UCase(searchString)) then	
    	rVal = oHeaderObj.SetValue("UDF_NEWHIRE$", "Y")
    
    End If

  • 0 in reply to jland47
    SUGGESTED

    You'll want to use UCase() on both the string being searched and the string you are searching for.  Any time you are doing a comparison like this where you want it to be case insensitive then you should force BOTH, not just one or the other, to either lower case or upper case.

    For the dates, that will be tricky, as Elliot said, you will have to come up with code to attempt to parse and search for dates in all of the know variations that you anticipate will occur if you can't standardize it.  As Elliot said, if you can add a note requesting the user enter dates in a specific format, then that would help.

  • 0 in reply to David Speck

    So adding the UCase() here should do the trick?  Haven't had an order come through yet to test.

    rVal = 0
    sNewHire = ""
    searchString = "New Hire"
    
    rVal = oBusObj.GetValue("UDF_SHIPPINGMESSAGE$", UCase(sNewHire))
    
    If InStr(sNewHire, UCase(searchString)) then	
    	rVal = oHeaderObj.SetValue("UDF_NEWHIRE$", "Y")
    
    End If

  • 0 in reply to jland47

    No, you need to use it on both arguments used in the InStr function or on a separate line, assign the uppercase value to the variable.

    So either use,

    If InStr(UCase(sNewHire), UCase(searchString)) then

    Or after the GetValue, use,

    sNewHire = UCase(sNewHire)

  • 0 in reply to David Speck

    Puzzled why it didn't work, data in the UDF came over as - NEW HIRE.  But my UDF_NEWHIRE did not get checked off.

    rVal = 0
    sNewHire = ""
    searchString = "NEW HIRE"
    
    rVal = oBusObj.GetValue("UDF_SHIPPINGMESSAGE$", sNewHire)
    
    sNewHire = UCase(sNewHire)
    
    If InStr(sNewHire, searchString) then	
    	rVal = oHeaderObj.SetValue("UDF_NEWHIRE$", "Y")
    
    End If

  • 0 in reply to jland47
    SUGGESTED

    I would use the following.

    rVal = 0
    sNewHire = ""
    searchString = "NEW HIRE"
    
    rVal = oBusObj.GetValue("UDF_SHIPPINGMESSAGE$", sNewHire)
    
    If InStr(UCase(sNewHire), UCase(searchString)) > 0 then	
    	rVal = oHeaderObj.SetValue("UDF_NEWHIRE$", "Y")
    End If

    And double check your spelling of the UDF names.

  • 0 in reply to David Speck

    Still didn't get a correct output, this is the UDF field I am trying to check off, and used script exactly as David posted;

    Here is the raw data in JSON format

    "UDF_CoreOrderNumber": "8089972",
            "UDF_ExtraOrderInformation": "",
            "UDF_ShippingMessage": "NEW HIRE",
            "UDF_EXTCUSTPO": "",
            "PointsAmount": "0.00",
            "PoAmount": "259.35",
  • 0 in reply to jland47
    SUGGESTED

     what event do you have this attached too?  Are both the UDF_NewHire and UDF_ShippingMessage in the same table?  

    I just noticed you are using oBusObj.GetValue() to retrieve the value of UDF_ShippingMessage, but using oHeaderObj.SetValue() to update the UDF_NewHire.   This would indicate to me that this is attached to a SO Lines event (Pre-Write?) or something?  And the header has the UDF_NewHire.  Is that correct?

    E

  • 0 in reply to jepritch

    I have it set on the Sales Order Detail with a table-prewrite.  I think I see now, I was using other scripts I have as a guide but those scripts are looking on the SO details,  both of these fields are in the header.  Let me fix script/event trigger and see if that works