Need access to 'Table Relation Metadata' table

[MikeGlue]

In order to create a test similar to Codeunit 134926 (Table Relation test) that's specifically for my extension, I need to be able to access the 'Table Relation Metadata' table. As my app is 100% standalone I do not want to run the base test which covers every table in the database.

[defr0std]

The Table Relation Metadata itself could not be opened for extension development since it reveals data from all installed extensions.

Is there anything else that you would like to test besides what is already in Codeunit 134926? Perhaps some of these checks could be performed at the compilation time since such metadata is available to the compiler as well.

[MikeGlue]

Making it part of the compiler only works if I am making changes to my extension. Being able to run this via automated test allows me to test the scenarios when our extension hasn't changed but the base product has changed.

The test that is in COD 134926 is quite useful. It would be really nice if we could leverage this test somehow for our extension. If that table cannot be made available, perhaps the test can be changed to call an external library function that I can pass an object from/to range for it to check?

[NKarolak]

I will need the functionality from Table Relations Metadata as well, in that way:

  TableRelationsMetadata.SETRANGE("Table ID",1,1999999999);
  TableRelationsMetadata.SETRANGE("Related Table ID",My_Custom_Set_Table);
  TableRelationsMetadata.SETRANGE("Related Field No.",My_Custom_Set_ID_Field);
  IF TableRelationsMetadata.FINDSET(TRUE) THEN BEGIN
    REPEAT
      FieldRefModifyAll(
        TableRelationsMetadata."Table ID",
        TableRelationsMetadata."Field No.",
        FORMAT(My_Custom_Set_ID_Field),0);
    UNTIL TableRelationsMetadata.NEXT = 0;
  END;

Is it possible somehow to expose at least the records referring to own tables ("Related Table/Field ...") ?

[defr0std]

@MikeGlue Just thinking out loud, the scenario with base product changes could also be supported. A new base product version always comes with newer symbol .app files. If you recompiled your code against the new symbols, you would get the validation errors. This is of course given that validation rules could be implemented in the compiler. Would this scenario work for you or would you still prefer to run the test on the actual database?

@NKarolak Thanks. Could you describe a bit a business scenario? In what situation do you need to run this code to update the "Field No."?

[NKarolak]

@dennisfrostlander In our add-on, we have created something similar to Dimension Sets, just for a set of Drawings (custom master table). The Drawing Set ID field has been added to some standard tables (with an according table relation to a new "Drawing Set" table: here, the concept differs from the Dimension Sets).

Whenever a Drawing (the master table) will be deleted, we need to delete all Drawing Set Entries of that Drawing. But when we delete the last entry of some Drawing Set, every table that has this Drawing Set assigned, needs to be updated with the Value 0 --> "no Drawing Set attached".

Of course, I could run the update of affected tables "old-school", by coding each affected table as a record variable. But if I do it as suggested above, my code does not need to be updated whenever the Drawing Set ID is added to additional tables, no matter whether standard or customized, no matter whether by me or by the customer.

[MikeGlue]

I'd prefer an automated test I think. What I'm imagining is that anytime an update CU, etc.) comes out from Microsoft I'll be running my suite of tests for each of my apps against that new version via Docker. I think it would make more sense to be able to test this in an automated fashion as opposed to having to manually open each of my apps in VS Code to build against the new symbols.

[defr0std]

@MikeGlue No doubt about test being automated. It is possible to compile AL project from the command line (although this is not yet documented) so you will not need to compile manually in VSCode. In AL extension installation folder, there is the alc.exe utility.

@NKarolak Thanks for the info, we'll add this to our backlog.

[fzoeter]

Any news on this metadata issue? We desperately need table relation info for our add-on. Type of relation (composition, reference etc.) and the field mappings. Until now we could easily retrieve that information from the Object Metadata table. Or is there any other way in AL code?

[defr0std]

@fzoeter Is all necessary information for your scenario available in Table Relation Metadata? This table is on our backlog, but we have not started working on this yet. Object Metadata is not likely to be opened for extensions since it exposes internal undocumented format of objects. So if you need additional fields which are not provided by Table Relation Metadata, please let us know the full list.

P.S. If you targeting on-premise extension only, you can always set "target": "Internal" in app.json and get access to all tables as before. However, you will not be able to submit such extensions to AppSource.

[fzoeter]

Hi Dennis,

I just checked the relations of tables 36 -> 18 and 37 -> 36. The first one Sales Table to Customer looks ok, just a foreign key value that refers to the primary key of the reference table (even field ‘Bill-to Name’ seems have a relation to the Customer table defined?). For the second one I do miss the fields that are used in the WHERE clause, "Sales Header".No. WHERE (Document Type=FIELD(Document Type)). Field ‘Document No’ info is available in the Table Relation Metadata while ‘Document Type’ is not. It seems to me that both fields should be present in the Table Relation Metadata. But, if you agree on that, then everything I need is available in that table.

Best Regards, Frans Z

[dmytrk]

Hi @dennisfrostlander , In our add-on, we want to validate child tables when parent table is changed. For example, when you release sales invoice, we need to validate related (child) table 37. So far we were using “Table Relations Metadata” (fields "Table ID", "Field No.", and "Related Table ID"). As a workaround, we can hardcode relations but doesn’t sound right to me. br, Dmytro