To find invalid character(s) in number field.

[Genelia]

Hi Guys,

I have big database where i am having problem of invalid characters like $, ,(COma),(Double dot for decimal number), and few other invalid character entry on number fields.

There are around 90 number fields where this kind of data entered. and i am not able to find out any special search criteria by which i can find all invalid character.

 

I have tried "?" but it is showing only double dot entry data. 

I have tried getasnumber(field)<> field  and getasnumber(field)<> length(field) but showing 0 entry data.

 

So please help me out if you have any idea.

 

There are around 40000 records and also i hav etried to clean up all th 90 fields for 40000 records but file stopped suddendly after 3 hours working. So its better to find out invalid records and then clean up as i am sure thre are hardly 2000 records having this problem.

 

SO really i need your expert suggestion.

 

Thanks in advance,

[Vaughan]

First get a backup copy of the database to play with, the optimum strategy will take some time to work out.

Decide how you'd like to clean the data. For instance, should double-dots be replaced with a single dot or removed completely? Replacing double-dots to single would be

Substitute( self ; ".." ; "." )

As for cleaning up the rest of the data, the easiest method is to replace the field contents with GetAsNumber( self ) which will take the data, interpret it as a number, and put that number back into the field. This will strip all non-numeric characters and spaces etc. You'll need to test it and see how it looks. "$4.60" will be replaced with "4.6" for instance.

 

 

[comment]

1 hour ago, Genelia said:

There are around 90 number fields where this kind of data entered.

You have 90 number fields in the same table?! Surely, that's a symptom of wrong structure. You should probably have 90 related records in a related table. Unless you fix this, you will have to repeat any cleanup measure 90 times.

 

1 hour ago, Genelia said:

I have tried "?" but it is showing only double dot entry data. 

That is not my experience. Searching for ? in a number filed will find only records that contain a value with no digits - i.e. a value that cannot be converted to a number using the GetAsNumber() function. It will not find a value of "12..345" or "1.2.3".

 

1 hour ago, Genelia said:

There are around 40000 records and also i hav etried to clean up all th 90 fields for 40000 records but file stopped suddendly after 3 hours working.

You didn't say how you tried to clean them up. 40k records is quite a small number, even times 90, so I don't see what could take upwards of 3 hours. The correct way to clean this up is more or less what Vaughan suggested in the second part of his post:

1. Make a backup;

2. Show all records;

3. Click in the first field and replace the field's contents with a calculated result = GetAsNumber ( YourFirstField );

4. Repeat 2 & 3 90 times.

This could be scripted, but it would be much better to have a single field a related table first, so you only have to do it once. Not to mention other benefits of normalization.

 

@Vaughan

1 hour ago, Vaughan said:

Replacing double-dots to single would be

Substitute( self ; ".." ; "." )

You shouldn't take OP's word for granted. GetAsNumber ( Self ) will fix double dots just fine. Except you cannot use Self when replacing field contents.

P.S. Nice to see you back.

 

Edited March 23, 2017 by comment

[Genelia]

Yes i got the point just to replce fields as getasnumber(field) is working perfect. but instead of replace code for all 90 fields for 40000 records is very time consuimg. I just wanted i say way i can find those invalid charas , so that i would have less records to replcace.

 

Thanks,

[comment]

9 minutes ago, Genelia said:

just wanted i say way i can find those invalid charas , so that i would have less records to replcace.

I don't know of a way to find them in the existing number field. You would have to construct a calculation field along the lines of =

GetAsText ( YourFirstField ) ≠ GetAsNumber ( YourFirstField )

and search for 1 in this field. Of course, with 90 fields, this is not practical.

 

[Genelia]

I tried but it is also showing me records having data as 0(zero).

[Genelia]

Thanks to All, But i got the solution. Let me share. Actually when we export the number fields in excel format, it just automatically takes the valid number data only and it is the feature of filemaker export function by that it just omit invalid character and export only valid numbers. So my plan is just export the records in excel and then again import that excel and replce all number fields from excel data columns and it will clean up my database.

 

 

[comment]

3 hours ago, Genelia said:

it is also showing me records having data as 0(zero).

No, I don't think so. This is the second time you report a behavior that I cannot reproduce. Are you sure you're using Filemaker?

[Genelia]

Yes i am using Filemaker and result found as i mentioned(might be by deafult i have set all number fields as Auto entry as 0 checked in database definition, so all records have 0 as be default). But good thing is that  i got the way to solve my problem.

 

Just export all the records in excel format with required all number fields along with their unique field and then just again import all records from excel to filemaker by using that unique field matching and it will update all number fields in validated number format.(During excel export, all number fields only export validated data  means  itself it removes white space and non numeric chars)

[comment]

56 minutes ago, Genelia said:

i have set all number fields as Auto entry as 0

A zero is zero, no matter how it was entered. And GetAsText ( 0 ) ≠ GetAsNumber ( 0 ) returns false.

 

19 hours ago, Genelia said:

But i got the solution.

IMHO, your real problem is having 90 fields - and I suspect you will run into it again.

[Genelia]

Yes now i have defined fields as restricted numeric only, and imported data from excel so now problem is seems to solved. 

 

I have attached screenshot where i mentioned, How i coded that find condition and what i got as result. please see that.

[comment]

1 hour ago, Genelia said:

How i coded that find condition

This is very different from what I suggested:

On 03/23/2017 at 10:53 AM, comment said:

You would have to construct a calculation field along the lines of =

GetAsText ( YourFirstField ) ≠ GetAsNumber ( YourFirstField )

and search for 1 in this field.

 

[Genelia]

OK but as fields are 90 so not good choice. But yes if fields are less then it is wise decision.