Splinting one record into many records!

[rbross]

Hello,

I have a large text field called Notices that I need to try to split up into individual records.
The records look something like this:

record1
**2% AND 5% UNTIL 7/5/19* **PASSPORT REQUIRED** *CIRCLE TOUR** LETTER OUT WEEK OF 9/23

record2
2% AND 5% UNTIL 7/11/19**CANCELLATION FEES BEGIN 30 DAYS PRIOR**BURLINGTON P/U LOCATION NEEDS TO BE ADVISED BY CUSTOMER AS WE HAVE NO P/U LOCATION (GET OK'D BY BETTY OR JANE)**LETTER OUT WEEK OF 9/30

Users do their best to separate Notices by ** at the end of each notice.

Not sure if INDEX_OF and LAST_INDEX_OF and SUBSTRING would work for this? Maybe something like this if Functions can be combined in one rule:

2% AND 5% UNTIL 7/11/19**CANCELLATION FEES BEGIN 30 DAYS PRIOR**

INDEX_OF('**',BOName.Attribute) should return 25
SUBSTRING(BOName.Attribute,0,INDEX_OF('**',BOName.Attribute))
Translated this rule might look something like this:
SUBSTRING(BOName.Attribute,0,25)

Then if there was a way to retain the last returned INDEX_OF that could be used as the starting position for the next SUBSTRING Rule.

There should not be more than 4 or 5 notices contained within each record.

May the While Loop Rule could work for this or maybe it has to all be done in a process???

Confused?

[BobK]

Roger,

I have been thinking of your requirements and I believe I have a solution for you.

I just want to verify that I fully understand what you want.

You have a BO with a large text field called “Notices” that contains many “Notice” each separated by 2 or more asterisks (*).
For Each “Notice” you want to create a new record of a different BO that has a text field with just 1 “Notice” taken from the first BO “Notices”

Correct?

[Jaymer]

... and is this a one time process for conversion or they will continue to enter data this way?

[BobK]

Users do their best to separate Notices by ** at the end of each notice.

@Jaymer
From the above, I assume this is an ongoing way of date entry.


Here is my solution and I would like to hear what others think of it.


BO1.MultiNotices contain the data like from the OPs post: **2% AND 5% UNTIL 7/5/19* **PASSPORT REQUIRED** *CIRCLE TOUR** LETTER OUT WEEK OF 9/23
BO1.TempNotices is a temporary field used in processing.

BO2.Notice will end up with the individual notices that are between the double asterisks.


The following rules are BO rules on BO1.


First I move the MultiNotices to the TempNotices after TRIMMING it, removing any leading or trailing double (or more) asterisks, TRIMMING again and adding ' **' to the end

If BO1.MultiNotices WAS CHANGED Then
BO1.TempNotices=TRIM(REPLACE_PATTERN(REPLACE_PATTERN(TRIM(BO1.MultiNotices), '^\*{2,}', ''), '\*{2,}$', '')) + ' **'


Then I create a new BO2.Notice with the data from the TempNotice upto the first set of double (or more) asterisks, after which I remove everything up to and including the first set of double (or more) asterisks. This rule has the 'While' loop selected so it will continue executing until the TempNotices is empty

If BO1.TempNotices CONTAINS '**' Then
CREATE BO2 WITH BO2.Notice=TRIM(REPLACE_PATTERN(BO1.TempNotices, '\*{2,}.*$', ''))
BO1.TempNotices=TRIM(REPLACE_PATTERN(BO2.TempNotices, '^.*?\*{2,}', ''))


If BO1.MultiNotices contains:
**2% AND 5% UNTIL 7/5/19* **PASSPORT REQUIRED** *CIRCLE TOUR** LETTER OUT WEEK OF 9/23

after executing the above rules, there would be 4 BO2 records with

2% AND 5% UNTIL 7/5/19*
PASSPORT REQUIRED
*CIRCLE TOUR
LETTER OUT WEEK OF 9/23