AnsweredAssumed Answered

Relationship or New Table

Question asked by BetoBoton on Sep 10, 2009
Latest reply on Sep 16, 2009 by BetoBoton


Relationship or New Table




Sorry if the explanation is long...

I'm using an "adaptation" of FMP 9 Adv Business Productivity Pack Services Edition.


I need to create the following Fields in my Contacts Table:

Contacts::Last Invoice Service ID

Contacts::Last Invoice Service Description

(I need this contents to be used in a mail merge below)


Table Contacts already have the following fields working as unstored Calculation and giving me the correct results based on a Relationship (Contacts::Contact ID and Service Orders::Bill to Contact ID.

Contacts::Last Invoice ID = Max ( Service Orders::Invoice ID )

Contacts::Last Order Date = Max ( Service Orders::Sale Date )


Table Service Orders use a Related Table "Line Items" to fill in the orders items within the Service Orders .


I need to get the contents of 

Line Items::Serial Number into Contacts::Last Invoice Service ID


Line Items::Description into Contacts::Last Invoice Service Description

for this particular Service Orders::Invoice ID


The fact is (until today) I use  1 service per order 

and based on the ( Line Items::Serial Number) I'll Choose a specific template to send an email, in which I have to mention on the following email


mail merge:

"Since you have <<Contacts::Last Invoice Service Description>> service done on <<Contacts::Last Order Date>> and it will expire on <<Contacts::Expire Order Date>> I would like to suggest doing another <<Contacts::Last Invoice Service Description>> on <<Contacts::Next Order Suggested Date>>."



PROBLEM: I'm trying very hard to access both Line Items::Serial Number and 

Line Items::Description for the order I know its ID via [Contacts::Last Invoice ID = Max ( Service Orders::Invoice ID ) ]with no success.


What is it ?

Do I need a Relationship between Line Items and Contacts that doesn't exist?

Do I need a table in between these 2 (Line Items and Contacts) to hold global values and the fill in fields in my Contacts table?



I am absolutely lost on this one.

ANY suggestion would be appreciated


Thanks a million