Once a year I must manually delete the number 6 from a field from anywhere between 300-600 records. I do this because I was informed that there is no way to automate the task since we also have other data in there, some of the data will contain a 6 (i.e. 16). The 6 can be by itself (no spaces), at the beginning of the string, the middle or the end. There are always spaces between the different numbers.

I usually do this task on New Year's day and I would LOVE to spend my time enjoying the first day of the year in a different way, but I have not been able to come up with a solution - after trawling google and many forums, I'm thinking wasting my time and should just suck it up this weekend, but before I give up, I wanted to check if anyone has any ideas.

TIA!

Edited December 30, 201510 yr by schrodinger

39 minutes ago, schrodinger said:

The 6 can be by itself (no spaces), at the beginning of the string, the middle or the end. There are always spaces between the different numbers.

I am not sure I understand your description. If you mean the 6 you want to delete is always a word (where words are separated by spaces), then I believe LaRetta has you covered.

 

Anyway, I wanted to concentrate on another part:

42 minutes ago, schrodinger said:

Once a year I must manually delete the number 6 from a field from anywhere between 300-600 records.

There is something fundamentally wrong with your setup if you need to do this - whether manually or automated.

I deleted my suggestion because if the field only contained a single 6 or contained a 6 on second line of a multiline, it would not work.

13 minutes ago, LaRetta said:

I deleted my suggestion because if the field only contained a single 6 or contained a 6 on second line of a multiline, it would not work.

I believe it would work in the former case, and I am not convinced you need to be concerned with the latter.

 

Edited December 30, 201510 yr by comment

Oh, right!  I entered it in a rush.  I was unsure also if the numbers were in a sentence, ending with a period.  And what about other non-space word break symbols.  I balked when I considered carriage returns as well.  It would sure make sense to break that '6' out of the string since it indicates it has different meaning.

You could not be more right @comment!! The system is fundamentally flawed. But it's the DB that has not been built well for over 20 years, we have been assured that we will move to a new system over the last 3 years or more, I expect another couple of years, or more. But it is what it is....for this year....

This field is full of "disposition codes" for the year, the 6 code indicates it's still open, it gets moved to the newly created box for the year, but is among a number of other disposition codes. While I would love to change this system, I don't think it's in my hands...yet.

@LaRetta I would like to look at your solution with a fresh brain tomorrow.

I really appreciate your time looking at my bizarre question/situation.

6 minutes ago, LaRetta said:

Oh, right!  I entered it in a rush.  I was unsure also if the numbers were in a sentence, ending with a period.  And what about other non-space word break symbols.  I balked when I considered carriage returns as well.  It would sure make sense to break that '6' out of the string since it indicates it has different meaning.

 

28 minutes ago, comment said:

I believe it would work in the former case, and I am not convinced you need to be concerned with the latter.

 

Yes, no line breaks in this field. It's usually different "codes" - e.g. 10 7 16 6 - no periods, but often the 6 can be anywhere in there. Hope that makes some kind of sense

2 minutes ago, schrodinger said:

is among a number of other disposition codes.

That is the key issue WRT to automating this task. We need to understand exactly how the field is structured, including all possible variations.

1 minute ago, comment said:

That is the key issue WRT to automating this task. We need to understand exactly how the field is structured, including all possible variations.

Exactly! Ok, it's an open text field and there are 15 numbers that can be in there (usually 1-5). The numbers are entered manually, so the 6 can be anywhere in the field (e.g. "1  6  10" or "6 7 2" or "12 10 7 6" etc).

If it seems like the whole concept was created by an insane person, you've nailed it. 

I hope this answers your questions, if I've missed the mark, I'm sorry, please let me know and thank you for your patience.

Well, as I said earlier, if the values are separated by spaces, and you want remove a value consisting of a single digit 6, then LaRetta's solution should work for you. IIRC, it went like this:

Let ( 
pad = " " & YourField & " "
;
Trim ( Substitute ( pad ; " 6 " ; " " ) )
)

You can test this using "6 66 666 6 666 66 6" as the input. Note, however, that it will fail with "10 11 6 6 12 13": only one of the consecutive values will be removed.

Wonderful! I think you might have it. I will try this out tomorrow with a fresh mind.

Thank you!

You can also export the fields to a csv file, open it in word pad and do a find/replace taking care of clicking on "Match Whole Word Only".

Find 6

Replace with (leave empty)

This will remove all 6's but will leave alone numbers like 16 and 66.

 

I tried with the following:

1 12 6 2

1 2 6 6 16 9

1 15 29 66 12 6

And after doing as above got the following result

1 12  2
1 2   16 9
1 15 29 66 12 

Then you import the records again.

It is fully manual, but since it is only once a year it should be OK.

Best of luck

Carlos 

 

4 hours ago, carlosnorvik said:

You can also export the fields to a csv file, open it in word pad and do a find/replace

Filemaker has Find/Replace, too - and it can be scripted.

Hi Comment

I had not noticed FM also has the "Match whole words only" that will also easily do it.

I didn't notice the "match whole words only" option either @carlosnorvik 

Thank you all for your suggestions!! I have figured it out and tested. I believe that my New Years will be very happy indeed now! Thank you! Thank you! Thank you!