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.

amitchandak's avatar
amitchandak
Super User
6 years ago

Create a visual table or matrix, take invoice number and again take invoice and change aggregation to count.

Sort desc on Count of Invoice no. Make sure there is nothing having count more than 1

  • RossBateman's avatar
    RossBateman
    Helper I
    6 years ago

    Thanks, it looks like this is the problem. The remove duplicate function doesnt seem to be romoving all of the rows! As I've got over 55k rows do you know of any other ways to delete duplicates?

     

    • 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.