Forum Discussion

RossBateman's avatar
RossBateman
Helper I
6 years ago
Solved

Creating a Relationship Error

I have two tables with an invoice number being the unique field in both. When trying to create it comes up with the error message of ‘You can’t create a relationship between these two columns because...
Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi RossBateman 

    As a workaround, If the column has only number type value, you can change its type from "text" to "number",

    Then add "prefix",

    Then remove duplicates works.

     

    Best Regards

    Maggie

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-juanli-msft's avatar
v-juanli-msft
Community Support
6 years ago

Hi RossBateman 

As tested, "remove duplicates" works on my side.

Please share some sample data for me to test and find a solution for you.

 

Best Regards

Maggie

RossBateman's avatar
RossBateman
Helper I
6 years ago

Thanks Maggie. The problem was that some of the invoices were different but werent classed as unique when trying to set up the relationship. For example there was invoice number 003 and 0000003. The remove duplicates didnt class those as the same but when not in the query mode they were classed as the same. 

 

The only way I think I can get around this if I add a letter to the begining of every invoice.

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi RossBateman 

    As a workaround, If the column has only number type value, you can change its type from "text" to "number",

    Then add "prefix",

    Then remove duplicates works.

     

    Best Regards

    Maggie

    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • HuiYin's avatar
      HuiYin
      New Member
      5 years ago

      Need help!

      I have the same issue when creating the relationship between the tables. 

      I have 3 Facts tables:

      Table 1: Have columns of "Acad Session", "Modules" and "hours per week for the module"

      Table 2: Have columns of "Acad Session",  "Teaching staff" , and "modules staff are teaching"

      Table 3: Have columns of "Acad Session",  "Teaching staff" , and "Status, (Active or Retired)" 

      Dimension tables for modules are created by duplicating the Table 1 and keep the column  "Modules" first and then remove the duplicate in the column. But I received the error message "You cann't create a relationship between two columns because one of the columns must have unique values" when trying to create the relationship with Table 1.

      Same error for creating Dimension table for "Teaching Staff"

      Dimension table for Acad Session and Status have no issue (these two dimension tables have few rows only)

       

      Thank you in advance for the advice/help