Fox Pro Expressions - NEMRC



FoxPro Expressions

Make your data work for you!

Have you ever wanted a report that showed all the parcels that are valued at $80,000? Sure you could physically count each parcel on your Grand List report but did you know that you could get this information from the Grand List software by using FoxPro expressions?

FoxPro expressions help you search for specific data. You can narrow down any report output by using the FoxPro expression input line. For example, you can run a Grand List report for all parcels that have a ‘V2’ category code. Below are the basics for creating your own FoxPro expressions.

Three components that all FoxPro expressions require:

1. Database Field name – The field in the Grand List that contains the information to be analyzed. For example, real value or owner1. You must spell the field name exactly as it is seen in the data dictionary included with this handout.

2. Operator - greater than, less than, equal to, not equal to, and contained within.

3. Value – Basis of the comparison. For example, homesteads that are valued at $50,000 or

parcels owned by the Smith family.

Rules you need to keep in mind when building FoxPro expressions:

1. Character fields require quotes or apostrophes around your search value. For instance, in P_NAME1 = ‘SMITH’, note the apostrophes.

2. Numeric fields do not require quotes or apostrophes. Simply type the value you are using as a basis. For example, in P_REAL = 150000 do not add commas, decimals, or dollar signs. If a field has decimals then include the decimals. Refer to your data dictionary for information about the fields that you are using.

3. If the value you are using for a basis is in uppercase, you must type the expression value in uppercase as seen in item one above with ‘SMITH’. If you are not exact, you may not get accurate data back.

4. Field names must be typed exactly as they are seen in the data dictionary

5. A space between field names, operators, and value is not required. However, spaces make the expression easier to read. Be consistent, this will help you the most when writing FoxPro expressions.

Operators

Operator Works on these field types Expressions are will look like

Less than - < Numeric field Fieldname < your value

Less than or equal to - your value

Greater than or equal to- >= Numeric field Fieldname >= your value

Equal to - = Numeric, Character or Date Fieldname = your value

Not equal to - Numeric, Character or Date Fieldname your value

Contained within = $ Character field ‘your value’ $ Fieldname

Special Commands

Character to date – CTOD Date field Fieldname Operator ctod(‘99/99/9999’)

Note: This command is used to search on date fields. For example, if you wanted all of the parcels that

you updated since October 2, 1999. The field name is P_LUPDATE.

FoxPro expression is: P_ LUPDATE > ctod(‘10/01/1999’)

You need to have the parentheses and apostrophes when you use this command because you are

changing the character 10/01/1999 into a date.

Sub String – Substr Character field substr(Fieldname,startpoint,number of characters) = ‘your value’

Note: Use this command to search for specific data within a field. The key to this command is the

starting point and number of characters value. Let’s say you want all the parcels with a 05701 zip

code. You know the first 0 in the zip code is in the first character of the field; Therefore, 1 is my

starting point. Now count the length of the zip code value you want to find (05701), the result is

the number of characters portion of this expression. This expression will find all the parcels that

have the numbers 05701 in the first five positions of the zip code field.

FoxPro expression: substr(P_ZIP,1,5) = ‘05701’

Conjunctions – connect two separate expressions with ‘AND’ / ‘OR’.

You use conjunctions the same way you do when speaking to others. Let’s say you want a list

of parcels that are owned by Doe John and have a real value of $60,000. The word ‘AND’

means that you are looking for a combination of criteria. The expression would be:

P_NAME1 = ‘DOE JOHN’ and P_REAL = 60000

If you want all of the parcels that are owned by Doe John or have a real value of $60,000. The

expression would be written the same except the ‘AND’ would be replaced with ‘OR’.

P_NAME1 = ‘DOE JOHN’ or P_REAL = 60000

Now that you know the three components and the basic rules to building an expression can you tell what the following FoxPro expression will do? The answers are at the bottom of this page.

P_REAL 90000

P_VETEXEMP > 0

A. (‘EDDY’ $ P_NAME1 AND P_EQPM > 50000) or P_REAL > 50000

B. P_CD2 = ‘R1’ or P_CD2 = ‘R2’

C. P_CD2 = ‘R1’ and P_CD2 = ‘R2’

-----------------------

Click on theses buttons

➢ New - create an expression.

➢ Edit - modify an existing expression.

➢ Delete - remove an existing expression.

1st Expression

2nd Expression

Expression

Number of Characters

Starting Point

Click on the drop down button to select an expression you created earlier.

FoxPro expression answers

A. Gives you all parcels that are not equal to $90,000. Probably most of your town will be on this report.

B. Shows you everyone that has a value greater than zero in the veteran exemption field.

C. Lists all parcels that contain the letters ‘EDDY’ in the Owner 1 field. (It will find EDDY JOHN as well as VALENTE EDDY) and have an equipment value of $50,000. It will also list any parcels with a real value greater than $50,000. Note the use of parentheses are for logical grouping.

D. Find all parcels that have a category code of R1 or R2.

E. Will not list anything because a parcel has to be either an R1 or R2. It can not have both values.

ZIP CODE S 05701

REAL VALUES > 75,000

LAST UPDATED

Give the expression a name that will describe the output. You can name it anything you would like.

Type your FoxPro expression here.

Click the Ok button to save your FoxPro expression for future use.

1st Expression

2nd Expression

Expression

Your Value

CREATING YOUR FOXPRO EXPRESSION

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download