You are here:

FoxPro/Calculated fields

Advertisement


Question
QUESTION: Hi Fred
Am pleased to say that I have foxpro 9 now and with some help and reading beginning to find my way around. I have a simple but frustrating query that I hope you may be able to help me with?

I have grid within a form that is bound to a table. The grid displays, amongst others, the fields Net, VAT and Gross. I want the Gross field to be calculated from the Net plus VAT fields. How (or more to the point - where) do I tell foxpro to calculate this value? Its property control source is the underlying field from the table.

Thanks for any assistance.
Dave

ANSWER: Hi David,

  There are a few choices for storing the GROSS value.

1.  At the time of data entry, as part of the save process use:

   REPLACE GROSS WITH NET + VAT

   If you are using memory fields along with SCATTER & GATHER use:

   m.GROSS = m.NET + m.VAT
   Use this before the GATHER command.
       OR
   Use the REPLACE command above, but AFTER the GATHER.

   With this method, the GROSS field in your primary table will always
   be populated, for whatever you need it for later.

2.  Store the GROSS value for the entire table at the same time with:

   REPLACE ALL GROSS WITH NET + VAT

   this will also allow the GROSS value to be used when & where you need
   it, but requires an extra step whenever new data is added to the table.

3.  Display the value in the grid only.  If the table bound to the grid is
   the result of a query,

   use:  "NET + VAT AS GROSS" as part of your query

   Such as:  select <your fields>, NET + VAT AS GROSS from...

   IMPORTANT NOTE:  This will populate the grid, but the GROSS field in the
         primary table will still NOT have data.

I would strongly recommend option 1.  It will take nearly zero time to store
the value and has the advantage of always being there when you need it.

Good Luck,

Fred



---------- FOLLOW-UP ----------

QUESTION: Hi Fred

Many thanks for your reply. Please may I advise you of my uncertainty which is not necessarily the coding but exactly where to write the code.

I have created a temporary table which after being populated with data will be used for further processing. Once the data passes its validation and is “saved” the temporary table is used to update another table and can be cleared out. This temporary table (not a query) is used by the form and displayed as a grid. The grid is to contain the fields of the data input. I specifically want the grid to allow for as many lines as the user desires. [I have chosen to use the bound table approach because I do not know whether you can create a scrollable list from unbound fields].

I want the user to enter the Net and VAT amounts and then I want the Gross amount to be updated and displayed in the grid but not editable by the user.  This will help provide a visible check of the total to the user. My lack of understanding is where to write the summation command. The Gross field currently being displayed is part of the underlying table. I presumed that the summation would be entered against one of its properties, within the form. But I cannot see which one if indeed this is where it should be written. Again I am presuming that the grid must bound to the table and cannot contain unbound fields.

From what you have said I glean that you may need to create a variable to store the calculated field – but can you do that as part of the grid bound to the table?

I hope this makes sense to you and sorry for my lack of knowledge but hope you might be able to point me in the right direction.

Thank you for your assistance.

Best regards

Dave

Answer
Dave,

I assume you posted this follow-up before you figured out to put the code in the LOSTFOCUS method.  

The code sample I gave you in the comment after your rating did not take into account the grid.
I don't have a great deal of experience using grids, so I'm not certain that process will work.
If it does, there will be additional elements to the command in the NET field.
It will be something like "thisform.grid1.column#.lostfocus" (there may be others between the column and the method).

As you type the command into the LOSTFOCUS method of the NET field, it will prompt you for the next object in the string as soon as you enter the period at the end of each object.  That way you will be able to find the appropriate LOSTFOCUS method as you type.

Fred

FoxPro

All Answers


Answers by Expert:


Ask Experts

Volunteer


Fred Frase

Expertise

Questions about development only. No installation or hardware specific questions, please. Your first step in requesting assistance should be to identify the version of FoxPro you are using. I can write short functions but, PLEASE do not ask me to write programs for you.

Experience

Nearly 30 years professional programming experience using FoxPro (FoxBase through VFP 9) or other xbase language, primarily in Windows environments.

Education/Credentials
Completed a 1,000 hour diploma course in programming at International Academy of Ohio (subsequently merged with Southern Ohio College).

©2016 About.com. All rights reserved.