one - many - many relationship

[daniel z]

Hi all

I am using a filemaker pro 6.

I have 3 files:

SALES – each record represents a single sale to a customer. Primary key = invoice id. Each record can contain MULTIPLE batches of serial codes. (each batch itself contains MULTIPLE serial codes)

SERIAL CODES – each record contains a unique serial code (primary key). Each record also contains a batch number, allowing multiple serial codes to be assigned to the same batch number.

LINKING DB – This file only contains 2 fields – invoice id and batch id. This allows me to associate as many batches as I want to each invoice id.

(to summarise: each sale can contain multiple batches. Each batch can contain multiple serial codes)

This is all fine and well. However I now want to display all serial codes from all batches associated with a particular invoice id, in the sales file. I tried to do this by creating an invoice_id field in the SERIAL CODE db which was a calculated field based on the linking db, and then creating a relationship between this field and the invoice_id field in SALES. However at this point filemaker objected and said I cant create relationship based on a calculated field which cannot be indexed.

What am I missing here ? I’m sure this kind of thing is quite common and has come up before ? Any help is much appreciated !!

Thank you

Daniel

[Wim Decorte]

FM6 can't display records from more than one relationship away (FM7/8 can). So yes, you'll need "invoiceID" in the serial codes file, but not as a calculation. Calcs across relationships can not be stored or indexed. And "indexable" is a requirement to create a relationsip to the field. You'll need to set the invoiceID field through a script.

[daniel z]

Thank you !

I thought as much. I've done it like that already, but I'm not keen to have a loop going through and setting the invoice_id in the serial code db for all serial codes in the batch as a standard everyday script to be used by everyone in our office. I have visions of the loop running away into infinity... I guess I'm just being paranoid.

Anyway thanks again for your help !

Much appreciated.

Daniel

[comment]

You could set up an unstored calculation field in Invoices, getting all the related BatchID's from the linking file via a value list. Then define a relationship from this field to the BatchID field in the Codes file.

[daniel z]

Thank you !

I'm sorry to be thick but I dont know how to do that - can you elaborate on the steps involved please ? Specifically "getting all the related BatchID's from the linking file via a value list"

Much appreciated -)

Dan

[comment]

I don't have version 6, so my syntax may be a bit off: in Sales, define a value list using values from field BatchID in in the linking file, showing only related values. Define a calculation field (unstored, result is text) =

ValueListItems ( Status (CurrentFileName) , "yourNewValueListName" ).

[daniel z]

That is amazing !!

Thank you - you have just opened my eyes.

It is a much better way of doing it.

Is this method a recommended / stable / efficient way of doing things ?

One of the reasons I ask is that I'll be making calculation fields adding up the number of activations in all related serial codes and then doing searches on these fields.

I've never heard of creating relationships to multiple records based on multiple values in the same field.. Do you use it often ?

[comment]

It might be a tad slow, but other than I am not aware of any problems.

The calculation must be unstored, so any searches based on it will be slow, too. But this is true for searching any field that is derived from related records.

Another option is to lookup all the codes of a batch into a field in the Linking file. You will need a value list for that, too - but at least the field can be stored and indexed.

[daniel z]

Thank you again - I am trying out that option now and it seems excellent.

This alternative method also allows me to include serial codes that are not in any batch by creating a field which is the combination of 2 value lists and then creating a relationship based on this -- and it worked fine, as long as I added a "new line" character between the 2 value lists. (there shouldn't be a problem with that, should there ?)

Is there a limit to how many "entries" filemaker will allow me to have in a single field ?

[comment]

There shouldn't be any problem concatenating 2 VLs. However, this is replicating the data, so you should consider what to do (or not) in case the original data is modified.

There's a limit to a text field, unfortunately I don't remember what it was for version 6 - 64k characters?