I have Table 1 with fields Group No., Sales Order No., Sales Line No., Item No., Component ID. Item No. is Code 20 and Component ID is Integer. The primary key is Sales Order No.,Sales Order Line No.,Item No. I have Table 2 with fields Group No., Sales Order No., Sales Line No., Item No, Type. In a form whose source table is Table 2, I have to lookup Item table(base Navision Item table 27) if the Type field is ‘Item’. If the Type field is ‘Component’ I have to lookup Table 1 and need to bring in the Component ID of Table 1 into the field Item No. of Table 2. I am using the OnLookup trigger to do that. My code looks like this: IF Type = Type::“BOM Component” THEN BEGIN BOMComponent.RESET; BOMComponent.SETCURRENTKEY(“Sales Order No.”,“Sales Order Line No.”,“Component ID”); BOMComponent.SETRANGE(“Group No.”,“Group No.”); IF FORM.RUNMODAL 50061,BOMComponent, BOMComponent.“Component ID”) = ACTION::LookupOK THEN BEGIN IF “Line no.” <> xRec.“Line no.” THEN xRec.INIT; VALIDATE(“Item/BOM Component No.”, FORMAT(BOMComponent.“Component ID”)); END; END; The above code fails and gives error that there is no record within the filter. However if I replace the line VALIDATE(“Item/BOM Component No.”,FORMAT(BOMComponent.“Component ID”)); with the following line “Item/BOM Component No.” := FORMAT(BOMComponent.“Component ID”); lookup works but if I manually enter the component ID it gives error. Is it because the primary key of Table 1 does not have component ID in it? I cannot add Compnent ID to the primary key because the value to this field is assigned some time after the record is created. Please advise if there is a work-around for this problem. Thanks. Pari Somasundaram.
Because it is not possible to set the tablerelation between an integer and a code field, I guess that you check the relation in the OnValidate-Trigger of “Item/BOM Component No.”. If so, maybe something goes wrong over there. It looks like that you set to many filters somewhere in your code. (It’s only a wild guess). Reijer.
quote:
Originally posted by reijer: Because it is not possible to set the tablerelation between an integer and a code field, I guess that you check the relation in the OnValidate-Trigger of “Item/BOM Component No.”. If so, maybe something goes wrong over there. It looks like that you set to many filters somewhere in your code. (It’s only a wild guess). Reijer.
Reijer: Thanks. It now works. All I had to do was remove the Table Relation from the property of the field since I’m now using the OnLookup trigger. If the table relation is still there, it validates with the primary key of the table that I’m looking up and so gives the error. The integer to Code conversion does not cause a problem because I’m using the FORMAT function to legally convert the type.
Ok, now I understand the problem. By the way, it is possible to set a relation to a field that is not part of the key. You only have to fill the Field property in your tablerelation. But in your case it does not work because it is a code – integer relation. Beware that you have to check, by code, the relation. If the user enters a value, without using the lookup, it is possible that the entered value is not a “Component ID”. Greetings, Reijer PS: Funny to see the xrec.init again.
quote:
Originally posted by reijer: Ok, now I understand the problem. By the way, it is possible to set a relation to a field that is not part of the key. You only have to fill the Field property in your tablerelation. But in your case it does not work because it is a code – integer relation. Beware that you have to check, by code, the relation. If the user enters a value, without using the lookup, it is possible that the entered value is not a “Component ID”. Greetings, Reijer PS: Funny to see the xrec.init again.
Thanks for the input on tablerelation. I have taken care of validation thru code for the component ID. To include xrec.INIT in lookup trigger was something I learnt from the Forum. Pari.