Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Calculate using a field in current & prev record

Hi,

Is there an easy way to calculate the difference (movement) between a field value in my current record and the value of the same field in a either the previous or the next record. This should be done with all the records in the table (like a calculated field).

i.e.
                  field         calculated field
record 12        1090
record 13        1096        6(movement 13-12)

Thanks
 
0
Surv
Asked:
Surv
1 Solution
 
johnstonedCommented:
would this work for you?

function Difference(Direction: string): integer;
var
  Current: integer;
begin
  Current := table.fieldByname('calculated').AsInteger; // get current value;

  if Direction = 'Next' then
  begin    
    table.Next;
    result := table.fieldbyname('calculated').AsInteger - current;
    table.previous;
  end
  else if Direction = 'Previous' then
  begin
    table.previous;
    result := current - table.fieldbyname('calculated').AsInteger;
    table.next;
  end
  else
    result := 0;
end;

assuming your table is called table etc.

Hope that helps,

Dave.
0
 
SurvAuthor Commented:
Adjusted points from 40 to 100
0
 
SurvAuthor Commented:
Edited text of question.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
simonetCommented:
What database server are you using?

Alex
0
 
SurvAuthor Commented:
Hi Simonet,

No server, just a small paradox 7 table, stand alone pc.

Is my question clear ? It almost seems as if I wrote it a bit confusing.

I have a field which stores a value. All I would like to do is create an extra field or whatever to display the movements of the difference between this field's values between records It should display this "movement" field in a dbgrid preferably.

I am quite new to delphi / Paradox etc.
If you know FileExpress for example, you could enter a function which will calculate the movement between fields in different records by accessing the next or previous records without moving the focus to them i.e.



field_z := field_3 - field_3(prev)

Can you possibly help me to an answer.

Cheers
0
 
IPCHCommented:
Hi Surv

If you use Paradox I'll propuse you some kind of decision. Use 2 TTable components to connect to one Paradox table. Create calculated field , let say
Table1Calc and take a look at this code:

procedure TForm1.Table1CalcFields(DataSet: TDataSet);
begin
  If Table1.RecNo=1
  Then Table1Calc.Value:=0
  Else Begin
         Table2.First;
         table2.MoveBy(Table1.RecNo-2);
         Table1Calc.Value:=Table1AmountPaid.Value-table2.FieldByName('AmountPaid').Value;
       end;
end;

It's not very elegant, but it's working I checked it with Delphi demo database table orders.db.

Hope it'll help you.

Regards
Ivan
0
 
SurvAuthor Commented:
Comment accepted as answer
0
 
SurvAuthor Commented:
Thanks Ivan,

I can use it, it works ok.

cheers - Surv
0
 
IPCHCommented:
You well come.

Ivan
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now