Strip special characters from a field for reporting

Hello,

We have been exploring enforcing entry rules on the subscribernumber field to ensure data accuracy with phone numbers. A specific need has come up to extract phone numbers of a certain type into a separate emergency alert system. At this time, we are generating an excel file, stripping the data in excel, and then manually sending it to the new system until an interface can be explored. Trouble lies with the external system's parameters for accepting the numbers - numerical only. We do not want our subscribernumber field in GHR limited to numerical only for multiple reasons. Is there is a way to extract only the numerical data in the subscribernumber field when reporting while allowing ()- to be used for entry rules and display purposes?

Thanks,

Kara

Find more posts tagged with

Accepted answers

andy

Hi Kara, It looks like you may just need to create a relation from Employee to EmployeeContact and use that relation is the stripped phone number derived field on the Employee. The example below assumes you have the ONEIDAPhoneNumberDescription field on the EmployeeContact though. ContactMethod < 3 will include only Landline (1) and Mobile numbers (2). Contact Methods 3,4 and 5 are Page, Fax, and TTYTDD.

//Employee
RavePhoneRel
    one-to-many relation to EmployeeContact
    Field Mapping uses ByContactMethod
        related.HROrganization  = HROrganization
        related.Employee = Employee
    Instance Selection
        where (related.ContactMethod < 3
        and    related.ONEIDAPhoneNumberDescription.RaveAlertNumber
        and    related.Active)


ONERAVEPhoneWithNoPunctunation is a DerivedField
    type is Numeric size 10
    if (RavePhoneRel exists)
        FieldFormatting.Array40 = RavePhoneRel.ContactDetail.Telephone.SubscriberNumber
        return FieldFormatting.FormattedNumber

All comments

andy

There is an EmployeePhoneWithNoPunctuation derived field on the Employee that might meet your needs.

kmelche3

@andrew-tarver

Hi Andy, this field worked well in my report however it only pulls their work phone due to the Rel. I feel I will need to make my own derived field for this purpose and could use more direction.

We have created a specialized field we use for the emergency alerts "RaveAlertNumber" is the state name. Image below.

(our Rave system is not related to the infor Rave system for employee appreciation and is instead an emergency alert email and SMS system, fyi.)

My report has a filter to only pull ONEIDAPhoneNumberDescription.RaveAlertNumber and is pulling from field ContactDetailValues. Amy suggestions on how to create a derived field to pull just the digits instead of using ContactDetailValues?

First image is to show my Field

Second image is my poor attempt at the derived field

Thanks, Kara

cleverly

Kara,

You've told us the name of the state field that would indicate it is a Rave Alert Number, but not the name of the custom field that holds the number itself. I'm making an assumption that it might very well be "ONEIDAPhoneNumber".

If it is (or were) then I would expect this to work, as the definition for ONERAVEPhoneWithNoPunctunation (your second screenshot had ONDRAVEPhoneWithNoPunctuation, but since your text and the first screenshot began with ONE*, I assume that OND* is just a typo):

Employee is a BusinessClass
    owned by hr

    Persistent Fields

        ONERAVEPhoneWithNoPunctunation is a DerivedField
            type is Numeric size 10
            if (ONEIDAPhoneNumberDescription = ONEIDAPhoneNumberDescription.RaveAlertNumber)
                FieldFormatting.Array40 = ONEIDAPhoneNumber
                return FieldFormatting.FormattedNumber

andy

Sorry for the late reply Kara, but Michael's suggestion looks like the way to go; using the desired field instead of the Contact Detail relation.

Btw, nice code block in your comment Michael. Did you just add the "pre" tag in the HTML to get that in your comment?

cleverly

Yes, I did just drop it inside of a <pre> tag. Luckily there were no less than conditionals (which would need to be replaced with <, or ampersands with & which I imagine you know, but I'm just mentioning for future readers :-).

kmelche3

@andrew-tarver @mcleverly

Hi Andy, Good guessing but the field that holds the number itself is just SubscriberNumber and is not custom.

However when I use SubscriberNumber the field breaks. Says it needs a key field or relation.

The customization we added was to the Description states so we could make our own. and I need to use one of the homemade states to point to which SubscriberNumber I want to derive from.

I also used:

If ( if (ONEIDAPhoneNumberDescription = ONEIDAPhoneNumberDescription.RaveAlertNumber)

and it broke the field BUT

if (ONEIDAPhoneNumberDescription.RaveAlertNumber = ONEIDAPhoneNumberDescription.RaveAlertNumber)

does work.... which doesnt make sense to me because its the same.

I believe my issue is what comes after FieldFormatting.Array40 - ******* , but I could be wrong.

How can point it away from a preferred or nonpreferred relation and instead to the SubscriberNumber field Only if the Description is Rave Alert Number?

New to this and all suggestions welcome. I think ive tried 100 things. here are some tries ive made.

Result of above - no number produced. (blank)

Result of above : Pulls a stripped number, but then realized that not all rave numbers entered are checked as preferred.... only the phone user has marked as Preferred Non Work Phone (due to this relation) . It would be a struggle to require users to mark their Rave emergency alert numbers as preferred upon entry.

Result of above: blank

Result of above - strips formatting but pulls the work number and Not the rave number.

Copyright © 2025 Infor. All rights reserved.