Custom Function text fields

[vwgtiturbo]

Okay, so in my searching for Access-style input masks, I stumbled across Custom Functions. Very cool indeed, BUT I am noticing a trend that is annerving, and I was wondering if there was a way to deal with it...

Most of the custom functions I have looked at reference a text field by name. I assume that you are supposed to change 'textfield' to the name of your text field. My question is this... If I have ten fields that I want to use this function on, do I need to make a 10 copies of this function, changing the name of 'textfield' in the function to it's respective field name? It seemed like a big waste to have 10 copies of the same function (well, with exception to the field names, of course), but I don't know how I would do it otherwise. Thanks in advance!

[Maarten Witberg]

no, the parameters in the custom function can remain as they are and then in the field definitions, you change them to fieldnames, constants or nested-in calcs just as you would use a normal function, and reuse them like a normal function too. no need to store duplicates.

[vwgtiturbo]

Hmmm... Well, I tried to save this custom function by simply copying the function into the custom function text box and was told that the specified parameter cannot be found, flagging the 'textfield' in the function...

[vwgtiturbo]

Here is what I am seeing:

 

http://rondeagle.com/misc.files/customfunctionerror.png

[Maarten Witberg]

you got to fill in the function parameters first.

 

 

You can call two things in a cf:

 

- function parameters belonging to the cf

- other functions available in filemaker, among which custom functions, among which the cf itself (whereby it becomes recursive).

[vwgtiturbo]

Okay, this is going to be stupid, but what exactly is a function parameter? I guess I just thought I would be copying the CF into the box and auto-calc'ing my field control, and be done. There weren't any notes about how to use the CF, so I am assuming that I should be able to gather the necessary parameters from the function??

[Maarten Witberg]

the parameters are the input the function performs stuff upon.

 

so

 

CustomFunction ( parameter1 {; parameter2 ;....} )

 

A custom function typically has at least one parameter, but not necessarily. Many people have a cf that outputs just a tab character. Your CF will probably have one, name Textfield.

 

you have to think of it in the same terms as the built-in functions, only this time you get to see under the hood.

 

the function parameter box is top left, it is visible in the screen you posted.

[vwgtiturbo]

the parameters are the input the function performs stuff upon.

 

so

 

CustomFunction ( parameter1 ; parameter2 {;....} )

 

you have to think of it in the same terms as the built-in functions, only this time you get to see under the hood.

 

the function parameter box is top left, it is visible in the screen you posted.

 

 

I apologize for my basic questions... I have only used Access, and even then, didn't really get too involved with it. I guess I am just not sure what exactly I need to put in as a parameter... field names? I'll try to find the FileMaker book somewhere around here, and read up on the functions and parameters. I don't want to waste your time dealing with a complete noob! I have yet to really get a firm grasp on how FM works, and once I step outside of the basics, I start getting the 'deer in the headlights' look...

[AHunter3]

Custom Functions 101.

 

Let's say you have a custom function named "webURLme". All it does is stick "http://www." in front of whatever you apply the function to. So webURLme("google.com") would return "http://www.google.com".

 

In defining that custom function, the part that you provide to the function --> "google.com" in this case -- is a parameter.

 

Let's call it "Text".

 

So you type the actual word "Text" at the top of your Custom Function dialog box, and hit the + key. Now "Text" is a parameter.

 

Now in your formula you can have something akin to:

 

 

"http://www." & Text

 

 

See how that works?

 

So you save it as a custom function. Now you go into a script and invoke webURLme from the list of existing functions, and what it inserts in your scriptkl dialog should look like this:

 

webURLme (Text)

 

 

it's waiting for you to replact "Text" with the fieldname or the literal string that you want to apply your custom function to. "Text" is a placeholder for that.

[vwgtiturbo]

Okay, your example makes perfect sense. Adapting this function to my DB just doesn't make sense to me. I did end up finding my FileMaker book, so I'll read the functions section and come back. Thanks!

