Tricky Relationship Situation

[Anonymous]

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?

[danextian]

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]
)

[Vvelarde]

@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]

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.