Removing last 6 characters from a field

[Cabinetman]

Blank once again.....

Field 1: imports a URL but with 6 extra #'s

i.e. /images/1234AL.jpg475311

Field 2: calculation to remove them - I'm blank!

Thanks

[comment]

See this recent thread:

http://www.fmforums.com/forum/showpost.php?post/255047/

[Cabinetman]

Thanks...... right in front of me!

If you don't mind one more. Date problem... I did search but so many unrelated posts.

2007-01-30 to 3-30-2007 ?? I've done it before but once again blank.... I need to come back to this later !!

If not thanks for the help !!

Edited June 16, 2007 by Guest

[comment]

The question is not clear. Is the input "2007-01-30" a date or is it a text string? The same question applies to the result. Keep in mind that Filemaker can DISPLAY a date in a variety of formats, while it's STORING only the date's serial number.

[Genx]

Given that you're presenting the date string hyphenated rather than slash separated, I'm going to assume this content isn't in a date field in fm.

See if this recent thread helps:

http://fmforums.com/forum/showtopic.php?tid/187717/post/255322/#255322

(you do realize though that 2007-01-30 <> 3-30-2007 no matter which way you convert it )

[Cabinetman]

Specifically imported data looks like:

2007-01-21 16:17:23 PST

I have to remove the last and convert the date into something I can sort and use calc.'s

I did read the other post...........

[comment]

Given that you're presenting the date string hyphenated rather than slash separated, I'm going to assume this content isn't in a date field in fm.

That's some assumption - my own dates are hyphenated in the OS setting and consequently in Filemaker date fields as well.

[comment]

Well, if you're willing to bet on an undocumented feature, you could use:

Substitute ( LeftWords ( Imported ; 1 ) ; "-" ; "+" )

Otherwise, you could go with the more conservative:

Let ( [

y = Left ( Imported ; 4 ) ;

m = Middle ( Imported ; 6 ; 2 ) ;

d = Middle ( Imported ; 9 ; 2 )

] ;

Date ( m ; d ; y )

)

Make the result type a Date in both cases. As I said before, this will be displayed in your default date format, or you can custom-format the field in Layout mode.

[Cabinetman]

Text string as illustrated:

2007-01-21 16:17:23 PST

is what's imported.........

[Genx]

That's some assumption - my own dates are hyphenated in the OS setting and consequently in Filemaker date fields as well.

Fair call, but the YYYY-MM-DD format also suggests its not FM unless it's actually been formatted that way. Anyway that's why I said I would make the assumption not the presumption. See a presumption is an assumption that is presumed to be true until facts to disprove the assumption are presented. An assumption however just presents a possible scenario in order to generate a solution. Given the lack of information (and that the post can correct you by supplying more information) I didn't think it was necessary to present a solution for both possible cases.

In any case,

You can use: Left( string ; 4) to extract your year. Middle( string ; 6 ; 2) to extract your month. Middle( string ; 9 ; 2 ) to extract your day.

I.e: Let( string = "2007-01-21 16:17:23 PST";

Date( Middle( string ; 6 ; 2) ; Middle( string ; 9 ; 2 ) ; Left( string ; 4) )

)

(note that the Let statement is not necessary and is just for the sale of clarity. You can just use: Date( Middle( yourField ; 6 ; 2) ; Middle( yourField ; 9 ; 2 ) ; Left( yourField ; 4) )

HTH

[Cabinetman]

I guess we were posting at the same time...Anyway works great. THANKS!

Now I'm only down to downloading a jpeg for each record from a url and importing it into the cont. field for visual reference....

Think I'll sleep on that one...

Thanks to all! ! ! !

[comment]

I should add that the feature is not as undocumented as I thought - but who would have thought of looking at:

Help > Adding and viewing data > Entering data in records > Entering Japanese Emperor Year dates

So as long as your input has a 4-digit year, you should be safe.

[Genx]

You really are quick at utilizing new knowledge comment: Substitute ( LeftWords ( Imported ; 1 ) ; "-" ; "+" ).

Oh well, I guess I'll be like you some day (maybe when your 90 and going senile and i'm only 80... though i don't know how old you are, 28 sounds like a sweet spot) lol!!

Edited June 16, 2007 by Guest

[comment]

LOL, I wish. Let me put it this way: Sgt. Pepper was a great record.

[Genx]

Well you're always so energetic, enthusiastic and quick witted that I took you for someone younger (most times old people aren't fun lol -- probably shouldn't say that on a forum where basically everyone is older than me - don't worry I don't consider any of you guys as "old" )

On a side note, Sgt. Pepper is still great - though i must admit i've never heard it on a record.

[comment]

Hm, energetic and enthusiastic ... (yawn) ... I don't know. But enough about me.

Of course Sgt. Pepper is still great. But the impact when it came out - it would take an alien landing to get anything like that today.

[Genx]

Lol, aight. Anyway, I have to get back to exams, so if anyone see's me on these forums I expect you to tell me to go away!!