3 Replies Latest reply on Aug 3, 2010 9:30 AM by philmodjunk

    hi, I have a calculated field that contains "product code". example: RECORD 1 code:...

    SamueleCioni

      Post

      hi,

      I have a calculated field that contains "product code".

      example:

      RECORD 1

      code:

      aaa1

      bbb1

      aab

      as u can see I have more than 1 code in the code field.

      and I need to verify if one of the code inside the code field is a already in use in a different record (or in the same one).

      any suggestion?

        • 1. Re: hi, I have a calculated field that contains "product code". example: RECORD 1 code:...
          philmodjunk

          In use in a different record, that can be done

          What do you mean by "or in the same one"? Can you post an example?

          Listing multiple codes in the same field very well could be a less than optimum design for your database. There are other approaches that may work better. What does this list of product codes represent?

          • 2. Re: hi, I have a calculated field that contains "product code". example: RECORD 1 code:...
            SamueleCioni

            hi,

            ty for ur reply.

            Let me explain better.

            I have 2 fields for the code of the items:

            - one for the main code

            - the other for the alternative codes (because in my warehouse I have same itmes coded with different codes).

            The point is that I don't know how many "alternative codes" I have for each item; sometimes I have none, other times 2, other times 5...

            In fact in the "alternative code" field I put all the codes I have in this way:

            Code: a1

            Alternative Codes: b2

                                          b3

                                          c1

            I have made another field called "all codes" that is a calculated field of "codes" + "alternative codes".

            Now i need that all the codes must be unique.

            A lot of people say me that I need to do a self relation and than use the "calculate" function...but it doen't seems to work.

            Do u have any suggestion?

            ty in advance.

            • 3. Re: hi, I have a calculated field that contains "product code". example: RECORD 1 code:...
              philmodjunk

              So if you have a combined list of a1, b2, b3, c1 all of the codes must be unique? I interpret that to mean you don't want any other records where a1 or b2 or b3 or c1 is listed in code or alternative codes.

              The self relationship should work.

              Your all codes calculation should look like:

              List (Code ; Alternative Codes)

              or like: Code & ¶ & Alternative Codes

              Then this relationship could be defined:

              YourTable::All Codes = YourTable 2::All Codes

              Where YourTable 2 is a 2nd table occurrence of YourTable

              Then this calculation: Count ( YourTable 2::All Codes ) will return a value greater than 1 if you have other records where a value in All codes matches a value in All Codes of the current record.