multiple finds for Avery label layout

[flybriguy]

Hello all,

I'm just a simple user of filemaker for a single database that I'm building for myself - Magazine database with names of editors and contact info etc. What I'm trying to do is specifically locate the names to certain editors to certain magazines so that I can print them out on my Label layout. My problem is that I can't seem to get them all on to a single convenient sheet(s) and if you read how I've got mine organized perhaps you'll understand and maybe find a way for me to do this efficiently.

For my "Magazine" database I keep one record for each magazine with multiple fields for each "Editor" contact. For instance: Rolling Stone magazine . . . Editor 1 (field 1), Editor 2 (field 2), Editor 3 (field 3) and so forth. So for my predefined Avery #8663 label layout I only have the fields "Editor 1" "Magazine name" and "Address." Since I only want to keep one name per label I only know how to create a separate Avery label layout but with an "Editor 2" field for me to search and print afterwards. This seems ineffecient and wasteful of paper as any blank labels will be unused since printing of contacts always starts up top continuing downwards and in the event of printing just 2 or 3 names.

Even if I put multiple finds for different names of the same magazine only one name will show on the Avery Label layout no matter how many names there are to a mag since the layout only calls for one editor's name. So how can I go about doing this without having to resort to creating multiple records for the same magazine but with a different editor's name? Or how can I add to a search function the additional editor's names with a separate Avery label layout (Editor 2, magazine name, address . . . . . . . Editor 3, magazine name, address) Please help newbie here . . . . . thanks

-brian

[Genx]

Flat File is no good... You want relational

Create a second Table to store editors names (2 fields, one for name, one for foreign key), relate the foreign key field to a primary key (unique serial or unique name etc.) in your mag table and enable record creation. Then goto your magazine layout. Delete your 3 editor fields. Place a portal on your layout (to your editor names table) and put in the name field from that table occurance.

Next, goto your label layout. Change it to be sourced from the editor name TO. Pull the mag details from across the relationship (i.e. source foreign fields <>). Finally, don't change your script.

What all this ensures is that you will have one label for each editor rather than each magazine. Hoping thats what you want, and that this at least makes some sense (feel free to repost if it doesn't).

~Genx

[flybriguy]

What a great help you are and so detailed with instructions . . . . it looks I will have to pick up a whole book or manual to execute your directions since I've learned and used this from scratch (hence why I only know how to make a flat file). Thanks so much!

-brian

[flybriguy]

since I am a novice at this could you/anyone explain to me that in creating the second table what do I make as a "foreign key"? And specifically how/what do I relate that to the primary key? Should I create say a serial number for the foreign key as well as a serial number for the primary key too?

with no formal training at this, it's very confusing for me.

brian

[Genx]

Yeh, i probably should have made a sample file... sorry,

See attached.

~Genx

magazine___edit.zip

[Genx]

For a good introduction to Relational database i found the stuff by Linda to be very useful when i was starting out, i don't know the link sorry, just try googling it.

But then, I didn't olike reading the content of the books to much because i didn't know where to start.

~Genx

[Genx]

Sorry one thing to note about the file. The field names "primarykey" and "foreignkey" just make them easier for you to identify. The names themseleves have no deep significance.

Primary key is basically an identifier unique to that table.

Foreign key simply stores the Primary Key from the other table so that it knows that its related to that particular record, hence the requirement for uniqueness..

The primary key in the second table wasn't necessary in this case unless say, you wanted to store multiple phone numbers per each editor.

~Genx

[flybriguy]

ahhhh how easy

*so on the editors table/layout I have to manually enter the data or ID into the foreign field. . . . . . I was mentally getting caught up and confused that somehow no entry was required and the relation/data would automatically be shown and generated alone.

**for kicks I've gone a step ahead and dragged an extra field from the magazine table (the "magazine" name) onto the editors layout and of course the magazine name is now displayed as it's now related . . . . is it possible though to view this layout without without having to show the foreign field entry? Or does this require a whole new layout altogether?

much much thanks again,

brian

Edited April 19, 2006 by Guest

[Genx]

Huh?...

If you goto define database and choose relationships, double click on the equals thing in between the two TO's (table occurances) you will notice that the create related records tick box is marked.

What this means is that the foreign ID key is automatically added whenever a new record is created through the portal (added through the portal)...

I think your a bit confused, all you have to do is put in the editors name in the last blank field and a record will automatically be generated in the foreign table.

~Genx

[flybriguy]

I see now, duh, I was approaching it backwards . . . . rather than entering the names through the portal I was entering people's names through the people layout

-brian