@@JohnMarkOsborne Hi John, I would be more than happy with sharing. I tweaked it just a tad so that it works for dates in this format. (YYYY-MM-DD) I needed the data in this field immaculately austere, so this setting plus a couple validation settings help me achieve that. Let ( [ @field = Self; @format = Filter ( @field ; "1234567890" ) ]; Left ( @format; 4 ) & "-" & Middle ( @format; 5; 2 ) & "-" & Right ( @format; 2 ) )
I have watched at least 4 videos on this subject. GOD BLESS YOU as you are the only one who was able to convey how to do this in a manner that I understood and was able to replicate!
I tried to find a way to do this on my 3 phone fields... All the others are so complicated... And finally I found this tuto !!! WOW, it is so simple and so easy to understand ! Thank you !
By the way I’m getting a little more familiar with the anchor buoy method after watching your video on it like 10 times...lol I’m a little slow! Again much thanks and I appreciate your patience with my frustrations! FileMaker does kinda force you to be more of a developer than Access unless one desires to dive into vba! I’m basically comfortable with a few related tables and lots of layouts...lol
Thanks so much JMO for helping a newbie - this has been driving me crazy! Finished script looks like this & works perfectly: Let( [@Phone = Self; @Numbers = Filter ( @Phone; "1234567890")]; "(" & Left( @Numbers; 3) & ") " & Middle( @Numbers; 4; 3) & "-" & Right( @Numbers; 4) )
alexrosana branquinho No need for a calculation to format a date. Just use the Inspector while in layout mode. You’ll see the options fir formatting numbers, times and dates at the bottom of the last tab in the Insoector.
Lol lol lol just do 1 step in Access none of this other mundane scripting just to have consistent phone number format!! I try to justify FileMaker over Access but it’s simple things like being able to format a phone number that kinda makes me wanna stay with Access! Yes FileMaker has much higher graphic appeal but to learn all this scripting to just format phone number entry seems pointless! Why would FileMaker not provide this format ability as a easy step? I mean I’m pretty sure most people have phone numbers that can afford FileMaker or not🤔
John Mark Osborne My apology ‘s John!! I completely missed that my bad! I guess what I seem to be learning here as I get more involved is probably everything I do in Access I can do also in FileMaker but rather in scripting or calculations where as in Access most of these things are standard and if one desires to really take it to the next level must learn vba and even sql vis a vie queries! That’s one of the things I do like about FileMaker just gotta learn the scripting steps! I do though wish FileMaker could do better reporting vis a vie cross tabs ! The good thing though is I can always perform a found set and export to excel and do all the magic there that one could ever dream of! I guess I just get a little frustrated with FileMaker cause I am much more familiar with office products!! I’ll get there one day! In the meantime I’ll ask more questions as I go along!! Again thanks and Happy Fathers Day
Access has added lots of features aimed at specific tasks while FileMaker has a taken a different approach and offers broad features. In this example, FileMaker doesn't offer a phone formatting feature but instead offers a calculation engine that can accomplish a wide variety of tasks, one of which is formatting a phone number. Once the formula is completed, you don't need to enter it again. Just copy and paste. I think when you consider FileMaker, you need to look at the bigger picture rather than the isolated view from your limited Access point of view.
John Mark Osborne Thanks John after going over your video on this as well as your website I gained more insight on how this works in Filemaker!! Access does provide many options that you can pick from with no separate calculations or scripting required which does serve a good purpose! Although much beyond that it’s vba!!!! FileMaker on the other hand uses scripting. I am beginning to see that with some investment of time most things can still be done in FileMaker and actually more Customized to what you want!! I just wish I could perform things like cross tab reports which I find very useful for me. That being said though it’s not a terrible compromise to get a found set in FileMaker and export to excel and do it there! Actually (pivot tables) which are even better I think than crosstabs!! Please let me know if you think I’m wrong! Perhaps if I stick with getting more comfortable with how FileMaker thinks I may find a better report I can design to be customized for me from within FileMaker itself! Thanks John
Thanks a-ton John. I took your formula and modified it slightly for dates, instead of a phone number. Works like a dream.
Cool! You interested in sharing your formula?
@@JohnMarkOsborne Hi John, I would be more than happy with sharing.
I tweaked it just a tad so that it works for dates in this format. (YYYY-MM-DD) I needed the data in this field immaculately austere, so this setting plus a couple validation settings help me achieve that.
Let (
[
@field = Self;
@format = Filter ( @field ; "1234567890" )
];
Left ( @format; 4 ) & "-" &
Middle ( @format; 5; 2 ) & "-" &
Right ( @format; 2 )
)
I have watched at least 4 videos on this subject. GOD BLESS YOU as you are the only one who was able to convey how to do this in a manner that I understood and was able to replicate!
Thanks for your kind words. Happy FileMaking!
I tried to find a way to do this on my 3 phone fields... All the others are so complicated... And finally I found this tuto !!! WOW, it is so simple and so easy to understand ! Thank you !
You are most welcome! If you liked my free videos, try my full length videos at:
philosophy-of-filemaker.thinkific.com
Happy FileMaking!
Thanks John. Wish I had implemented this logic in my solutions years ago. You simplify things so nicely.
Glad you liked it. Thanks for your kind words!
wonderful explanation
By the way I’m getting a little more familiar with the anchor buoy method after watching your video on it like 10 times...lol I’m a little slow!
Again much thanks and I appreciate your patience with my frustrations! FileMaker does kinda force you to be more of a developer than Access unless one desires to dive into vba! I’m basically comfortable with a few related tables and lots of layouts...lol
Thanks so much JMO for helping a newbie - this has been driving me crazy!
Finished script looks like this & works perfectly:
Let(
[@Phone = Self;
@Numbers = Filter ( @Phone; "1234567890")];
"(" & Left( @Numbers; 3) & ") " &
Middle( @Numbers; 4; 3) & "-" &
Right( @Numbers; 4)
)
Steve - KoruGroup you are most welcome!
This is awesome thanks!
This is excellent - thank you heaps
Thanx for the video, easiest way. I tried to do for date mask but it doesn't work.
Left(date;2) & "/" & Middle(date;3;2) & "/" & Middle(date;5;4)
alexrosana branquinho No need for a calculation to format a date. Just use the Inspector while in layout mode. You’ll see the options fir formatting numbers, times and dates at the bottom of the last tab in the Insoector.
Lol lol lol just do 1 step in Access none of this other mundane scripting just to have consistent phone number format!! I try to justify FileMaker over Access but it’s simple things like being able to format a phone number that kinda makes me wanna stay with Access! Yes FileMaker has much higher graphic appeal but to learn all this scripting to just format phone number entry seems pointless! Why would FileMaker not provide this format ability as a easy step? I mean I’m pretty sure most people have phone numbers that can afford FileMaker or not🤔
Trackhoe075 no scripting here... just used a calculation
John Mark Osborne My apology ‘s John!! I completely missed that my bad! I guess what I seem to be learning here as I get more involved is probably everything I do in Access I can do also in FileMaker but rather in scripting or calculations where as in Access most of these things are standard and if one desires to really take it to the next level must learn vba and even sql vis a vie queries! That’s one of the things I do like about FileMaker just gotta learn the scripting steps! I do though wish FileMaker could do better reporting vis a vie cross tabs ! The good thing though is I can always perform a found set and export to excel and do all the magic there that one could ever dream of! I guess I just get a little frustrated with FileMaker cause I am much more familiar with office products!! I’ll get there one day! In the meantime I’ll ask more questions as I go along!!
Again thanks and Happy Fathers Day
Access has added lots of features aimed at specific tasks while FileMaker has a taken a different approach and offers broad features. In this example, FileMaker doesn't offer a phone formatting feature but instead offers a calculation engine that can accomplish a wide variety of tasks, one of which is formatting a phone number. Once the formula is completed, you don't need to enter it again. Just copy and paste. I think when you consider FileMaker, you need to look at the bigger picture rather than the isolated view from your limited Access point of view.
John Mark Osborne Thanks John after going over your video on this as well as your website I gained more insight on how this works in Filemaker!! Access does provide many options that you can pick from with no separate calculations or scripting required which does serve a good purpose! Although much beyond that it’s vba!!!! FileMaker on the other hand uses scripting. I am beginning to see that with some investment of time most things can still be done in FileMaker and actually more Customized to what you want!! I just wish I could perform things like cross tab reports which I find very useful for me. That being said though it’s not a terrible compromise to get a found set in FileMaker and export to excel and do it there! Actually (pivot tables) which are even better I think than crosstabs!! Please let me know if you think I’m wrong! Perhaps if I stick with getting more comfortable with how FileMaker thinks I may find a better report I can design to be customized for me from within FileMaker itself! Thanks John
I am trying now to decide if I should upgrade to 17 from my 15 advanced!!! Your input would be very appreciated