Genelia

To find invalid character(s) in number field.

13 posts in this topic

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,

Share this post


Link to post
Share on other sites

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.

 

 

Share this post


Link to post
Share on other sites

Posted (edited)

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 by comment

Share this post


Link to post
Share on other sites

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,

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

 

 

Share this post


Link to post
Share on other sites
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?

1 person likes this

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites
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.

1 person likes this

Share this post


Link to post
Share on other sites

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.

COde_with_result.png

Share this post


Link to post
Share on other sites
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.

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now