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

    Relationship or New Table

    BetoBoton

      Title

      Relationship or New Table

      Post

      Hello 

       

      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

      and

      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
          TSGal

          BetoBoton:

           

          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.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Relationship or New Table
            BetoBoton
              

            Thank you

             

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

            Brilliant.

             

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

             

            That solved the problem.

             

            Cheers

            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.