SET FIELDS, Set(“Fields”), FLDLIST()

SET FIELDS lets you limit some commands to dealing with only a subset of the fields in a table or even a group of fields drawn from several tables. The two functions give you information about the current settings. SET FIELDS affects those commands that let you specify a field list with the FIELDS clause, as well as INSERT INTO in its FROM MEMVAR and FROM ARRAY forms.

Although the things you can do with SET FIELDS are pretty cool, we’ve never used it much and don’t expect to now. Pretty much everything you can do with SET FIELDS, you can also do with views (the SQL kind, not the CREATE VIEW kind).

Usage

SET FIELDS ON | OFF
SET FIELDS LOCAL | GLOBAL
SET FIELDS TO [ FieldList | ALL [ LIKE | EXCEPT Skeleton ] ]

As the syntax shows, the command has three forms. The ON/OFF version determines whether the SET FIELDS setting is used. The second form, LOCAL/GLOBAL, determines whether each table has its own setting for SET FIELDS or if there’s a single field list across all work areas (in the data session). SETting FIELDS GLOBAL can be pretty neat, since it lets you refer to fields from other work areas without preceding them with an alias. With the right settings, you can put up a one-to-many browse without a FIELDS clause—it works for grids, too.

SET FIELDS TO lets you specify a list of fields to be included. Unlike most SET x TO commands in FoxPro, the list is additive. That means each time you issue SET FIELDS, the fields you list are added to the current field list. The list can include calculated fields—use the same format you do in a BROWSE. The LIKE and EXCEPT let you rule fields in or out based on their names—they’re useful only if you have a strict naming convention. Any form of SET FIELDS TO with anything after TO also SETs FIELDS ON. SET FIELDS TO with nothing following it, however, doesn’t use the additive rule. It empties the field list and subsequent commands complain of “No fields found to process.”

SET FIELDS TO ALL is a little strange—it behaves differently depending on the LOCAL/GLOBAL setting. With LOCAL, it restores the field list to the entire table. With GLOBAL, ALL adds all the fields in the current table to the current list of fields, which means some may be listed more than once.

Usage

cOnOrOff = SET("FIELDS")
cListOfFields = SET("FIELDS", 1)
cLocalOrGlobal = SET("FIELDS", 2)
cListOfFields = FLDLIST()
cFieldName = FLDLIST( nWhichField )

SET(“FIELDS”) and FLDLIST() tell you all about the current fields set up. SET(“FIELDS”, 1) and FLDLIST() both return the list of fields, but FLDLIST() always includes the alias while SET(“FIELDS”, 1) includes the alias only when FIELDS is set to GLOBAL. If you SET FIELDS TO ALL, SET(“FIELDS”, 1) returns “ALL,” while FLDLIST() spells them all out. Passing a numeric parameter to FLDLIST() lets you pull out the individual fields in the list. A parameter beyond the number of fields causes FLDLIST() to return an empty string.

Example

USE Customer
SET FIELDS TO Company_Name, Customer_Id
BROW             && Notice that only name and id show up
? SET("FIELDS")   && Returns "ON"
? SET("FIELDS", 1) && Returns "COMPANY_NAME,CUSTOMER_ID"
? FLDLIST()
        && Returns "CUSTOMER.COMPANY_NAME,CUSTOMER.CUSTOMER_ID"
? SET("FIELDS", 2) && Returns "LOCAL"

See Also

AFields(), Append From, Blank, Browse, Copy Structure, Copy To, Display, Export, Gather, Insert-SQL, List, Replace From Array, Scatter, Sort