Multiple Values in a Field

[jbil...@gmail.com]

I have a database that links to another one containing a field of photos (I prefer to keep the files separate rather than include a field with the photos in the main DB). They are linked by a ‘record #’ field in both.

In many cases, several records refer to the same photo. I have been creating duplicate records with the same photo and different record #’s in the Photo DB.

Is there a way to have one photo list several record #’s in the Photo DB so that different records in the Main DB can link to the same photo?

Any help would be appreciated?

Regards…JohnB

[Helpful Harry]

In article <2e6495d1-09e8-46b1...@googlegroups.com>,

jbil...@gmail.com wrote:
>
> I have a database that links to another one containing a field of photos
> (I prefer to keep the files separate rather than include a field with
> the photos in the main DB). They are linked by a record # field in both.
>
> In many cases, several records refer to the same photo. I have been

> creating duplicate records with the same photo and different record #s
> in the Photo DB.
>
> Is there a way to have one photo list several record #s in the Photo DB

> so that different records in the Main DB can link to the same photo?
>
> Any help would be appreciated?
>
> Regards JohnB

Each Photo DB record only needs one unique ID, and it can then be linked
to as many Main DB records as you want using that ID. The link can go both
directions, so there's usually no need for multiple IDs.


If there is a good reason why you need to use different IDs, then using a
Text field as the linking field you can have multiple IDs for the
Relationship - each one needs to be separated by a carriage return
character and then FileMaker Pro can match them separately.

Helpful Harry :o)

[Christoph Kaufmann]

<jbil...@gmail.com> wrote:


> Is there a way to have one photo list several record #'s in the Photo DB
> so that different records in the Main DB can link to the same photo?

The proper way is creating a cross table where one record links 1 main
record to one photo.

The cross table need two fields: one for the foreign key of the Main DB,
and one for the foreign key of the photo DB.

It one main record links to one photo, you have one record in the cross
table.

If 10 main records link to one photo, you have 10 records in the cross
table:
_kf_main _kf_photo
1 1
2 1
3 1
4 1
5 1
6 1
7 1
8 1
9 1
10 1


--
Christoph Kaufmann
http://clk.ch

[Christoph Kaufmann]

Martin ?rautmann <t-us...@gmx.net> wrote:

[Kreuztabelle statt mehrere Werte in einem Feld]
> What would its advantage be?

- zus�tzliche Informationen zur Verbindung speichern (was stellt das
Photo f�r diesen Datensatz dar?)
- Kompatibilit�t mit anderen DB-Systemen
- Bezug kann �ber ein Portal erstellt und einfach gel�scht werden
- Mehr Uebersicht dank geringerer Feldzahl (Normalisierung & so)

Bonus: Das Einsparen einer Tabelle ist die L�sung eines nicht mehr
existierenden Problems. Die Beschr�nkung auf max. 50 Tabellen im
Serverbetrieb ist seit Filemaker 7 aufgehoben.

[Bill]

In article
<HelpfulHarry-2...@203-118-187-7.dsl.dyn.ihug.co.nz>,

It sounds like the OP wants to link one photo to many main table
records. In that case the simplest way, as you say, is to put a foreign
key field for the photo ID in the main table, so that one photo can be
linked to many main table records. This is commonly known as a
one-to-many relationship.

If the OP also wants to link many main table records to one photo, then
he would need a many-to-many relationship. The most robust way to do
that is by means of a join table (earlier response called is a "cross"
table), that has two foreign keys, one for the main table record and one
for the photo record, and would have one record for each Join of a photo
to a main record. The join table could also have other fields to hold
useful information about that specific Join, such as why the photo is
significant to the main table record it is joining or why the main table
record is significant to the photo.