Hi again!

Having been a bit busy building my db solution to make my job easy [and enjoyable], I have come across a few niggling calculations that I've put on the back burner until a solution popped into existence...

The current one: formatting MAC Addresses for Network records.

Until recently, just punching in the full MAC address was the norm eg:

00:Aa:Bb:Cc:Dd:Ee

but since making the field UPPERCASE, I wondered, is there a way of popping in the colons in between?

Checking:

http://www.fmforums.com/forum/showtopic.php?tid/194855

Replace ( Replace ( "aired" ; 4 ; 0 ; "h" ) ; 6 ; 0 ; "a" )

 gives: airhead !



... I've been able to deduce the following:

Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 2 ; 0 ; ":" ) ; 5 ; 0 ; ":" ) ; 8 ; 0 ; ":" ) ; 11 ; 0 ; ":" ) ; 14 ; 0 ; ":" )

would allow you to input:

00aabbccddee

...into the textfield and the outputfield with the above calculation [set to uppercase] would produce:

00:AA:BB:CC:DD:EE

Now, is there a way that I could get the calculation into the textfield and let it enact on the input for that textfield after entry has completed?

I reckon some of you would recommend a script trigger of some kind, but so far, running these from within IWP isn't possible.

The query is down to the fact my db has become advanced enough to warrant buying a barcode scanner to handle the data input and I want to slightly minimize the number of buttons and fields that impinge on data input.

Any insight would be greatly received.

Edited October 27, 200916 yr by Guest
YAY! I put too many replaces in... I'm sure someone could've jumped in and point that out before the off :D

let it enact on the input for that textfield after entry has completed?

Take a look at this:

http://dwaynewright.blogspot.com/2007/04/filemaker-auto-enter-calculated-value.html

--sd

Soren, thanks for nudge in the right direction.

Upon rechecking the calculation and adjusting to:

Case ( Length ( textfield ) = 12 ; Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 3 ; 0 ; ":" ) ; 6 ; 0 ; ":" ) ; 9 ; 0 ; ":" ) ; 12 ; 0 ; ":" ) ; 15 ; 0 ; ":" ) ;

Middle ( textfield ; 3 ; 1 ) = " " ; Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 3 ; 1 ; ":" ) ; 6 ; 1 ; ":" ) ; 9 ; 1 ; ":" ) ; 12 ; 1 ; ":" ) ; 15 ; 1 ; ":" ) ;

Middle ( textfield ; 3 ; 1 ) = "-" ; Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 3 ; 1 ; ":" ) ; 6 ; 1 ; ":" ) ; 9 ; 1 ; ":" ) ; 12 ; 1 ; ":" ) ; 15 ; 1 ; ":" ) ;

 textfield )

I now have a box that can accept the following:

AaBbCcDdEeFf

aa-bb-cc-dd-ee-ff

AA bb CC dd EE ff

and instantly reformat to:

AA:BB:CC:DD:EE:FF

before committing the record to the db.

I'll be working on IPv6 strings later

[and colour flagging for incomplete entries...]

I just need the barcode scanner to work properly now!

Ok , I have had a bit of a fiddle and I've got the error correction colouring working.

Here is is:

// MAC Address is 17 chars long divided into 6 hexadecimal words separated by colons

// Upper ( text )

//

// and Filter ( textfield ; "0123456789ABCDEFabcdef:" )

// TextColor ( text ; RGB ( red ; green ; blue ) )

//

// Don't forget to calculate for the the correct entry

// ####:##:##:##:##

Case ( Length ( textfield ) = 12 ; TextColor ( Upper ( Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 3 ; 0 ; ":" ) ; 6 ; 0 ; ":" ) ; 9 ; 0 ; ":" ) ; 12 ; 0 ; ":" ) ; 15 ; 0 ; ":" ) ) ; RGB ( 0 ; 0 ; 0 ) ) ;

Length ( textfield ) = 17 and Middle ( textfield ; 3 ; 1 ) = " " ; TextColor ( Upper ( Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 3 ; 1 ; ":" ) ; 6 ; 1 ; ":" ) ; 9 ; 1 ; ":" ) ; 12 ; 1 ; ":" ) ; 15 ; 1 ; ":" ) ) ; RGB ( 0 ; 0 ; 0 ) ) ;

Length ( textfield ) = 17 and Middle ( textfield ; 3 ; 1 ) = "-" ; TextColor ( Upper ( Replace ( Replace ( Replace ( Replace ( Replace ( textfield ; 3 ; 1 ; ":" ) ; 6 ; 1 ; ":" ) ; 9 ; 1 ; ":" ) ; 12 ; 1 ; ":" ) ; 15 ; 1 ; ":" ) ) ; RGB ( 0 ; 0 ; 0 ) ) ;

Length ( textfield ) = 17 and Middle ( textfield ; 3 ; 1 ) = ":" and Middle ( textfield ; 6 ; 1 ) = ":" and Middle ( textfield ; 9 ; 1 ) = ":" and Middle ( textfield ; 12 ; 1 ) = ":" and Middle ( textfield ; 15 ; 1 ) = ":" ;

TextColor ( Upper ( textfield ) ; RGB ( 0 ; 0 ; 0 ) ) ;

TextColor ( textfield ; RGB ( 255 ; 0 ; 0 ) ) )

But, I know have a new query based on what I have done so far.

Upon checking various barcodes, I can see that there will be no problem with this auto-calculation reformatting input that looks like:



1st Case: aabbccddeeff

2nd Case: 00 88 6b 77 22 12

3rd Case: Aa-Bb-11-D3-00-A4



or even now the correct entry:

4th Case: 00:44:bb:00:93:16



or incorrect spacing:

4th Case: 0:12:34:456:78:aa



or matching nothing:

5th Case: aa:bb-cc ddeeff123456



What I want to correct now is um... user error :

Well, not quite correct... more like flag user error.

I thought that I could run a filter in unison with the the auto-calculation via the validation tab to check that only hexadecimal characters had been entered. So I added...

Filter ( textfield ; "0123456789ABCDEFabcdef:" )

...to the validation calculation.

But that's not quite what I am after. The Error window popped up with my validation error "Only hexadecimal characters with colons can be entered. Do you want to revert? Y/N?"

But this validation won't allow a blank [null] field through either. A bit frustrating really.

So if someone can nudge me towards the right guide again, that would be helpful.

Using my existing calculation code, I would like to check that hexadecimal characters have been entered - if not, just colour red [flag].

eg: 00:0X:00:00:00:00

That's it so far. The formatting is coming along and I'm enjoying the result. I can get the data in quicker and more correct now - which is the idea in the first place.

Well I didn't check if you solution exhibited bugs, but usually do I exploit this method:

http://www.briandunning.com/cf/892

When ever something is repeated just a few times....

--sd