Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Tricky Relationship Situation

I have 2 data sources connected through a unique ID. One is a SQL data source, the other is from Salesforce. I have a one-to-many relationship between the 2 tables:

 

So, 'Unique ID' from Delta account = 'Co' from M3 Billing. This works fine for the most part, but there is some data from my M3 Billing table that didn't link up correctly due to the unique ID being different between the data sources. So any company that starts with a 'F' has blank fields for everything in a table I have. The "Co" column is coming from my M3 Billing dataset;

 

So what's happening, is for some reason these companies have a different 'Unique ID' field in Salesforce:

 

So for these particular set of records, I need it to be linked up to the 'Flex M3 ID' field rather than the 'Unique ID' field so I can get my data populated the way I need to. It's tricky because most records are fine and give me what I need when linking 'Unique ID'->'Co' from both tables, but I don't know how to account for the remaining records that need the relationship to be 'Unique ID'->'Flex M3 ID' so all the data gets populated. Is this possible?

 

 

General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion
    8 years ago

    Anonymous

     

    I will try to created a calculated column in both tables with this condition.

     

    New Column = Unique ID Column except That Co Start with F. If Start with F use Flex M3 ID.

     

    And use this new column to related.

     

    Victor

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Vvelarde, this makes perfect sense but I'm not exactly sure what that formula would look like. The Flex M3 ID only exists in one of my datasets.

  • danextian's avatar
    danextian
    Super User
    8 years ago

    Hi Anonymous

     

    I guess what Vvelarde was trying to say was to create a calculated column using a formula like below and use this column to connect the two tables assuming that you are referring to Company as Delta Account. 

     

    New Unique ID =
IF (
    LEFT ( Delta[Delta Account], 1 ) = "F",
    Delta[Flex M3 ID],
    Delta[Unique ID]
)