<:#1704,4200><:f240,6Times New Roman,0,0,0>A one-to-many relationship is where one record in one file relates to one or more records in a second, detail file. An example might be cities within a state. The state record could have one or more related city records in a
different file.
@Normal@<:#240,4200>
@Normal@<:#2272,4200><:f240,6Times New Roman,0,0,0>How can you display this relationship in Approach? Generally, the one to many relationship will be displayed by a form with a repeating panel or with a report that includes a summary panel. Given the example of States and Cit
ies within States, the following are instructions on how to set up both a form and a report.
@Normal@<:#284,4200><:f240,6Times New Roman,0,0,0>
@Normal@<:#1988,4200><:f240,6Times New Roman,0,0,0>A one to many relationship displayed on a form uses a repeating panel. The form is based on the main record. The repeating panel is based on the detail records. In each form only the detail records that are related to the cu
rrent main record will be displayed in that form's repeating panel.
@Normal@<:#284,4200><:f240,6Times New Roman,0,0,0>
@Normal@<:#586,4200><+!><:f240,BHelvetica,0,0,0>Displaying a One-to-Many Relationship on a Form<-!><:f240,6Times New Roman,0,0,0>
@list@<:#1136,4200>1. Join the two databases together. Again, with the example of states, we might have two files with the following fields:
@list@<:#852,4200>2. Go into Approach's design mode by clicking on the pencil icon in the toolbar.
@list@<:#284,4200>
@Normal@<:#1642,4200><:f200,FArial,0,0,0> <:f200,FArial,0,0,0>The two files are named <+">State<-"> and
<+">Cities<-">. They are joined on the field <+">St_Abbr<-">, the 2 letter abbreviation of the state name.<**><:f200,FArial,0,0,0><**><+"><:f200,FArial,0,0,0>State<-"> also contains a field for the full state name,
<+">St_Name<-">. <+">Cities<-"> also contains a field for the city name,
<+">City_Name<-">. <:f200,FArial,0,0,0>
@list@<:#284,4200>
@list@<:#2556,4200>3. Select Create Form from the Design Menu. Add the fields for <+">State<-">. The file name that's showing when you click 'okay' from this dialog is the main file for the new form. Because we're only adding the
<+">State<-"> fields, the <+">State<-"> file should still be selected. If not, make sure
<+">State<-"> is showing in the dialog and click okay to create the form.
@list@<:#1988,4200>4. The new form is drawn out using just the two fields from <+">State<-">. Switch to Browse by clicking on the Browse button in the toolbar. Notice as you browse the records how the information from just one
<+">State<-"> record is shown on each form.
@list@<:#3124,4200>5. Switch to Design mode again. From under the Design Menu select Add Repeating Panel. When the dialog for the repeating panel comes up, make sure the panel is based on the
<+">Cities<-"> file. Move just the <+">City_Name<-"> field to the right side of the dialog; this is the only field that will display in the repeating panel. Leave all other panel options set to the defaults. Click okay and the panel will be added to the
form.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@Normal@<:#1440,4200><+"><:f><[>Design Tip: If you need to move the repeating panel, click within with the panel with the right mouse button. The panel is highlighted in blue. While still holding down the button you can move the panel to where you'd like it placed on the for
m.]<-"><:f200,FArial,0,0,0>
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#2556,4200>6. Switch to Browse mode again. You'll see the city names from each state listed in the repeating panel. If there are fewer cities than rows in the repeating panel, you'll see them all listed. If there are more than five, the panel will have a scroll bar
. You can scroll within the panel to look at all the entries.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#2840,4200> If you need to print the one-to-many information, the form may not be the most efficient method. In the form there's a limit of 30 of rows that can be displayed in a repeating panel. The form won't be able to print more rows than are displayed in the rep
eating panel. Why? Because the form is intended for browsing and data entry. Reports are for printing data.<:f240,FArial,0,0,0>
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#1420,4200> To display all the Cities within each State we need a report showing detail, (individual) records from
<+">Cities<-"> and summarizing on the join field, <+">St_Abbr<-">, from
<+">State<-">.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@Normal@<:#293,4200><+!><:f240,BHelvetica,0,0,0>Creating the Report<-!><:f200,FArial,0,0,0>
@list@<:#2840,4200>1. Switch to Design. This time, select Create Report from the Design menu. Because we want to display each individual city name, this time the report will be based on
<+">Cities<-">. Add the field <+">City_Name<-"> to the right side of the dialog. Choose a columnar report with one column. Make sure the
<+">Cities<-"> file name is still showing and click okay to create the report.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#2272,4200>2. At this point the report will contain a header, with the text label "City_Name" and a body panel, the region of the screen containing the field
<+">City_Name<-">. This body panel is where each individual record in the
<+">Cities<-"> database will be displayed; it's based on the <+">Cities
<-">file.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#2556,4200>3. From the Design menu select Add Summary. In the Summary dialog choose "A Group Sorted on
<+">St_Abbr<-"> from the file <+">State<-">." At the bottom of the dialog select center and leading. Click okay. A new summary panel, a box outlined in blue, is added to the report between the header and the body panel.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#2272,4200>4. Using the field tool from the toolbar at the top of the report, add the
<+">St_Name<-"> field into the summary panel. After drawing out the field use the Field Style dialog to deselect the label and borders, if those are set. Click okay to close Field Style. Make sure that this field is fully within the Summary Panel.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@Normal@<:#3976,4200><+"><:f240,6Times New Roman,0,0,0><[>Design tip: A new field can also be added by selecting an existing field, choosing Copy from the edit menu, clicking where you want the field added, and then selecting Paste from the Edit menu. The field that's pasted
in will be the same field as the one that was copied. Double-click on the new field to bring up the Field Style dialog. Within this dialog you can select an existing field, and click okay; the pasted field will now be the field that you picked. Or, if th
e new field hasn't been added yet, go into Field Definition from Field Style to add a new field.]
@Normal@<:#231,4200><+"><-"><:f200,FArial,0,0,0>
@list@<:#3124,4200>5. Switch to Browse mode. Only see City listings? That's natural. Summary panels only display in Preview Mode. Press the Preview button or select Preview from the File menu to display the report. The report repeats the body panel as often as necessary,
once for each city. The states are only included once, with the cities within a state grouped together and listed under the state they're related to.
@Normal@<:#231,4200><:f200,FArial,0,0,0>
@list@<:#3124,4200> When should you choose a form? When a report? If the information will be printed, especially if the total amount of information isn't known, use a report. For data entry or display on screen, typically a form is used. There are always exceptions, this
is just a general guide. Once you understand this example, though, you'll be able to apply this to many other one-to-many relationships within Approach.
