problem with a number field

[aaroncp]

Good morning. I'm having problems with a field in our company database that holds a number value.

 

We import the data to this database from several sources and sometimes the data we import has a dollar sign ($) and sometimes it doesn't.

 

So, some records in our database have dollar signs and some don't.

 

How do I go through all the records in our database and remove the $ from that number field in all records that contain it so that I'm working with just plain numbers?

 

Thanks,

Aaron

[HBMarlowe]

How do I go through all the records in our database and remove the $ from that number field in all records that contain it so that I'm working with just plain numbers?

 

One way would be to use a looping script that sets the field to

Filter ( YourNumberField ; "0123456789." )

 

If you don't know how to set that up ask here.

[aaroncp]

One way would be to use a looping script that sets the field to

Filter ( YourNumberField ; "0123456789." )

 

If you don't know how to set that up ask here.

 

That sounds like what I need to do. I tried searching for "$" in the field but it never finds any even though I can see them.

 

Can you tell me how to set up that field filter script.

 

Aaron

[HBMarlowe]

1. Backup your database before you begin!

 

2. Make the following script:

Go to Record/Request/Page [First]

Loop

Set Field [YourNumberField ; Filter ( YourNumberField ; "0123456789." )]

Go to Record/Request/Page [Next;Exit after last]

End Loop

 

3. Go to a layout that contains the field. Make sure the layout is based on the same same table that the field is in (if you're not sure how to check, ask here).

 

4. Temporarily change the field type to TEXT (if you're not sure how to check, ask here).

 

5. You can now search for "$" in the field.

 

6. While still on the same layout, run the script.

 

7. Change the field type back to NUMBER.

[aaroncp]

Thank you very, very much! Got all my $'s stripped out in about 3 minutes.

 

Thanks!!!

[HBMarlowe]

We import the data to this database from several sources and sometimes the data we import has a dollar sign ($) and sometimes it doesn't.

 

You did not say what the data source is, but it might be possible to strip the $ before you import.

[aaroncp]

It's a pricing spreadsheet from several of our suppliers (some include the $, most don't) but from now on I'll be deleting all the $'s from the excel spreadsheet before importing.

[David Head]

1. Backup your database before you begin!

 

2. Make the following script:

Go to Record/Request/Page [First]

Loop

Set Field [YourNumberField ; Filter ( YourNumberField ; "0123456789." )]

Go to Record/Request/Page [Next;Exit after last]

End Loop

 

3. Go to a layout that contains the field. Make sure the layout is based on the same same table that the field is in (if you're not sure how to check, ask here).

 

4. Temporarily change the field type to TEXT (if you're not sure how to check, ask here).

 

5. You can now search for "$" in the field.

 

6. While still on the same layout, run the script.

 

7. Change the field type back to NUMBER.

#1 is good advice!

#3 the layout does not necessarily need to contain the field - it just needs to be based on the right table. I suppose if you can see the field, you are pretty sure that you are on a good layout.

#4 why change the field to text? Is it to search for records with $ and therefore cut down the time to run the script? I would show all records, skip straight to #6 and run the script - then all records would be fixed. #7 then not required. smiley_cool

 

For future data entry and imports, you might want to set an auto-entered calculation that replaces existing data = Filter ( YourNumberField ; "0123456789." )