2 Replies Latest reply on Sep 16, 2009 4:55 AM by BetoBoton

    Relationship or New Table



      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

        • 1. Re: Relationship or New Table



          Thank you for your post.


          If you sort the portal by Invoice ID Descending, the first record in the portal will have the last Invoice ID.  You can then use GetNthRecord ( LineItems::Serial Number ; 1 ) to get the most recent Serial Number, and GetNthRecord ( LineItems:: Description ; 1 ) to get the most recent Description.



          FileMaker, Inc. 

          • 2. Re: Relationship or New Table

            Thank you


            It is the answer I've been struggling to find.



            I realised  I need to print the Functions Reference Manual, and have a hardcopy handy (I'm a visual learner).


            That solved the problem.



            PS.: This forum is an essential tool for people trying hard to learn FileMaker Pro. It has been the answer to every single difficulties I encountered and I'm learning a lot from you experts. Sincerely thankful for that.