3 Replies Latest reply on Jan 2, 2014 8:48 AM by philmodjunk

    Problem with altering the discount option in the FMPro12 'Invoices' Starter Solution

    Bazz

      Title

      Problem with altering the discount option in the FMPro12 'Invoices' Starter Solution

      Post

           Hi Phil et al,

           I'm trying to alter the startup solution so that it displays a discount via line item on a lookup? basis from a related field in the Customers table.

           Background….

           I added two product items, one called "testproduct12" and one called "testproduct13" to use as test products to determine if my discount logic works. 

           i then added a "Customer Discount" field to the Customers table. It's a calculation field which reads:  if (Invoice Data::Item = "testproduct12" ; .12; if (Invoice Data::Item = "testproduct13" ; .13))

           I then altered the portal found on the Invoice Details layout so that it correctly referenced my new Customers::Customer Discount field.

           Result…..      

           This all works fine for the first line in the portal whereby if i select "test12product" then .12 returns correctly and if I select "testproduct13" then .13 is returned correctly.

           Problem…..

           The problem occurs on line 2 of the portal where the value returned remains the same as that for the 1st line of the portal no matter which product is selected on line 2. i.e. if line 1 reads "testproduct12" then .12 is returned on line 2 even if line 2 reads "testproduct13"

           Can you assist with a script? solution which will restart for each line on the portal or am I barking up the wrong tree? (I did try adding a discounts table but I could not get this to work no matter where I joined the table)

           Many thanks in advance,

           Bazz

            

            

            

        • 1. Re: Problem with altering the discount option in the FMPro12 'Invoices' Starter Solution
          philmodjunk

               When a calculation refers to a field from a related table, such as is the case with your If function, it refers to data from only the first related record and that's why you are getting the results that you are getting.

               In my answer, I am assuming that you have the typical set of tables and relationships found in an Invoice solution. My names may differ from yours so you'll have to substitute your names for mine in this example:

               Clients-----<Invoices----<LineItems>-----Products|Services

               In order to set up item-client specific discounts, you'll need a related table that is linked to both clients and the invoice lineitems via two Tutorial: What are Table Occurrences? of the same table:

               Clients---<ClientDiscounts

               LineItems>-----ClientDiscounts 2

               LineItems::_fkClientID = ClientDiscounts 2::_fkClientID AND
               LineItems::_fkItemID = ClientDiscounts 2::_fkItemID

               Then you can set your auto-enter field options to copy data from ClientDiscounts 2.

               This requires that you use one method or another to pull the ClientID into the LineItems table. (Note: in the FileMaker 12, Invoices starter solution, the table I am calling "line items" is called "invoiceData".)

               One method for automatically pulling the ClientID into LineItems is to modify the Invoices to LineItems relationships like this:

               Invoices::ClientID = LineItems::ClientID AND
               Invoices::InvoiceID = LineItems::InvoiceID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Problem with altering the discount option in the FMPro12 'Invoices' Starter Solution
            Bazz

                 Thanks Phil. Does this Relationship graph now look correct? and should my "Customer Discount" field, which currently resides in the Customer table, now be moved to the Client Discounts table? 

            • 3. Re: Problem with altering the discount option in the FMPro12 'Invoices' Starter Solution
              philmodjunk

                   The match fields are set up as I recommended--though I'd add a new occurrence of Products and link it by Item to the _fkItem ID field in Client Discounts. That way I can see info from Products when using a portal to Client Discounts on a customer layout to set up the discounts.

                   But:

                   There is no need for a customer name field in the Client Discounts table. Any time that you are looking at data from this table, the relationship to Customers will allow you to access the Customer Name field in Customers.

                   And you don't have a number field in Client Discounts for recording the discount amount or percentage for each client discount.