[vwgtiturbo]

Okay, I got it to work with no reading (although I really do need to do the reading!). The function referenced "textField", so I simply added "textField" as a parameter. I then changed my 'Time' fields to 'Text' fields on the field setup in the database management pane, and changed it to auto-enter a calculation, using the function, changing "textField" in the formula box to the name of my field ("Take Off Time"). It is working! WOOHOO!

 

I just want to say thanks to anyone that commented on this thread. I couldn't have understood it without you (although I don't understand it excellently without having done the formal reading!). I know it's not always easy dealing with someone that has no clue, and you could have just as easily tossed it aside. In any case, your help has been greatly appreciated. I'll try not to be a pain today

[vwgtiturbo]

Darn... I thought it worked. The function works, in that I can enter "800a" and it places "8:00 AM" in the text field, but two things are wrong... If I enter "0800" (which I would prefer using the 24 hr time scale... must be the military in me), it reverts to "8:00 PM" due to this code in the function:

 

Case ( inter > 0 and inter

Case ( inter ≥ 700 and inter

Case ( inter ≥ 1000 and inter

Case ( inter ≥ 1200 and inter

)))

 

 

Granted, that isn't too big of a deal... just annoying. I could either take that part of the code out, or get in the habit of adding 'a' or 'p' after entering the time. No biggie really... My big issue is that, since it stores the field as text, my elapsed time calculations no longer work (unless it is exactly 24 hours, for some reason...). I guess I need to find a function that works on time fields? Or maybe find a way to convert the text to a time??

[Maarten Witberg]

I don't very much like that calc because, as you state, it returns text instead of time.

 

try this one and see if it works: the result is time so you can use it in time calculations. when used in an auto-enter situation on a time field, it will accept only 24 hour numerical input. when used in a calculated time field, using a text field as data entry, you can use "a" and "p" suffixes. Any number string not equal to 3, 4 or 6 digits will result in the number being returned as entered.

I have only made a limited test so let me know if you encounter weirdness.

It can be used as a CF or as a normal calc since it is not recursive.

 

[b]ParseTime ( number ) =
[/b] Let ( 
[
string = Filter ( number ; "0123456789" ) ;
am = PatternCount ( number ; "a" ) ;
pm = PatternCount ( number ; "p" ) ;
digits = Length ( string ) ;
error = not ( digits = 3 or digits = 4 or digits = 6 )
] ;
Case 
( not error ; 
 Choose 
 ( 
   digits ; "" ; "" ; "" ; //zero, one or two digits return null
   Time // three digits parse string as h : mm
       ( 
          Case  // test am or pm hours
             ( 
               am ;  Left (string ; 1 ) ; pm;  Left ( string ; 1 ) + 12 ; Left ( string ; 1 ) 
             ) ; // end case hours
          Right ( string ;2 ) ; //minutes
          0  // seconds
       )  ;   // end 3 digits
    Time  // 4 digits parse string as hh:mm
       ( 
         Case // test am or pm hours
             ( 
               am ;  Left (string ; 2 ) ; pm;  Left ( string ; 2 ) + 12 ; Left ( string ; 2 ) 
              ) ; // end case hours
         Right ( string ; 2) ; //minutes
          0 //seconds
       ) ;    // end 4 digits
     "" ; //5  digits return null
    Time  // 6 digits parse string as hh:mm:ss
       ( 
          Case // test am or pm hours 
             ( 
               am ;  Left (string ; 2 ) ; pm;  Left ( string ; 2 ) + 12 ; Left ( string ; 2 ) 
             ); //end case hours
          Middle ( string ; 3 ; 2 ) ; //minutes
          Right (string ; 2 ) //seconds
       ) // 6 digits
   ) // end choose
  ;
  number // if error, then return number as entered; filemaker will read as time in hours
) // end case error
) // end let

 

maarten

[vwgtiturbo]

I love you. No, really, I do. This works like a champ. I can enter in the time on a 24 hour scale without entering the ":", and it works with my calculated time formula that calculate the length of time that spans overnight. I don't know enough about scripting and such to know exactly how it works, but you are the man. Thanks a TON. You have really helped calm my OCD on this issue!

[Maarten Witberg]

thanks you're welcome. Please do test thoroughly before deploying. I have made some indentations to the function so maybe it reads a bit better.

[vwgtiturbo]

So, out of curiosity, did you come up with that on-the-fly? If so, wow, do I have a lot to learn!

[egulk2000]

i found a bug with this cf and i was wondering if you may be able to help me with it.

when you enter 1230 pm it returns 1230 am and visa versa.

unless maybe i set this up wrong, which is also a possibility.

also do you have any ideas on how to work with time calculations? i am having problems with calculations involving this scenario

start time = 6 pm

end time = 1 am

how do i figure out the 7 hour differential? subtracting the 2 just gets me -17 ?

hours?

[vwgtiturbo]

You should only need to enter an 'a' or a 'p'. I think that's what I end up doing. I also use a lot of military time, in which case, this CF handles wonderfully. The only way to get the elapsed time to work is by using a date field (because if you start something at 6pm and end at 1am, it doesn't know that the 1am is the next day unless you use a date field) THEN use an auto-calc field for the elapsed time field with the formula:

 

