Retrieve related Record from a Table Relation Field in FieldRef

NiklasScheuermann
NiklasScheuermann Member Posts: 3
in NAV Three Tier
Hello all,

is there a way to find out which exact record lies behind Table Relatation Field via FieldRef?

I want to be able to find out every record related to any given record.
In many cases this could be done via a .Get() command, since many related tables have a single field Primary Key: Just get the Table ID via FieldRef.Relation, open a RecordRef on said table, find out which one the PK-Field is and set a filter.

But what if I have a field which is related to a table which has several fields in PK.
Say I have a table which contains a field, related to "Sales Header"."Document No.".
Now I want to find out, for a record of said table, to which exact record my Table Relation field points.

I hope this question doesn't read like gibberish to you. In my head it all makes sense but to write it down came out to be pretty difficult.

Thanks in advance and Best Regards!
Niklas

PS: If the category is unfitting, please tell me! I didn't really know where to place it.

Best Answer

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Answer ✓
    No, I'm afraid you cannot get the full relation information dynamically, only the table ID, but nothing about remaining PK fields.

    Having said that - the information about the rest of PK of the related table is buried into the design, so while it is not possible to retrieve it dynamically, you can make some assumptions about remaining values, hardcode them in your code.

    For example, if a given field has relation defined as TableA.FieldA WHERE (FieldB=something) then you could hardcode the 'something' value in your code (be it a constant value or a value from another field).

    It is not a very elegant solution, not dynamic, and limited to simple filter expressions, but perhaps sufficient.

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03

Answers

  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Answer ✓
    No, I'm afraid you cannot get the full relation information dynamically, only the table ID, but nothing about remaining PK fields.

    Having said that - the information about the rest of PK of the related table is buried into the design, so while it is not possible to retrieve it dynamically, you can make some assumptions about remaining values, hardcode them in your code.

    For example, if a given field has relation defined as TableA.FieldA WHERE (FieldB=something) then you could hardcode the 'something' value in your code (be it a constant value or a value from another field).

    It is not a very elegant solution, not dynamic, and limited to simple filter expressions, but perhaps sufficient.

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • wait
    wait Member Posts: 53
    Hi, you can try to use the key table, it's a system table so you won't find it in the designer. If you create a page for the key table and look at the data you will see that the primary key seems to be always No. 1
  • NiklasScheuermann
    NiklasScheuermann Member Posts: 3
    @Slawek_Guzek
    Mhh, that's what I thought. Thanks anyway! Too some extent I already use something like what you suggested. I was hoping though that there was a more generic approach to this.
    Thank you for your answer though!
  • NiklasScheuermann
    NiklasScheuermann Member Posts: 3
    @wait
    wait wrote: »
    Hi, you can try to use the key table, it's a system table so you won't find it in the designer. If you create a page for the key table and look at the data you will see that the primary key seems to be always No. 1

    Finding out what PK the related table has is not the problem here, as I could do so via RecRef as well.
    I wanted to retrieve something like a recordlink out of a Table Relation field, to determine which exact record lies behind, as the field itself only contains the value of one PK field of the related table.

    But Thanks for your answer!
  • wait
    wait Member Posts: 53
    NiklasScheuermann wrote: »
    @wait
    wait wrote: »
    Hi, you can try to use the key table, it's a system table so you won't find it in the designer. If you create a page for the key table and look at the data you will see that the primary key seems to be always No. 1

    Finding out what PK the related table has is not the problem here, as I could do so via RecRef as well.
    I wanted to retrieve something like a recordlink out of a Table Relation field, to determine which exact record lies behind, as the field itself only contains the value of one PK field of the related table.

    But Thanks for your answer!

    Ok, sorry I misunderstood your question, maybe I'm still misunderstanding you but can't you use the FieldRef.RECORD property to get the exact record that lies behind the field ref?

    something like:

    MyRecref.OPEN(DATABASE::Item);
    MyRecref.FINDFIRST;
    MyFieldRef := MyRecref.FIELD(1);
    MyRecRef2 := MyFieldRef.RECORD;
  • Remco_Reinking
    Remco_Reinking Member Posts: 74
    Sometimes there is a need for something like this.

    It happened to me when I wanted to clean up a bunch of records in our dev environment, without touching any business logic called during a delete. And without having to find out all relations.

    This worked fine for me
    LOCAL DeleteWithAllRelated(TestMode : Boolean; BaseRecAsVar : Variant)
BaseRecRef.GETTABLE( BaseRecAsVar);
BasePK := BaseRecRef.KEYINDEX(1); // get the PK 
BasePKField := BasePK.FIELDINDEX(1); // Assume this PK has only one field

Field.SETRANGE( RelationTableNo, BaseRecRef.NUMBER);
Field.SETRANGE( Class, Field.Class::Normal);

IF Field.FINDSET THEN
  REPEAT
    IF Field.TableNo <> BaseRecRef.NUMBER THEN
    BEGIN
      RecRef.OPEN( Field.TableNo);
      IF RecRef.WRITEPERMISSION THEN // check if delete allowed (to prevent the system to delete eg GL entries
      BEGIN
        FieldRef := RecRef.FIELD( Field."No.");
        FieldRef.SETRANGE( BasePKField.VALUE);
        IF RecRef.FINDSET THEN
        BEGIN
          IF NOT TestMode THEN
          BEGIN 
            IF RecRef.HASLINKS THEN
              RecRef.DELETELINKS;
            RecRef.DELETEALL;
          END;
        END;
      END;
      RecRef.CLOSE;
    END;
  UNTIL Field.NEXT=0;
IF NOT TestMode THEN
BEGIN
  IF BaseRecRef.HASLINKS THEN
    BaseRecRef.DELETELINKS;
  BaseRecRef.DELETE;
  COMMIT;
END;

    Just call it with any record you want.
    It will go through all fields of any other table having a relation to this record and delete it.
  • Slawek_Guzek
    Slawek_Guzek Member Posts: 1,690
    Remco_Reinking wrote: »
    Sometimes there is a need for something like this

    ...Just call it with any record you want. ...

    @Remco_Reinking Someone may copy and paste your code and destroy his data.

    Slawek Guzek
    Dynamics NAV, MS SQL Server, Wherescape RED;
    PRINCE2 Practitioner - License GR657010572SG
    GDPR Certified Data Protection Officer - PECB License DPCDPO1025070-2018-03
  • Tony_NCD
    Tony_NCD Member Posts: 32
    I suggest you download my Live Links add-on. You don't have to install it, but you will need to at least import the FactBox page. Look at the codes behind this page and hopefully you can learn how I did it.
  • vaprog
    vaprog Member Posts: 1,163
    You may find some additional data using the "Table Relations Metadata" system table.

Categories