Retrieve related Record from a Table Relation Field in FieldRef

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_GuzekSlawek_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_GuzekSlawek_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
  • waitwait 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
  • NiklasScheuermannNiklasScheuermann 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!
  • NiklasScheuermannNiklasScheuermann 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!
  • waitwait Member Posts: 53
    @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_ReinkingRemco_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_GuzekSlawek_Guzek Member Posts: 1,690
    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_NCDTony_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.
  • vaprogvaprog Member Posts: 1,113
    You may find some additional data using the "Table Relations Metadata" system table.
Sign In or Register to comment.