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:
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:
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
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?
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.
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.