Replacing text before a search

[cwilly76]

I'm working on a computer inventory database for the school I work at. Part of this inventory records the hardware addresses (MAC addresses) for each computer. I also receive the blocked page notifications from our firewall and I try to monitor their screen in Remote Desktop. However, the firewall likes to send the notifications with the hardware addresses formatted like this 00.03.93.xx.xx.xx, while our inventory (like everyone else's in the world) has them formatted like 00:03:93:xx:xx:xx. Is there a way to have a script reformat the hardware address from the version with the periods to the version with colons? It would make the lookup process a little quicker without me having to retype everything manually.

[Lee Smith]

If you just want to change the . to a :, then use the substitute function.

Substitute ( [color:blue]MAC addresses; "."; ":" )

[color:blue]MAC addresses = your field for this address.

There are three ways you can do this:

Main Menu >> Records >> Replace Field Contents

The script Step Replace

Or, my new favorite:

Change the field[color:blue]MAC addresses to Calculaation Result equal to Text. This will convert the filed to use the : instead of the .

Next, change the field type back to text, and select the Options for the field

Select the Calculated Result and the calculation that you used will already be there. Click OKAY.

[color:red]Deselect the checkbox "[color:blue]Do not replace existing value of the field (if any).

HTH

Lee

[Lee Smith]

I forgot to mention, do this on a copy until you have it figure out, as any and all of this is not reversible.

[cwilly76]

Thanks for the reply and the help! I think I might need to clarify a little though. The database already has the MAC addresses with the colons (and I don't want to change what I currently have in the database). The MAC address that has the periods comes from an email I get from our internet firewall. I used to go into Find mode, copy & paste that MAC address into one of the fields I would search in (there are four total), manually retype each period to be a colon, then search that field. Once I had searched that field, I would move to the next field until I found a match.

I'm hoping that a script can do the period to colon conversion for me, as well as loop it's search function to all four fields so I don't have to do anything but copy & paste and hit a button.

Is that clearer?

[Lee Smith]

Not to much clearer.

The subsitute calculation can be used in a few different ways. I.e. Script step, Main Menu Replace, Separate Calculation Field, Auto Enter Option for the Field.

Substitute ( MAC addresses; "."; ":" )

Replaces the period with the colon

Substitute ( MAC addresses; ":"; "." )

Replaces colon with a period.

If this doesn't help you should post a copy of your file and what you are trying to import. Actual data and files really help us nail down problems.

HTH

Lee

[mr_vodka]

You can use a global field (lets call it gEnterFind) to paste in your data that you copied from the email. (The one with the ' . ' delineation) Then you can use the method as Lee pointed out to you as an auto-entered calc field of Substitute ( MAC addresses; "."; ":" ) with the checkbox for 'Do not replace...' unchecked.

This will convert your xx.xx.xx.xx.xx.xx to xx:xx:xx:xx:xx:xx

Then create a script that will search all 4 fields.

Allow User Abort [off]

Set Error Capture [On]

Enter Find Mode []

Set Field [field1; gEnterFind]

New Request/Record

Set Field [field2; gEnterFind]

New Request/Record

Set Field [field3; gEnterFind]

New Request/Record

Set Field [field4; gEnterFind]

Perform Find []

Put the button thAt will run the script next to your gEnterFind entry field on the layout.

P.S. Do not forget to compensate for error checking in your find script for no returned records and empty field criteria.

[cwilly76]

Mr Vodka & Lee thanks so much!

Lee: I didn't understand earlier that I would need two separate fields in order to make that substitution thing work. Once I set up the second global calculation field that substitution thing worked perfectly!

Vodka: thanks for helping on that script. I didn't realize you could add new requests while in find mode.

Here's what I wrote (for those who might need the answer). I used one field called HW Address Search to paste my address into and a second field called gWatchlist (it's a global) to do the substitution part. Here's the actual script:

# I put these two lines in there because I had issues with these fields saving their values and returning any search to the same computer.

Set Field [Hardware::HW Address Search; ""]

Set Field [Hardware::gWatchlist; ""]

# This next line takes you to the HW Address Search page where you paste the address to search for

Go to Layout [HW Address Search" (Hardware)]

# The "Search" button on the layout resumes the script to the next step.

Pause/Resume Script Script [indefinitely]

All User Abort [Off]

Set Error Capture [On]

Enter Find Mode []

Set Field [Hardware::Ethernet MAC #1; Hardware::gWatchlist]

New Record/Request

Set Field [Hardware::Ethernet MAC #2; Hardware::gWatchlist]

New Record/Request

Set Field [Hardware::Wireless MAC; Hardware::gWatchlist]

New Record/Request

Set Field [Hardware::Bluetooth MAC; Hardware::gWatchlist]

Perform Find []

If [Get( LastError) = 400]

Show Custom Dialog ["Message"; "No find criteria were entered. All records will be displayed."]

Show All Records

Else If [Get(FoundCount) = =]

Show Custom Dialog ["Message"; "No records match this request."]

Show All Records

Else If [Get(FoundCount) = 1]

Go to Layout ["Hardware" (Hardware)]

Sort Records [Restore; No Dialog]

Else

Go to Layout ["Hardware" (Hardware)]

Sort Records [Restore; No Dialog]

End If

So there you go. Thanks a ton guys!

Edited February 14, 2007 by Guest

[mr_vodka]

Lee: I didn't understand earlier that I would need two separate fields in order to make that substitution thing work. Once I set up the second global calculation field that substitution thing worked perfectly!

You dont need two fields necessarily as pointed out in my earlier post.

You can use a global field (lets call it gEnterFind) to paste in your data that you copied from the email. (The one with the ' . ' delineation) Then you can use the method as Lee pointed out to you as an auto-entered calc field of Substitute ( MAC addresses; "."; ":" ) with the checkbox for 'Do not replace...' unchecked.

This will convert your xx.xx.xx.xx.xx.xx to xx:xx:xx:xx:xx:xx

If you use the auto-enter option of calculated result with the substituation formula, as soon as you paste the data into the formula and exit out of the field, it will convert it.

Furthermore, if you do not want to see the value change in front of you, you can do it on the backend by setting a variable with the substituted value. this way you dont need two global fields.

Your code...

Set Field [Hardware::gHW Address Search; ""]  

# This next line takes you to the HW Address Search page where you paste the address to search for

Go to Layout [HW Address Search" (Hardware)]

# The "Search" button on the layout resumes the script to the next step.

Pause/Resume Script Script [indefinitely]

All User Abort [Off]

Set Error Capture [On]

Set Variable [ $macadd; Substitute ( Hardware::gHW Address Search; "."; ":" ) ]



Enter Find Mode []

Set Field [Hardware::Ethernet MAC #1; $macadd]

New Record/Request

Set Field [Hardware::Ethernet MAC #2; $macadd]

New Record/Request

Set Field [Hardware::Wireless MAC; $macadd]

New Record/Request

Set Field [Hardware::Bluetooth MAC; $macadd]

Perform Find []

etc... etc...