8 Replies Latest reply: Apr 13, 2016 1:11 PM by Russ Golden RSS

    Help me out of this circular reference!

    Russ Golden

      Hello! This is my first question to the community, though I have lurked for some time and found many great resolutions via both the questions and the blogs.

       

      I'm having an issue with a circular reference that I am unable to solve completely, even after going through the blog on the matter (which was enlightening). I have three tables, below. Two are raw, non-configurable files (I cannot change their output). One serves as a simple relationship between partner and price list. They are as such:

       

      Partner Table (manual)Price List Table (generated)Exception Table (generated)
      Partner CodePrice List

      Part Number

      Price ListPart NumberPartner Code
      DiscountList PriceQuantity

       

      The ask - In the exception file there is a unique line ID for each row. The row will contain a Part Number, a Partner Code, and a quantity. From this, I need to associate the price list the partner uses, then use that list to find the appropriate list price, and multiply through with the appropriate discount. It looks like this:

       

      [Quantity] * [List Price] * (1 - [Discount]) = [Extended Value] for each row. I use variations of this calculation to Sum or Average in aggregate.

       

      Each Part Number has up to four prices (one for each price list), so the Price List Table consists of duplicate part numbers in the Part Number column. Each Partner Code is associated with only one Price List in the Partner Table. From what I understand, I am using Price List in two ways - one is the Price List the Partner Code uses, the other is the Price List the List Price and Part Number are associated with. This is where I am unable to solve my issue.

       

      Somehow, I need each unique row in the Exceptions Table to connect the Partner Code to the correct Price List to the correct List Price for the Part Number on that row. It sounds so simple I have to be missing something right in front of me. Thanks in advance for any help on this!