How to validate a table relation in C/AL code?

frisch

I'm running into an issue with validating tables, and hope someone here has an idea about how to solve this.
Example:

In table Sales Header (36), there's a field Shipping Agent Service Code (5794), which has a complicated relation to the Shipping Agent Services (5790) table:

"Shipping Agent Services".Code WHERE (Shipping Agent Code=FIELD(Shipping Agent Code))

In my requirements, I have to check the contents of this field before running code that requires my Sales Header to be Status::Released.

I started by running a TESTFIELD, but this only checks if the field is populated. It does not check the contents of the field against the table relation!
My first idea to solve this was try to VALIDATE in this situation, but VALIDATE does the following:
- check the table relation;
- run code in OnValidate trigger.

However, the OnValidate trigger for this field contains this line:

Shipping Agent Service Code - OnValidate()
TESTFIELD(Status,Status::Open);

Hmm... so the code I'm running requires Status to be Released, and the OnValidate trigger requires it to be Open. Problem!

I don't give up thát quick, so I built a little function that checks the Field table and picks up TableRelation and FieldRelation.
This works for most fields, but if you do that for Shipping Agent Service Code, you will see that both are set to 0 Actually, this is the case for all complicated relations.

Question: How can I validate the contents of the field in table 36 against the table relation, without running the OnValidate trigger?
If this is not possible by standard, is there a way to retrieve the complicated table relation and either parse this or load it into a filter?

Kishorm

Hmmm, just had a look at the code in 2016 and I don't particularly like how they are validating the relation (calling COMMIT followed if an IF CODEUNIT.RUN())

I'd suggest having a look at the virtual table 2000000141 "Table Relations Metadata" - it basically gives to all the info you enter when setting up table relations - even complex ones with filters and relations to multiple tables etc... (like "No." field on Sales Line for example).

postsaurav

I am not sure that I get you completed, but does this make sense -

You can have something like -

ShippingAgentServices.SETRANGE("Shipping Agent Code",SalesHeader."Shipping Agent Code");
IF NOT ShippingAgentServices.FINDFIRST THEN
//NOT FOUND
ELSE
//FOUND

Where -
ShippingAgentServices = Rec of Table 5790.
SalesHeader = Rec of Table 36.

rsaritzky

The table relation that you show is not that complicated in the grand scheme of the NAV world - there are many other table relations that are much more complex. That being said, Suarav gave an example that is something you should keep in your pocket of "Nav things to know" - just look at the table relation and translate it into SETRANGE or SETFILTER commands and execute the same "validation" as in the TableRelation property.

One thing to mention however - if the field is populated, then it MUST have passed the TableRelation. So any value in that field WILL appear in the the Shipping Agent Services Table - with one exception. It IS possible to DELETE a value in the Shipping Agent Services table AFTER it has been entered on a Sales Header - there is no checking upon DELETE in that table.

The TESTFIELD(Status,Status::Open) in the OnValidate trigger is enforcing a business rule that you can't change the Shipping Agent Services Code if the order has been released - you need to Reopen the order before changing. This, in my opinion is a pretty good business rule - a common reason for this might be if you change the services from "Next Day" to "overnight", you might want to change the shipping charge.

So use Suarav's code as a template, and do other checks/code as necessary in the "found" and "not found" blocks.

Good luck!

Ron

Kishorm

If I understand correctly you just want to validate that the "Shipping Agent Service Code" is valid? Assuming this is the case then all you need to do is...

IF NOT ShippingAgentService.GET(SalesHeader."Shipping Agent Code",SalesHeader."Shipping Agent Service Code") THEN
  ERROR('Invalid Shipping Agent Code')

...hopefully the variables are self-explanatory, also good practice to use TextConstants instead of hard coded text strings

frisch

First things first, thanks for all your responses!

However, it's a bit more complicated than it looks.

About the relation: If I remember right from my database lessons, a table relation with filters was called a "complicated relation". Then again, I was in that class in 2000 and back then I like to party, so correct me if I'm wrong

Basically, what I built is a generic function which I can call from anywhere, similar to VALIDATE("Shipping Agent Service Code"). I built it as generic as possible, so I can basically throw any record variable at it that I want to. Like this:

MyCodeunit.IsTableRelationOK("Sales Header", 'Shipping Agent Service Code', TRUE);

Where "Sales Header" ends up in a variant that is handled into a RecordRef (or accepted as a RecordRef, if a RecordRef is sent in).
Shipping Agent Service Code is also sent into a variant, which is checked for being either Integer, Text or FieldRef.
The TRUE is always a boolean, which determines if I want the function to either throw an error or just return a boolean answering the "Is Table Relation OK?" question.

The function works fine as long as the relation is simple, and it gets a TableRelation and FieldRelation integer. I also want it to check the complicated relation as described, and that's my challenge...

Reasons I want to do it this way are:
- My code is much clearer and more compact when I call MyCodeunit.IsTableRelationOK, than it is to call all SETRANGE and FINDFIRST or FINDSET commands every time I need to check a certain relation;
- Generic functions might save me time in the future (when I need something similar);
- If Microsoft (or someone else ) ever decides to change the relation, my code won't fail but will follow;

Kishorm

Ah generic relationship validation...if only you'd said at the start

Have a look at how standard NAV does it - take a look at codeunit 8611 "Config Package Management" - I'm sure you'll find it interesting

Kishorm

Hmmm, just had a look at the code in 2016 and I don't particularly like how they are validating the relation (calling COMMIT followed if an IF CODEUNIT.RUN())

I'd suggest having a look at the virtual table 2000000141 "Table Relations Metadata" - it basically gives to all the info you enter when setting up table relations - even complex ones with filters and relations to multiple tables etc... (like "No." field on Sales Line for example).

frisch

rsaritzky wrote: »

One thing to mention however - if the field is populated, then it MUST have passed the TableRelation. So any value in that field WILL appear in the the Shipping Agent Services Table - with one exception. It IS possible to DELETE a value in the Shipping Agent Services table AFTER it has been entered on a Sales Header - there is no checking upon DELETE in that table.

Getting back to this, because I think it's actually quite important. During my time at end users I saw this happening a lot. I think it's good to "recurse" table relations too. So if you f.e. build a custom field on the Customer table that refers to MyCustomerzation table, and then someone does a RENAME or DELETE on your table, you should check the Customers again to ensure consistency. Standard NAV does this sometimes.. but not always

Kishorm wrote: »

Hmmm, just had a look at the code in 2016 and I don't particularly like how they are validating the relation (calling COMMIT followed if an IF CODEUNIT.RUN())

Hmm... going to take a look at that code in 2015 though, because ...

Kishorm wrote: »

I'd suggest having a look at the virtual table 2000000141 "Table Relations Metadata" - it basically gives to all the info you enter when setting up table relations - even complex ones with filters and relations to multiple tables etc... (like "No." field on Sales Line for example).

... I was getting ready to send you kisses. Had been searching for a table like this, and this table is exactly what I was looking for. It will solve the problem for the future (in our 2016 databases). However, in 2015 (where the problem that I'm solving occurred), there is no such table

frisch

... took a look at codeunits around the 8600 range. They use VALIDATE to check the contents of a field.
So generic relation validation is only really possible from NAV 2016.

Funny to see though how similar my code is to the GetRelationInfoByIDs function in cu 8617