You need a many to many relationship.
Create a join table to put between customer and document. The table will capture the customer ID and the document ID. Your portal will be based on the join and pull data from the document table.
Here is a very very simple example file for you.
Many2Many.fmp12.zip 206.5 K
"…we are only interested in the What Customer Received What Doc side"
That statement is a little ambiguous.
Do you want to look from customers and see a list a documents they have received?
Do you want to look from documents and see which customers have received that document?
If you are only concerned about the first, let's take a look at that. If customer A and customer B both receive document 27, are you OK with having multiple entries in your documents table (with different DocumentID values), each representing document 27?
If you think there is ever a possibility that you will need to track/look/report from either direction, or will not want duplicate records for the same document, then you need a join table. Call it CustomerDocuments or Received or whatever you want. All you need is an ID field for that table, and then foreign keys to your CustomersID and DocumentID fields.
All that to say… it sounds like there might be an issue with your script. How are you creating the related record? Perhaps there is something going to the wrong portal row and setting the fields? Overwriting the existing record instead of creating the new one?
"One customer can receive more than one document; the same document can be send to more than one customer (so it’s many-to-many but business wise we are only interested in the What Customer Received What Doc side)"
This says that a join table is needed.
If the fk's in the join table are set correctly then ...
From a layout based on documents with a portal to customers you will see the customers that are related to a document in the portal.
From a layout based on customers with a portal to documents you will see the documents that are related to the customer in the portal.
If you don"t need to see the Customer>Document portal than don't put it a customer layout.
As noted, the relationship you need is many-to-many, even though you're only going to display if from one side.
Vyke's suggestion to add a join table is the conventional way to create a many-to-many relationship. This involves a table containing a customer key and a document key, so that each document and customer has a one-to-many relationship to "distribution" (working title). This also allows you to store information in the distribution table about that particular customer/document combination.
However, IF you are sure that you're never going to need attributes in your distribution table other than the two keys, FM does actually allow for direct creation relationships. You could enter a list of document keys in the foreighn document key in customers, in a pilcrow-separated list, and your relationship to documents would link to all documents with keys that are in the list. Other databases, afaik, don't allow this "multiline key", but sometimes it's pretty handy.
a customer receives more than 1 document … then the first document sent event is overwritten and replaced by the newer one.
In any case, make sure that when dealing with multiple related records, make sure you're using a portal. If you're setting fields via the relationship, without being on a specific portal row, FM will assume the first record.
Hi Vyke, Shawn, Kris and Chris,
Thank you all for your comments, they are very helpful and gave me more insight.
First of all probably my explanation was not clear enough but in my view I do have a join table already (only relevant fields shown):
Relationships: Customers>CustomerID —> Mailings>CustomerID / Allow to create records in Mailings
Documents>DocumentID --> Mailings>DocumentID
"All that to say… it sounds like there might be an issue with your script. How are you creating the related record? Perhaps there is something going to the wrong portal row and setting the fields? Overwriting the existing record instead of creating the new one?"
"In any case, make sure that when dealing with multiple related records, make sure you're using a portal. If you're setting fields via the relationship, without being on a specific portal row, FM will assume the first record."
I think the problem is somewhere as mentioned in the above quoted replies:
Weekly it is decided using a Layout (MailingsPrepare) with Customer, Document, last received mailing (and other info) what customer should receive what document now. This is NOT from any kind of portal view (but a 2-dimensional layout). Choosing what document to send is done by choosing from the value list based on the Documents table.
How does the Mailings (join) table get updated after?
(posting the actual script would be confusing so..)
the update MailingsTable script in short does this:
Go to Layout [MailingsPrepare]
PerformFind (to select records with the same DocumentID in the SendNow field)
GoTo RRP first
SetField Mailings::CustomerID; Customers::CustomerID
SetField Mailings::DocumentID; "documentID selected before with PerformFind
SetField Mailings::DateSent; "mailingdate"
Go to RRP [Next; Exit after last]
Result: everything goes well, but if a customer has received a document before, the "older" record is overwritten
1. Is it with the above description impossible to achieve what I want? (Mailingstable with historic records of ALL docs sent)
2. If not, then how should I proceed?
I don't expect you guys to take me by the hand, but just point me in the right direction please.
Again, thanks to all for your help!
It seems that your existing join table models every combination of Customer and Document, and on every mailing event, you simply update the dateLastSent field.
What I gather you really want is a table that records/describes the actual send events; so after it has been decided in your weekly get-together what documents to send to which customers, you will want to create new records, rather than “update” existing ones; e.g. using a simple two-level loop:
Set Variable [ $listOfDocumentIDs ; … ]
Set Variable [ $listOfCustomerIDs ; … ]
Set Variable [ $docCount ; ValueCount ( $listOfDocumentIDs ) ]
Set Variable [ $cusCount ; ValueCount ( $listOfCustomerIDs ) ]
Go to Layout [ Mailings ]
# through customer list
Exit Loop If [ Let ( $cusCounter = $cusCounter + 1 ; $cusCounter > $cusCount ) ]
Set Variable [ $docCounter ; 0 ]
# through document list
Exit Loop if [ Let ( $docCounter = $docCounter + 1 ; $docCounter > $docCount ) ]
Set Field [ Mailing::id_customer ; GetValue ( $listOfCustomerIDs ; $cusCounter ) ]
Set Field [ Mailing::id_document ; GetValue ( $listOfDocumentIDs ; $docCounter ) ]
Set Field [ Mailing::dateSent ; Get ( CurrentDate ) ]
sorry for my late reply to your great help.
I will work on your suggested two level loop solution when I return from my
I really appreciate the effort you (and the previous posters) have done to
help me on this issue!
(I will report the item solved when I have my laptop again, the button
doesn't work on my phone).