((stopdatefield - startdatefield) * 86400) + stoptimefield - starttimefield

 

Works like a champ!

[touchMe]

Iso8601

 

2008:03:13t13:41:01

[Maarten Witberg]

The only way to get the elapsed time to work is by using a date field

Alternatively, use timestamp fields.

Elapsed = calc, time result, = TimestampEnd - TimestampStart

 

Edited: egulk2000:

notoriously trippy

Opps!

try this one, I think I have everything covered now, but I'm not making any promises. Also I'm wondering if this could be done in a more compact way.

 

 

Let ( [
string = Filter ( number ; "0123456789" ) ;
am = PatternCount ( number ; "a" ) ;
pm = PatternCount ( number ; "p" ) ;
digits = Length ( string ) ;
error1 = digits = 5 or digits > 6  ; // time format can't be resolved 
error2 = ( digits = 2 or digits = 4 or digits = 6 ) and Left ( string ; 2 ) >12 and ( am or pm) ; // mixup 24 hr and 12hr notation by user, then ignore am or pm 
twelve = ( digits = 2 or digits = 4 or digits = 6) and GetAsNumber ( Left ( string ; 2) ) = 12  and (am or pm); //workaround for  12h notation 
StringToUse = Case (  not twelve ; string ; Replace ( string ; 1 ; 2 ; 0 ) ); // part 2 of workaround
digits = Length ( stringtouse )
] ;
Case ( 
not error1 ; 
Case 
 ( 
   not error2 ; 
   Choose 
     ( 
      digits ; "" ; 
       Time ( Case ( am ;  StringToUse ; pm; StringToUse  + 12 ; StringToUse ) ; 0 ; 0  )  ;  // 1 digit; 
       Time ( Case ( am ;  StringToUse ; pm; StringToUse  + 12 ; StringToUse ) ; 0 ; 0  )  ;  // 2 digits ; 
       Time ( Case ( am ;  Left (StringToUse ; 1 ) ; pm; Left ( StringToUse ; 1 ) + 12 ; Left ( StringToUse ; 1 ) ) ; Right ( StringToUse ; 2 ) ; 0  )  ;   // 3 digits
       Time ( Case ( am ;  Left (StringToUse ; 2 ) ; pm; Left ( StringToUse ; 2 ) + 12 ; Left ( StringToUse ; 2 ) ) ; Right ( StringToUse ; 2 ) ; 0 ) ;    // 4 digits
       "" ; 
       Time ( Case ( am ;  Left (StringToUse ; 2 ) ; pm; Left ( StringToUse ; 2 ) + 12  ; Left ( StringToUse ; 2 ) ) ; Middle ( StringToUse ; 3 ; 2 ) ; Right (StringToUse ; 2 ) ) // 6 digits
     ) // end choose
    ;
  Choose 
     ( 
      digits ; "" ; "" ; "" ; "" ;
       Time  ( Left ( stringtouse ; 2 ) ; Right ( stringtouse ; 2) ; 0 ) ;    // 4 digits
       "" ; 
       Time ( Left ( stringtouse ; 2 ); Middle ( stringtouse ; 3 ; 2 ) ; Right (stringtouse ; 2 ) ) // 6 digits
     ) // end choose
 ); //end case error 2
 number // if error1, then return number as entered (will result in "?" in calc field when am or pm and number as hours when not)
) // end case error1
) // end let

