Jump to content
Salesforce and other SMB Solutions are coming soon. ×

A script to delete a digit?


schrodinger
 Share

Recommended Posts

Once a year I must manually delete the number 6 from a field from anywhere between 300-600 files. 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!

Link to comment
Share on other sites

Can you give some examples? I'm confused by your description.

 

EDIT and are the files FileMaker files, or some other sort?

Edited by doughemi
Link to comment
Share on other sites

Sure, I can make things confusing sometimes, sorry. It's an open text field and there are 15 different numbers that can be in there (usually one to six digits). 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). It's the 6 that is removed from that field (from a found set) because it's added to a newly created field for the year.

 

If it sounds crazy, that's because it is. It's not an ideal system, but it's the system I'm working with.

 

Thanks!

Link to comment
Share on other sites

Try this on a COPY of your file:

 

1. Add this Custom Function (because you have Advanced, you can add custom functions under File->Manage->Custom Functions...):

// ValueRemove ( valueList; itemValue )
// version 1.0, Daniel A. Shockley
// found in the comments of https://www.briandunning.com/filemaker-custom-functions/detail.php?fn_id=1175&comments=all#comment_top

Let( 
cleanedListPadded = Substitute( "¶" & valueList & "¶"; "¶" & itemValue & "¶"; "¶")
; Middle( cleanedListPadded; 2; Length( cleanedListPadded ) - 2 )
)

2. Add a script named RemoveValue:

Go to Record/Request/Page [First]
Loop
Set Variable [$theList; Value:Substitute(YourTable::theField; " "; "¶")]
Set Variable [$theValue; Value:6]
Set Variable [$theFixedList; Value:ValueRemove ( $theList ; $theValue )]
Set Field [YourTable::theField; Substitute ( $theFixedList ; "¶" ; " " )]
Go to Record/Request/Page [Next; Exit after last]
End Loop

3. Run the script on your found set.

 

Because this is a looping script, it may take some time to execute if you have many records in the table. (but it sure beats doing it manually!)

 

Note that the next comment at Brian Dunning's site says that if there are two 6s in a row in the field, the function will not remove one of them.

 

PS Did you really mean 600 files, or did you mean 600 records?

Edited by doughemi
Link to comment
Share on other sites

How about using a new calculated field such that Calc_Field_Without_Six =

 

Filter(Field_Name_To_Remove_Digit_Six;"012345789")

 

EDIT: After the 7th or 8th re-read of the original post, this technique won't be useful since all "6" characters are eliminated and the OP stated...

...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).
Edited by Techphan
Link to comment
Share on other sites

Thanks everyone for your help. I managed to find a simple solution with find/replace and checking off "exact word only" and replacing the 6 with nothing. It works like a charm! I'm going to have a very happy new year this year :)

Link to comment
Share on other sites

 Share



×
×
  • Create New...

Important Information

Terms of Use