If "set by" is always going to be there, then it's easy:
c = table::yourfield;
p = position(c;"set by";1;1);
cut = left ( c ; p-1);
clean = trimall( cut ;1;1)
Basically you find the position of where the constant word "set by" appears in your string, and just take everything to the left of that.
There are some "word" functions that may help. I suggest you study want makes a word (what separators):
& perhaps this kb topic will help:
Extracting Title, First Name, Middle Name, Last Name from a field in FileMaker Pro
apologies, Lind. some of the message did not show up in email. Now I see your included image.
Can you post a few more examples of what you are trying to parse?
2 of 2 people found this helpful
I would do a
GetValue ( substitute(yourTable::yourDataField; [" set by "; ¶]; [" on "; ¶]; [" at "; ¶] ); xxx )
xxx = 1 will give you what,
xxx = 2 will give you who,
xxx = 3 will give you the date,
xxx = 4 will give you the time
1 of 1 people found this helpful
Nice, yeah forgot about the substitute to list method...
thanks for the suggestion, after reading through I went with Siplus... seemed to make a little more sense in my mind... plus you told me to forget about substitute
For the example I gave you, this worked perfectly, and your explanation of what to do helped as well. Thank you!
As you can see the "notes" section on the screen shot is the field I used to parse from. So, it works flawlessly when there is only 1 "set by."
As you can se sometimes there are multiple "set by" and they are listed from oldest to newest, is there a way to nest the calculation in with (GetRight?)
If you look at record 1, in the notes there are multiple "set by" data. I don't see this as happening more than 10 times, but I do know that it will happen on average 2-3 times. The program I am using lists it in order from oldest change to newest, and I would need the newest. Is there a way to nest this into something like "Get Right"?
Those links helped a little. I am VERY visual, so sometimes I get lost in the copy. I had seen a few of those pages, but will continue to dig through the forums to learn as much as possible.
Like everyone, I am working on a deadline and having to learn the program I really appreciate not only the "Type this calculation in to get what you want" - sorta like black magic.... but know that I learn best from understanding what I am doin-- and those links help tremendously!
if you have multiple events, they are separated by a pipe as I see.
So you first separate a multiple entry in single entries, by substituting the | with ¶ and becoming a EventList.
Then you go with $i from 1 to ValueCount(EventList), you grab the event with GetValue(EventList; $i) and you do what you already know with it.
1 of 1 people found this helpful
x_String = "Added set by LR on 6/17/16 at 3:59:32 PM|Sign Type Change set by LR on 6/17/16 at 4:19:58 PM|Added set by LR on 6/17/16 at 3:59:32 PM" ;
x_ModCount = PatternCount ( x_String ; "|" ) + 1 ;
x_LastModStart = Position ( x_String ; "|" ; Length ( x_String ) ; -1 ) ;
x_LastModString = Right ( x_String ; Length ( x_String ) - x_LastModStart ) ;
x_LastModList = substitute ( x_LastModString ; [" set by "; ¶]; [" on "; ¶]; [" at "; ¶] ) ;
x_Title = GetValue ( x_LastModList ; 1 ) ;
x_SetBy = GetValue ( x_LastModList ; 2 ) ;
x_Date = GetValue ( x_LastModList ; 3 ) ;
x_Time = GetValue ( x_LastModList ; 4 )
It uses siplus' substitute method, plus a let to parse out the last modification. You'd replace the literal text I have associated with x_String with YourTable::YourFieldName. If you used this formula for each of the four values, you'd only need to modify the last line (just change x_Title to x_SetBy, and so on.
That makes things a lot more clear.. I'm still having trouble making it work though.
Could you advise where I stop the calculation so it gets everything after the last | (pipe)? There will be multiple pipes.
The current field name is "status"
Added set by LR on 6/17/16 at 3:59:32 PM|Sign Type Change set by LR on 6/17/16 at 4:19:58 PM|Added set by LR on 6/17/16 at 3:59:32 PM
I would like it to return:
Added set by LR on 6/17/16 at 3:59:32 PM
Attached is my FMP file and the CVS I'm working from.
You can use the same substitute method that siplus detailed before, paired with ValueCount() to get the number of values in the new list (which equals the LAST value in the list):
Let ( [
string = "Added set by LR on 6/17/16 at 3:59:32 PM|Sign Type Change set by LR on 6/17/16 at 4:19:58 PM|Added set by LR on 6/17/16 at 3:59:32 PM";
sub = substitute ( string ; "\" ; "¶")
GetValue ( sub ; ValueCount(sub) )
= Added set by LR on 6/17/16 at 3:59:32 PM
I've added a number of fields to your file, and put the fields on the layout. I've made them all calculation fields for now, but I would most likely change them to auto-enter calc fields. The data won't change often (in each record), so an auto-enter would allow for indexing and would be fast for sorts and finds and so on.
Test.fmp12.zip 180.7 K
It works flawlessly.
I'm still not sure I understand how exactly it all works, but it gives me something to learn from!