IsValid not checking for valid values?

[FUBAR]

My problem involves Importing, Defining Fields, and Scripting but since I'm trying to fix my problem by using a script I chose to post it here.

I am importing records from an excel file into FM 5.5. One column of the excel file both contains text and numbers (the numbers designating years.) What I want to do is have the information imported into the database into a field called Year, but leave out the text that is not needed (without editing the excel file.)

My first idea was that possibly FM would disregard the text being imported since Year is a Number field. Didn't work.

Second idea was to use the strict data entry option on Year to allow only numbers to be used. Although this works when the information is being entered manually, importing the information bypasses this and the text is enterend anyway.

Third idea was to import everything then use a script to delete the "bogus" records (getting to why the post is in this topic and the title.)

I tired using the IsValid(Year) command since its described as "Returns false (0) when field contains an invalid value." However, this command will not return false when the information in a number field is actually text. This seems like an "invalid value" to me. Any suggestions/help?

Thanks in advance.

[LiveOak]

If there are any valid numbers in a number field, even if there is also text, the IsValid(number) will return a 1. To get rid of the text, define a calculation field with the result number set equal to your number field or perform a SetField to itself "SetField(MyNumber,MyNumber).

-bd

[FUBAR]

Almost...if IsValid(Year) returns false, then Set Field["Year","Year"] will erase the invalid text leaving it blank.

However, "Year" returns true from IsValid(Year) and then Set Field["Year","Year"] replaces "Year" with "1" (I suppose I could have a work around deleting all records with "1" but I'd like to avoid that.

On further testing, "test" in a number field is being returned as true from IsValid(Year). Is there something I'm missing about IsValid()?

[Thom]

I am unclear what you are trying to accomplish. You mention wanting to "import everything then use a script to delete the "bogus" records". Do you really want to delete records with text in the Year field, or do you want to get rid of the text?

If you just want to zap the text, make Year a text field and do a Replace with calculated result. The calc is Texttonum(Substitute(Substitute(Substitute(Substitute(Year,"t",""),"f",""),"y",""),"n","")) . The reason for eliminating t,f,y,n is that these letters may be interpreted as 1,0,1,0 respectively. For example, if you enter the word "test" by itself into a number field, it will numerically evaluate to 1, or true. However, if there are any numbers in the field, they will override any text. You may run into trouble, if someone entered a range. The range "10-20" in a number field will evaluate to 1020. In this case all non-numeric characters are ignored, and the remaining digits are concatented to make a number.

If you want to find records that have non-numeric data in the Year field, make Year a text field. Make a calc field:

validNumber(calc,number) = TextToNum(Year) = Year

In records with only numbers in the Year field, validNumber will return 1. In records with non-numeric data in the Year field, validNumber will return 0. You can then search for validNumber=0, and deal with those records however you want.

[Steven H. Blackwell]

Let's not confuse a valid value, a validation as it were, with the IsValid function. The ISValid function refers to matters of relationships. I am going to let Droid explain this more fully since it is very tricky and he is the engineer.

Old Advance Man

[FUBAR]

I want to delete all of the records that I imported that don't have a number for the field Year (I was using "bogus" to refer to any record with text in the Year field and I want to delete these records.)

Once again any and all help is appreciated.

[BobWeaver]

You probably need to make up a calculation field that returns a value depending on the validity of the field. It can be something as simple as:

year>0

which will return a 1 if the field contains any digits at all (other than all zeroes), even if they are mixed in with text. Or, more likely you may want to make a tighter filter like:

(year>1950)and(year<2050)

assuming a 4 digit year.

You could also try:

NumToText(TextToNum(year))=year

Then search the calculated field for zero values, which indicate invalid records.

[Anatoli]

I am using IsValid for testing IP connections on VPN.

[per]

Hi there,

Did the year always on the same spot in the field or does it come on different places?

If it always come on the same place you could do as I do:

I called your Excel import field for "Excel".

Make a calculation field "Year 2"

MiddleWords(Excel; x ; 1 );"")

x = word number where the year appears.

Then make a script:

Set "Year" with "Year 2".

Ready.

By the next step, making it automaticly, I have some problems.

Maybe someone could help both of us:

How could I select one specific row in the field, cutting it out past it into a new field where I can use above "MiddleWords(Excel; x ; 1 );"")"

Good luck.

-Per