Hi everyone,
I have two tables with the same primary key and many identical boolean fields(same id,same value).
I want that when I delete a record from table1, the boolean in table2 take all the value false.
Example: record in table1: key=xxx, bool1=true bool2=true
record in table2: key=xxx, bool1=true bool2=true
If I delete the record in table1:
record in table2: key=xxx, bool1=false bool2=false
Is it possible?
Get table 2 record in Table 1 OnValidate trigger and change the Booleans to false.
Are you sure??? Is the trigger onvalidate executed when I delete a record???
I’d say that Mohana meant that you should put your code in the OnDelete trigger of table 1, not in an OnValidate trigger.
One caveat though, if the process of deleting the record in table 1 doesn’t cause the OnDelete trigger to fire (as might be the case if you’re deleting the record in code), then you won’t see this update in table 2.
Sorry, its a typo.
It should be onDelete trigger.
Or you could make the fields in table 2 as lookup flowfields to table 1. If the table 1 record exist, the fields in table 2 would have the same value. If the table 1 record does not exist, the fields in table 2 would be false.
Or redesign your solution so you don’t need 2 tables that hold the same information.
Hi,
If the tables contain less no. of records, then you can make the two fields as flowfield in Table 2 and provide the calcformula as
Exist(Table1 WHERE (PKey=FIELD(PKey))) and for second field Exist(Table1 WHERE (PKey=FIELD(PKey))).
If the table will have large amount of data, It is not preferred to use flowfields, then write the code on OnDelete trigger of the Table1 to change the status of both Booleans onto the table2.
Regards
Bheem
I think that the suggestions for re-architecting the existing solution by creating flowfields and the like, while technically valid (yes, you can create flowfields), fail on their face for lack of a key fundamental condition … we do not know the underlying business case for the current architecture. We can throw all sorts of tools into the discussion, but without knowing the reasons and objectives of the feature, we have no basis for suggesting that any of those tools would be better than any of the others to address the business case.
Normally, when a customer walks into a hardware store and asks for a 1/4" drill bit, you may safely assume that what the customer really needs is a 1/4" hole. We can’t make any such assumptions about this case, so we’re left to conclude that what Poppins is asking for is what he really needs; in this case, how to update one record when another is deleted. Unless the request is so convoluted as to defy reason, we really ought not beg the question.
IMHO, your mileage may vary 
I would agree with you in a situation where there was a 1-to-many relation between the tables. Or there was not index relationship between the tables. However in this situation there’s a 1-to-1 relation based on the primary key (let’s make the reasonable assumption that it’s also the clustered index). Thus the size of either table is really not an issue.
Also, while the original post does not mention the NAV version, if this is NAV 2013 (or above) the use of SETAUTOCALCFIELDS would allow the retrieval of data from both tables in a single query. Further reducing (and effectively removing) the performance impact. While also avoiding the data integrity risk of a trigger not firing.