CSPro: How Do I Use LookUp (External) Files?

CSPro: How Do I Use LookUp (External) Files?


Hello CS Pro users and welcome to CS Pro Video Tutorial How Do I Use a Lookup File? A lookup file is a useful tool to get
and/or verify information from other files. Lookup files must be fixed-format text files. Lookup files may contain data such as geocodes, industry codes, occupation codes,
sample weights, etcetera. For example, our cartography staff
could give us a file of valid Geo codes. When data are being entered, we can check the Geo codes
being entered against that file. If the codes are found in the file, it is a valid Geo code. If the codes are not found in the file, it is an invalid Geo code and we would need to put out an error message. Our subject matter specialists could give us
a file of industry codes. Remember, for CS Pro we need to have
that file in fixed-text format. We could use that file to validate industry codes. When data are entered for industry, We check these codes against the data file – the lookup file. If the code is found in the lookup
file, that is a valid code. If the code is not found in the file, it is an invalid code and we would put out an error message. We will now use an external file as a lookup file
to verify a four-digit occupation code. We often use the term “lookup file” when using external files in CS Pro because the most common use for an external file is to look up information in a reference file. That is a file containing reference data
which is not part of our input data file. This example uses CAPI, however,
lookup or external files, work the same way for all modules of CS Pro. To use this file as an external file we will need to create a CS Pro data dictionary that describes the data in the file. Select: File, New from the Main Menu. Select the “Other” tab. Click on Data Dictionary. Click OK CS Pro will ask for the new dictionary name. Navigate to the folder containing your application and enter a name for your dictionary. I will give the dictionary the name ISCO-08 Click on Save and CS Pro creates
our dictionary template. We modify this template so the dictionary describes the occupation code file by by first clicking on the plus (+) to expand the dictionary tree so we can see all the items right-clicking on the ID item ISCO_08 ID, and selecting modify item, changing the item label ISCO_08 to Occupation Code. Pressing Enter and changing the item name to LU_OCCUPATION_CODE pressing Enter then adding a new item occupation description to the ISCO_8 record by right-clicking on the record Add Item. The item label is Occupation Description. The item name will be LU_OCCUPATION_DESCRIPTION. I am using the prefix LU to indicate that
this item is from a lookup file. I’ll press Escape to indicate that I am
done entering items. Since we only have one record type,
we do not need a record type indicator. I change the length of record type to 0. Entering a 0 tells CS Pro that we do not
need a record Type Indicator. I change the length of Occupation Code to 4 so it matches the occupation code in the occupation code file. I change the length of Occupation Description to 80 and the data type to Alpha so it matches the data in the Occupation Code file. When these changes are complete, this is how the Occupation Code data dictionary, ISCO-08.DCF, should look. Let’s compare the dictionary with the file. Occupation Code is a four-digit, numeric item. Occupation Description is an 80 character Alpha item. Now that we have the dictionary for our lookup file, we will add Occupation to our MyCAPI Data Dictionary. Remember we are adding the Occupation Code to the MyCAPI example application. In our logic we will check to see if the occupation code is valid. That is, is it in the lookup file. If it is, our application will issue a message with the Occupation Description. If the Occupation Code is not found
in the Occupation Code file, our application will issue a message stating that the Occupation Code was not found in the Occupation Code file. The message will include the Occupation Code and the path and name of the Occupation Code file. We will now add the code to the MyCAPI application. To check Occupation. Open the MyCAPI Data Entry application Go to the Dictionary panel by either clicking on the Dictionary button on the toolbar or clicking on the Dictionary tab at the bottom of the Dictionary tree. I will add Occupation to the Person Record. Right click on Person Record, Add Item, and add the item label of Occupation. I will accept the default item name of Occupation. In this case our Occupation Codes are four digits so I will need to make the length, 4. I will accept the default on the remaining attributes. I am done adding Occupation. Press Escape Switch to Forms by clicking on the forms button. Add Occupation to the form by dragging Occupation from
the dictionary tree to the form. We now add the Occupation Coded Dictionary
to our application. If you are not already at the Dictionary tab, go to the Dictionary tab by clicking
on the tab at the bottom of the tree. We add the external dictionary by clicking on: File, Add Files. This brings up the dialog box
to enter the name of the file. Note that it is important to use
Add Files rather than Open. Add Files adds the dictionary file
as an external file. Open does not. Navigate to the application folder, select the External Dictionary. Verify the Dictionary Name and if it is OK, click OK. CS Pro adds the Dictionary to the
application as an external file. We are now ready to write to the CS Pro Code,
to check the Occupation Code. Let’s examine this code. The code is in the post, PROC of Occupation, since the check has to be done
after Occupation is entered The Occupation Item contains the Occupation Code
that has been entered. Occupation is represented in this code
by a dollar sign ($). Remember in CS Pro, dollars ($) can substitute for the name
of the item in the current PROC. The value of the Occupation Code needs
to be given to the Lookup File. After this assignment, LU_OCCUPATION_CODE contains the value entered for Occupation. The lowercase function searches the external file for the value contained in LU_OCCUPATION_CODE. The parameters for the load case function are: 1) The name of the dictionary
describing the external file, and 2) An item list containing
the ID items of the external file. CS Pro will search these items to find a match. In this example, the ID item is LU_OCCUPATION_CODE. Let’s run the program. We will only look at the parts related to Occupation. Run the program by clicking on the
traffic light icon on the toolbar. The Define File Associations dialog box will appear. Input Data File is the name of the file that
contains the data we are entering. Here, I am calling it MyCAPI_Test.dat. External file, ISCO_08_DICT is the name of the data dictionary
that describes the external file. CS Pro gets the name from the External Dictionary defined in the applications data dictionary section. Here we enter the name of the file containing the external data. It is not the name of the data dictionary it is the name of the file the dictionary describes. It is separate from the input file. In this example we are using ISCO_08_4.prn, the reference file containing the
4 digit ISCO Occupation Codes. Once we have entered the file names in the
Define File Associations dialog box, we click OK and begin entering data. I will go directly to the Occupation item
and enter 5142 for the Occupation. Our program places the value entered for Occupation in the ID item LU_OCCUPATION_CODE
of our external file. The loadcase function then searches the file for the value contained in LU_OCCUPATION_CODE. Occupation 5142 is found in the external file and the message is issued. I will enter 1234 for the occupation. Our program places the value entered for Occupation in the ID item of our external file. The loadcase function then searches the
external file for a match to this value. This time it does not find an Occupation Code 1234. The program issues the message that
the occupation code was not found. This video has given a brief introduction to using external, or lookup files, in CS Pro. Please check the CS Pro Helps
for more information on external files. There are many uses for external files in CS Pro. The CS Pro Examples folder contains several programs which makes use of external files. Please examine these programs for various ways you can use external files in CS Pro.