[vwgtiturbo]

Along these lines, though not exactly related, is there a way to convert text (such as "10:00 AM") to a valid time to be used in a calculation? I have been curious about this, but am not sure how a function could parse and convert this... I have tried calculating using the text, but of course, that didn't go well.

[AHunter3]

GetAsTime ("10:00 AM") or

GetAsTime ("10:00:03 AM") or

GetAsTime ("10 AM")

 

all work

[CobaltSky]

try this one, I think I have everything covered now, but I'm not making any promises. Also I'm wondering if this could be done in a more compact way.

Hi Kjoe,

It seems to me that the problem is open-ended. Ie vwgtiturbo would need to define all the possible input formats (and you'd need to test them all). There are plenty of combinations one can think of that might throw up a problem. Eg, off the cuff, I'm not sure why you've set your formula to accept "1:59:59 pm", "159p" but to choke on "15959p" (ie why do you need to throw out five digit input values?), and in its present form it seems to erroneously return a null result for "120101 pm", "12:30:00 PM" or "12:00:00 PM". I didn't test it exhaustively, however.

 

Notwithstanding that, I suspect a majority of input formats can be addressed using a slightly more compact logic. For example:

 

Let([

Num = Filter(TxtTime; "0123456789");

Len = Length(Num);

Sec = If(Len > 4; Right(Num; 2); "00");

Min = If(Sec or Len > 4; Middle(Num; Len - 3; 2); Right(Num; 2));

Hr = If(Sec or Len > 4; Left(Num; Len - 4); Left(Num; Len - 2));

Td = Mod(Time(Hr; Min; Sec); 86400);

Sfx = Choose(PatternCount(TxtTime; "p") or Td > 43199; "AM"; "PM");

Tv = If(Td > 46799; Td - 43200; Td);

Th = Let(h = Mod(Hour(Tv); 12); If(h; h; 12));

Ts = Right("0" & Seconds(Td); 2)];

Th & ":" & Right("0" & Minute(Tv); 2) & If(Ts; ":" & Ts) & " " & Sfx

)

 

The above expression receives a text input (TxtTime) and returns a text-formatted time value for the following input strings (outputs are shown in colored text):

 

120101 pm >> 12:01:01 PM

800a >> 8:00 AM

8:00 AM >> 8:00 AM

0800 >> 8:00 AM

1201 >> 12:01 PM

11:45:00p >> 11:45 PM

80020 >> 8:00:20 AM

13:59:59 >> 1:59:59 PM

15959p >> 1:59:59 PM

15960p >> 2:00 PM

152522p >> 3:25:22 PM

2305 >> 11:05 PM

230535 >> 11:05:35 PM

 

If there are other input formations vwgtiturbo needs this to handle then some tweaks might be needed, but on first blush this seems to deal with a reasonable number of possibilities.

 

It's worth noting, however, that while this construction handles some input errors gracefully - eg "15960p" is commuted to 2:00 PM and "60p" is commuted to 1:00 PM - it makes no attempt to reject out-of-range values.

[Maarten Witberg]

awesome. very glad to see you back here, as well.