home *** CD-ROM | disk | FTP | other *** search
- >a3:" LESSON7 -- Using Associated Files
- >a6:" Throughout these lessons we have seen how two separate files
- >a7:" can be displayed simultaneously to the screen. This capability is made
- >a8:" possible by Perfect Calc's unique system of 'multiple buffers', which
- >a9:" allows more than one spreadsheet to be held in machine memory at one
- >a10:" time. As we have mentioned, up to seven associated spreadsheets can be
- >a11:" active simultaneously, permitting us to switch back and forth between
- >a12:" them with ease, copying and sharing data as needed (this feature is
- >a13:" only available with Perfect Calc!).
- >a15:" In this lesson we are going to examine how spreadsheets can be
- >a16:" linked together and made to share data--a feature we are sure you will
- >a17:" agree represents one of the most remarkable advances ever to appear
- >a18:" in personal computer software. For the first time, a powerful set of
- >a19:" programming tools are available to the typical personal computer user
- >a20:" in a 'natural language'. With this feature you will find yourself
- >a21:" doing programming without ever knowing that you are!
- >a24:" When a spreadsheet is copied from disk to machine memory, it is
- >a25:" placed in a temporary work space called a 'buffer', which serves to
- >a26:" isolate it from other spreadsheets and internal program functions.
- >a27:" Thus, changes and recalculations made to a spreadsheet in one buffer
- >a28:" do not affect files held in other buffers, a necessary condition if
- >a29:" the integrity of spreadsheet files is to be maintained.
- >a31:" However, sometimes it is desireable or even necessary to have
- >a32:" spreadsheets work together sharing both functions and data. For
- >a33:" example, if we are preparing a tax report, it might be convenient if
- >a34:" the 'tax' spreadsheet could directly reference positions on other
- >a35:" spreadsheets that calculate our income, net worth, etc., thus elimin-
- >a36:" ating the need for manual transfer of data.
- >a38:" Perfect Calc provides a means of doing this through a process
- >a39:" called 'linking' or 'associating' files.
- >a45:" Consider the following formula: b17 = b18 + income[c6]
- >a46:"
- >a47:" This formula instructs Perfect Calc to calculate the sum of two
- >a48:" variables, one of which is held in the current buffer and one in a
- >a49:" buffer called 'income'. When calculating this formula, Perfect Calc
- >a50:" will go to the spreadsheet in the buffer 'income' and retrieve whatever
- >a51:" value is located in position 'c6', afterwards returning to complete
- >a52:" the calculation in the current buffer. (Note the syntax of the cross-
- >a53:" reference: the variable is enclosed in square brackets and follows
- >a54:" immediately after the name of the buffer containing it.)
- >a56:" The referenced buffer must exist (i.e. be active in memory) and
- >a57:" must hold the correct spreadsheet. If it does not, the formula will
- >a58:" either compute to 'Error!' or produce a faulty value.
- >a59:" However, more is needed than just cross-references between the
- >a60:" spreadsheet files. Spreadsheets which reference each other must be
- >a61:" 'linked' or 'associated' in such a way that a recalculation in one
- >a62:" automatically causes a recalculation in the other. Otherwise data in
- >a63:" the non-current spreadsheet may become outdated, if it is not regularly
- >a64:" updated by switching to it and forcing a recalculation. Remember, a
- >a65:" recalculation normally affects only the spreadsheet in the current
- >a66:" buffer, and no other.
- >a68:" Perfect Calc provides a means of doing this through its
- >a69:" ASSOCIATE FILES Command, which will link the files such that a recalcu-
- >a70:" lation in one will cause a recalculation in all.
- >a72:" Linked or associated files exist in a special 'dependency' to
- >a73:" each other. The first file (considered the master file) is dependent
- >a74:" upon the file, or files, which follow it. Dependency means that the
- >a75:" files are recalculated in a specific order: that is, the 'depended-on'
- >a76:" files are always recalculated BEFORE those files which depend on them
- >a77:" are recalculated.
- >a79:" HOW TO LINK FILES
- >a81:" Given several files which reference each other, you would place
- >a82:" the cursor in the file which would depend on all the other files, and
- >a83:" type:
- >a84:" Control--x a
- >a86:" Perfect Calc would respond with the message:
- >a88:" "Associate file: <CR> "
- >a89:" You would then type the name of the first file which the current
- >a90:" file would 'depend on', and which would have to be recalculated before
- >a91:" the current file is recalculated.
- >a93:" Perfect Calc would 'associate' the two files and switch you to
- >a94:" the file that you have just named. If this second file depends on yet
- >a95:" a third file, you would give the ASSOCIATE FILES Command again, linking
- >a96:" the third file to the second. Up to seven spreadsheets may be linked
- >a97:" consecutively in this way.
- >a99:" To preserve the 'associative' links you must save each file in
- >a100:" turn, using the SAVE FILE Command (Control--x Control--s).
- >a102:" When reading such a chain of linked files into memory, either when
- >a103:" beginning Perfect Calc or by using the FIND FILE Command, you have only
- >a104:" to name the first file in the chain. Perfect Calc will automatically
- >a105:" read the remaining linked files without your having to specify them.
- >a107:" Associated spreadsheets, as we will see shortly, allow us to
- >a108:" handle data in ways that previously were not possible. In the remain-
- >a109:" der of this lesson let us examine a set of linked spreadsheets to
- >a110:" demonstrate exactly how this feature works.
- >a112:" At this time move the cursor to line 121 and give the CREATE
- >a113:" TWO WINDOWS Command (Control--x 2).
- >a115:" (Do not synchronize the windows.)
- >a116:" Move the cursor to the top window, using the OTHER WINDOW
- >a117:" Command (Control--x o ).
- >a119:" Type the FIND FILE Command (Control--x Control--f), followed
- >a120:" by the filename: 'b:Rental.pc'.
- >a121:" In the next few moments Perfect Calc will read into machine
- >a122:" memory three separate, but 'linked', files:
- >a124:" 1) 'Rental.pc'
- >a125:" 2) 'Unit1.pc'
- >a126:" 3) 'Unit2.pc'
- >a128:" When the read operation is complete, Perfect Calc will display
- >a129:" the 'unit2.pc' file in the top window. Use the SWITCH BUFFERS Command
- >a130:" to go to the first buffer in the chain, 'rental'.
- >a131:" As you can see, 'Rental.pc' is a simple rental property manage-
- >a132:" ment program, which calculates the total monthly income generated by
- >a133:" various holdings of property, in this case 'Unit1' and 'Unit2' .
- >a135:" Move the cursor to position 'b5' and examine the formula contain-
- >a136:" ed there:
- >a137:" Formula: unit1[b19]
- >a139:" This tells Perfect Calc to display in position 'b5' whatever
- >a140:" value is held in position 'b19' of the spreadsheet buffer 'unit1' .
- >a141:" Let us switch to buffer 'unit1' to see what data is being refer-
- >a142:" enced in its spreadsheet. Use the SWITCH BUFFER Command
- >a143:" (Control--x b), followed by the buffer name: 'unit1' .
- >a145:" Perfect Calc switches us to buffer 'unit1' . The spreadsheet
- >a146:" here contains income and expense figures for a three bedroom house,
- >a147:" from which it will calculate a 'Cash Flow' for the property. Move
- >a148:" the cursor to position 'b19', and examine the formula which accomplish-
- >a149:" es this:
- >a151:" Formula: b8 - (b16 + b18)
- >a153:" This formula instructs Perfect Calc to subtract 'Total Expenses'
- >a154:" and 'Mortgage Payment' from 'Monthly Rent', producing a figure that
- >a155:" represents either a positive or negative 'Cash Flow' ('b19'). To see
- >a156:" how this value is referenced by the previous spreadsheet, 'rental.pc',
- >a157:" enter the following values for January:
- >a158:" Monthly rent: $550.00 Maintenance: $35.00
- >a159:" Repairs: $45.00 Taxes: $110.75
- >a160:" Insurance: $12.50 Mortgage: $350.00
- >a162:" Perfect Calc automatically calculates 'Total Expenses' and 'Cash
- >a163:" Flow'. Since this file is 'linked' to the file 'Rental.pc' which
- >a164:" depends on it, we can assume that a recalculation has already occurred
- >a165:" in the other file. Switch back to the buffer 'Rental' to see what
- >a166:" has happened. (use Control--X b to SWITCH BUFFERS)
- >a168:" As you can see, 'Rental.pc' has automatically referenced a value
- >a169:" for January 'Cash Flow' from 'Unit1' and, using this value, has calcu-
- >a170:" lated figures for 'YEARLY TOTALS' and 'Monthly Cash Totals'.
- >a172:" Move the cursor to 'b6' of 'Rental.pc' and examine the formula
- >a173:" at this position:
- >a174:" b6 = unit2[b19]
- >a176:" This formula is identical to the previous one, except that its
- >a177:" value is taken from the spreadsheet in buffer 'Unit2' . Switch to
- >a178:" this buffer using the SWITCH BUFFER Command (Control--x b). This
- >a179:" spreadsheet is identical to the spreadsheet in buffer 'Unit1', except
- >a180:" that it calculates rental income for a 'warehouse' . Enter several
- >a181:" sample values in this spreadsheet, afterwards switching back to the
- >a182:" master spreadsheet in buffer 'rental' .
- >a184:" As before, 'rental.pc' has already obtained a 'Cash Flow' value
- >a185:" from 'unit2.pc' and has recalculated its formulas accordingly.
- >a187:" As we mentioned, a particular order of dependency exists between
- >a188:" the spreadsheet files, which determines their order of recalculation.
- >a189:" This relationship is reflected in the Buffer Directory, which displays
- >a190:" the 'links' existing between the spreadsheets.
- >a192:" Move the cursor to the bottom window, using the OTHER WINDOW
- >a193:" Command (Control--x o ), afterwards typing the CREATE ONE WINDOW
- >a194:" Command (Control--x 1). Now, give the BUFFER DIRECTORY Command:
- >a196:" Control--x Control--b
- >a198:" The Buffer Directory will display at the bottom of the screen,
- >a199:" overwriting some of this text. If necessary, scroll the window to
- >a200:" erase the directory, or give the REDRAW WINDOW Command (Escape...
- >a201:" Control--L ).
- >a203:" In the Buffer Directory 'arrows' indicate the links between
- >a204:" spreadsheets. 'Rental' is linked (i.e. depends on) 'Unit1', which
- >a205:" is linked to 'Unit2'. You should note that we are currently using four
- >a206:" different spreadsheets in memory. Amazing!
- >a207:" {Type Escape...Control--L}
- >a209:" As you perhaps realize 'Unit1' does not really depend on 'Unit2'.
- >a210:" The only dependent spreadsheet in the chain is 'Rental.pc', which
- >a211:" depends on all the files which follow it. The files 'Unit1.pc'
- >a212:" and 'Unit2.pc' could therefore have been linked in reverse order,
- >a213:" and a correct recalculation would have resulted. However,
- >a214:" this will not always be the case, and we can easily conceive of
- >a215:" instances in which the order of recalculation among a series of files
- >a216:" IS important and must be strictly followed.
- >a218:" One can see how simple and convenient associated spreadsheets are
- >a219:" to use. One master spreadsheet, working in conjunction with two
- >a220:" subsidiary spreadsheets (a total of six subsidiary spreadsheets would
- >a221:" have been possible), results in a very orderly and intelligible format,
- >a222:" for what might otherwise have been a hodgepodge of loosely connected
- >a223:" financial data, involving awkward and inefficient manual transfers of
- >a224:" information. The associated spreadsheet files, while maintaining their
- >a225:" functional integrity, operate in a unified and harmonious fashion.
- >a227:" This concludes our lesson on 'Using Associated Files' . However,
- >a228:" before we end this series of lessons on learning to use Perfect Calc,
- >a229:" we wish to conclude with a few important 'suggestions' regarding the
- >a230:" use of Perfect Calc, which we are sure you will find helpful.
- >a232:" At this time delete entire machine memory, and proceed to the
- >a233:" concluding lesson, 'lesson8.pc' with the FIND FILE Command.
- >a235:" END OF LESSON7
- >a1
-