filemaker pro 10 basics manual - information systems &...
TRANSCRIPT
Copyright©2009byMITIS&T.
FileMakerPro
10
BasicsManual
Draft
BronwenHeuer
IS&T
ApplicationsUserExperience
September,2009
Introduction
Copyright©2009byMITIS&T.
1
Introduction
WelcometoFileMakerProversion10.
FileMakerProisanapplicationthatallowsyoutocreateadatabase,tostoreandorganizeinformationinit,andtosearchandreportonthatinformation.SometimesFileMakerProisreferredtoasadatabasesolutionbecauseitfulfillsbothstorageandreportingneeds.
Thiscoursebeginswiththenotionthatyouhaveinheritedadatabase.Youneedtodiscoverwhatinformationisstoredinit,howthatinformationisorganized,whatreportshavebeencreatedfordataentryandreporting.Youwillneedtokeeptheinformationup‐to‐datebycreating,editinganddeletingrecordsandyouwillneedtoprintoutsomeofthosereports.
CourseObjectives:• Understandtermssuchasrecords,fields,fieldtypes,tocommitarecord,indexed,
foundset,layout• UnderstandFileMaker’sfourmodesandidentifythetasksthatcanbeperformedin
eachmode.• Understandthedifferentviewsofdata.• Navigatethroughfields,records,layoutsandmodes.• Enter,edit,duplicateanddeletedatafromadatabase.• Performsimpleandcomplexfindsandsortstolocateandorganizedataas
appropriateforreporting.• Saveandreusefinds.• Createasimplelayout.• Printoutareport,createapdf,orsavedatatoaspreadsheet.
Thebusinessofcreatinganewdatabaseisreservedforalaterclass.Inthenextclassinthesuite,Reports,Layouts,andUserInterfaceDesign,wewillexplorethevariouswaysFileMakerallowsustoreportoutontheinformationstoredinourdatabasebyexploringthemyriadwaysofcreatinglayouts.Wewillalsocreateasmall,simpledatabasethatwillserveasawayofexploringreportingandkeydatabaseconcepts.Intheprocess,wewilltakeacloserlookatfileattributesandfieldcharacteristics.Inthethirdcourse,wewilllookatconceptsofdesignandgoodpracticeswhencreatingamulti‐tabledatabasesolution.Wewillworktowardanunderstandingofrelationaldataandhowmultipletablescanbejoinedtoallowdynamicreportingcapabilities.
Version10ofFileMakerlookssignificantlydifferentfromallpreviousversions.Almostsinceitsinception,astatusbarhasoccupiedtheleft‐handsideoftheFileMakerinterface.Herewefoundthetoolsofeachmode,buttonstoswitchbetweenmodes,andfirstarolodexandthenbookiconthatallowedustomovethroughrecords,layouts,orreportpages.Thisstatusareahasbeenmovedtothetopofthescreenandnewbuttonsappearthatallowforswitchingbetweenthedifferentmodes.Thisclassandthemanualyounowhaveinyourhandsreflectsversion10’snewconfigurationoftheinterface.Shouldyouhavenotyetmigratedtothisversion,youcanrequestoldermaterialsfromtheinstructorforthepurposeofunderstandingandreviewofversion9andbefore.FileMakerfilescreatedwithversions
Introduction
Copyright©2009byMITIS&T.
2
7through9arereadilycompatiblewith10.Thefiletypeoffp7hasnotchangedsinceversion7.
Version10wasannouncedinearlyJanuary,2009.IS&TannouncedsupportanddistributionofthisversioninMay.
Copyright©2009byMITIS&T.
3
Introduction.............................................................................................................................. 1CourseObjectives:...........................................................................................................................................1
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10 .................................................................................................................................................. 5
Objectivesofthismodule:............................................................................................................................5Exploringanexistingdatabase..................................................................................................................6Navigatingthroughrecords ........................................................................................................................9Viewinginformationasforms,lists,tables .......................................................................................10IdentifyingFileMakerModes.....................................................................................................................13BROWSEmode...............................................................................................................................................13FindMode ........................................................................................................................................................14LayoutMode ...................................................................................................................................................15PreviewMode ................................................................................................................................................16Exercise1–Determinethemodeforeachofthetasks ...............................................................16Exercise2–ReviewNavigationTechniques ....................................................................................17
Module2:WorkinginFindMode....................................................................................18FindingRecords ............................................................................................................................................18FindMode ........................................................................................................................................................19DrawingValuesfromtheIndex..............................................................................................................20UseFindwithSearchOperators ............................................................................................................21ReviewofFindTechniques ......................................................................................................................22RefiningYourRequestsWithAND,OR,OMIT..................................................................................22TheANDrequest....................................................................................................................................................... 22TheORRequest ......................................................................................................................................................... 22OMIT:ExcludeRecordsfromtheFoundSet.............................................................................................. 23
ReusingFinds .................................................................................................................................................24FindandReplace ...................................................................................................................................................... 25Exercise5‐ReviewofANDandORSearches .............................................................................................. 27
Module3:WorkinginBrowseMode..............................................................................28MovingfromFieldtoFieldwithaRecord .........................................................................................28EnteringandEditingData.........................................................................................................................28Committingarecord ............................................................................................................................................... 29RevertRecord ............................................................................................................................................................ 29DeleteDataandReplaceExistingDatainaField ....................................................................................... 29CreateaNewRecord............................................................................................................................................... 29DeleteaRecord ......................................................................................................................................................... 29DuplicatePreviousRecordsorFields.............................................................................................................. 30DuplicateSelectFieldsfromPreviousRecord............................................................................................. 30SelectFieldEntriesFromaListofAllPreviousEntries .......................................................................... 30Exercise3–ReviewofDataEntryTechniques ........................................................................................... 31
ContainerFields ............................................................................................................................................32SortingRecords .............................................................................................................................................32FileManagementtopics.............................................................................................................................34ExportRecords… ...................................................................................................................................................... 34SaveaCopyAs ........................................................................................................................................................... 36Exercise4–Sorting ................................................................................................................................................. 37
Module4:PreviewingandPrinting ...............................................................................38SaveasPDF .....................................................................................................................................................39
Copyright©2009byMITIS&T.
4
Module5:LayoutBasics.....................................................................................................40CreatingaNewLayout .......................................................................................................................................... 42
Module6:Resources ...........................................................................................................46
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
5
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Youhaveinheritedadatabaseandneedtomaintainit:Updateandeditthedata,runreportsandcreatenewreports.Howdoyoudiscoverwhatisinthisdatabaseandfindyourwayaround?
Objectivesofthismodule:• BecomefamiliarwithFileMakerPro’sfourmodesforperforming
databasetasks.• Learnhowtoopenandexploreanexistingdatabase
• Introducethetermsrecords,fields,tables,andlayoutsWhatisadatabase?Oneauthor1defineddatabasesasanybodyofinformationthatcanbesearchedandsorted.Thismaybeaverygeneraldescriptionbuttheseareimportantstandardstokeepinmindaswegoaboutthetaskofdevelopingadatabase.Theabilitytosearchandtosortarekeycriteriawhenwedesignourtablesandfields.
Adatabaseisacollectionofrelatedpiecesofinformation(data)organizedintodiscreterecordsandfields.Ifyouweretocreateastudentaddressdatabase,alltheinformationpertainingtoonestudentwouldbeconsideredasinglerecord.Theseparatepiecesofinformationaboutthatstudent,i.e.,astudent’slastname,firstname,andphonenumberwouldbestoredinseparatefieldswithintherecord.Thesearesometimesreferredtoasattributes.Theentirestudentbody’saddressdatacouldinturnbestoredasasingleFileMakertablemadeupofmanyrecords.
Visually,adatabasecanberepresentedbyatable‐likestructure.Eachrowinthetablecontainsarecord;eachcolumninthetablestoresoneparticularkindofdata.Intheexampleofthestudentaddressdatabase,eachrowwouldbeassignedtoasinglestudentandeachcolumntoaspecificpieceofinformation,suchasfirstnameorzipcode,relevanttoallstudents.
FieldName
1JohnM.Osborne,theauthorofFileMakerbooksandtrainingCDs.[http://www.databasepros.com/training.html]
StudentID LastName FirstName Street
99850012 Rashmon Kira 55OakSt.
99850153 Feingold John 12PineSt.
99851542 Percy Reginald 6GoldRd.
99612542 Hwong Nan 2FrontSt.SingleRecord
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
6
Withthisismindmanyask“CanIconvertmyExcelspreadsheetintoaFileMakerdatabase?”Yes.Thereareseveralwaystodoitdependinguponyourspreadsheetandthedesireresults.Itisoneoftheeasiestwaystocreateaspreadsheet.Inessence,ifyouareusinganExcelspreadsheettomaintainalistofonekindoranother,youareusingitlikeadatabase.FileMakerisanelegantwayofmaintaininglists.[andsoontherewillbeaclasscalledfromSpreadsheettoDatabase].
ExploringanexistingdatabaseFileMakerhasfourmodes.Ineachmode,differenttasksarepreformed.Theyare
Browse Enterdata,viewrecords,createnewrecords,deleterecords.Whenyoufirstopenadatabase,youareinBrowsemode.
Find Searchforarecordorgroupofrecords.
Layout Usingthemanytoolscreatelayoutsandreports.Designthewayinformationwillappearonthescreenorprintedpage.
Preview Seehowpageswilllookwhenyouprintthem.
Eachmodehasadistinctstatusareaandtoolbar.2
Let’sbeginbyopeningtheclassfilecalledEmployeeRecords.DoubleclickingontheiconwillautomaticallylaunchtheFileMakerapplicationandopenyourdatabasefile.
Weareopeningafilethatresidesoneachofourindividualcomputers.Manyofus,backintheworkplacewillclickonaniconanditwillbeopeningadatabasethatresidesinaremotelocation,onasharedserver.Wemaybeoneofmanypeopleaccessingthatdatabaseatanyonetime.Clickingontheiconmaylaunchascriptthatautomaticallytakesyoutothatdatabase.Alternatively,youmaygotoFileOpenRemoteandthennavigatetotheappropriateserverandFileMakerfile.
ThereistheoptiontoopenFileMakerandgotoFileOpenRecent.Adrop‐downmenuoftherecentlyvisiteddatabasefilesisdisplayed.
RESULTS:Youwillseeascreenlikethefollowing.Whatisthis?
2OnethingtobeawareofwhenresizingyourFileMakerwindowsisthattoolbariconsappearanddisappeardependingonthesizeofthewindowandsometimesitisnotthetoolsyouwouldexpectthatdisappearfirst.Stretchandshrinkthetoolbarandobserve!
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
7
WearecurrentlyinBROWSEmodelookingatalayoutcalled“Salary.”Fivefieldsaredisplayedacrossthelayoutandrecordsaredisplayedinalist‐likeform.
Browsecanbedistinguishedbyitsstatustoolbaracrossthetopofthescreen:
Inthelowerframeofthewindow,isthedesignationofthemode. .Thisdesignationisalsoapop‐upmenuthatallowsyoutomovebetweenmodes.Clickonitandsee.
Howmanyrecordsarethereinthisdatabase?Howdoyouknow?Whatorderaretheyin?
Thereare30records.Thestatusaredisplaysthecountoftotalrecords.Theyarecurrentlysorted.When“Unsorted”isdisplayed,itsignifiesthattherecordsareintheorderinwhichtheywereenteredintothedatabasewiththenewestentrylast.
Layoutname
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
8
NoticethedarkenedverticalbaralongsidetherecordforPappas.Thissignifiesthatitisthecurrentrecord.Inthestatusbarittellsyouthatthecurrentrecordis2.
Howisfielddatadisplayedandwhatisalayout?
FileMakerProdisplaysfieldonlayouts.WearecurrentlylookingattheSalarylayoutwhichdisplays5fieldsinalistformat.Toswitchlayouts,gotothepull‐downmenuinthestatusbararea:
Hereyouwillfindalistofthelayoutsthathavebeendesignedforusewiththisdatabase.Simplychoosealayoutfromthemenuanditwillbedisplayed.
SwitchtotheStandardlayout.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
9
NoticethatincontrasttotheSalarylayout,theStandardlayoutdisplaysonlyonerecordperscreen.
Noticealsotherearemanymorefieldsdisplayedonthislayout.Totheleftweseethefieldlabelsandtotherightisthedata,thefieldcontents.
Navigatingthroughrecords
Tomovethroughtherecordsinthedatabase,youcanusethebookicon.Clickingontherighthandpagemovesforwardthroughthedatabaserecords.Clickingontheleft‐handpagemovesbackwards.Thecounterincrementsasyoumovethroughtherecords.This
numberisrelativetotheorder(sortedorunsorted)thattherecordsarein.
Tomovethroughrecords,youcanalsomovetheslider.Thisisespeciallyhandyifyouwishtomovetothebeginningorendofthefile.
Finally,abovetheslideristheCurrentRecordslot.Youcanclickintheslot,typeinanumberandpressEnterorReturn.FileMakerwillgotothatrecord.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
10
ThesearejusttwoexamplesofwaysinformationcanbeviewedinFileMaker.Layoutsallowyoutoviewsomeorallyourfields.Herearesomeotherexamples:
AboveisasamplingofdifferentwaysthatFileMakerlayoutscanbedesignedtodisplayinformation:(goingclockwisefromtheupperleft)asamailmergeletter,asalayoutwithinformationgroupedintodifferenttabpanels,asalist,aslabels.
Task:Perusethedifferentlayoutsinthesamplefile.Searchout:Whichlayoutsincludehiredate,birthdateworkphone….Comparethetabbedlayoutwhereyouseeonerecordperscreenwiththatofalistwhereyouseemultiplerecords.
Viewinginformationasforms,lists,tablesLayoutscanbeviewedinthreedifferentways:asaform,asalist,asatable.
• FormViewdisplaysthelayoutwithonerecordatatime.(Standard,MailMergeareexamples)
• ListViewdisplayslayoutasalist,displayingrecordsoneaftertheother.• TableViewremovesalltheembellishmentsanddisplayingfieldsand
recordsasiftheywereinagridorspreadsheet.Whencreatinganewlayout,thereisadefaultviewassociatedwithit.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
11
YoucanswitchbetweenFormView,ListView,andTableViewbyusingthebuttonsintheLayoutbar.
FormView ListView TableViewHereistheStandardlayoutdisplayedineachoftheviews:
Formviewdisplaysthelayoutwithjustasinglerecord.Listviewdisplaysrecordsoneafteranother.SeehowwhenonelayoutendsatSalaryttl,foronerecord,thenextrecordisdisplayed.
InTableview,thefieldsofthelayoutareorganizedintoascolumnsinagridwiththerecordsbecomingtherows.
TASK:ExperimentwiththedifferentviewsfortheStandardandforthePersonnelInfo‐Tabbedlayouts.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
12
TheremaybeevenmorefieldsthanwhatweseeintheStandardLayout.Todeterminehowmanyfieldsaredefinedinthedatabase,gotoFileManageDatabase…ThekeystrokeisCtrl‐Shift+DonWindowsorCmd‐Shift+DontheMac.(Think:Control–CapitalD.)
RESULT:TheManageDatabasedialogboxopens.
BesuretheFieldstabisselected.Hereyouseealistofthefieldsthathavebeendefinedforthisdatabasetable.
Additionalfieldscanbedefinedforthedatabasehere.Fieldsarenamed,assignedatype(text,number,date,calculation,etc.)andalsogivenoptions.ClickOKtoexitthisdialogbox.
“Ican’visitthisdialogboxfrommydatabasebackinmyoffice.Why?”Viewingthisinformationisgrantedorprohibitedbyaccessprivilegesthathavebeensetup.Inthisclasswewillbeviewingsampledatabasesasifwehadcreatedthem,therebyhavingAdministratorprivilegeswhichallowviewing,editinganddeletingall.
Thistellsthenumberoffieldsinthetable.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
13
IdentifyingFileMakerModesThereareavarietyofwaystomovebetweenFileMakermodes.Therearebuttons,menuselections,andkeystrokes.
ReturningtotheFileMakerapplicationwindow,pulldowntheViewmenuandnoticethelistofmodes.Alongsideeachmodeisthekey‐strokedesignationforswitchingtothatmode.
TotheleftaretheMacintoshkeystrokes.OnWindows,usetheControlkeyplustheletterdesignation.
Inthelowerleft‐handcorneroftheapplicationwindowispop‐upmenuthatallowsyoutonavigatefromonemodetoanother:
BROWSEmodeReturningtothestatusbarinBrowsemode,noticethetasksthatcanbeperformedasdesignatedbythebuttons:NewRecordandDeleteRecord.WhileinBrowsemode,noticeonthestatustoolbarthatthereisabuttontoinitiateafind(switchesyoutofindmode),onetoPreview(switchtoPreviewmode)thedocumentandabuttontoEditLayout(switchesyoutolayoutmode).
WhileinBrowsemode,thereisalsoamenucalledRecords,comprisedoftasksthatcanbeperformedinBrowsemode:
YouwilldothemajorityofyourworkinBrowsemode.
Asmentionedabove,Browsemodeallowsyoutobrowsethroughtherecordsinthedatabase.Thebookicon,thesliderandtherecordslotallowyoutomovethroughtherecords.Ifyoupreferkeystrokes,
CTRL+⇑movestopreviousrecord
CTRL+⇓movestonextrecord.
TheCTRLkeyisusedwithboththeMacandWindow.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
14
Ifyouareusingamousewithawheel(Windows),youcanuseittoscrollforwardandbackwardsthroughtherecord.
Initiallytherecordsareinanorderknownas“Unsorted.”Thissignifiesthattheyarein“creationorder,”thatistheorderinwhichtheywereenteredintothedatabase.
Hereyoucancreatenewrecords.SwitchtotheStandardlayoutandtryit.ClickontheNewRecordbutton.
RESULTS:AnewblankrecordappearsawaitinginputAfewfieldsalreadyhaveinformationinthem.Why?
Insomefields,dataisautomaticallygenerated.Forexample,EmployeeIDissetto“Auto‐enterSerial,Can’tModify.”YoucanseethespecificationofthisoptionbyreturningtoFile Manage Database…ThetwootherfieldsSalaryTotalandSalaryAveragearecalculationfields.Thenumberyouareseeingintheresultofthosecalculations.
ThesearejustafewofthethingsyouwillseeasyouperuseyourdatabaseinBrowsemode.Ifyouarestillinthe“createnewrecord”process,clickoutsidethefields,thatis,inawhitespace.
UnlikeWord,Excelandotherprogramswhereyousaveyourworkfrequentlythroughoutasession,FileMakersavesyourworkautomatically.Thereforeitisimportanttobeawareofwhatyouaredoingespeciallywhendeletingormakinglargechangestoyourfile.Abackupcopyofafilecanbecreated
FindModeFindmodeallowsyoutosearchforrecordsinthedatabase.Youcansearchbasedonasinglecriteriaoronmultiplecriteria.WiththisnewversionofFileMaker,youcansavefindsforreuselater.
OntheBrowsetoolbar,thereisamagnifyingglassiconwithadownwardpointingtriangle.
Clickingonthedownwardtrianglebringsupamenuofrecentfindsandtasksforsavingfindsasisshownbelowtotheright.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
15
ClickingontheicontakesyoutoFindmode.Hereyouwillseeadifferenttoolbar,withoptionsforcreatingfinds:
Anemptyrecordisdisplayedwiththefieldboundaryboxesandonceyouclickinafield,smallmagnifyingglassesappeartocueyouthatyouareinFindandnotBrowsemode:
Simply,ifyouenterinformationintoafieldandclickPerformFind,FileMakerwillfindallrecordswhosefieldmatchesthat,returnstoBrowsemodeanddisplaysonlythematchingrecords,knownasthefoundset.TheBrowsetoolbardisplaysthenumberofrecordscomprisingthefoundsetandapiechartdisplayingtheratiooffoundrecordstotheentirety.Eachofthelayoutsnowdisplayonlythisfoundset.Clickingonthebookicon,youcanmarchthroughtherecordsinthefoundset.
Thispiechartisalsoabutton.ClickonitandFileMakerdisplaystheoppositeofthefoundset,theomittedrecords.Clickagainandyoureturntothefoundset.
Toseeallrecordsinthedatabaseagain,clickontheShowAllbutton.
LayoutModeAlayoutprovidesameansfordisplayingorreportingontheinformationinthedatabase.Itcanbeascreenthatallowsdatatobeinputintothedatabase.Alayoutcanbeassimpleasnamebadge,oracomplexasareportwhichorganizesanddisplaysrecordswithtotals,subtotalsandothercalculations.Itcanbeamailmergeletter.
SwitchtoLayoutmodeandobservethedifferenttoolstforcreatingandmodifyinglayouts.Designingalayoutisalotlikeworkingwithagraphicsprogram.
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
16
InLayoutmode,usethepull‐downmenutomovethroughthedifferentlayouts.Observethedifferences(headers,footers,reportsummaryparts,tabpanels).Alternatively,youcanclickonthebookicontomovethroughthedifferentlayouts.
ClickonExitLayoutbuttonandyoureturntoBrowsemode.
PreviewModePreviewmodeallowsyoutoseewhatalayoutwilllooklikewhenitisprinted.
Noticethebuttonsinthetoolbar.Hereyoucanquicklycreateaspreadsheetorapdfofthelayoutyouareviewing.
TherearesomereportfeaturesthatarenotshowninBrowsemodebutthatcanbeseeninPreview.Forexample,layoutssuchaslabelsarecreatedinFormviewandinBrowsemodeshowonlyonelabelwhileinPreview,theentirelabelrunisdisplayed,sheetbysheet.
ViewthelabellayoutfirstinBrowseandtheninPreview.
Anotherexampleisalistthatismultiplecolumns.OnlyasinglecolumndisplaysinBrowse,butthemultiplecolumnsappearinPreview.
ViewSampleReport2inBrowseandPreview.
Exercise1–DeterminethemodeforeachofthetasksSpecifythepropermodeforthetaskintheleftcolumn:
BrowseFindLayoutPreview
Function Mode
1.Editdata
2.Createanewreport
3.Seehowalayoutwillprint
4.Createanewrecord
5.Searchforasubsetofdata
6.Theresultsofafindaredisplayedhere
7.Viewmailmergedocumentsastheywillprint
Module1:WorkingwithanExistingDatabase:AnOverviewofFileMakerPro10
Copyright©2009byMITIS&T.
17
Exercise2–ReviewNavigationTechniques1. Ifnotalreadyopen,opentheEmployeeRecordsfileintheclassfolder.Gotothe
Standardlayoutandnavigatetorecordnumber21.Whoserecordisit?
_________________________________________________________
2. FindEarlDunn’srecord,WhatisEarl’srecordnumber?
______________________________________________________________
3. Nameatleastonelayoutthathasallofthefollowingfields:
FirstName
LastName
DateofBirth
DateHired
4. Wheredoyougotoseeaspreadsheet‐likearrangementofthedatainyourdatabase?
Copyright©2009byMITIS&T.
18
Module2:WorkinginFindMode
Inthismodule,wewill
• Locaterecordsthatmatchasinglecriterion• Locaterecordsthatmatchmorethanonecriterion• Locaterecordsthatmachatleastoneoftwocriteria• Omitrecordsfromthefoundset• Performafindandreplace• Reusefinds/• CreateandmodifySavedfinds
Searchingisoneofthemostimportanttasksindatabaseuse.TodaywehaveadvancedsearchenginestobringusinformationfromtheWorldWideWebinanswertooureverydayneedsandcuriosities.InFileMaker,inordertocreatemeaningfulreports,selectingonlytheappropriateinformationfromthedatabaseiscrucial.Withafewbasicskills,itiseasytosearchaFileMakerdatabasequicklyandefficiently.
Whenyouperformafind,FileMakersearchesthroughalltherecordsinatable.Thoserecordsthatmatchthecriteriabecomethefoundset.Thereafter,youareonlyworkingwiththoserecordsuntilsuchtimeasanotherfindisperformedoruntilyourequesttoseeallrecords.
FindingRecordsA“quickfind”canbeperformedinBrowsemodebyusingashortcutmenu.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
19
Forexample,above,wearelookingattheSalarylayout.IfwewantedtofindallemployeeswholivedinEverett,wecouldselectEverett,Rightclick(Windows)orCtrl–Click(Mac)andfromtheshortcutmenu,chooseFindMatchingRecords.
YourfindrequestreturnstworecordsofpeoplewholiveinEverett.Thisisthefoundset.
Thestatusbardisplaysthenumberoffoundrecordsoutofthetotalrecordsinthedatabaseandthepiechartdisplaysthefoundsetasaportionofthetotalrecords.Clickingonthebookiconnowmovesyouthroughonlythoserecordsinthefoundsetinallofthelayouts.
ThispiechartisnewtoFileMaker10.Itisalsoabutton.Clickingonitdisplaysthereverseofthefoundset.Clickingasecondtimereturnsyoutothefoundset.
FindModeOtherwise,gotoFindModetoperformafind.ThereareseveralwaystogetintoFindmode:
• Fromthepopupmenu,selectFind.• GotoView FindMode• IntheStatustoolbar,clicktheFindtool.• PressCtrl/⌘+F
Asmentionedinouroverview,whenyougointoFindmode,ablankrecordisdisplayedandmagnifyingglassesappearinthefields.
Intheappropriatefield,enterthetext,number,ordateyouwanttofind.Thisisreferredtoasafindrequest.
ClickonthePerformFind.Orifyouchangedyourmind,clickonCancelFind.
Alternatively,youcansimplypressReturn/Enterandthefindwillbeperformed.
RESULTS:PerformFindplacesyoutoBrowsemodedisplayingtheresultsoftheFind.ThefindsequenceisaddedtotheRecentFindlist.
CancelFindreturnsyoutoBrowsemodewithnochangetothepreviousstatus.Thatis,ifafoundsetwerecurrentlyineffect,itstillis.(ThiswasnotthecasewithpreviousversionsofFileMakerwhereeachtimeyouwentintoFindmodeitwouldcanceloutthepreviousfoundset.)
Tryit
1. FindallpeoplewhohavethejobtitleofSecretary.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
20
2. FindtheindividualwhoselastnameisDunn.
3. FindallpeoplewholiveinSomerville.
4. FindallProfessors.
Thedefaultsearchperformedisafulltext“beginswith”search.Thismeansthatitwillsearchforwordsbeginningwiththestringyouspecify.Forexample,ifyoutypeProfintothetitlefield,anytitleswhosewordsbeginwithProfwillbereturned.Thiswouldreturn“Professor”,“AssistantProfessor”aswellas“ProfessionalWrestler”ifitwereinthefile.Justasanexample,itwouldnotreturnarecordthathad“FileMakerPro”inthefield.,becausethestringisembeddedinthetext.Ifyouwantedtofindanyoccurrenceofthestringprointhejobtitlefield,youcouldusethewildcardoperatorandsupplythestring* p r o * .
DrawingValuesfromtheIndexAswehaveseenorwillsee,FileMakerautomaticallyindexesthevaluesinafieldandwecandrawfromthislist.Forexample,whatifweweren’tsureaboutaparticularjobtitlethatwewantedtosearchon.Withthetitlefieldselected,gotoInsert FromIndex…Thefollowingdialogboxopens.YoucanselectfromthelistandchoosePaste.
ThecheckboxlabeledShowindividualwordsdoesjustthat:itliststheoccurrenceofeverywordfoundinthatfield.
Clickinthecheckboxandobservetheresults.Clickagaintoreturntotheoriginallist.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
21
UseFindwithSearchOperatorsWhatifyouwishtofindarangeofvalues,forexampleallpeoplewhoselastnamesbeginwithDthroughM,orallsalarieslessthat$75,000?InFindmodethereisalistofoperatorsthatcanbeusedinspecifyingyoursearchrequest.Youcaneithertypethesecharactersintothefindrequestorselectthemfromthemenu.
Intheexampleabove,ifyouwishedtojustretrieveindividualswiththetitleProfessorandnotAssistantProfessoryouwouldprefixProfessorwith==(thedoubleequalsign).
Note:Thereisnospacebetweenthevaluesandthethreedotsforrange.
Tosearchforanemptyfield,usethesingleequalsignandnothingelseintherequestfield.
Todeterminewhatyourlastfindwas,inBrowsemode,gotoRecords ModifyLastFind.ThisautomaticallytakesyouintoFindmodeanddisplaysthelastfindrequestyouperformed.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
22
Exercise3:ReviewofFindTechniquesForthisexercise,gotothefileFind_EmployeeRecords.OpenthefileandnavigatetotheSalaryLayout.
1. Findallpeoplewhoareprofessors
2. FindallpeoplewholiveinBoston
3. Findallpeoplewhoearnexactly$47,190
4. FindallpeoplehiredafterMarch1,2000
5. Arethereanyrecordswherethehiredateismissing?
6. Findallpeoplewhoearnbetween$33,000and$42,000
7. FindallpeoplewhoselastnamesbeginwithAtoMandthensortthefoundset.
8. Showallrecords.Whatisthesortordernow?
RefiningYourRequestsWithAND,OR,OMIT
TheANDrequestYoucanrefineyoucurrentFindrequesttomatchmultiplecriteriainseparatefields.Forexample,youmaywanttofindallprofessorswhobeganworkingafterMarch1,2000.ThisisknownasanANDsearch.AnANDsearchspecifiesmultiplecriteriainsinglerequest.
1. ClickFindinthestatustoolbar.
RESULT:Thisplacesyouinfindmode,viewingthefieldboundaryboxeswiththemagnifyingglasses.
2. EnterProfessorintothetitlefieldandthe>3/1/00inthedatehiredfield.
3. PressPerformFind.
TheORRequestAttimesyoumaywanttofindrecordsthatmatchmultiplecriteriainthesamefield.ForexampleyouwanttoknowallpeoplewholiveineitherBostonorJamaicaPlain.ThisisreferredtoasanORrequestandiscreatedbysubmittingmultiplerequestsinFindmode.
1. InFindmodetypeinthefirstcriterionintotheappropriatefield.
HerewewillenterBostonintheCityfield.
2. ClickontheNewRequestbutton.
RESULT:Thisopensupanotherlineintheformforenteringthesecond
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
23
criteria.Ifthelayoutisaformtype,thatisonerecordperscreen,itwillopenupanotherscreenwithblankform.Observethescreenandthestatusbar:
Thisshowtheadditionallinewhereasecondcitycanbespecified.ThebookiconandtherecordcounterslotinFindmoderefertoFindRequests.Itcurrentlyreads“2”becausewearecurrentlyonthesecondrequest.
3. Enterthesecondcriteriainthespacecreated.
InourcasewewillenterJamaicaPlain.
Youcanaddathird,andfourth,etc.asareappropriatefortheFindyouneedtoperform.
Tomovebetweenrequests,eitherclickinthatareaofthescreenorusethebookicontomove.TheDeleteRequestbuttonisusedtoremoverequests.
4. Whenready,clickeitherPerformFindorEnter/Return.
RESULT:AllrecordsofpeoplelivingineitherBostonorJamaicaPlainaredisplayedinBrowsemode.
OMIT:ExcludeRecordsfromtheFoundSetOftentimesweknowwhatweareNOTlookingfor.Forexample,maybewewanttodoamailingtoeveryoneexceptthosethatliveinCambridge.InacaselikethisweusetheOMITbuttoninFindmode(ofcourseyoucanfindallthatliveinCambridgeandthenpressthelittlepiechartfortheoppositeofthefoundset).Bydefault,Includeisineffect.Withyourrequestselected,clickonOmit.
ForaFindwithmultiplecriteria,movetotheappropriaterequestandclickOmit.
Onceyouhaveafoundsetandyouwishtoremovearecordfromit,inBrowsemode,selecttherecord,gotoRecordsmenuOmitRecord.
Youcanalsoomitamultipleofrecordsstartingatthecurrentrecord.SeeRecordsOmitMultiple.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
24
RESULT:Thefollowingdialogboxappearsallowingyoutospecifythenumberofconsecutiverecordsyouwishtoexclude.
ReusingFindsFileMaker10introducedthesavingoffinds.Now,your10mostrecentfindsareautomaticallysaved.
Thetrianglealongsidethefindiconleadstoapulldownmenuwheremostrecentfindsarelistedwithadesignationofthefindrequest:
ThereisasimilarmenuinFindmodewiththeSavedFindicon:
Tore‐executeafind,simplyselectitfromthelist.
RESULTS:InBrowsemode,itperformsthefindanddisplaysthefoundset.
InFindmode,itsetsuptherequest(s).YoumustnowclickonthePerformFindbutton.
ForFindsthatyouwillbeexecutingrepeatedly,youcansavethem,assignthemaname,eveneditthem.Thissavesthesomeoftheneedtoscriptfindsforlateruse.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
25
Tosaveyourlastfindrequest:
1. InWindows,clickthedownarrowontheFindbuttonandchooseSaveCurrentFind.OntheMac,clickandholdtheFindbuttonandthenchooseSaveCurrentFind.
RESULT:ThefollowingdialogboxopensallowingyoutorenametheFindrequestifyousodesire.TheAdvancedbuttontakesyoutoaseriesofdialogboxeswhereyoucandelete,duplicateoredityourfindrequest.,
2. Assignitanewnameifyousodesire.ClickSave.
RESULT:TheFindrequestwillbeaddedtoyourSavedFindslist.
FindandReplaceItispossibletoeditmultiplerecords,changingtheentirecontentsofafield.Forexample,ajobtitleoradepartmentmayneedtobechangedgloballythroughoutthedatabaserecords.
1. PerformyourfindrequesttoarriveattheFoundset.
2. Clickinthefieldtobereplaced.
3. Typeinthefield,replacingtheentirecontentsofthefield.
4. GotoRecords ReplaceFieldContents…
RESULT:TheReplaceFieldContentsdialogboxopensreflectingthechange,awaitingyourconfirmationofthechange.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
26
Observe:HerewesearchedforalloccurrencesofE14intheaddressfieldandfound5records.WechangedthefieldcontentsoftheDepartmentfieldandbeforecommittingtherecord,wenttoReplaceFieldContents.
Pauseinthisdialogboxtoobservethatheretooyoucanaddserialnumberstorecords(Tryit!)oruseacalculationinthereplacementofthefieldcontents.
5. ClickonReplace.
RESULTS:ThiswillchangethedepartmentnametoMediaArtsandSciencesforallrecordsinthefoundset.
Tryit!
InBrowsemode,thereisalsoaFind/Replace.ThisisdifferentthanFindmode.Find/ReplaceinBrowsemodesearchesforpiecesoftextacrossfieldsandrecordswhereasFindmodesearchesforRecordsbasedonfieldcontents.InBrowsemode,goingtoEdit Find/Replaceopensthefollowingdialogbox:
ObservethecheckboxesandradiobuttonsthatallowyoutorestricttheFind/Replaceaction.
Thiscanbeusedinconjunctionwithafind,performinganedituponallrecordsinafoundset.
Module2:WorkinginFindMode
Copyright©2009byMITIS&T.
27
Exercise4‐ReviewofANDandORSearches
Ifnotalreadyopen,opentheFind_EmployeeRecordfileandnavigatetotheSalarylayout.
NumberofFoundRecords
1.FindpeoplewhoareLabTechsorConsultants
2.FindallwhoareProfessorsorwholiveinCambridge
3.FindpeoplewholiveinBostonANDmakeover$45,000
4.Findpeoplewhoearnlessthat$40,000andstartedworkingatMITbeforeJanuary1,1996.
5.FindallsecretariesexceptthoselivinginCambridge.
6.Findallpeoplewhoearnmorethat$45,000,excludingprofessors.
7.Revisitthefindin#3andchangeittoallthatmakeover$35,000.(UsetheRecentFindlist).
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
28
Module3:WorkinginBrowseModeInBrowsemodewewill
Navigatethroughthefieldsofarecord
Enterandeditdata
Deleteandreplacedata
Createanewrecord/Deletearecord
Sortrecords
Exportdata
Createacopyofthedatabase
MovingfromFieldtoFieldwithaRecord Verysimply,youcanclickinafieldwithyourmouseandmakethatcellactive.
AquickerwayistousetheTABkey.RepeatedpressingoftheTABkeymovesyoufieldbyfieldthroughthelayout.
PressingShift‐Tab,movesyoubackwardsthroughthefields.
Note:UsingtheReturn/Enterkeydoesnotmoveyoufromonefieldtoanotherbutinsteadplacesacarriagereturninthefield.Shouldyoudothisbymistake,backspaceoverittoremoveit.Onceyouclickoutsidethefieldboundarybox,thestretchedappearanceofthefieldwilldisappear.
GototheStandardlayoutandtabthroughallthefields.Noticesomefieldsareskipped.Thesearefieldswheredataisnotenteredbyyoubutinsteadistheresultofacalculationorasummarization.
ObservewhathappenswhenyoutabtotheSalaryfield:thenumberisdisplayedwithoutthecurrencyformatting.Thisisbecausethedollarsignandcommaseparatorareformattingthatisappliedtothedataandisnotthedataitself.Clickoutsidethefield,andyouseethenumberwiththeformattingapplied.
Exploreotherlayouts.Howistheorderofnextfieldandpreviousfieldestablished?You,thedeveloperdoesit!Wewilldothisinthenextcoursewhenwedesignlayouts.
EnteringandEditingData1. Gotoarecordandlocateyourcursorinthefieldyouwishtoenterdate.Begintyping.2. Whenfinished,ifyouclickoutsidethefield,youhavecommittedtherecord.
Whenselectingdatainafield,theusualselectiontechniquesareineffect:
• draggingacrossselectsdata,• doubleclickinginawordselectsjusttheword,• tripleclickingselectsaparagraph.
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
29
CommittingarecordFileMakerautomaticallyupdatesthedatabaseasyouwork.GototheFilemenuandnoticethereisnoSAVEcommandbutthereisaSaveACopyAs.
Whencreatinganewrecordorupdatinganexistingrecord,andyouclickoutsideofallfieldsorpressenter,youdowhatiscalledcommittherecord.ThistellsFileMakerthatyouarecommittingthechangesyoujustmadeandthedatabaseisupdatedaccordingly
RevertRecordBeforecommittingarecord,youcanreverttotherecord’soriginalcontents.
Forexample:
1. GototheSalarylayoutandchoosetherecordforEdgewater.ChangehisjobtitletoAdministrativeAssistant.Tabtothesalaryfieldandchangehissalary.
2. Withoutclickingoutsidetherecord,gotoRecords RevertRecord.
RESULT:Adialogboxappearsaskingifyouwantto“Revertallchangessinceitwaslastentered?”
3. ClickRevertandobservethefieldsreturnedtotheiroriginalvalue.
4. Trythisagainwithanotherrecord,butchangethefirstfield,clickoutsidetherecord,changeanotherfieldandthengotoRecords RevertRecord.*
DeleteDataandReplaceExistingDatainaFieldToclearafield,selectthefieldcontentsandpressBackspace/Delete.
ToremoveselectedtextorhecharactertotheleftofthecursorpressBackspace/Delete.Toremovetexttotherightofthecursor,presstheDeletekey(inthekeypadarea,fn+deleteonMaclaptopkeyboards).
CreateaNewRecordPresstheNewRecordbutton.(YoucanalsogotoRecords NewRecordorusethekeystrokeCtr/⌘+N.)
GototheStandardlayoutinBrowsemodeandpressNewRecord.
RESULTS:InthecaseofthisFORMlayout,ablanklayoutappears;inthecaseofaLISTlayout,asintheSalarylayout,ablankrecordareaappearsreadyforyoutoinputdataintothespacesforthefields.
Thisnewrecordwillbeplacedattheendofhedatabaseandwillbepermanentlyassignedtothatpositioninthedefault/unsortedrecordorder.
OBSERVE:TheEmployeeIDfieldalreadyhasinformationinit.Why?Trytochangethenumberinthefield.Whathappens?
Thisistheresultofcreatingafieldwiththeoptions“AutoEnter,Serial,Can’tModify,Unique”toit.GotoFile Manage DatabaseandclickontheFieldstabifitisnotalreadyselected.Noticetheassignmentofthisoption.
DeleteaRecordTopermanentlydeletearecordfromthedatabase:
1. Navigatetotherecordinthedatabaseyouwishtodelete.
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
30
2. PresstheDeleteRecordbuttoninthetoolbar.(OrgotoRecords DeleteRecord.)
RESULT:Youwillgetaconfirmationmessageaskingifyouaresureyouwishto“PermanentlydeletethisENTIRErecord.”
BEWARE:Noticeifyouusethemenu,DeleteRecordandDeleteAllRecordsaredangerouslyclosetoeachother.Later,wewillseethatwhenwehaveafoundset,thiswillchangetoreadDeleteFoundRecords.
DuplicatePreviousRecordsorFieldsUsetheDuplicateRecordcommandtosavetimeandtoavoidre‐keyingrepeateddata.
1. Toduplicatearecord,navigatetotherecordyouwishtoduplicate.2. GotoRecords DuplicateRecord.
RESULT:FileMakercreatesaduplicateofthecurrentrecordandplacesitafterthelastrecordinthefile.
Youcannoweditthatrecordasappropriate.Noticeinourcase,anewEmployeeIDisassigned,followinginthesequence.
DuplicateSelectFieldsfromPreviousRecordWhatifanewrecordcontainssomebutnotallfieldsofanotherrecord?Withakeystroke,youcanenterthatrepeatedinformation.Here’show:
1. Navigatetotherecordwhosefield(s)youwishtoduplicate.ClickINtherecord.Clickinginoneofthefieldsoftherecordsignifiesthatyouhave“visited”therecord.
2. Navigatetotherecordwhereyouwishtoduplicatethisinformation.Inourcasewewillcreateanewrecord:presstheNewRecordbutton.
3. ClickinthefieldyouwishtorepeattheinformationfromtheotherrecordandeithergotoInsert FromLastVisitedRecordorusethekeystrokeCtrl/⌘+‘(singlequotemark).
RESULT:Thevalueofthefieldfromthe“visited”recordisduplicatedinthefieldofthecurrentrecord.
Youcanrepeatthecommand/keystroketoduplicateotherfieldsfromthatrecord.
Tryit:Let’ssaythatanotheremployeewiththesamejobtitleandsalaryasConniePetricelli.
1. FindConniePetricelli.(GotoFind,enterPetricelliintheLastNamefield)andclickintherecord.
2. ThencreateanewrecordbyclickingontheCreateRecordbutton.
3. Inthatnewrecord,clickintheTitlefieldandgotoInsert FromLastVisitedRecord.
4. TabtotheSalaryfieldandrepeat,thistimeusingthekeystrokeCtrl/⌘+‘.
SelectFieldEntriesFromaListofAllPreviousEntriesToguaranteeuniformityofdatavalues,itispossibletoselectfromanindexofexistingvaluesforthatfield.ThiscanbeusedinFindmodeaswell.
1. Locatethecursorintheappropriatefield.
2. GotoInsert InsertfromIndex(Ctrl/⌘+I).
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
31
RESULT:Youshouldseealistofallhecurrentvaluesforthatfield.ByclickingontheShowIndividualwordscheckbox,allthefieldvaluesarebrokendownintoindividualwords.
3. Selecttheappropriatevaluefromthelist.
4. EitherdoubleclicktheentrytoselectandpasteitintothecurrentfieldorchoosePaste.
Exercise5–ReviewofDataEntryTechniques
1. IntheEmployeeRecordfilecreateanewrecordforGretchenTwitchellwiththefollowingadditionaldata:
MITBldg24‐1108‐4577gwitchell44RoseCt.Somerville,MA02445617578‐4586$38,000salaryAdministrativeOfficerDOB05/24/60DateofHire07/21/1999
2. DeleteSteveSawyer’srecordfromthedatabase.
3. Whichshortcutcouldyouusetorepeatdatathatappearedinanotherrecord?
Usingthisshortcut,addanewemployeetothedatabasewhohasthesamejobtitleandsalaryasCraigGarelli.
4. Whichshortcutcanyouusetoassureconsistencywithexistingvalues?
Usingthisshortcut,addanewemployeeandassignherthejobtitleofFiscalOfficer.
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
32
ContainerFields
FileMaker7introducedcontainerfields,fieldsthatcanbeusedtostorefiles.Thiscanincludeanyfilesuchasgraphics,movies,photos,bitmaps,soundsupto4GBinsize,multimediafiletypessupportedbyQuickTime6,anddocuments,includingMicrosoftWordandExcelfiles,PDFfiles.
Howyouplaceinformationintothecontainerfieldwilldeterminewhatyoucandowiththatinformationlater.
Youcannotsearchoncontainerfields.
Youcaninsertinformationintoacontainerfieldinavarietyofdifferentways.Youcan
• cutandpaste• insert• insertalink(areference)tothefile.
BeawarethatWindowsandMacintoshhandletheseprocessesdifferently.
TryIt>GototheLayoutcalledPersonnelInfo–TabbedandclickonthePersonaltab.NavigatetoGraceBaumgartnerandinsertherpicturefromtheEmployeePhotosfolder.
1. Clickinthecontainerfield.2. GotoInsert Picture
Anavigationaldialogboxopens.
3. Navigatetothefileyouwishtoinsert.
Noticeatthebottomofthedialogboxtheoptiontoenteralinkorreferencetothefile.
4. ClickOpen5. RepeatforDavidEdgewaterandinserthisphotoasareference.Comparethetwoexamples.
SortingRecordsInBrowsemode,youcantemporarilychangetheorderoftherecordsbyusingSort.ThisorderwillthenbeineffectforalllayoutsuntilanotheractionsuchasaFindoranUnsortisperformed.
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
33
BeginninginFileMaker10,thissortbecameakindofpersistentsort,thatis,wheneverarecordisaddedorchanged,itwillbeinsertedintheappropriateplaceinthesortorder.
1. Tosort,eitherclickontheiconinthetoolbar,gotoRecords SortorusethekeystrokeCtrl/⌘+S.
RESULT:TheSortRecordsdialogboxappears.Ontherighthandside,youwillsee,ifany,thecriteriaofthelastsortperformed.
2. IffieldsappearintheSort‐Orderbox,clickClearAll.3. Doubleclick(SelectandMove)thefieldsyouwishtosortby.
RESULTS:Thefield(s)youselectednowappearintheSortOrderbox.Usethedoublepointedarrowalongsidethefieldnametochangethesortorderpriority.
Ascendingorderisassumed.Tochangetodescending,selectthefieldintheSortOrderboxandthenclickontheDescendingradiobutton.Observethe“Customorderbasedonavaluelist”option.Laterwewilldiscoverthatwecancustomizesortordersbasedonsomethingwedefineasavaluelist.
ObservetheUnsortbutton.
4. ClickSort.RESULT:Thefileissorted,youareinBrowsemodeandtheStatusareareflectthissortedstatus.
Tryit>Sorttherecordsbyjobtitleandthenbydateofhireindescendingorder.
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
34
Ifasortisineffectwhenafileisclosed,itwillbeineffectwhenitisreopened.(Thisisnottrueofdatabasesonasharedserver.)Tryit>Performasort,closethefileandthenreopenit.FastSortAlternatively,youcanperformasortusingtherightclickinWindowsandaCtrl‐ClickontheMac:1. InBrowsemode,movethecursortothefieldyouwishtosorton.2. Rightclick(Win)orCtrl‐click(Mac).3. Fromthecontext‐sensitivemenu,chooseSortAscending.
Tryit>Sorttheemployeesindescendingorderfordateofhire.
FileManagementtopicsBeforeweleaveBrowsemode,therearetwofilemanagementtasksweshouldvisit:
TheabilitytoexportdatafromaFileMakerfileand“SaveaCopyAs.”
ExportRecords…Ifyouwishtoexportdatafromyourdatabase,youcandosobyusingthecommandExportRecords…
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
35
1. 1.GotoFile ExportRecords…Youwillbeaskedtospecifywhereyouwishtosavethenewlycreatedfile.
FileMakercreatesopensaSavedialogbox,allowingyoutonavigatetowhereyouwishtosavetheresultantfile.Oftentimes,itwillsuggestaTabSeparatedTextfilebydefault.Therearemanyotherformatstochoosefrom,includingaFileMakerProfile.
2. GotoTypeandnoticethedifferentfiletypes.Selectthedesiredtype.
Noticetheotheroptionsthatareavailable.Youcanopenthisfileorcreateanemailwiththefileasanattachment.Remembertousetheoperatingsystem’smailprogram(OutlookExpressorAppleMail).
WhenyouspecifyExcel,theExcelOptionsdialogboxopensallowingyoutouseornotusthefieldnamesascolumnnames.YoucanalsonametheWorksheetandassigntitle,subjectandauthorinformationtoit.
3. WhenyouclickContinue…thefollowingdialogboxopens:
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
36
Bydefault,FileMakerofferstoexportthosefieldsonthecurrentlayout.
Toincludeothers,pulldowntheCurrentLayoutmenuandselectCurrentTable.
4. Selectthefieldstobeexported.Doubleclicktoselectandmove.5. Usetheup‐downarrowtotheleftofeachfieldnameintheFieldexport
orderlisttoarrangetheresultingcolumnsinthedesiredorder.
Tryit>GototheSalarylayoutandexportittoanExcelfile,exportingonlythefieldsFirstName,LastName,Salary,HireDateandBirthdate.
SaveaCopyAsAsdiscussedabove,thereisnoSaveoptionontheFilemenu,howeverthereisSaveaCopyAs….RatherthanyoutellingFileMakerwhentosavethefile,itperformsasaveeachtimearecordiscommitted.Withthatinmind,youmaywishtosaveacopyofyourdatabasebeforeyoubeginmakingchangestoitasabackup.
Oneofthechoicesis“clone(norecords).”Thiswillcreateacopyofyourfilewithallthetables,fields,relationships,layouts,andscriptsdefinedbutnodata.
Tryit.2. GotoFile SaveaCopyAs
Module3:WorkinginBrowseMode
Copyright©2009byMITIS&T.
37
3. Giveitaname,andspecifythedesktopaswheretosaveit.4. Underfiletype,choseclone(norecords).5. Openthefileyoujustcreatedandviewtheresults:visitFileDefine
Database…viewtheTables,FieldsandRelationshipstabs.6. Visitthedifferentlayouts.Observetheabsenceofdata.
Exercise6–Sorting,etc1. UsingFastsortandtheEmployeeRecordsdatabase,
• Whowashiredfirst?
• Whowashiredlast?
2. Whatarethetwowaystoenteragraphicorpdfintoacontainerfield?Whatisthedifferencebetweenthetwo?
3. Whatisasortconsideredpersistent?
4. CreateanExcelspreadsheetfromthedatabasewithjusttheFirstName,LastName,StreetAddress,City,State,Zip,andDateofBirth.
Module4:PreviewingandPrinting
Copyright©2009byMITIS&T.
38
Module4:PreviewingandPrinting
Inthismodule,wewill
ReexaminethevisualdifferencesbetweenBrowseandPreviewmode
PrintaFileMakerDocument
SaveaFileMakerdocumentasaPDF,asanExcelfile
CompareMacversusWindowsprintersettings
PreviewMode
InPreviewMode,weseethedocument,asitwillprint.Aswehavealreadyseen,differentobjectsarevisibleinPreviewmodethaninBrowse.Thisisbasedonafewconditions.
LayoutsthatareFormviewbydefaultshowasinglerecordinBrowsebutallrecordsinthefoundsetinPreview.(ComparetheLabellayoutinBrowsevs.Printmodes.)
Partsofasubsummaryreportarenotdisplayedunlessthesortisineffect.PriortoFileMaker10,Browsestilldisplayedthereportwithoutdividedintoparts.Now,aslongasthesortisineffect,partsaredisplayedinPreviewandinBrowse.(ComparethereportinBrowseandPreviewpriortoclickingontheSalaryAveragesbyTitlebutton.NoticethebuttondoesnotappearinPrevieweither.NowclickthebuttoninBrowsemodeandobservethereportinBrowseandPreview.)
Forreportsthatareformattedintomultiplecolumns,thecolumnsareonlydisplayedinPreviewmode.(CompareSampleReport–TwoColumnExampleinBrowseandPreviewmodes.)
Fieldsorobjectsthathavenon‐printingappliedtothem(e.g.thebuttonintheSubsummaryReport).
Printing
Whenitcomestimetoprintadocument,FileMakerofferssomeadditionaloptions.Noticethatyoucanprintthe
Recordsbeingbrowsed(thefoundset), thecurrentrecord oracopyofthelayoutwithoutanyrecordshowing:
Togettothemenuwherethisisestablished,inWindowsgotoFilePrintandinthedialogbox,pulldownthePrint:menu.:
Module4:PreviewingandPrinting
Copyright©2009byMITIS&T.
39
OntheMac,gotoFilePrint.FromthemenubelowPresets:chooseFileMakerPro.Thisautomaticallyopensupthedialogboxtodisplaythethreeradiobuttons:
SaveasPDFSavingasPDFcanbeperformedinbothPreviewmodeandBrowsemode,sometimeswithdifferingresults.Inbothplaces,thereistheoption(apulldownmenulikeabove)tosaveeithertheRecordsbeingbrowsed,Currentrecord,oraBlankrecord,showingfields.
InBrowsemodetosaveasPDF,gotoFileSend/SaveRecordas…PDF.
InPreviewmodesimplyclickonthebutton.
Module5:LayoutBasics
Copyright©2009byMITIS&T.
40
Module5:LayoutBasics
“Ifthetablesformtheheartofadatabase,layoutsgiveitaface.”
Coffey&Prosser,
FileMakerPro,TheMissingManual
Inthismodulewewill:
Definethetermlayout
LearnhowaStandardlayoutiscreated
WhatisaLayout?
InFileMakeryouusuallyhaveachoiceofscreens,eachonesetuptodisplayyourdatainaslightlyorsometimesdramaticallydifferentway.Regardlessofappearance,allFileMakerscreensarelayoutswhichcanbecreated,editedandevendeletedinLayoutMode.Somelayoutsaredesignedtobeusedasdataentryscreenswhileothersarebettersetuptodisplayaggregatedatainareport‐likeformat.WhetheritisreferredtoasaLayout,aReport,oraFormLetter,inFileMaker,itisstillcalledalayout.Layoutsmayrangefromtheverysimpletothecomplex,thelatteremployinganarrayofcolors,interactivebuttons,thedisplayofwebpages,thecompartmentalizationofinformationintotabs,andothergraphicaltoolstohelpusersbetternavigatethroughortointerpretdata.
DataissharedinaFileMakerfilebyalllayoutsevenifsomelayoutsdon’tshowallthefieldsstoredinthefile.ConsideryourFileMakerdatabaseasingle‐roomstructure.Theexteriorwindowsanddoorsareequivalenttoyourlayouts.Ifyoucreatedanewwindow,youarestilllookingatthesamecontentsinsidethestructure.JustaswhenyoucreateanewlayoutyouarestillseeingthesamedatainFileMakerthatyouwouldseefromanotherlayout.Ifwhilelookingatthedatafromonelayoutyoudecidetochange,deleteoraddnewdata,thatdatawillchangeineveryotherlayoutinthecurrentfile.Howlayoutsdisplaydatagetstobeatrickiermorecomplexthingwhenmultipletablesi.e.arelationaldatabase,areatplay.Eventhen,thelayoutisthevehiclethatmakesitpossibletoviewthisrelationaldatadrawnfromnumeroustables.
Muchlikealistoffiles,FileMakerdisplaysadrop‐downmenuoflayouts.
Toswitchfromonelayouttoanother,simplyselectitfromthelist.
Module5:LayoutBasics
Copyright©2009byMITIS&T.
41
Inlayoutmode,astatustoolbaracrossthetopofthescreenhasiconsforthevarioustoolsthatareusedincreatinglayouts:
Intheworkareaofthewindowyouseetheconstituentsofthelayout.Layoutsarecomprisedoffields,textandgraphics.
ThepalettemarkedInfodisplaysinformationaboutwhateverobjectiscurrentlyselectedandcanbeusedforresizingobjects,namingthemandanchoring
Exercise6–AccessingExisitingLayouts
1. InBrowsemodeperusethedifferentlayoutsthathavebeencreatedforthedatabase.
Module5:LayoutBasics
Copyright©2009byMITIS&T.
42
2. IntheSalarylayout,observehowfieldsaredesignated.GotoLabelsandcompare.InLabels,youareseeingsomethingcalledMergeFields.IfyouhavecreatedamailmergeinWordyoumaybefamiliarwiththeseanglebrackets.ComparelayoutmodeforlabelswiththefinalproductinPreview.Observehowthelabelisrepeatedthreeacrossandasmanydownastherearerecordsinthefoundsetandwhatfitsonthepage.
3. ViewtheSubsummaryreportandnoticetheappearanceofadditionalreportparts.Noticealsothebuttonintheupperright‐handcorner.Doubleclickonthebuttonandnoticeitopensascriptdialogbox.Thisiswhatisexecutedwhenyouclickonthebutton.ClickCanceltoclose.
4.
Exercise7:CreatingaNewLayoutLayoutmodeisarichenvironmentoftoolsandtoolbarsthatarefamiliarifyouhaveworkedwithgraphicsprogramsbefore,butthisrichenvironmentcanalsobeoverwhelmingforthepersonwhohasneverworkedwithvisualorgraphicobjectsbefore.
1. TocreateanewlayouteitherclicktheNewLayout/ReportbuttonorgotothemenuLayouts NewLayout/Report,orusethekeystrokeCtrl/⌘+N.
RESULT:ThisopenstheNewLayout/Reportdialogbox:
Takeaminuteandsamplethedifferentlayouttypesanditsresultingpreview.
Module5:LayoutBasics
Copyright©2009byMITIS&T.
43
ThisdialogboxusedtobereferredtoastheReport/LayoutAssistantorWizardbecauseitisdesignedtostepyouthroughtheprocessofmakingalayout.Itallowsyoutocreatealayoutinseconds,butanoteofcaution:Justbecauseitiseasytocreatelayoutsshouldnotbecausetooverindulgeinthecreationofnewlayouts,particularlywhenadatabaseisusedbymanypeople.Ifyouneedaparticularlayoutbesuretocheckifonedoesn’talreadyexistthatmayserveyourneeds.Andifyoucreateanewonethatrendersapreviouslayoutobsolete,don’tforgettodeletetheolderlayout.ThiscanbedonebygoingtoLayouts DeleteLayout.
2. Showrecordsfrom:shoulddisplaythenameofthetablethatlayoutisbasedon.Inourcase,thatisEmployeeRecords
Everylayouthasacontext,thatisitisbasedonaspecifictable.Inourdatabasethereisonlyonetable,sothisisnotanissuenowbutwillbecomeimportantwhenwehavemultipletablesinadatabase.Therefore,EmployeeRecordsiscorrecthere.
Everylayoutshouldbegivenauniqueanddescriptivename.Ifyouareamemberofagroupusingadatabase,youmaywishtodecideuponanamingconventions.Forexampleatwoletterprefixonlayoutnameswoulddesignatethe“owner”orcreatorofthelayout.
3. WewillcallthislayoutClassExample.ClickthecheckboxforIncludeinLayoutmenu.
4. Wewillbeginbycreatingasimplestandardlayout.ChooseStandardformfromthelayouttypelistandclickNext>
TheNewLayout/ReportSpecifyFieldsdialogboxopens.
Module5:LayoutBasics
Copyright©2009byMITIS&T.
44
5. Hereyouchoosethefieldsthatwillbeincludedinthereport.Selectthefollowingfieldbydoubleclickingonthefieldnamesintheleft‐handscrollbox,orselectandclickMove.
EmployeeID LastName FirstName MITAddress StreetAddress City State Zip Email
6. Usethesmallnorth‐southarrowicontotheleftofthefieldnametodragtheEmailfieldbeforetheEmployeeId.YouwillseethischaracterthroughoutdialogboxesinFileMaker.Itallowsforthereorderingofelements.
7. Whenallthefieldsarespecifiedandinthedesiredorder,clickNext>
Module5:LayoutBasics
Copyright©2009byMITIS&T.
45
RESULT:TheNewLayout/Report,SelectaThemedialogboxopens.Thesearethemesforreportlayouts.Asitsays,athemesetsbackgroundcolors,textsize,textcolorandstyle.
8. Perusethedifferentthemesthatareavailable,viewingthepreviewedsampletotheright.Somethemesaremoreappropriateforviewingonascreenwhileothersareforprinting.
FileMaker10sawarefurbishingoftheoldthemesandthecreationofnewones.
9. Chooseathemeofyourliking,orjustgowithStandard,andclickFinish.
RESULT:Youareinlayoutmodeviewingtheconstructsofyournewlayout.
10. ClickontheExitLayoutbutton.
RESULTS:YouareinBrowsemodeviewingyournewlycreatedlayout.TheStandardlayout,bydefault,isinformview,thatis,onerecordperscreen.Thelayoutcontainsthefieldsyouspecified.Thefieldsareleftalignedandtheirrespectivefieldlabelsarerightaligned.
11. Perusetherecordsofthedatabaseusingthislayout.
Module6:Resources
Copyright©2009byMITIS&T.
46
Module6:Resources
FileMakerresourcesareespeciallyplentifulforreferenceandself‐study.
Oneofthefirstplacesyoumaywanttolookisinthefilesthatgetdownloadedwiththesoftware.Thereisbothatutorialwithexamplefilesandareferencemanual.LookinthefoldermarkedEnglishExtras.BothonWindowsandtheMac,gotoFileMaker10folderwithinProgramFiles(Win)orApplications(Macx)thereisafoldercalledEnglishExtras.WithinitisafolderElectronicDocumentation.Therearetwopdfs:Thefmp10_tutorialandfmp10_users_guide.
AhandyandveryreadablebookonFileMakerisSusanProsser&GeoffCoffey’sFileMaker10:TheMissingManual.Whymissing?Thisisthebookthatshould’vebeenintheboxwiththesoftware.ItisfromO’ReillyPress.O’ReillypressbooksareavailablethroughSafari(notthebrowse,theelectronicbookshelf)andcanbeaccessedbyallMITaffiliatesinthelibrary’sVERA,theVirtualElectronicResourceAccess.Gotohttp://libraries.mit.edu.SelectVERAandinthesearchslottypeSAFARI.ClickontheSafarilinkthatcomesup.Youwillarriveatapagewhereyoucansearchforthebookorbytopics.Fromthelistofhits,selectthebookandnavigatetothetableofcontents,orwhereveryouwishtogointhetopic.
FileMaker,Inc.produces(ofcourse)thefinestself‐studymaterialsavailable.TheirFileMakerTrainingseriesisaself‐pacedlearningpackagedesignedfortheindividualwhowishestobuildknowledgeandskillsinthedesignanddevelopmentofFileMakerdatabases.ThesearethetrainingmaterialsthatwillprepareonefortheFileMakerDeveloper’scertificationexam.
JohnMarkOsborne,theauthorofbooksandtrainingvideos:http://www.databasepros.com/training.html
GeoffCoffeyhasafinewebsiteandblogcalledSixFriedRice.