Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2075
  • Last Modified:

Field Size in Filemaker

How can I find out what the field sizes are for defined files in Filemaker 7.0.  I'm new to filemaker and trying to find field size to a table that I want to export.
0
NBilly
Asked:
NBilly
  • 6
  • 5
  • 5
2 Solutions
 
NBillyAuthor Commented:
I made a mistake. It should be "defined fields".
0
 
billmercerCommented:
FileMaker doesn't have fixed field sizes or fixed numeric types.

The maximum number of characters you can have in an FM7 text or number field
is one billion, but that's not very practical as a fixed field width in an export field.
 
If you want to find out what the longest value for a field is in your database, you can
use the Length() function to determine the length of a given value, and use the Max()
function with a relationship to determine the longest length for that field.
An easy way to find the maximum lengths for all the fields is to export the file to
.DBF format, then look at the DBF. The field descriptions in the file include the
maximum length of that field as a number.

The date and time fields are stored internally in a numeric format, the date fields really
store a number of days since 01/01/0001, and the time fields store a number of seconds.
0
 
lesouefCommented:
There is no fixed size in FM, text fields can be up to 64k, numbers can be up to 256 char. Dates follow the systems settings.
So the best is to export tab delimited files (depends what you gonna do with it)
If you need to find out the largest record for a given field, you can create a field which will be = the length of a given field, then sort upon that field to locate the largest one. Of course you've got to do this for every field of which the length may vary...
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
billmercerCommented:
Lesouef, the field sizes you describe are for FileMaker 5-6. FM7 has much larger limits.

0
 
lesouefCommented:
Bill,
You may be right, I once read the migration doc and I can't remember of anything changed in this area. Where did you see that?
I just had a quick look at the online help, found nothing special about field size, but it is still true that I am mainly a FM6 user. I am still a lot faster in FM6, and I hate those endless list of layouts and scripts, putting everything in the same basket has some drawbacks! and I have not found any application yet where I could get rid of os9.
Also, my answer will however help him to size the destination database fields, I think that was the real prupose of the question.
So can you specify the fm7 limits for our questioneer? or refer to a doc item?
0
 
billmercerCommented:
"Also, my answer will however help him to size the destination database fields, I think that was the real prupose of the question."
That's how I understood it as well, which is why I suggested the .DBF export trick in my first post. It's quite handy for getting effective field sizes all at once. :)

