Jump to content
Salesforce and other SMB Solutions are coming soon. ×

field format


luisitor
 Share

Recommended Posts

Hi everybody

 

a simple question but I am novice.

I have a numeric field that I want to force to have 4 digits format, if I type 26

I want it to autofill into 0026. Can anybody help me?

If easier, this field can be text.

 

Thanks in advance

Regards

Link to comment
Share on other sites

Numbers being what they are, and zeros being what they are, there's no numerical meaning to the leading zeros in 0026. Nor does FileMaker happen to support a number-field formatting option of leading zeros the way it does with dates (where you have the option of displaying as 01/05/2007 for example).

 

So you have essentially two choices that I can think of off the top of my head:

 

• Create a calc field, TEXT not numerical, Right("0000"&YourNumberField, 4). If you would sometimes be entering non-whole number, you'd have to modify that to accomodate the decimal bits if you still want four places to the left of the decimal point. You could, in fact, end up losing legitimate live & important digits on the left if you have decimal bits on the right pushing the whole works beyond four significant characters. Come to think of it, you've also got to know whether or not you'll ever be entering numbers greater than 9999 in that number field. But if it's always going to be whole numbers of no more than 4 digits, that formula would do it. Anyway, you make the true number field so that it can be entered in Browse Mode but not Find Mode and the calc text field so it allows cursor entry only in Find Mode; and you put the calc text field directly on top of the actual number field. That way people see only one field; when they click to do data entry they go into the number field and type "26". They tab onwards or click out of field and what remains visible is the calc text field displaying "0026".

 

• Change the numerical field to a text field. Generally a bad idea (you won't be able to sort by number or do simple mathematical operations relying on that fields' value) but perhaps OK for what you're using it for (whatever that may be...serial number perchance?). As a text field, "0026" is just a string of four characters. You can put field validation on to restrict data entry to the ten numerical characters so folks can't enter "A256" or "N/A" or something. Anyway, then you can use auto-entry options to substitute in leading zeros if the string is less than 4 characters.

Link to comment
Share on other sites

thankyou for your answer.

 

I think I am going to use the second option

 

"Change the numerical field to a text field. Generally a bad idea (you won't be able to sort by number or do simple mathematical operations relying on that fields' value) but perhaps OK for what you're using it for (whatever that may be...serial number perchance?). As a text field, "0026" is just a string of four characters. You can put field validation on to restrict data entry to the ten numerical characters so folks can't enter "A256" or "N/A" or something. Anyway, then you can use auto-entry options to substitute in leading zeros if the string is less than 4 characters.

10-02-2006 03:34 PM"

 

but I am not sure about how to do it, please, could you give specific instructions?

 

Thanks

Luis

Link to comment
Share on other sites

Odd...I replied to this once but it didn't "take". OK, let's try this again...

 

First, make a backup copy of your database.

 

Then open the original, go to Field Definitions, select the field in question, and convert it from Number to Text. You'll probably get a warning dialogue.

 

Once you've converted the field, click "Options" and click the overhead Tab for Validation. Check the validation option "Strict data type" and use the pulldown option "Numerical".

 

Now, I said you could auto-input the "00" if it were missing using auto-enter options, but I didn't notice what forum you'd posted this to. (You're using what version, FileMaker 5.0? Something earlier?) If you didn't accidentally post this to the wrong forum, I'm going to have to back down, you can do that kind of trick in more modern editions of FileMaker but auto-enter worked differently on those older versions. What you can do is create a second validation requirement. On the same tab as the Strict Data Type requirement, farther down, you should see "Validate by Calculation" or some such thing; click that and enter this as the validation formula:

 

Left(YourFieldName, 2)="00"

 

 

Obviously replace "YourFieldName" with your actual field name.

Link to comment
Share on other sites

  • 2 months later...

Hi,

How can a field be checked for valid numeric value? I used database validation tab to set 'strict data type' as numeric only. But during import (using script) it added the records with field empty for invalid values. Isvalid() function takes value as 'B3' as valid.

 

Another step I tried :

GetAsText($TEMP_1) ≠ (($TEMP_1 + 1) - 1)

where $TEMP_1 contained the field value. This worked for all cases except where zeros are prefixed with number. Example: '011'.

 

I need to throw a error if any invalid numeric value is encountered. Any suggestions?

 

Regards

Tushar

Link to comment
Share on other sites

So you're trying to import the invalid data into the fields where it will continue to be invalid data?

 

No. Here are the exact procedure I am performing:

Import data from excel into a temporary table in filemaker. Than run a script which performs some checks (data validation and logical) on each record in temporary table and than commit it to corresponding table. If a record with invalid data comes, than this record should not be committed to table and a error should be thrown to user. The temporary table is cleared after the last record is read.

 

To ensure that invalid data does not come in the database table, I have placed validations in the field validation tab. But when I commit through script, the field takes a blank if the value does not pass validation. The GetLastError is zero. Only when the field is a required value, the error comes during commit.

 

My requirement is simple: check whether the value in the field is number or not. If it's number than commit to database table, if not than throw a error.

 

Regards

Tushar

Link to comment
Share on other sites

You could create a new field, calc, numerical, simply defined as the problematic field. You know, Field A, calc, defined as = Field B.

 

It should resolve to only the authentic numerical content of the original field.

 

If you want to specifically isolate the records with invalid data in them, try entering Find Mode and doing a Find for "?" in the field. That should bring up records with text in the numeric field.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

 Share



×
×
  • Create New...

Important Information

Terms of Use