When creating calculations using nested IF statements, it is important to recognize that no matter how complex they look, they can always be broken down into single IF statements.
The syntax for an IF statement in FileMaker Pro is:
If (test, result one, result two)
It might be easier to think of an IF statement as a yes/no question that determines the next action taken:
If (question, do this if the answer is yes, do this if the a
3. If EndDate occurs on a day of the week before StartDate, then subtract the 2 weekend days.
This formula provides an accurate calculation of the work days between any two work dates. It assumes that StartDate is earlier than (or the same as) EndDate. If you want Monday through Tuesday to return 2 work
Arial
MS Sans Serif
Times New Roman
Wednesday
Thursday
Friday
Saturday
January
February
March
April
August
September
October
November
December
1st Quarter
2nd Quarter
3rd Quarter
4th Quarter
5/5/999
Pro 3.0 - 4.1F!
Pro 5.0
CALCULATION
CATEGORY
CLIENT ID
CONTENT
DATE FIELD
DUPLICATE CHECK GLOBAL
EMAIL ADDRESS
EMAIL TO
FIRST NAME
L PAGES
CategoryB
ContentB
NotesB
CalculationB
SubjectB
TitleB
Search
TechInfoB
InformationB
Total PagesB
A Client IDB
MarkB
Duplicate Check Global
ome sample formulas. It should be noted that a number field or calculation with a result being a number, may be formatted to be blank if the value to be displayed is zero.
Number Calculation that results in a blank instead of a zero if any field in the formula is blank:
If (Hours Worked * Rate > 0,Hours Worked * Rate,TextToNum (""))
or (same formula, shorter version)
If (Hours Worked * Rate,Hours Worked * Rate,TextToNum (""))
-- In this example, if either Hours Worked or Rate is blank, the calculation results in a blank.
Date Calculation that results in a blank instead of 00/00/0000 or "?" displayed if any field in the formula is blank:
If (Shipdate >
.com>
G;Send Mail: Including the Recipient Name in an Email Message
starting
calculating
elapsed
ending
later
calculating
number
weekdays
between
dates
combining
fields
single
field
concatenati
finding
range
dates
using
scriptmaker
fourteen
scriptmaker
printing
total
numbers
script
serializing
records
category
using
relationsh
MS Sans Serif
Times New Roman
Courier
(LIST
LISTA
Calculation
Script
VDEFA(
(LIST
LISTA
Logical
VDEFA(
CategoryB
Subject
for any desired weekday for any desired number of weeks in the future.
As a convenience, sample formulas to return each weekday 4 weeks in the future are provided below.
To return the Monday 4 weeks in the future, use:
OrderDate + 28 + Middle ("1065432",
Mod (OrderDate - Date (1, 3, 1904), 7) + 1, 1)
To return the Tuesday 4 weeks in the fu
ture, use:
OrderDate + 28 + Middle ("2106543",
Mod (OrderDate - Date (1, 3, 1904), 7) + 1, 1)
To return the Wednesday 4 weeks in the future, use:
OrderDate + 28 + Middle ("3210654",
Mod (OrderDate - Date (1, 3, 1904), 7) + 1, 1)
To return the Thursday 4 weeks in the future, use:
OrderDate + 28 + Middle ("4321065",
Mod (OrderDate - Date (1, 3, 1904), 7) + 1, 1)
To return the Friday 4 weeks in the future, use:
OrderDate + 28 + Middle ("1065432",
DayOfWeek (OrderDate), 1)
Calculation
To use this calculation you must create 2 fields:
StartDate (Date)
Days (Number)
To find a date in the future, you simply add the number of days in the future to the existing date.
DaysFuture (Calculation, Date result) =
StartDate + Days
If StartDate was January 1, 1999 and the field Days contained a 7 the result of this calculation would be January 8, 1999.
DaysFuture (Calculation, Date result) =
StartDate + Days
tDate + Days
If StartDate was January 1, 1999 and the field Days contained a 7 the result of this calculation would be January 8, 1999.
DaysFuture (Calculation, Date result) =
StartDate + Days
* A text field, called Mark. When th
statusI
suffixJ
textN
self-join
relationship
examples
timeF
tipsQ
titleJ
totalP
usingI
weekdaysG
whatU
whenF
withA
workD
zeroB
logical
scripts
Pro 4.0, you can create a calculation (see below) and set up the Send Mail script step to use this field. Send Mail will placlac
the day of the week:
Work Days 0 1 2 3 4
Sunday 1 2 3 4 5
Monday 0 1 2 3 4
Tuesday 0 1 2 3 6
Wednesday 0 1 2 5 6
Thursday 0 1 4 5 6
Friday 0 3 4 5 6
To determine the day of the week for StartDate, we can use the Mod() function (with
DateG
Finding Future Dates
Calculation
You may wish to create a text calculation field that includes a numeric result, with a formatted number. However, a text calculation field cannot be formatted using the Format Number command (e.g.- you cannot display a fixed number of decimal places in a text field). To get around this problem, you can create a calculation field with a text result, and build the number formatting into the calculation. An example of how to do this follows.
This calculation assumes that you have a number fB
ield named:
Numberfield
Balance Due (calculation, text) =
"Your Current Balance is: $" & Int (Round (Numberfield,2)) & "." & Right (Round (Numberfield,2) * 100,2)
er formatting into the calculation. An example of how to do this follows.
This calculation assumes that you have a number f as
extractingJ
extracting
title
first
middle
suffix
fieldJ
findI
findingK
finding
range
dates
using
scriptmakerK
finding
duplicate
recordsL
finding
future
datesM
finishD
firstJ
formattingN
formatting
numbers
calculation
fieldN
fromD
functionsI
futureM
includingR
insteadB
lastJ
laterF
mailR
messageR
middleJ
nameJ
nestedA
numberD
numbersN
pageP
person'sE
printingP
printing
total
numbers
scriptP
rangeK
recipientR
recordsL
relationshipT
scriptI
scriptmakerK
scriptmaker
tipsQ
selfT
self-joinU
sendR
including
recipient
email
messageR
serializingS
serializing
records
category
using
scriptS
leftA
leftwordsJ
lessA
let'sA
levelA
likeA
lineU
listL
listingU
listviewU
logicD
longerF
lookA
lookedA
lookingQ
looksA
loopL
lowerA
machineQ
macintoshD
mailR
mainU
makeB
makesA
manyA
markL
matchL
match-fieldU
matchingQ
matchingrecordsU
mondayD
monthE
monthsE
moreA
moveD
multipleD
multiple
record
viewI
multiplesD
multiplyD
nameJ
namedD
namesJ
needF
neededA
needsH
nestedA
nestsA
nextA
noteF
notedB
numberA
numtotextE
obtainD
obtainedE
occursD
onesA
onlyA
optimallyA
orderL
ordinaryJ
originalJ
otherH
overA
Arial
MS Sans Serif
Times New Roman
Courier
Verdana
Helvetica
Palatino
Times
Geneva
Arial
MS Sans Serif
Times New Roman
Courier
Verdana
Helvetica
Palatino
Times
Geneva
Palatino
Times
Geneva
Times
Geneva
Geneva
Geneva
Extra: If you want to be able to enter a negative number of work days as well as a positive number of workdays then use the following formula:
DateGECalculating a Finish Date from a Start Date & the Number of Work Days
Calculation
ver the five day multiples. That is,
Mod (WorkDays,5)
The difficult step is logic step #3 - determining if the excess days takes us across a week
can also specify related fields within many script steps giving you access to related data.
When you use the Cut, Copy, Paste, Clear,Paste Result, Paste from Index, Paste from Last Record, or Paste Current Date/Time/User steps, Set Field, there is no need to use a Go to Field step first; all of these commands allow you to specify a field for the operation.
Remember that if you use the Relookup command, and you're using a calculation field as the matching value, you must specify on
e of the input fields for the calculation as the field parameter for the Relookup, not the calculation field (calculation fields cannot be used for the Relookup command).
When you use the Copy command without any parameters, it copies all values from all fields on the current layout. If you use the Paste command, all of the data will paste into one field. There is no way to force FileMaker to paste the data into each destination field, though you could paste into a word processor, save
Script
What it is:
A self-joining relationship is a relationship in which a file is related to itself: the main file and the related file are the same database. Any Text, Number, Date or Time field may be used as a match field. Other potential match fields include calculation fields with stored results and Global fields (on the "left" side of the relationship only). Using these field types, self-joining relationships may be defined between two different fields or from a single field to itself.
se. Any Text, Number, Date or Time field may be used as a match field. Other potential match fields include calculation fields with stored results and Global fields (on the "left" side of the relationship only). Using these field types, self-joining relationships may be defined between two different fields or from a single field to itself.
st.com>
e where yo
space.
a space.
Datee
gical
as Text, and use an Import command to bring in the record (the copied information is separated by tabs).
Use the Toggle Window command to zoom the window to full size for the current screen, if you don't know how large the monitor will be on any machine. This is a good command for a startup script.
Use the Save a Copy As command to periodically back up your file. The script step can save a Clone, a Compressed Copy or just a Copy of the current file.
ScriptMaker Tips
already calculated, it will copy a null value. This happens because the script progresses before the summary field is given time to calculate.
Whenever you use the Enter Find Mode command and choose to Restore Find Requests, use the Perform Find command immediately after it. Be sure to uncheck the Restore option on the Perform Find command, if any chang
gical
These calculations compare the value in an existing date field called "Birthdate" in conjunction with the value obtained using the Today function.
The following formula displays a person's age in
the text format of "yy Years, xx Months, zz Days":
Full Age
FullAge (calculation, text result) =
NumToText (Year (Today) - Year (Birthdate) - If (Today < Date (Month (Birthdate), Day (Birthdate), Year (Today)), 1, 0)) & " Years, " & NumToText (Mod (Month (Today) -
Month(Birthdate) + 12 - If B
(Day (Today) < Day (Birthdate), 1, 0), 12)) & " Months, " & NumToText (Day (Today) - Day (Birthdate) + If (Day (Today) >= Day (Birthdate), 0, If (Day (Today - Day (Today)) < Day (Birthdate), Day (Birthdate), Day (Today - Day (Today))))) & " Days"
Import New FAQs
\\ZED\DoubleDuty
Script
This script uses 2 layouts named:
Single Record View
Multiple Record View
You should create both of these layouts in your database before importing this script.
The following script results in one of three paths depending on how many records are found. The three possible find results are: no records, one record and many records. Here is the script:
Set Error Capture [On]
Enter Find Mode [Pause]
Perform Find [ ]
If ["Status(CurrentFoundCount) = 1"]
Go to Layout ["Single Record View"]
ElseB
If ["Status(CurrentFoundCount) = 0"]
Show Message ["No records were found"]
Go to Layout ["Multiple Record View"]
End If
End If
Import the script "Conditional Find Script using Status Functions", and modify it for use with your database.
1. It finds all records, and then sorts them by the ClientID field so that records with the same Client ID are grouped together.
2. Starting with the first record, the script copies the value from the ClientID field into the Duplicate Check Global field.
3. The script goes to the next record and compares the value in ClientID with the value in Duplicate Check Global.
* If the values match, the record is a duplicate, the script puts an X in the Mark field .
* If the values don't match,
. FileMaker Pro copies the Client ID value into Duplicate Check Global. It doesn't change the Mark field.
4. The script repeats step 3 until it reaches the last record in the file.
5. The script finds all records with an X in the Mark field, and then displays the found set of the duplicate records.
nd []
When you perform the Find Duplicates script:
Script
The Send Mail script step allows you to specify field values for the different fields in
your email messages. The "To:" field, for example, could take values from the Email
Address field.
If you wish to include the recipient name along with the email address in email messages sent from FileMaker Pro, you can create a calculation (see below) and set up the Send Mail script step to use this field. Send Mail will place the recipient name into the recipient area and the email address in u wish to include the recipient name along with the email address in email messages sent from FileMaker Pro, you can create a calculation (see below) and set up the Send Mail script step to use this field. Send Mail will place the recipient name into the recipient area and the email address in
he Enter Find Mode command and choose to Restore Find Requests, use the Perform Find command immediately after it. Be sure to uncheck the Restore option on the Perform Find command, if any chan
Calculation
(calculation, text result) =
NumToText (Year (Today) - Year (Birthdate) - If (Today < Date (Month (Birthdate), Day (Birthdate), Year (Today)), 1, 0)) & " Years, " & NumToText (Mod (Month (Today) - Month
(Birthdate) + 12 - If (Day (Today) < Day (Birthdate), 1, 0), 12)) & " Months, " & NumToText (Day (Today) - Day (Birthdate) + If (Day (Today) >= Day (Birthdate), 0, If (Day (Today -
Day (Today)) < Day (Birthdate), Day (Birthdate), Day (Today - Day (Today))))) & " Days"
DateG
Calculating a Person's Age
Calculation
k of your medicine
cabinet.
A special note about the Today function.
The Today function calculates every time you open your database. If your database stays open for more than a day, then the Today function may not actually give you today's date but
rather the date the database was last opened.
"Match".
3. Double click on the Match Field.
4. Select the FirstValue field and also check the Create Field Label box.
5. Move the new FirstValue label into the Header where the Match field label was.
6. Add the Qty field to the layout, beside the DupCheck field, then move the field label.
7. Return to Browse mode.
8. Choose Find All from the Select menu.
9. Sort the by the Match field.
Only the first record of each series of values in the Match field will show the Match field value.
tch DupCheck Qty
A123 1 1
0 2
0 3
0 10
B456 1 9
0 8
0 7
C789 1 6
0 5
0 4
D159 1 5
Example Five:
Show summary data similar to that generated via a sub-summary report.
This method assumes that your database has four fields: DateStart (Date), DateEnd (Date), TimeStart (Time) and TimeEnd (Time). You need to enter the data into all four fields. Use this method if your elapsed times might be longer than 24 hours.
To display the time as hours:minutes:seconds, set the calculation to result in a Time result. To display the time as number of seconds, set the calculation to result in a Number result.
ulating a Person's Age
Calculation
o indicate where you should type a space.)
Set up the Send Mail script step to use a
the address area of your email message.
Assuming you have first name, last name, and email address in separate fields, create a text calculation field to combine the information. One formula that works is:
Email To (calculation, text) =
"(" & First Name & "^" & Last Name & ")" &" " & Email Address
Note: For clarity, a caret (^) has been used to indicate where you should type a space.
Set up the Send Mail script step to use a field value for the
field, and specify the
Email To cC
alculation field.
A field yielding any of the following formats will work:
(Sally Jones) sally_jones@host.com
sally_jones@host.com (Sally Jones)
Sally Jones <sally_jones@host.com>
"Sally Jones" <sally_jones@host.com>
e where yo
space.
Mail script step to use a fi
ScriptsG.Conditional Find Script using Status Functions
Calculation
The following calculation field definitions can extract any ordinary combination of title, first name or initial, middle name or initial, last name and suffix (e.g., Jr.) from a single name field. The formulas can correctly separate out names without a title, middle name or initial, and/ or suffix.
These calculations assume that you have the following field defined:
FullName (text) [this is the original field containing the entire name]
Title (Calculation, text result)=
If(LeftWords(Ful
Balance Due (calculation, text) =
"Your Current Balance is: $" & Int (Round (Numberfield,2)) & "." & Right (Round (Numberfield,2) * 100,2)
Favorites
NumberG/Formatting Numbers in a Text Calculation Field
Script
To display page total information on each page of a printed document using a script, follow these steps:
1. In Define Fields, create a global field titled Total Pages with type set to number.
2. In Layout mode place the Total Pages in the header or footer. If you wish to display
page 1 of 10,
create a text block with the text
page ## of
and place it to the left of Total Pages on the layout. ( The ## will display as the current page number when in Preview or printed.)
3. Create a scria scri
singleJ
startD
statementsA
relationsh
singleJ
startD
statementsA
elds in the formula have data (are not blank). To make a calculation field result in a blank instead of a zero, use the TextToNum, TextToDate or TextToTime function in a formula. Use TextToNum in a calculation field with a number result. Use TextToDate in a calculation field with a date result. Use TextToTime in a calculation field with a time result. Put this function inside an IF statement. Here are sB
ome sample formulas. It should be noted that a number field or calculation with a result being a number, may be formatted to be blank if the value to be displayed is zero.
descriptive of it
s function. In the case of the above example, name the relationship,
Location Location
7. In the same dialo
= If(DupCheck = 1, Match, "")
This calculation shows a result only if the record is the first instance of each Match field value.
MatchingRecords (Calculation, Number result)= Count(MyFileMatch::TextField)
This calculation shows the total number of records having each value in the Match field.
These are just a few of the possibilities; any calculation that would work for an ordinary relationship should work in the self-join operation.
4. Create the following sample records
Record Match Qty TextField
1 A123 1 ZZZ
2 A123 2 YYY
3 A123 3 XXX
4 B456
CategoryB
ContentB
NotesB
CalculationB
SubjectB
TitleB
Search
TechInfoB
InformationB
Total PagesB
A Client IDB
MarkB
Duplicate Check GlobalB
Date Field
Sum for A123 has changed to 10 and the new record is at the end of the database.
Example Two:
Show in a portal all records that are related to the current record .
1. Create a new blank layout called Show Instances. Place the Match field on it.
2. Add a portal using the MyFileMatch relationship, showing 5 portal rows.
3. Place the following fields in the first line of the portal:
::Match
::Qty
::TextField
4. Return to Browse mode.
The portal should display this information for the first
record:
Match Qty TextField
A123 1 ZZZ
A123 2 YYY
A123 3 XXX
A123 4 AAA
The portal shows all of the records in the database that have this same match field value. Note that the current record also shows in the portal. Flip to another record and observe the related records in the portal.
Example Three:
Identi
astructure:
1. Create a file called MyFile.fp3 with the following fields:
Match (Text)
SerialNumber (Number, Auto enter serial number)
Qty (Number)
TextField (Text)
2. Still in MyFile.fp3, create a self-join relationship:
Create a new relationship and choose MyFile.fp3 (i.e., the same file you are working in) as the related file. Name the relationship MyFileMatch, and select Match as the match field for both sides of the relationship. Allow creation of related records, but not deletion.
(If deletion of related records is allowed in a self-join relationship, deleting one record will delete all records in the database that share the same match field value).
3. Define the following calculation fields referencing related fields from the self-join relationship:
DupCheck (Calculation, Number result) = If(MyFileMatch::SerialNumber = SerialNumber, 1, 0)
This calculation flags the first record in the database that has each Match field value.
FirstValue (Calculation, Text result)
Note: This article assumes you know how to create relationships and define calculations that include related fields. For more instruction on these, see the User
s Guide and the onscreen help.
How to use it:
Some of the things a self-join relationship can be used for:
--Getting statistical information about a particular item; for example a count or total of all records in the database having each value in a field. (Example One)
--Listing in a portal all records that have the same Match fi
eld value as the current record. (Example Two)
--Identifying the first record having each match field value; identifying duplicate records. (Example Three)
--Displaying the Match field value only once for each group of records having that value. (Example Four)
--Showing sub-summary data without a summary field or sub-summary layout. (Example Five)
There are, of course, many other possibilities.
Setting up the example file:
To illustrate these examples, we will first need to build the infr
parametersH
partA
particularU
pasteK
pathsI
pauseI
performA
performedQ
performingL
performsQ
periodicallyQ
person'sE
phoneL
phraseA
phrasedA
phrasingA
placeP
placedQ
placesL
plugA
pluggingA
pointA
portalU
positionD
possibilitiesU
possibleA
potentialA
precedingA
previewP
previousA
printP
printedP
probablyA
problemN
processA
processedA
processesA
processorQ
progressesQ
putsL
puttingD
qtyavgU
qtymidpointU
qtysumU
quantitiesU
questionA
questionsA
rangeK
reachesL
readA
real-worldA
recipientR
relationship:
DupCheck (Calculation, Number result) = If(MyFileMatch::SerialNumber = SerialNumber, 1, 0)
This calculation flags the first record in the database that has each Match field value.
FirstValue (Calculation, Text result)
anotherA
answerA
answersA
appearL
applyA
applyingA
appreciateA
appropriateA
areaR
aroundN
articleU
ascendingL
askedA
assemblingA
assumeG
assumesD
assumingR
backupL
balanceN
basedA
becauseA
becomesA
beforeA
beginningD
beingB
belowA
bestA
betweenA
birthdateE
birthdayL
blankB
blockP
bothA
bringQ
bringsD
brokenA
browseL
buildN
buildingA
building
calculations
nested
statementsA
calculateQ
calculatedD
calculatesA
calculationA
calculationsA
calledE
cannotN
captureI
caseA
casesQ
categoriesA
cautionL
changeD
. Duplicate the layout from Example Three
2. Add the QtySum field to the layout.
3. Perform a Find for a 1 in the DupCheck field; this will net a found set having only a single instance of each Match field value.
4. Note that the QtySum field still gives the total of all the records with each value in the Match field.
This is the same result as a report that uses a summary field to total the Qty field and a Sub-summary when sorted by Match part in the layout. The advantages of this methQ
od are: it shows the results in Browse mode, and there is no need to perform a sort so it is faster to generate than a sub-summary report.
elf-joinin
TipGDThe Self-Join Relationship: What It Is And Examples Of How To Use It
Calculation
ulation
ssibilities.
Setting up the example file:
To illustrate these examples, we will first need to build the infr
Calculations With Nested IF Statements
When creating calculations using nested IF statements, it is important to recognize that no matter how complex they look, they can always be broken down into single IF statements.
The syntax for an IF statement in FileMaker Pro is:
If (test, result one, result two)
It might be easier to think of an IF statement as a yes/no question that determines the next action taken:
If (question, do this if the answer
is yes, do this if the answer is no)
The trick is to phrase the test so there is only a true or false (yes or no) answer possible, for example:
If (2+3=5, "true", "false")
FileMaker first calculates "2+3=5", then processes the appropriate result, which is to display "true." Another example of test phrasing would be to guess a number from 1 to 1,000,000. If the test were phrased "number = 500,000" and the answer was no, up to 999,999 more IF statements may be required to guess the
TitleG
Show FAQs for:R
FAQS.Frequently Requested Calculations and Scripts
Search
Information
Overview
ames.
FileMaker Pro uses the ampersand (&) to concatenate fields and/or text. Notice in the examples below that text (as opposed to numbers, field names, dates or functions, etc.) are surrounded by quofield names, dates or functions, etc.) are surrounded by quo
Go to Layout[The layout you want to print]
Enter Preview Mode [ ]
Go to Record / Request / Page [Last]
Set Field ["Total Pages", "Status (CurrentPage Number)"]
If you wish to print, add these print steps:
Print [No Dialog]
Set Field ["Total Pages", ""]
If you wish to Browse, add this print step:
Enter Browse Mode []
About the Calculations and Scripts.fp5 database
This database is provided to help you get results quickly with FileMaker Pro.
Description
This database contains calculation formulas, scripts, and tips.
How to use this template
Clicking on the Overview button gives you access to instructions, formulas, or script names; and, in some cases, notes.
The Instructions tab explains what you need to do to create a calculation or a script.
The Calculation or Script tab will show:
If the record is
scribing a calculation, formula(s) will be displayed without instruction so that you can bypass the instructions and just copy the formula for use in your database.
If the record is describing a script, the script steps will be displayed without instruction so that you can examine them.
There are 6 scripts included with this database that you can import and modify for use with your database. Follow the directions for using these scripts (create any necessary fields, etc.), open your dat
each record
This field could be a customer identification number, a phone number, etc. Any field that uniquely identifies a record is acceptable. This example will use a field titled Client ID.
TIP: If your database doesn't have a unique identification field, define a calculation field to create one from existing fields. For example, combine first name, last name, and birthday create a unique identification for each client.
Example step 2: Add two fields
* A text field, called Mark. Whe
n the script finds a duplicate record, it places an x in this field to mark the record.
* A global field, called Duplicate Check Global, to store the unique identification while comparing records. Duplicate Check Global should be the same data type as the Client ID field. For this example it is a number field.
Example step 3: Display the Mark field
Create a layout that displays the Mark field, or add the field to an existing layout. You'll use this layout to store Find setting in the fol
lowing step.
Example step 4: Create settings
Store Sort and Find settings for the script.
Sort: In Browse mode, choose Sort from the Mode menu. If fields appear in the Sort Order list, click Clear All. Click Client ID, choose Ascending order, click Move, and then click Done.
Find: Choose a layout that displays the Mark field. In Find mode, type and X into the Mark field, and then switch to Browse mode. (You don't have to perform the find request.)
Example step 5: Define the script
criptMaker, define the Find Duplicates script.
Find All
Sort [Restore, No Dialog]
Go to Record/Request/Page [First]
Replace [No dialog, "Mark", """"]
Set Field["Duplicate Check Global","ClientID"]
Go to Record/Request/Page [Exit after last, Next]
If ["Duplicate Check Global = ClientID"]
Set Field ["Mark", ""X""]
Set Field ["Duplicate Check Global","ClientID"]
End If
End Loop
Enter Find Mode []
Set Field["Mark", ""X""]
Perform Find []
When you perform the Find Duplicates script:
a summary field in a script may not work reliably in all cases. If a script specifies a Copy step for a summary field that has not already calculated, it will copy a null value. This happens because the script progresses before the summary field is given time to calculate.
Whenever you use the Enter Find Mode command and choose to Restore Find Requests, use the Perform Find command immediately after it. Be sure to uncheck the Restore option on the Perform Find command, if any chang
es to the find requests in the Enter Find Mode step will occur.
Most script steps execute in the current file (i.e. the file where the script is defined). For example, to copy and paste between files, you will have to write two scripts: a script in the file where you want to copy and a script in the file where you wish to paste. In the script with the Copy command, include a Perform Script command that performs an external script, which uses the Paste command in the other database. You
fy the first record having each value in the Match field; identify duplicate records.
1. Create a new Columnar Report layout and place the Match, DupCheck, and Text fields on it.
2. Enter Browse mode. The report should look like this:
Match DupCheck Text
A123 1 ZZZ
A123 0 YYY
A123 0 XXX
B456 1 WWW
B456 0 TTT
B456 0 SSS
C789 1 R
C789 0 QQQ
C789 0 PPP
D159 1 NNN
A123 0 AAA
The first instance of each value in the Match field will have a one (1) in the DupCheck field. Each duplicate match-field value will have a zero (0) in the DupCheck Field.
Example Four:
Show the field value only once for a group of records with the same value in the Match field.
1. Duplicate the layout created in Example Three.
2. Delete the field label
Search Subject
ion, text result)=
Case(IsEmpty(Title) = 1 and MiddleWords(FullName, 2, 1) <> Last Name, MiddleWords(FullName, 2, 1),
IsEmpty(Title) = 0 and MiddleWords(FullName, 3, 1) <> Last Name, MiddleWords(FullName, 3, 1), "")
Your computer will probably require some time to perform these calculations.
[NOTE: for clarity, field names are listed first, followed by their field type in parentheses with formulas and options after the = sign]
It finds all records, and then sorts them by the ClientID field so that records with the same Client ID are grouped together.
2. Starting with the first record, the script copies the value from the ClientID field into the Duplicate Check Global field.
3. The script goes to the next record and compares the value in ClientID with the value in Duplicate Check Global.
* If the values match, the record is a duplicate, the script puts an X in the Mark field .
* If the values don't match, Fil
Script
Finding a Range of Dates Using ScriptMaker
To find a range of dates in a script:
1- Use the Paste Result step (vs. the Set Field or Paste steps)
2- Use the DatetoText function inside the Paste Result step to convert each date to text format
Your script would look like this:
Enter Find Mode []
Paste Result [
Date Field
DateToText(Today-14) &
& DateToText(Today)
Perform Find []
s another example using global date fields:
Enter Find Mode []
Paste Result [
Date Field
DaBKteToText(GlobalField1) &
& DateToText(GlobalField2)
Perform Find []
would look like this:
Enter Find Mode []
Paste Result [
Date Field
DateToText(Today-14) &
& DateToText(Today)
Perform Find []
s another example using global date fields:
Enter Find Mode []
Paste Result [
Date Field
DaBKteToText(GlobalField1) &
& DateToText(GlobalField2)
Perform Find []
Deleting the duplicate records
To delete the duplicate records you can manually delete the found set using the Delete All command from the Mode menu after running the Find Duplicates script. This way you can control the deleting of records before making any mistakes. If your script works properly to find the duplicate records you can add the Delete All command as the last step to your script. This will automate the entire process for you.
CAUTION: Deleting records is NOT undoable. Use ofB
this script to delete duplicate records should be run on a copy of your database file. Always create a backup of your database before performing any scripts that include steps for deletion of records.
Import the script "Finding Duplicate Records", and modify it for use with your database.E
LogicalG
Finding Duplicate Records
ind setting in the fol
Calculation
mula provides an accurate calculation of the work days between any two work dates. It assumes that StartDate is earlier than (or the same as) EndDate. If you want Monday through Tuesday to return 2 work days (instead of 1), add one (+1) to the end of the calculation formula.
DateG8Calculating Number of Weekdays (Work Days) Between Dates
Calculation
Graphing C
xample Three.
2. Delete the field label
TimeG;Calculating Elapsed Time When Ending Time is on a Later Day
from another, FileMaker calculates the difference as the number of seconds. Therefore, it's easiest to calculate time based on seconds, rather than as hours, minutes and seconds. The 86400 equals the number of seconds in a day. When you set the calculation field to result in time, FileMaker converts the number (of seconds) to time (hours:minutes:seconds).
To display the time as hours:minutes:seconds, set the calculation to result in a Time result. Optionally, use the Time Format command to change the display format. To display the time as number of seconds, set the calculation to result in a Number result.
n to your ListView layout. Note that the Qty
eting the duplicate records
To delete the duplicate records you can manually delete the found set using the Delete All command from the Mode menu after running the Find Duplicates script. This way you can control the deleting of records before making any mistakes. If your script works properly to find the duplicate records you can add the Delete All command as the last step to your script. This will automate the entire process for you.
CAUTION: Deleting records is NOT undoable. Use ofB
this script to delete duplicate records should be run on a copy of your database file. Always create a backup of your database before performing any scripts that include steps for deletion of records.
nswer is no)
The trick is to phrase the test so there is only a true or false (yes or no) answer possible, for example:
If (2+3=5, "true", "false")
FileMaker first calculates "2+3=5", then processes the appropriate result, which is to display "true." Another example of test phrasing would be to guess a number from 1 to 1,000,000. If the test were phrased "number = 500,000" and the answer was no, up to 999,999 more IF statements may be required to guess the correct number. However, i
f the test were phrased "number > 500,000", the answer may still be no, but no more than 20 tests would be required to find the correct number. The point is, optimally phrasing the test part of an IF statement makes a big difference in how many subsequent nests are needed.
The simplest way to describe a nested IF statement is to think of an IF statement inside of an IF statement, or to use the analogy above, a series of yes/no questions designed to lead to a desired result.
In a nested I
First NameB
A Last NameB
Email AddressB
Email ToB
A* "(" &
& " " &
& ")" &" " &
ds(FullName, 1) = "Jr" or RightWords(FullName, 1) = "II" or RightWords(FullName, 1) = "III" or RightWords(FullName, 1) = "IV" or RightWords(FullName, 1) = "
or RightWords(FullName, 1) = "Esq"or RightWords(FullName, 1) = "MD" or RightWords(FullName, 1) = "Sr" or RightWords(FullName, 1) = "PhD", RightWords(FullName, 1), "")
IsEmpty(Title) = 0 and MiddleWords(FullName, 3, 1) <> lastnam
threeD
throughA
thursdayD
timeA
timeendF
timesF
timestartF
titleJ
titledL
display
total
information
printed
range
dates
scriptK
calculation
create
fieldsM
todayA
today-14K
togetherL
toggleQ
totalP
towardA
trickA
trueA
tuesdayD
typeL
typesQ
uncheckQ
understandL
understandingA
undoableL
uniqueL
uniquelyL
unlikeW
therH
whichA
willB
withA
withoutJ
workD
workdaysD
workdays/5D
workingD
wouldA
yearE
yearsE
yes/noA
obtain
calculation
results
whether
yourA
zeroB
paste
result
field
paste
stepsK
1000000A
1002N
12345012340123601256D
1904D
1999M
500000A
86400F
99999Q
999999A
calculation
field
always
result
number
a123U
aboutU
aboveA
acceptableL
accessQ
accidentallyD
accomplishedD
foreA
beingB
belowA
bestA
betweenA
blankB
bothA
bringsD
brokenA
buildingA
building
calculations
nested
statementsA
calculatedD
calculatesA
calculationA
calculationsA
caseA
categoriesA
changeD
chartA
functionJ
logical
number
scripts
textJ
functionJ
betweenG
blankB
buildingA
building
calculations
nested
statementsA
calculatingB
calculating
blank
instead
calculating
finish
start
number
calculating
person's
calculating
elapsed
ending
later
Mep #2 above)
Mod (WorkDays, 5)
Now to obtain the correct value, add one to all of this to grab the appropriate value. For example, if StartDate is a Sunday (returns zero from first Mod() function), and excess days is zero, it leaves us at the beginning of the string. Therefore, add one to get us to the first position.
1 RR
recognizeA
recordI
record/request/pageL
emberA
repeatH
replacedA
replacesD
requestL
requiredA
requiresH
restoreL
resultA
result
result
resultsA
returnD
returnedD
returnsD
rightwordsJ
sameG
sampleB
satisfactoryH
saturdayD
saysA
scoreH
scriptI
scriptmakerK
scriptsL
secondsF
sectionsL
separateJ
seriesA
settingL
settingsL
sevenD
shouldB
showI
simplestA
sinceA
singleA
single
record
viewI
situationA
someB
somehowD
sortL
startdateD
startingA
statementA
statementsA
statusI
stepD
stepsK
stillA
storeL
stringD
subsequentA
substituteA
subtractD
suffixJ
sundayD
switchL
syntaxA
systemD
tableD
takeG
takenA
takesD
zeroB
accordinglyA
achieveA
achievedA
acrossD
actionA
actualD
addedD
addressR
advantagesU
afterA
agingA
alertQ
allowQ
allowedU
allowsR
alongR
alreadyA
alsoA
alternateW
alternativeW
alwaysA
analogyA
umesD
backA
basedA
becauseA
becomesA
beforeA
beginningD
beingB
belowA
bestA
betweenA
birthdateE
blankB
bothA
bringsD
brokenA
buildingA
building
calculations
nested
statementsA
calculatedD
calculatesA
calculationA
calculationsA
calledE
captureI
caseA
categoriesA
changeD
chartA
checkH
Zpt that has these steps:
Go to Layout[The layout you want to print]
Enter Preview Mode [ ]
Go to Record / Request / Page [Last]
Set Field ["Total Pages", "Status (CurrentPageNumber)"]
If you wish to print, add these print steps:
Print [No Dialog]
Set Field ["Total Pages", ""]
If you wish to Browse, add this print step:
Enter Browse Mode []
Import the script "Printing Total Page Numbers with a Script", and modify it for use with your database.
DragThing
ScriptsG)Printing Total Page Numbers with a Script
Script
e, add this print step:
Enter Browse Mode []
Import the script "Printing Total Page Numbers with a Script", and modify it for use with your database.
Scriptspts
eld emp
You can obtain the same calculation results whether you use the If statement or the Case statement, but as you can see from the following example the Case statement requires fewer parameters for the same equation.
Either calculation displays Excellent when the score is abo
9ve 90, Very Good when the score is above 80 Satisfactory when the score is above 50, and Needs Improvement for any other score, but use of the If statement requires you to repeat IF( for each test you want to check and it also requires that a ) for each test in the equation be entered at the end of the equation.
TipG(Comparison of the IF and Case Statements
en the answer to the preceding IF statement is false). The preceding formula now becomes:
If (Today - Invoice Date <= 30, "30 days or less",If (Today - Invoice Date<= 60, "31 - 60 days", result two))
Now the calculation says: IF the difference between Today and the Invoice Date is less than or equal to 30, perform result one, which is to display "30 days or less," or else perform result two...which is another IF statement. If the difference between Today and the Invoice Date is less
than or equal to 60, then perform result one, which is to display "31 - 60 days," or else perform result two.
By now this is probably starting to feel more comfortable. To find out if the difference between Today and the Invoice Date is less than or equal to 90 and
if so
to display "61 - 90 days," we would add to the formula accordingly:
If (Today - Invoice Date <= 30, "30 days or less",If (Today - Invoice Date <= 60, "31 - 60 days",If (Today - Invoice Date <= 90, "61 - 90 days", r
Your computer will probably require some time to perform these calculations.
[NOTE: for clarity, field names are listed first, followed by their field type in parentheses with formulas and options after the = sign]
feelA
fewerH
fieldA
fieldsA
fileL
filemakerA
filesQ
finalA
findA
findingK
finding
range
dates
using
scriptmakerK
findsL
firstA
firstvalueU
fiveD
fixedN
flagsU
flipU
followP
formulasB
foundI
fourA
fridayD
fromA
fullE
fullageE
fullnameJ
functionB
futureM
givenD
given
starting
number
working
endingD
globalK
globalfield1K
globalfield2K
goesL
goodH
greatA
greaterA
groupA
groupedL
groupingD
guessA
happensA
haveA
helpD
hereB
here'sD
here's
logicD
higherA
hoursF
howeverA
identificationL
identifiesL
identifyL
wwwwww
wwwwww
Instructionsf
Instructionsp
Notesq
FAQw.Frequently Requested Calculations and Scripts
wwwwww
wwwwww
Search
Information
Overview
>, <<
ABAF>>
Overview - NotesB
SearchB
SearchB
9 WWW
5 B456 8 TTT
6 B456 7 SSS
7 C789 6 RRR
8 C789 5 QQQ
9 C789 4 PPP
10 D159 5 NNN
Example One:
Get statistical data for each unique value in the Match field (for example, the total, average, and midpoint quantities of all records with each Match field value).
1. Create an Extended Columnar Report layout named ListView and place on
it the following fields: Match, QtySum, QtyAvg, QtyMidPoint.
2. Enter Browse mode; the report should start like this:
Match QtySum QtyAvg ...etc.
A123 6 etc.
A123 6
A123 6
B456 24
B456 24
B456 24
C789 15
C789 15
C789 15
D159 5
3. Return to Layout #1 and add a new record with Match=A123, Qty=4, TextField=AAA.
4. Return to your ListView layout. Note that the Qty
apply the above techniques toward a real-world situation by building a nested IF calculation to age invoices into the four categories below.
30 days or less
31 - 60 days
61 - 90 days
Over 90 days
If we looked at the invoice date the first question asked would be: "Is the age of this invoice 30 days or less?" In FileMaker Pro terms, this would be:
If (Today - Invoice Date <= 30, "30 days or less", result two)
In English, this calculation says: "If the difference
between Today and the Invoice Date is less than or equal to 30, then perform result one by displaying '30 days or less,' but perform result two when this is not true."
But what if Today - Invoice Date is not less than or equal to 30 days? Since we already know the age of the invoice is greater than 30 days, we could next ask if the difference between Today and the Invoice Date is less than or equal to 60. We can substitute this for the first result two (remember result two is processed wh
INFORMATION
unnecessary@
wish@
calculating
elapsed
ending
extracting@
single@
status@
INFORMATION
(LIST
LISTA
Calculation
Script
VDEFA(
(LIST
VDEFA(
CategoryB
Subject
FMRLA
SORTA
InformationB
A.Conditional Find Script using Status Functions
\\ZED\DoubleDuty
ListB
Overview - InstructionsB
techniquesA
termsA
testA
testsA
textE
textfieldU
texttodateB
texttonumB
texttotimeB
thanA
thatA
that'sA
following
calculation
field
definitions
extract
following
calculation
determine
number
following
sections
explain
database
script
allows
specify
field
valuesR
themD
thenA
thereA
there'sA
thereforeD
theseE
these
calculations
compare
value
existing
theyA
thingsU
thinkA
thisA
method
assumes
database
fields
datesF
method
assumes
database
three
fields
dateW
script
layouts
namedI
thoseD
thoughQ
ew and place on
Instructions
wwwwww
Notesy
Notesz
FAQ{.Frequently Requested Calculations and Scripts
wwwwww
wwwwww
wwwwww
wwwwww
wwwwww
wwwwww
Search
Information
Overview
Overview - CalculationB
Search
Information
Overview
Category
Subject
Title
p.Frequently Requested Calculations and Scriptsq
By:r#Click arrow to see more information
Sort by Subject
Sort by Title
Search by Category
\\ZED\DoubleDuty
FAQ{.Frequently Requested Calculations and Scripts
wwwwww
wwwwww
wwwwww
Search
Information
Overview
Instructions
wwwwww
Noteslationy
Noteslationy
Notes
wwwwww
Notesx
Notesy
Calculation
wwwwww
Notesx
Notesy
Calculationntly Requested Calculations and Scripts
Information
wwwwww
wwwwww
wwwwww
Overview
F calculation, the test part is evaluated first. Based on the result of the test (usually a false result), the next IF statement or test is evaluated. The desired result is achieved through the process of elimination from previous IF statements in the calculation.
Let's say that result two (the result you get when the test is false) is replaced by another IF statement to be processed. In this case, result two = If (test, result one, result two). Below is what the expanded IF statement loo
ks like:
If(test, result one, If (test, result
one, result
two))
In English, this would read: "IF the first test is true, process result one, or else process result two, which happens to be another IF statement."
For a different kind of nested IF, say that both results one and two are also IF statements. When we plug this back into our calculation, we get:
If (test, If (test, result
one, result
two), If (test, result one, result two))
Applying the concept to an example
Let's
elaps
b-14) & "..." &
A0Including the Recipient Name in an Email Message
\\ZED\DoubleDuty
e, 1) = "
Information about this databaseW
FAQX.Frequently Requested Calculations and Scripts
Search
Information
Overview
wwwwww
Single Record ViewB
Multiple Record ViewB
calculation, one IF statement at a time. A great way to design a nested IF calculation is to try to chart it. All that's left after that is assembling the calculation by plugging the lower level calculations into the higher ones.
TipG/Building Calculations with Nested IF Statements
ments may be required to guess the correct number. However, i
f the test were phrased "number > 500,000", the answer may still be no, but no more than 20 tests would be required to find the correct number. The point is, optimally phrasing the test part of an IF statement makes a big difference in how many subsequent nests are needed.
The simplest way to describe a nested IF statement is to think of an IF statement inside of an IF statement, or to use the analogy above, a series of yes/no questions designed to lead to a desired result.
In a nested I
pageP
person'sE
printingP
printing
total
numbers
scriptP
rangeK
recipientR
recordsL
relationshipU
sameW
scriptI
scriptmakerK
scriptmaker
tipsQ
self-joinU
sendR
including
recipient
email
messageR
serializing
records
category
using
script
scriptS
nd an expiration date we can figure out exactly how many years, months and days until you should finally throw
V out that big bottle of aspirin in the back of your medicine
cabinet.
A special note about the Today function.
The Today function calculates every time you open your database. If your database stays open for more than a day, then the Today function may not actually give you today's date but
rather the date the database was last opened.
e are, of course, many other possibilities.
Setting up the example file:
To illustrate these examples, we will first need to build the infr
Calculation
The following calculation will determine the number of work days between StartDate and EndDate.
These examples assume that you have the following fields:
1. Take the number of weeks between the two dates and multiply the result by five working days per week.
2. Add the remaining daB
ys which do not make up a full week.
3. If EndDate occurs on a day of the week before StartDate, then subtract the 2 weekend days.
TimeG;Calculating Elapsed Time When Ending Time is on a Later Day
FileMaker Pro has several powerful date functions which make it possible to determine the number of years, months and days that have elapsed between two dates. This is useful in many ways. Given a birth date and today's date, we can determine a person's age. If we know the hire date and the termination date we can calculate the employee's length of service. Based on today's date and an expiration date we can figure out exactly how many years, months and days until you should finally throw
U out that big bottle of aspirin in the back of your medicine
cabinet.
A special note about the Today function.
The Today function calculates every time you open your database. If your database stays open for more than a day, then the Today function may not actually give you today's date but
rather the date the database was last opened.
))) & " Days"
between
dates
finding
future
dates
ending
later
calculating
elapsed
ending
calculating
number
weekdays
between
dates
comparison
statements
conditional
script
using
status
functions
extracting
title
first
middle
suffix
finding
range
dates
using
scriptmaker
finding
duplicate
records
finding
future
dates
formatting
numbers
calculation
field
identifying
duplicate
records
printing
total
numbers
script
scriptmaker
including
recipient
email
message
self-join
relationship
examples
This formula provides an accurate calculation of the work days between any two work dates. It assumes that StartDate is earlier than (or the same as) EndDate. If you want Monday through Tuesday to return 2 work days (instead of 1), add one (+1) to the end of the calculation formula.
DateG8Calculating Number of Weekdays (Work Days) Between Dates
Note Pad
ame as) EndDate. If you want Monday through Tuesday to return 2 work
Monday. Using the chart, for a StartDate of Wednesday
calculating
elapsed
ending
calculating
number
weekdays
between
datesG
calculationN
calculationsA
caseH
comparisonH
comparison
statementsH
conditionalI
conditional
script
using
status
functionsI
dateD
datesG
daysD
duplicateL
elapsedF
emailR
endingF
examplesU
amplesU
0 1 4 5 6
Friday 0
3 4 5 6
To determine the day of the week for StartDate, we can use the Mod() function (with 7) and subtract a known Sunday date from StartDate (The first known Sunday date in the Macintosh system is January 3, 1904). This results in a value from zero (Sunday) through six (Saturday). If the result is Wednesday (value returned is 3), somehow, we want to drop down to the Wednesday row. Then, determine the number of excess work days above a multiple of five and move ac
tationery
eEnd (Time). You need to enter the data into all four fields. Use this method if your elapsed times might be longer than 24 hours.
To display the time as hours:minutes:seconds, set the calculation to result in a Time result. To display the time as number of seconds, set the calculation to result in a Number result.
te: When you set the calculation to result as Time and click OK, you will see an alert message. Ignore the alert and click OK.
not actually give you today's date but
rather the date the database was last opened.
Note: FileMaker calculates time in seconds. When you subtract one time from another, FileMaker calculates the difference as the number of seconds. Therefore, it's easiest to calculate time based on seconds, rather than as hours, minutes and seconds. The 86400 equals the number of seconds in a day. When you set the calculation field to result in time, FileMaker converts the number (of seconds) to time (hours:minutes:seconds).
To display the time as hours:minutes:seconds, set the calculation to result in a Time result. Optionally, use the Time Format command to change the display format. To display the time as number of seconds, set the calculation to result in a Number result.
Tu W Th F
Tuesday T
Orderdate, Orderdate + 14, TextToDate("") )
-- In this example, if either Shipdate or Orderdate are blank, the calculation results in a blank.
Time Calculation that results in a blank instead of 00:00:00 if any field in the formula is blank:
If (TimeOut > TimeIn, TimeOut, TextToTime("") )
-- In this example, if either TimeOut or TimeIn are blank, the calculation results in a blank.
TipG*Calculating a Blank instead of a Zero or ?
Calculation
her Hours Worked or Rate is blank, the calculation results in a blank.
Date Calculation that results in a blank instead of 00/00/0000 or "?" displayed if any field in the formula is blank:
If (Shipdate >
f either TimeOut or TimeIn are blank, the calculation results in a blank.
TipG*Calculating a Blank instead of a Zero or ?tle) = 0 and MiddleWords(FullName, 3, 1) <> lastnam
Go to Search Layout
Go to List Layout
A"Go to Overview Instructions Layout
A!Go to Overview Calculation Layout
Go to Overview Notes Layout
Go to Information Layout
emailto
derDate + 28 + Middle ("1065432",
DayOfWeek (OrderDate), 1)
Calculation, Date result) =
OrderDate + 42 + Middle ("5432106",
DayOfWeek (OrderDate), 1)
The text string "5432106" is used for each day of the week. That is, if the date falls on a Sunday, we want to add 5 additional days in order to get to the following Friday. 4 for Monday, 3 for Tuesday, etc.
The calculation above can actually return ANY weekday in the f
uture, for ANY number of weeks.
To calculate a date that is X number of weeks in the future, just calculate 7 times X (where X is the number of weeks). In this case, 7 days times 6 weeks = 42.
If you want to know the Monday 4 weeks in the future, you would type the following:
DueDate (Calculation, Date result) =
OrderDate + 28 + Middle ("1065432",
DayOfWeek (OrderDate), 1)
The number 28 is derived from four weeks multiplied by seven days per week. Again, the string is si
esult two)))
At this point, it's important to appreciate that each nested IF statement is designed to eliminate the potential answers until there's only one result left. Applying this to our invoice aging calculation, if the difference is not less than 30 days, and the difference is not less than or equal to 60 days, and the difference is not less than or equal to 90 days, there is only one result left...over 90 days. It's unnecessary to add another IF statement because at this point ther
e can be only one possible result. So the final calculation would be:
If (Today - Invoice Date <= 30, "30 days or less",If (Today - Invoice Date <= 60, "31 - 60 days",If (Today - Invoice Date <= 90, "61 - 90 days", "Over 90 days")))
The concept of a calculation using nested IF statements is a great way to process results from a field or a group of fields to achieve a desired result. The best way to do this is to have a clear understanding of the desired results before building your
G duplicate
eld to create one from existing fields. For example, combine first name, last name, and birthday create a unique identification for each client.
Example step 2: Add two fields
* A text field, called Mark. When t
correct grouping, first find the day of the week that StartDate occurs, and then multiply by five (since each grouping contains five values - zero through four).
Mod (StartDate - Date (1, 3, 1904), 7) * 5
Now, find the excess days above a multiple of 5 (from logic step #2 above)
Mod (WorkDays, 5)
Now to obtain the correct value, add one to all of this to obtain the appropriate value. For example, if StartDate is a Sunday (returns zero from first Mod() function), and excess daHmys is zero, it leaves us at the beginning of the string. Therefore, add one to get us to the first position.
hcked
ctual days replaces the day of the week:
Work Days 0 1 2 3 4
Sunday 1
Calculation
A calculation field will always result in a number, date or time if any of the fields in the formula have data (are not blank). To make a calculation field result in a blank instead of a zero, use the TextToNum, TextToDate or TextToTime function in a formula. Use TextToNum in a calculation field with a number result. Use TextToDate in a calculation field with a date result. Use TextToTime in a calculation field with a time result. Put this function inside an IF statement. Here are sr, date or time if any of the fields in the formula have data (are not blank). To make a calculation field result in a blank instead of a zero, use the TextToNum, TextToDate or TextToTime function in a formula. Use TextToNum in a calculation field with a number result. Use TextToDate in a calculation field with a date result. Use TextToTime in a calculation field with a time result. Put this function inside an IF statement. Here are s
ead to a desired result.
In a nested I
When you use the Perform Script command, there are two types of scripts which can be performed: internal scripts (scripts in the current file) and external scripts (scripts in other files).
Remember that when you use the Perform Script command to execute a script in another file, there is no need to use the Open command to open the file first.
Whenever you are using the Go To Record command, you can go to the last record by specifying a record number which is very high, like 9
9999. Make sure to perform this step "Without Dialog" to avoid getting an alert (or error) message.
Remember that whenever you exit a Find or a Sort command, you are automatically placed in Browse mode, looking at the first record in the found set or the sort order: there is no need to use an Enter Browse Mode command or to go to the first record.
Remember to use the Refresh command with an Enter Browse Mode step if you wish to copy the values from Summary fields. However, copying
ep first; all of these commands allow you to specify a field for the operation.
Replace and Reserialize can be interchangeable, if you perform the step with
dialog and have the user make the choice of what command is performed.
Remember that if you use the Relookup command, and you're using a calculation field as the matching value, you must specify one of the input fields for the calculation as the field parameter for the Relookup, not the calculation field (calculation fields cannot be used for the Relookup command).
When you use the Copy command without any parameters, it copies all values from all fields on the current layout. If
ield that has not already calculated, it will copy a null value. This happens because the script progresses before the summary field is given time to calculate.
Whenever you use the Enter Find Mode command and choose to Restore Find Requests, use the Perform Find command immediately after it. Be sure to uncheck the Restor
e option on the Perform Find command, if any changes to the find requests in the Enter Find Mode step will occur.
Most script steps execute in the current file (i.e. the file where the script is defined). For example, to copy and paste between files, you will have to write two scripts: a script in the file where you want to copy and a script in the file where you wish to paste. In the script with the Copy command, include a Perform Script command that performs an external script, which
describing a calculation, formula(s) will be displayed without instruction so that you can bypass the instructions and just copy the formula for use in your database.
If the record is describing a script, the script steps will be displayed without instruction so that you can examine them.
There are 6 scripts included with this database that you can import and modify for use with your database. Follow the directions for using these scripts (create any necessary fields, etc.), open your datC
abase, select ScriptMaker from the Scripts menu, click the Import button, and select this file (Calculations and Scripts.fp5). You will then be able to select the script you would like to import in your database.
e Mode command or to go to the first record.
Remember to use the Refresh command with an Enter Browse Mode step if you wish to copy
Cancelm
mNo records were found
Arial
MS Sans Serif
Times New Roman
Courier
Verdana
Helvetica
Palatino
Times
Geneva
Cancelm
mNo records were found
Open ScriptMaker
\\ZED\DoubleDuty
A)Printing Total Page Numbers with a Script
\\ZED\DoubleDuty
\\ZED\DoubleDuty
cript puts an X in the Mark field .
* If the values don't match, FileMaker Pro copies the ClientID value into Global. It doesn't change the Mark field.
4. The script repeats step 3 until
INFORMATION
LAST NAME
NOTES
SEARCH
SUBJECT
TECHINFO
TITLE
TOTAL PAGES
ContentB
NotesB
CalculationB
SubjectB
TitleB
Search
TechInfoB
InformationB
Total PagesB
A Client IDB
MarkB
Duplicate Check GlobalB
A DateField
and, in some cases, notes.
The Instructions tab explains what you need to do to create a calculation or a script.
The Calculation or Script tab will show:
If the record is
describing a calculation, formula(s) will be displayed without instruction so that you can bypass the instructions and just copy the formula for use in your database.
If the record is describing a script, the script steps will be displayed without instruction so that you can examine them.
There are 6 scripts included with this database that you can import and modify for use with your database. Follow the directions for using these scripts (create any necessary fields, etc.), open your dat
To always
Calculation, Date result) =
OrderDate + 42 + Middle ("5432106",
DayOfWeek (OrderDate), 1)
The text string "5432106" is used for each day of the week. That is, if the date falls on a Sunday, we want to add 5 additional days in order to get to the following Friday. 4 for Monday, 3 for Tuesday, etc.
The calculation above can actually return ANY weekday in the f
uture, for ANY number of weeks.
To calculate a date that is X number of weeks in the future, just calculate 7 times X (where X is the number of weeks). In this case, 7 days times 6 weeks = 42.
If you want to know the Monday 4 weeks in the future, you would type the following:
DueDate (Calculation, Date result) =
OrderDate + 28 + Middle ("1065432",
DayOfWeek (OrderDate), 1)
The number 28 is derived from four weeks multiplied by seven days per week. Again, the string is si
This method assumes that your database has three fields: DateStart (Date), TimeStart (Time) and TimeEnd (Time). Unlike method 1, you do not need to enter the ending date. However, only use this method if your elapsed times are always shorter than 24 hours.
To calculate the elapsed time, define a calculation field called ElapsedTime with this formula:
Mod (TimeEnd - TimeStart + 86400, 86400)
Alternate formula: here's an alternative formula for method 2 that also works.
Case(IsEmpty(Title) = 1 and MiddleWords(FullName, 2, 1) <> lastname, Mi
Righ
2 3 4 5
Monday 0 1 2 3 4
Tuesday 0 1 2 3 6
Wednesday 0 1 2 5 6
Thursday 0 1 4 5 6
Friday 0 3 4 5 6
To determine the day of the week for StartDate, we can use the Mod() function (with 7) and subtract a known Sunday date from StartDate (The first known Sunday date in the Macintosh system is J
anuary 3, 1904). This results in a value from zero (Sunday) through six (Saturday). If the result is Wednesday (value returned is 3), somehow, we want to drop down to the Wednesday row. Then, determine the number of excess work days above a multiple of five and move across to obtain the appropriate number of actual days.
This can all be accomplished by putting the entire table into one string, one row following the next. That is,
"12345012340123601256014560345623456"
To find the
end. For example, if the Starting Date is a Friday, and one day is added, then somehow, we want to return the following Monday.
The chart below will help us determine the ending day of the week given the Starting day of the week (left column) and the the number of excess work days over the five day multiple (top row).
Work Days 0 1 2 3 4
Sunday M Tu W Th F
Monday M Tu W Th F
Tuesday Tu W Th F M
Wednesday W Th F M Tu
Thursday Th F M Tu W
Friday F M Tu W Th
Saturday M Tu W Th F
Sunday and Saturday are included in case StartDate is accidentally entered as a weekend date. That is, if the StartDate is a Sunday, and the excess multiple is zero, we don't want to return Sunday, but the following Monday. Using the chart, for a StartDate of Wednesday and three excess days brings us to the following Monday.
Now, change the table so that the number of actual days replaces the day of the week:
Work Days 0 1 2 3 4
Sunday 1
Given a starting date and a number of working days, an ending date can be calculated. This calculation assumes your two fields are named StartDate (date field) and WorkDays (number field), and there are five working days per week.
Here's the logic:
1. Since there are five working days per week, convert each five working days into seven actual days.
2. For those extra days that do not make up a group of five, add them to the starting date.
3. If the ending date falls on either a weekend
or across a weekend, add two more days.
From logic step #1, use the Int() function, dividing by five, to determine the number of five day multiples, or actual weeks. With this result, multiply by seven to get the number of actual days. That is,
Int (WorkDays/5) * 7
From step #2, use the Mod() function to determine the excess days over the five day multiples. That is,
Mod (WorkDays,5)
The difficult step is logic step #3 - determining if the excess days takes us across a week
Note: FileMaker calculates time in seconds. When you subtract one time from another, FileMaker calculates the difference as the number of seconds. Therefore, it's easiest to calculate time based on seconds, rather than as hours, minutes and seconds. The 86400 equals the number of seconds in a day. When you set the calculation field to result in time, FileMaker converts the number (of seconds) to time (hours:minutes:seconds).
Recent S
ElapsedTime (calculation, time) =
Mod (TimeEnd - TimeStart + 86400, 86400)
TimeG<Calculating Elapsed Time When Ending Time is on the Same Day
mula: here's an alternative formula for method 2 that also works.
If (T
termining if the excess days takes us across a we
nested
statements
calculating
blank
instead
calculating
future
based
weeks
calculating
finish
start
number
calculating
person's
calculating
elapsed
ending
later
calculating
elapsed
ending
calculating
number
weekdays
between
dates
comparison
statements
conditional
script
using
status
functions
extracting
title
first
middle
suffix
finding
range
dates
using
scriptmaker
finding
duplicate
records
finding
future
dates
formatting
numbers
calculation
field
identifying
duplicate
records
printing
total
numbers
script
scriptmaker
including
recipient
email
message
self-join
relationship
examples
FullAge (calculation, text result) =
NumToText (Year (Today) - Year (Birthdate) - If (Today < Date (Month (Birthdate), Day (Birthdate), Year (Today)), 1, 0)) & " Years, " & NumToText (Mod (Month (Today) - Month
(Birthdate) + 12 - If (Day (Today) < Day (Birthdate), 1, 0), 12)) & " Months, " & NumToText (Day (Today) - Day (Birthdate) + If (Day (Today) >= Day (Birthdate), 0, If (Day (Today -
Day (Today)) < Day (Birthdate), Day (Birthdate), Day (Today - Day (Today))))) & " Days"
DateG
Calculating a Person's Age
illustrateU
immediatelyQ
importQ
importantA
importingI
improvementH
includeL
includedD
includesN
indexQ
indicateR
informationP
infrastructureU
initialJ
inputQ
insideA
instanceU
instancesU
insteadB
instructionU
internalQ
intoA
invoiceA
invoicesA
isemptyJ
it'sA
itemU
itselfU
januaryD
justQ
kindA
knowA
knownD
labelU
largeQ
lastJ
layoutI
layoutsI
leadA
leavesD
leavesD
potentialA
precedingA
previousA
probablyA
processA
processedA
processesA
puttingD
questionA
questionsA
readA
real-worldA
Import the script "Finding a Range of Dates", and modify it for use with your database.
hrol Panels
ScriptsG*Finding a Range of Dates Using ScriptMaker
Script
The following sections explain how to set up a database and define a script to find duplicate records. For this example, you should understand global fields and know how to define fields.
CAUTION: Deleting records is NOT undoable. Use of this script to delete duplicate records should be run on a copy of your database file. Always make a backup of your database before performing any scripts that include steps for deletion of records.
Example step 1: Identify or create a unique value for
eachA
easierA
eitherD
elapsedF
elapsedtimeW
eliminateA
eliminationA
elseA
emailR
enddateG
endingD
englishA
enterF
enteredD
entireD
equalA
equationH
errorI
evaluatedA
exampleA
examplesG
excellentH
excessD
executeQ
existingE
exitL
expandedA
explainL
extendedU
externalQ
extraD
extractJ
fallsD
falseA
fasterU
asterU
unnecessaryA
untilA
usedQ
user'sU
usesI
usingA
usuallyA
valueB
valuesD
veryH
viewI
wantD
wednesdayD
weekD
weekendD
weeksD
wereA
whatA
whenA
perform
script
command
there
types
wheneverQ
whereQ
whetherH
whichA
whileL
willB
windowQ
workdays/5D
workingD
wouldA
writeQ
yearE
yearsE
yes/noA
obtain
calculation
results
whether
create
calculation
field
includeN
you'llL
you'reQ
yourA
zeroB
recordsI
referencingU
refreshQ
relatedQ
relationshipU
relationshipsU
reliablyQ
relookupQ
remainingG
rememberA
repeatH
repeatsL
replaceL
replacedA
replacesD
reportU
requestL
requestsQ
requiredA
requiresH
restoreL
resultA
result
result
resultsA
returnD
returnedD
returnsD
rightN
rightwordsJ
roundN
rowsU
sameH
sampleB
satisfactoryH
saturdayD
saveQ
saysA
scoreH
screenQ
scriptI
seriesA
settingL
settingsL
sevenD
shouldB
showI
simplestA
simplyM
sinceA
singleA
single
record
viewI
situationA
sizeQ
someB
somehowD
sortL
sortsL
specifiesQ
specifyQ
specifyingQ
startdateD
startdate
dateM
matterA
menuL
messageI
messagesR
middlewordsJ
mightA
minutesF
missJ
modeI
mondayD
monitorQ
monthE
monthsE
moreA
mostQ
moveD
multipleD
multiple
record
viewI
multiplesD
multiplyD
mustM
nameJ
namedD
namesJ
needF
neededA
needsH
nestedA
nestsA
nextA
noteF
notedB
nullQ
numberA
numberfieldN
numericN
numtotextE
obtainD
obtainedE
occurQ
occursD
onesA
onlyA
openQ
operationQ
optimallyA
optionQ
orderL
ordinaryJ
originalJ
otherH
overA
pageP
pagesP
parameterQ
startingA
startupQ
statementA
statementsA
statisticalU
statusI
stepD
stepsK
stillA
storeL
storedU
stringD
sub-summaryU
subsequentA
substituteA
subtractD
suffixJ
summaryQ
sundayD
sureQ
switchL
syntaxA
systemD
tableD
tabsQ
takeG
takenA
takesD
containsD
convertD
copiedQ
copiesL
copyL
copyingQ
correctA
correctlyJ
couldA
countU
courseU
courseU
creatingA
currentN
currentfoundcountI
currentpagenumberP
customerL
dataB
databaseF
dateA
date/time/userQ
dateendF
datefieldK
datesG
datestartF
datetotextK
dayofweekG
daysA
numberM
daysfutureM
decimalN
defineL
definedJ
definitionsJ
deleteL
deletingL
deletionL
dependingI
describeA
designA
designedA
desiredA
destinationQ
determineD
determinesA
determiningD
dialogL
differenceA
differentA
difficultD
displayA
displayedB
displayingA
displaysE
dividingD
documentP
doesn'tL
don'tD
doneL
downA
dropD
duplicateL
duplicatesL
followingD
followsN
footerP
forceQ
rmatE
formattedB
formattingN
formulaA
formulasB
foundI
fourA
fridayD
fromA
fullE
fullageE
fullnameJ
functionB
futureM
generateU
generatedU
gettingQ
givenD
given
starting
number
working
endingD
givesU
givingQ
globalK
globalfield1K
globalfield2K
goesL
goodH
greatA
greaterA
groupA
groupedL
groupingD
guessA
guideU
happensA
haveA
havingU
headerP
helpD
hereB
here'sD
here's
logicD
highQ
higherA
hoursF
howeverA
identificationL
identifiesL
identifyL
identifyingU
chartA
checkH
chooseL
clarityR
arityR
wishN
withA
withinQ
withoutJ
wordQ
workD
workdaysD
workdays/5D
workingD
worksR
wouldA
writeQ
yearE
yearsE
yes/noA
obtain
calculation
results
whether
create
calculation
field
includeN
you'llL
you'reQ
yourA
zeroB
zoomQ
scriptmakerK
scriptsL
secondsF
sectionsL
selectU
self-joinU
self-joiningU
sendR
sentR
separateJ
separatedQ
serialU
serialnumberU
seriesA
settingL
settingsL
sevenD
shareU
shorterW
shouldB
showI
showingU
showsU
sideU
sidesU
similarU
simplestA
simplyM
sinceA
singleA
single
record
viewI
situationA
sizeQ
someB
somehowD
sortL
sortedU
sortsL
spaceR
specifiesQ
specifyQ
specifyingQ
startU
startdateD
startdate
dateM
tdate
dateM
methodF
middleJ
middlenameJ
middlewordsJ
midpointU
mightA
minutesF
missJ
modeI
mondayD
monitorQ
monthE
monthsE
moreA
mostQ
moveD
multipleD
multiple
record
viewI
multiplesD
multiplyD
mustM
myfileU
myfilematchU
nameJ
namedD
namesJ
needF
noteF
notedB
nullQ
numberA
numberfieldN
numericN
numtotextE
obtainD
obtainedE
occurQ
occursD
onceU
onesA
onlyA
onscreenU
openQ
operationQ
optimallyA
optionQ
orderL
ordinaryJ
originalJ
otherH
overA
pageP
pagesP
parameterQ
createI
createdU
creatingA
creationU
currentN
currentfoundcountI
currentpagenumberP
customerL
d159U
dataB
databaseF
dateA
date/time/userQ
dateendF
datesG
datestartF
datetotextK
dayofweekG
daysA
numberM
daysfutureM
decimalN
defineL
definedJ
definitionsJ
deleteL
deletingL
deletionL
dependingI
describeA
designA
designedA
desiredA
destinationQ
determineD
determinesA
determiningD
dialogL
differenceA
differentA
difficultD
displayA
displayedB
displayingA
displaysE
dividingD
documentP
doesn'tL
don'tD
doneL
doubleU
downA
dropD
dupcheckU
duplicateL
duplicatesL
autoU
automaticallyQ
averageU
avoidQ
b456U
backA
backupL
balanceN
basedA
becauseA
becomesA
beenR
beforeA
beginningD
beingB
belowA
besideU
bestA
betweenA
birthdateE
birthdayL
blankB
blockP
bothA
bringQ
bringsD
brokenA
browseL
buildN
buildingA
building
calculations
nested
statementsA
c789U
calculateQ
calculatedD
calculatesA
calculationA
calculationsA
calledE
cannotN
captureI
caretR
caseA
casesQ
categoriesA
cautionL
changeD
changedU
changesQ
neededA
needsH
nestedA
nestsA
nextA
noteR
notedB
nullQ
numberA
numberfieldN
numericN
numtotextE
observeU
obtainD
obtainedE
occurQ
occursD
onceU
onesA
onlyA
onscreenU
openQ
operationQ
optimallyA
optionQ
orderL
ordinaryJ
originalJ
otherH
overA
pageP
pagesP
parameterQ
formatE
formattedB
formattingN
formulaA
formulasB
foundI
fourA
fridayD
fromA
fullE
fullageE
fullnameJ
functionB
futureM
generateU
generatedU
gettingQ
givenD
given
starting
number
working
endingD
givesU
givingQ
globalK
globalfield1K
globalfield2K
goesL
goodH
greatA
greaterA
groupA
groupedL
groupingD
guessA
guideU
happensA
haveA
havingU
headerP
helpD
hereB
here'sD
here's
logicD
highQ
higherA
hoursF
howeverA
identificationL
identifiesL
identifyL
identifyingU
Search
Overview - Calculation
Overview - Notes
Overview - Instructions
Information
Single Record View
Multiple Record View
Import the script "Including the Recipient Name in an Email Message", and modify it for use with your database.
ScriptsG;Send Mail: Including the Recipient Name in an Email Message
has been used to indicate where you should type a space.
Set up the Send Mail script step to use a field value for the
field, and specify the
Email To cC
alculation field.
A field yielding any of the following formats will work:
(.txt)