Pathagoras Help System

Linking to External Databases via IDB Masks

Linking to External Databases via IDB Masks

Previous topic Next topic  

Linking to External Databases via IDB Masks

Previous topic Next topic  

Link to .csv file using an Instant Database Mask

   This is an excellent alternative to I. above. With this method, you need to 'things.'

1.An Instant Database Mask with variable names paralleling those in the documents you wish to create; and
2.An external database.

The link to the external data is indirect and does not involve inserting fields or complicated links like that. Indeed, the variables within your document remain plain text. You will use the tools provided by your external database to export data into a comma separated value ('.csv") file which Pathagoras can easily read and process.

For it to work, however, the order of fields of exported data must be in the same order as the variables of the mask. This should be easy to accomplish. Most programs allow you to select a collection of fields, and to set up export rules that lead to the creation of the desired.csv file. You should even be able to save your selections as an 'export template' so that it is easy to regenerate the .csv (or .txt or .dat, etc.) file whenever you want.

.csv file: This may be a new term, but it is not a complicated concept. ".csv" means 'comma separated value' and refers to the way the data in your primary database is exported to a file. It is sometimes called "comma delimited." It means that the values contained in the is stored in individual rows, one per record, with the breaks between each field of data in each row indicated by a comma. Here is what a comma separated file might look like:

Jane, Doe, 1234 Main Street, Jacksonville, FL, 32211, red
Harry, Johnson, 914 Oak Tree Lane, Ft. Collins, CO, 78765, white
Roy, Lasris, 117 Chisman Landing, Seaford, VA, 23696, blue
. . .

 

Typically the file extension of a .csv file is '.csv' (hence its common name) but some programs export to to '.txt' files and others to '.dat' files. So long as the data is truly 'comma delimited,' you are good to go.

   To import the external data into an Instant Database Mask, recall the prepared Mask from the drop down list in the upper right of an Instant Database screen. Then, 'import' data from the comma separated value (.csv or .txt) file. The steps are outlined in the following paragraphs. But first, a few preliminary notes:

Pathagoras can provide instant access to up to two .csv files.
Pathagoras can provide you quick access (but navigation is required) to an unlimited number of .csv files.
A little setup is required for the instant access part. They are, however, one-time setups and will not be repeated.

   Setup steps:

   To create the input form 'mask' that contains the variables for the typical document you want to create, see Create Mask in this Manual. It will provide all the information you need for the creating, care and feeding of masks. (If you already have an established mask that you want to use, you are practically ready to roll.)

   Create a comma separated value file from you external database. Here are the rules:

The fields you export from your external database must be in the same order as the variables in your mask. But since the start point of the expected import need not be at the top of the mask, it just needs to be in the same order as one section of the mask.

Typically the section of the mask into which you will be importing data will be the 'whole' mask, but it need not be. Maybe it will pair up with the first 10 variables of the mask, in which case the you will create a csv file which contains 10 fields. Maybe you want to allow the user to complete the first 10 fields of the mask, and you want to be able to import data from field 11 to the end. Pathagoras can do either. Just keep in mind that the data must pair up with the desired section of the mask and the only way to accomplish that is to keep the data in the same order as the variables in the mask you wish to pair up with.

You may have to spend a little bit of time getting the data fields properly set up, but once you do, your database program should let you save it for repeat uses. (As you add more clients and customers, you will want to regenerate the file so the records are available to Pathagoras. Saving the 'export template' or whatever your database program calls it will be critically important.)
The csv file can contain an unlimited number of records -- lines -- from one to a million.
The extension of the exported file can be anything (.csv, .txt, .dat, etc.) but the file must be a true comma delimited value file.

   Third, in Pathagoras, create a pointer to the export file. This is done on via the Instant Database tab of the All Settings and Options toolbox (1). Toward the bottom of that window, click the External Database Content tab (2).

Click to enlarge.

(1)Select the Instant Database tab in the All Settings screen.
(2) Select the External Database tab in the sub-window.
(3) Check the 'Offer .csv files for import' box and
(4) navigate to the file.
Your selection will be saved in the text box to the left of the selector button.
If you wish to add a second data import link, use the lower section (5).

  End user steps:

   After the setup steps are complete, assemble a document. Of course, for purposes of this topic, the document should contains variables that are reflected in the Mask we want to use.

   Display the Instant Database screen and select the mask from the Mask list (upper left corner of the IDB form).

   Complete the data that you know is not going to be imported (or do this second). When you are ready to import data from the external file, click Power Tools and then click the 'Import' button in the Power Tools section. From the resulting screen, select the nature of the file from which you wish to import data. In this case, select the last entry, "From prepared .csv file.' this screen will appear, with the data from the first field of the primary import file already displayed.

idbimport2

Notes:        

While the primary import file always displays by default, you have instant access to the secondary import file (if set).
You can navigate to any other data containing file, even if not registered.
You can use the controls at the right to designate the mask variable you wish to start import into. (Just as a reminder: You can reserve the first X number of fields in your mask for keyboard input, and take advantage of the external data to fill in the rest, or as much of 'the rest' as exists in your import file. Just select from the controls at the right the first  variable in the mask you wish to begin receiving the imported data.)

Click the button_next_h button in the menu bar to continue reading.