Updating value in one table based on value in another

blh

Hello all,
Here is my issue: I have a table (Sales Contract) which contains contract information and a date field which I need to update. The updates need to come from TableB which contains nothing but the contract number and a date. The date field from Sales Contract needs to be updated with the date value from TableB when the no. field from Sales Contract equals the ContractNo field from TableB. I wrote a report and in the OnPreReport() I wrote the following code:
IF SalesContract.”No.” = TableB.ContractNo THEN
SalesContract.”Created Date” := TableB.DocumentDate;
SalesContract.MODIFY;
When I run the report I get an error message saying that “Sales contract No. ‘’ does not exist.”
I’m not sure where the problem is. Any help or pointing to resources that might help would be greatly appreciated. Thanks in advance for any assistance.

nverma

you should try to putting the code in onaftergetrecord trigger...once the system fetches the record then you can do the if statement...and do the compare...

OnPreReport trigger is executed before a report is run. Typically, this trigger is used to get the filters of the report.

mohana_cse06

blh wrote:

Hello all,
Here is my issue: I have a table (Sales Contract) which contains contract information and a date field which I need to update. The updates need to come from TableB which contains nothing but the contract number and a date. The date field from Sales Contract needs to be updated with the date value from TableB when the no. field from Sales Contract equals the ContractNo field from TableB. I wrote a report and in the OnPreReport() I wrote the following code:
IF SalesContract.”No.” = TableB.ContractNo THEN
SalesContract.”Created Date” := TableB.DocumentDate;
SalesContract.MODIFY;
When I run the report I get an error message saying that “Sales contract No. ‘’ does not exist.”
I’m not sure where the problem is. Any help or pointing to resources that might help would be greatly appreciated. Thanks in advance for any assistance.

What are your dataitems in report and how they are linked?

blh

The dataitems are the Sales Contract table and TableB. The dataitem table link in the properties of TableB is ContractNo=FIELD(No.). I revised the report and placed the code in in the OnAfterGetRecord trigger of TableB but I wtill receive the same error. Thanks to you both mohana_cse06 and nverma for your replies.

nverma

is the tableb indented under the sales dataitem in the report viewer??

blh

nverma,
Yes TableB is indented under the Sales Contract table.

mohana_cse06

I suggest you to take tableB as first dataitem and indent Sales Contract dataitem under it..
Delete global record variables if declared

MGM08

//IF SalesContract.”No.” = TableB.ContractNo THEN

IF SalesContract.”No.” = TableB.ContractNo THEN begin //Changed
SalesContract.”Created Date” := TableB.DocumentDate;
SalesContract.MODIFY;
End;//Added

Try this code and check.

blh

Mohana_cse06 your suggestion worked! Thanks so much for your help. Thanks as well to nverma and MGM08 for your suggestions.

mohana_cse06

blh wrote:

Mohana_cse06 your suggestion worked! Thanks so much for your help.

Welcome