This is from the technical specs for FM7, TechInfo article 109047. (Unfortunately, FileMaker has just changed their knowledgebase, and you can't find this page on their site anymore, but you can still see it in the Google cache.)

Technical Specifications Of FileMaker Pro 7

• File size: Limited only by disk space, to a maximum of 8 TB (terabytes) on a hard disk and OS API capability.

• Number of files per disk: Limited only by disk space.

• Number of files open simultaneously: Up to 125 files recommended.

• Number of remote users per file: Maximum of 5 concurrent client.

• Number of files shared: There is no limit.

• Number of sessions via Web browser: Access to web-published database is limited to 5 concurrent sessions. Note. If another view is opened through 'New Window' that's still in the same session.

• Number of tables per file:1 million.

• Number of records per table: 64 quadrillion total records over life time of file.

• Maximum record size: Limited by disk space or maximum file size.

• Number of fields/columns per record: 256 million total fields over life time of file.

• Number of relationships per file: Limited only by disk space or maximum file size.

• Length of field name: Up to 100 characters.

• Field types: Text, Number, Date, Time, TimeStamp, Container (for OLE objects, sound, picture, or movie) and Summary. The Global type is specified as an option.

• Serial number options: The maximum number is limited by the Number type's range. In the Auto-Enter Serial Number option, user can enter up to 255 characters in the 'next value' edit control and numbers in the range 1 to 32767 in the 'increment by' edit control. If alpha numeric the rightmost characters that are numbers are incremented.

Maximum field size, by type:

• Text: Up to 1 billion characters per field per repetition (limited by available memory) with optional text style runs and paragraph runs. Index is based on the first 100 characters of each word or value.

• Number: Support values from 10^-400 up to 10^400 and the negative values of the same range. Index based on the first 400 significant digits. Up to 1 billion characters per field. The first 400 digits are indexed.

• Date: Gregorian calendar with the range of 1/1/0001...12/31/3000. Month, day, year order based on system settings when file is created.

• Time: Input formats: Hour:Minute:Seconds.Fractional, Hour:Minute:Seconds,Minute:Seconds.Fractional, Hour:Minute, Seconds.Fractional and Seconds. Time may be preceded by a negative sign and each numeric value must be in a range of 0 to 999999. Times are not limited to 24-hour format to allow for calculations spanning multiple days. If the minutes or seconds exceed 60, the excess is carried over to minutes or hours as appropriate.

• Timestamp: Standard date followed by spaces and then by a time in the range of 0:0:0.0 to 23:59:59.999999.

• Container: Multiple streams of binary data totaling no more then 4 gigabytes.

• Calculation: Depends on result type.

• Summary: Depends on result type.

• Number of field repetitions (sub records): Up to 32,767 for each field.

• Indexed (key) fields per file: Any field may be specified to index as an option, except: Container, Summary, Global, or unstored Calculation fields.

• Number of sort levels: No limit.

• Size of calculation formula: Maximum of 30,000 characters, including text and numbers, any referenced fields, operators, functions and parentheses.

&#8226; Calculation operators: =,<>, >, <, >=, <=, [ ¤, &#65533;, £, Mac OS only], AND, OR, NOT, XOR, (, ), +, -, /, *, ^, (paragraph), " " (text literal), & (concatenation).

&#8226; Number of calculation functions: 239.
Aggregate 9
Date 13
Design 20
Errors 1
External 1
Financial 4
Get 71
Logical 13
Miscellaneous 5
Number 18
Operator 22
Repeating 3
Summary 1
Text 40
Text Formatting 4
Time 4
TimeStamp 1
Trigonometric 7

&#8226; Number of summary functions: 12

&#8226; Number of layouts per file: Limited by disk space or maximum file size.

&#8226; Layout size: Up to 110 inches wide by 110 inches long; may be limited by currently selected printer and page setup. Objects beyond current page width do not print.

&#8226; Number of layout objects: Maximum of 32,768 objects on each layout.

&#8226; Number of scripts: Limited by disk space. Displayed in Scripts menu: 512 on Windows, 32,767 on Mac OS.

&#8226; Number of columns across the page: Up to 99 columns.

&#8226; Number of labels across the page: Up to 99 labels.

&#8226; File formats for import: FileMaker Pro, Tab-Separated Text, Comma-Separated Text, SYLK, Excel, ODBC, DBF (dBASE), DIF, WK1, WKS, BASIC, Merge

&#8226; File formats for export: Tab-Separated Text, Comma-Separated Text, SYLK, DBF (dBASE ), DIF, HTML, WK1, WKS, BASIC, Merge, XML, FileMaker Pro

&#8226; Picture formats for import: JPEG, GIF, PNG, BMP, Metafile (Windows only), Enhanced Metafile (Windows only), EPSF, PICT, TIFF, JFIF, JPEG 2000 (Mac OS X only), PDF (Mac OS X only), QuickTime movies and other formats supported by QuickTime (PSD, FlashPix, QIF, SGI, TGA, MacPaint).
All use of this article is subject to FileMaker´s terms of use at www.filemaker.com/legal/terms.html.
0
 
lesouefCommented:
Woahh! Sorry for yr first post, I hadn't read it, you were probably typing at the same time as me, and as usual, I did not re-read previous posts later on, I only read the last one assuming I've seen others. This is how I missed the dbf proposal.
0
 
NBillyAuthor Commented:
I did the dbf export but trying to find the field size. What program would I use to open the dbf file to see the size of the field.
0
 
lesouefCommented:
access may do it for instance, it does it from some ODBC sources.
Which data base are you taking these data to?
0
 
NBillyAuthor Commented:
I'm importing it to access but it gives me this error. 'The microsoft jet database engine could not find the object "inquiry.dbf". Make sure the object exist and that you spell the name and path name correctly'.
0
 
billmercerCommented:
Use Excel to open the DBF file.
Access has too many limitations on what kinds of field names and file names you can have, so it almost always chokes on DBFs.
Here's an example of how it will look.

CLIENTID,N,2,0     CLIENTNAME,C,17     VISITDATE,D     COMMENTS,C,53     ITEMPURCHA,C,20     COST,N,8,2

Look at the number at the end of the C type fields (C indicates a character, or text field)
The longest CLIENTNAME value is 17 characters, the longest COMMENTS value is 53 characters.

0
 
NBillyAuthor Commented:
Excel opens the DBF file with column header and the data, that's it.  Doesn't show anything like you type. (ie. cleintname,c,17)
0
 
lesouefCommented:
I am afraid he's right Bill. Are you sure that works with the kind of dbf Fm exports? I did the test because I did not remember having seen that in the past. On top DBF needs short names, ignores accents...
0
 
billmercerCommented:
Whoops! Sorry...
I have been using OpenOffice.org instead of Excel for some time now.
This works with OpenOffice, but not with Excel.

You can still get the info in Excel though.

Open the DBF file with Excel. Highlight the column you want to find the size of (don't resize anything),
go to the Column Width setting, and look at the number shown. That's the maximum field width.









0
 
NBillyAuthor Commented:
Got it.  Thank you.
0
 
billmercerCommented:
You can also get this information through FM's ODBC driver.

Select max(length(TheFieldName) from TheTableName
 
will return the length of the longest value in that field.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now