Circular Reference Issue

[Anonymous]

Hi All,

I have three tables.

Owner, Opportunity and Checkbook

In the Owner table I have:

Owner:

Id as "Opportunity Owner ID",

    User_Theater__c as "User Theater",

    User_Sales_Region__c as "User Sales Region",

In the Opportunity table I have the below script"

Opportunity:

OwnerId as "LT Opportunity Owner ID",

    Lookup('User Theater', 'Opportunity Owner ID', [OwnerId],'Owner') as "Region",

In the Checkbook Table:

Budget:

Team as "Region".

I want to link the "Region" in Opportunity table with the "Region" in Budget table. When I try to run this I get a circular reference.

How do I resolve this issue?

[NZFei]

Can you list all the fields in the three tables please?

[Anonymous]

Below are the fields:

Owner:

LOAD

    Id as "Opportunity Owner ID",

    User_Theater__c as "User Theater",

    User_Sales_Region__c as "User Sales Region",

     User_Sales_Group__c as "User Sales Group"

FROM [lib://Salesforce QVD/user.qvd]

Opportunity:

LOAD

    Id as "LT Opportunity ID",

    Name as "LT Opportunity Name",

    OwnerId as "LT Opportunity Owner ID",

    Amount/(Lookup('CR','IsoCode',[CurrencyIsoCode],'Currency')) as "LT Converted Amount",

    Date(floor(CloseDate)) as "LT Opportunity Close Date",

   Lookup('User Theater', 'Opportunity Owner ID', [OwnerId],'Owner') as "LT Theater",

    Lookup('User Sales Region', 'Opportunity Owner ID', [OwnerId],'Owner') as "LT Region"

FROM [lib://Salesforce QVD/Opportunity.qvd] (qvd)

Budget:

Team as "LT Theater",
    Department,
    "Year",
    "Group",
    Quarter,
    //concatenate Year and Quarter field
    [Year] & [Quarter] as "Year Quarter",
   "Forecast Amount",
    Activity,   
    Category,
FROM [lib://SharePoint - Marketing Finance/Shared Documents\2017 Checkbook Registers\Global\GLOBAL checkbook.xlsx]

[karthikoffi27se]

Hi Ajinkya,

Can you post the screenshot of your data model please

Many Thanks

Karthik

[Anonymous]

Hi Karthik,

Here the Region field is called "LT Theater". As you can see from the data model. When I rename Team as "LT Theater"(Checkbook table) in order to join with Opportunity table(LastTouchOpp) which has "LT Theater" I get the below circular reference

[NZFei]

Apart from NewOpportunity and LastTouchOpp, you have another table (on top of Checkbook, let's call it XXXX) that has the field CampaignID. That table is linked to LastTouchOpp by CampaignID, as well as the LT Theater field through Checkbook, hence caused the loop.

Remove or rename LT Theater in Checkbook, or the CampaignID in the XXXX table should solve the problem.

Fei