Removing "-"

[LaRetta]

I have a text field (called GROUP) which incorporates two types of data. The left side is a StaffCode, then a "-" and the right is a Program. Our old MedPac couldn't track by Program. In FM, of course we can. I have created two new text fields, Staff and Program and I need to split Group and set the two new fields.

Existing format and data variations are:

AS-T

GBG-OCP

DJ (meaning only staff listed and program would be "I" for Insurance)

There are a maximum of four letters on each side of the "-" and some entries have no "-" (only the Staff code as in DJ).

Question: Can I loop through once using a script to set the new fields, if so, what would the formula be, assuming that, if "-" doesn't exist, set Staff code to entire field AND set Program to 'I'?

[Ocean West]

first substitute a space for the "-" then use the right words or left words statment. in a loop script or a replace.

[Ocean West]

PSST... don't for get to back up...

[LaRetta]

Thank you. And, thanks for the reminder to backup. I do pretty well but, you know what would happen the one time I didn't! Right! Especially since it's me doing this thing -- I don't trust myself AT ALL ... and wisely so!

[LaRetta]

It worked!! I used Substitute( Grouped, "-" , " "), then

Set Staff - LeftWords( Grouped, 1)

Set Program - RightWords( Grouped, 1)

The only thing it did wrong ( I did wrong) is on DJ. It listed DJ in Staff AND Program. But I guess I can search for all invalid programs and change them to I with another script? It would save me some work if I could include this piece, as there are alot of Program/Payor combinations to identify. Any suggestions on correcting this in the original LeftWord/RightWord script step?

[LaRetta]

I have a text field (called GROUP) which incorporates two types of data. The left side is a StaffCode, then a space and the right is a Program.

AS T

GBG OCP

DJ (meaning only staff listed and program would be "I" for Insurance)

There are a maximum of four letters on each side of the space and some entries have no Program after the space

I have a script that:

Set Staff - LeftWords( Grouped, 1)

Set Program - RightWords( Grouped, 1)

The only thing it did wrong ( I did wrong) is on DJ. It listed DJ in Staff AND Program. Is there a way I evaluate if Right is blank, and if it is, Set Left as Staff and Set Right as 'I' and can this be done in one looping script?

[CobaltSky]

Given what has occurred so far, what I suggest you do is, perform your Replace with the formula:

Substitute(Substitute(Grouped, "DJ", "DJ-I"), "-" " ")

Then your existing looping script step will work correctly without the need for further action.

However if you want to persist with the data you're working on at present, the syntax for the second script step would be:

Set Field ["Program", "Case(WordCount(Grouped) > 1, RightWords(Grouped, 1), "I")"]

[LaRetta]

That makes perfect sense. And I have the backup db sitting right here (renamed, of course). Yes, set DJ-I FIRST, then loop through the records. One more consideration in using your formula: Staff (the left side will be different -- there are many different Staff (Left up to four letters) that also need to be converted. How would that change your formula?

Substitute(Substitute(Grouped, "DJ", "DJ-I"), "-" " ")

Would I need to add something inside the Substitute regarding LeftWord?

[LaRetta]

Sorry, what I mean is it won't always be DJ that needs to be converted. How do I generically change that?

[CobaltSky]

Try this:

Substitute(Case(PatternCount(Grouped, "-"), Grouped, Grouped & "-I"), "-", " ")

It will add a hyphen and an I to any single-part codes, then change all the hyphens to spaces in a single pass.

Then your original LeftWords and Rightwords script steps will do the rest!

[LaRetta]

That worked perfectly! Thanks Ray!