Is field part of primary key?

DarkSide · 2007-09-28

Is there a way through code to tell if a field is part of a tables primary key?

I would like to be able to determine this through code because the table is being referenced via RecordRef. So I don't necessarily know what table I'm dealing with. I want to be able to determine if the field I'm working with is part of the primary key of RecordRef. This way if I need to change the value of the field I'll know if I need to do a MODIFY or a RENAME.

Sorry to sound repetitive but I want to be clear, this may seem like an odd request.

Thanks in advance.

Luc_VanDyck · 2007-09-28

If your are working with RecordRef, you can also work with KeyRef. Have a look at Codeunit 423 Change Log Management, where you find some code to extract the primary key of a table:

KeyRef1 := RecRef.KEYINDEX(1);
FOR i := 1 TO KeyRef1.FIELDCOUNT DO BEGIN
  KeyFldRef := KeyRef1.FIELDINDEX(i);
  IF i = 1 THEN
    ChangeLogEntry."Primary Key" :=
      STRSUBSTNO('%1=%2',KeyFldRef.CAPTION,FormatValue(KeyFldRef,RecRef.NUMBER))
  ELSE
    IF MAXSTRLEN(ChangeLogEntry."Primary Key") >
      STRLEN(ChangeLogEntry."Primary Key") +
      STRLEN(STRSUBSTNO(', %1=%2',KeyFldRef.CAPTION,FormatValue(KeyFldRef,RecRef.NUMBER)))
    THEN
      ChangeLogEntry."Primary Key" :=
        COPYSTR(
          ChangeLogEntry."Primary Key" +
          STRSUBSTNO(', %1=%2',KeyFldRef.CAPTION,FormatValue(KeyFldRef,RecRef.NUMBER)),
          1,MAXSTRLEN(ChangeLogEntry."Primary Key"));

  CASE i OF
    1:
      BEGIN
        ChangeLogEntry."Primary Key Field 1 No." := KeyFldRef.NUMBER;
        ChangeLogEntry."Primary Key Field 1 Value" := FormatValue(KeyFldRef,RecRef.NUMBER);
      END;
    2:
      BEGIN
        ChangeLogEntry."Primary Key Field 2 No." := KeyFldRef.NUMBER;
        ChangeLogEntry."Primary Key Field 2 Value" := FormatValue(KeyFldRef,RecRef.NUMBER);
      END;
    3:
      BEGIN
        ChangeLogEntry."Primary Key Field 3 No." := KeyFldRef.NUMBER;
        ChangeLogEntry."Primary Key Field 3 Value" := FormatValue(KeyFldRef,RecRef.NUMBER);
      END;
  END;
END;

This code will give you the numbers of the fields which appears in the Primary Key. The name of the field can be looked up in the Virtual Table "Field".

Timo_Lässer · 2007-09-29

Because I had the same problem in the past, I wrote a small function for that:

IsPrimaryKeyField(RecRef : RecordRef) : Boolean
FOR I := 1 TO RecRef.KEYINDEX(1).FIELDCOUNT DO
  IF RecRef.KEYINDEX(1).FIELDINDEX(I).NUMBER = FieldID THEN
    EXIT(TRUE);

EXIT(FALSE);

DarkSide · 2007-10-01

Thanks! You really helped me out!

Savatage · 2007-10-01

Sounds great - perhaps you could put [Solved] in the 1st posts Subject Line.

Is field part of primary key?

Answers

Categories