Modifying data to Table

navRah

Hello all,

I'm a newbie here, just started learning navision programming. I'm planning to design a simple monthly installment payment plan which looks as following:

Line no Payment No. Amount to be paid

100 Payment 1 5000
200 Payment 2 5000
Payment 3 5000
Payment 4 5000

So far I have setup two tables with one filled with some random values (entered manually) and the second table takes this as an input and does some calculation before inserting records into it.

Table Structure:
1. Installment Payment Header: (No. [Primary Key], Installments, Amount)
2. Installment Payment Line: (No. (PK - Table relation is assigned),Line No.(PK), Payment No., Amount per installment, Due Date)

As now Table number 1 is assigned with the following value:
100, 10,111.13

And I've written a simple codeunit which fetches data from the header and performs some calculation before inserting the data to the line table.

post which the page looks like as follows:

[img]https://us.v-cdn.net/5022383/uploads/editor/yk/a7aw25hxy4wg.png[/img]

The question is, if i make a change to one of the value of amount to be paid (increase or decrease the value) then how do I change the amount to be paid for the rest of the payment numbers through c/al code. How can i read the updated data and recalculate the payments ?

Any help on this is really appreciated ?

Slawek_Guzek

Hi,

The typical NAV code structure for updating existing records is the loop.

Assuming that RecordVar variable is declared as a Record variable for the table RecordVarTable:

RecordVar.SETRANGE(SomeField1, 'Value1'); 
RecordVar.SETFILTER(SomeField2, '>%1', 'AnotherValue');
IF RecordVar.FINDSET(TRUE) THEN
REPEAT
  RecordVar."Another Field" := value;
  RecordVar.MODIFY;
UNTIL RecordVar.NEXT = 0;

Since you have asked the question in the SQL General forum I guess you have some T-SQL exposure so I will refer to T-SQL examples to explain C/AL coding.

The first two lines of C/AL code are setting filters on 2 different fields - this is an equivalent to SQL predicate WHERE SomeField1 = 'Value1' AND SomeField2 > 'AnotherValue'; (kind of - as these commands do not fire any SQL statements, they merely prepare the internal structures to build SQL query fired by another command )

RecordVar.FINDSET(TRUE) 'prepares' all the records to be sequentially transmitted into NAV, the remaining is the actual loop structure. This is where NAV fires SELECT * FROM RecordVarTable T-SQL query, and transmits partial results into the buffers.

REPEAT is the beginning of the loop.

RecordVar."Another Field" := updates the field in a record in memory, but the change is not written to the database yet.

RecordVar.MODIFY updated the current record in the database, it is equivalent of firing UPDATE on a single record - the current record.

UNTIL is the loop closing command, it transfers control to the first line after REPEAT as long as its parameter is FALSE.

REPEAT UNTIL FALSE constitutes an infinite loop. REPEAT UNTIL TRUE means no looping at all (the code between REPEAT and UNTIL however will be executed once.

RecordVar.NEXT fetches the next record from the record set, it returns numbers of records retrieved. 0 means no more records in the set, therefore the construct UNTIL RecordVar.NEXT =0 ends the loop when no more records were fetched by the NEXT command.

In trivial cases like in example above (all records within the filter updated to the same values) you can use simpler construct:

RecordVar.SETRANGE(SomeField1, 'Value1'); 
RecordVar.SETFILTER(SomeField2, '>%1', 'AnotherValue');
RecordVar.MODIFYALL("Another Field", value);

This is the direct equivalent of UPDATE RecordVarTable SET "Another Field" = value WHERE SomeField1 = 'Value1' AND SomeField2 > 'AnotherValue';

No looping and prefetching data into NAV Client goes here, NAV simply fires the UPDATE command.

When it comes to technicalities how to update records that's pretty much all.

Slawek

navRah

Thank you so much for your detailed explanation. I will try to work it out and then come back with further questions.