VFP: how to use data from different tables for calculations

[Viryato]

Hello

I am new to Visual FoxPro and I have been using it for setting up remote views to SQL servers (simple and useful).

I need to set up a costing calculation and I am thinking of using VFP to set it up (back end and front end). I have:
Table A: a list of article prices
Table B: Geometrical aspects and factors
Table C: Process and factors

I need to set up a form in which occurs the following:
1 - The user inputs information on what article he wants;
2 - The app choses the values form the tables accordingly and does the simple math;
3 - It presents the result and registers it;
4 - We can then print a report of this information.


What is the best way to get VFP to do this?
Relations between tables and expression builders?
Transforming the table data into variables and making them available for the calculations?
Can I associate data from a table to an array and vice-versa?

Any help is appreciated

Best Regards

 

[SBTBILL]

You've given an incomplete spec of what seems a fairly detailed project. Not quite sure what you want to do. In general the answer to your questions is yes. You'd need to put up more details to get a more specific answer.

 

[Olaf Doschke]

Just taking the spec that you have three tables, you'll be lost with the vfp wizards as they mainly do forms based on a single table and then a:n forms with a parent-child relation between tables.

In regard to the very general question of the post title, how to use data from several tables, the answer is SQL, not relations. Setting up relations is not even the foxpro way anymore. The sql engine is fast and allows subselects in many places, SQL is a very powerful tool, which is capable to do more than simple relations ond optimizes better.

Bye, Olaf.

 

[Mike Lewis]

I agree with Olaf.

To begin with, forget about the form. Concentrate on writing the query, that is, a SELECT statement that will produce the required result.

Once you've done that, it will be much easier to figure out the user interface and other details.

If you could explain in more detail what you want the query to do, we can give you some help with it. But at this stage, don't worry about how it will look to the user, or whether the data is in a remote view or whatever.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[JRB-Bldr]

First - welcome to Visual Foxpro and this forum.

Secondly - One other piece of advice I would add to the excellent advice given above is that once you have your SQL Query developed, you might want to look over the free video tutorials on VFP at:

http://www.garfieldhudson.com/FreeVideos.aspx

They are intended to give you a basic foundation on how to go about working with VFP such as building a Form and how to put everything into a Project which will eventually build into an EXE.

With that foundation 'under your belt' please feel free to come back with specific questions and we'll do our best to help you out.

Regarding - "What is the best way to get VFP to do this?". You will find that a number of us have varying ways of approaching an issue. It can sometimes just be a matter of development/programming style.

One thing about VFP that differs from many of the other languages is that it offers a wide range of data manipulation 'tools' to support a variety of methods towards getting your work done.

Good Luck,
JRB-Bldr

 

[Viryato]

Hello

Thank you all very much for your interest.
I am capable of developing a simple form application in VFP as I learned in tutorials available on the Internet. Right now I am trying to take
the next step - getting different tables to interact.
I will explain my problem with more detail.

I am attaching a .txt file. Please check the example inside.




My question is: how do you normally set a chain of calculations up?



Through direct procedures in a .prg?

Local lnquant

One line in Table G would be

if model= 'unique'
select table E
scan
if model = 'unique'
lnquant = ...


Associating data from a table to arrays and then using them?


*****************************************************************

If you know of any examples I could see, I would appreciate it.


Best Regards

Viryato

 

[JRB-Bldr]

Lets start by resolving issues with your proposed data table architecture.

The problem that I see in your proposed data tables (from your txt file) is that there is no common field and/or contents with which to 'connect' the records in one table to one or more records in another table.

I did see some common 001, 002, 003, etc. references within a different fields (ICode, GCode, FCode, etc) in each table, but nothing that was 100% common that can be used as a 'linkage key'.
Without any common 'connecting' field, how can you know which information to acquire from any of the other tables?

If all you are doing is allowing the user to select "One from table A and another from table B, etc" and make up a composite group of selections, then there isn't much to do.

Or, if as you say in your txt file, the users are merely making selections from the various tables and then running some calculations on the selected values, then, again, there isn't much to do.
* Retrieve values
* Run calculations
* Display result in some Form Textbox and/or print out.

Perhaps a better explanation is needed so that we can help you better.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Viryato - I will assume that all of your tables are in no way related to each other since there is no common field to use for 'linkage' between them. And with that assumption, I will assume that you are just having your users pick any one record from table A, and another any record from table B, and on and on until done - and then you want to make some calculation based on the variety of selections.

If that is correct, then let's forget about there being 2 or more tables and focus on just getting the selected info from 1 table (you can use the methodology separately on the other tables)

If that is not correct, then, again we need better clarification.

When a user selects a record in a table from a Grid in a form you want to acquire the field data from that record.

BUT the way VFP Forms work, the data collected in one method is not available to another method such as a calculation method so that data needs to be put somewhere so that it can be 'seen' in the calculation method.

Generally that is done by creating new Form Properties to hold the values of interest. These stored values are now available to be 'seen' by other methods, but they are not visible to the user.
ThisForm.Item = Items.Item
ThisForm.ValidPrice = Items.ValidPrice

Or the values can be put into Form Textboxes and displayed on the 'face' of the Form where the user can see them.
ThisForm.txtItem.Text = Items.Item
ThisForm.txtPrice.Text = Items.ValidPrice

Going on.. There are Grid Methods called AfterRowColChange and BeforeRowColChange which can be used to do something when the user changes selected records in a Grid.

I generally use the AfterRowColChange method to put my code where I want to collect information from the selected table fields.

So lets move on to the Calculation Method.
Someone has clicked on a Calculate button and answered a confirmation question "Do you want to make the Calculation?"
Now the calculation just has to 'harvest' the values from either the Form Properties or the Form Textboxes (or a mix of the 2) and run the calculation.

Again, if the above is off-target from what you are looking for, clarify, clarify, clarify.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Typos, typos, typos!

Sorry!
ThisForm.txtItem.Text = Items.Item
ThisForm.txtPrice.Text = Items.ValidPrice
should have been
ThisForm.txtItem.Value = Items.Item
ThisForm.txtPrice.Value = Items.ValidPrice

Good Luck,
JRB-Bldr

 

[Viryato]

Hi

*********************************************************************
I had written an answer and I just read the two last posts from today. If I understand correctly what you suggest is similar to what I do inside a form with one table using
the lost focus event:

local lDia

lDia = thisform.text2.Value

thisform.text4.Value = lDia*PI()
thisform.text5.Value = lDia^2*PI()

thisform.Refresh()


This I an capable of doing.

***********************************************************************

Back to general look on the project

Thank for your questions.I did miss some vital information.We would have to put the
secondary keys in new fields in the tables

1) The primary key for Table A is icode.
2) The primary key for Table B is gcode.
3) The primary key for Table C is ccode.
4) The primary key for Table D is pcode.It will have a secondary key which is gcode from Table B.
5) The primary key for Table E is mcode.It will have to have two secondary keys which are icode from Table A and gcode from Table B

6) The primary key for Table F is costnumber.

7) The primary key for Table G is detail.The secondary keys are costnumber (TableF),
mcode (TableE), pcode (TableD), ccode (Table C), gcode (Table B) and icode (Table A).
Everything is linked now.

In the field "costing_details.cost" we need to use a function to calculate the
sum (for example unique - det001) of the following lines:

a) sum(where model = "unique" itemname *quantity (Table E))
b) where model = "unique"; HR (table D)* other_costs.cost (table C)
c) where model = "unique"; Machine A (table D)* other_costs.cost (table C)

*******************************************************************
So now I think all of the indexes and relations between tables we need are stated.

Our main form would have Table F information in the header and a grid with Table G.

*******************************************************************

My question is what is the best way retrieve values from the other tables to the operations we do in Table G?


Best Regards

 

[JRB-Bldr]

Yes, your tables have Primary Keys (and secondary keys in some instances). You might use them to display the tables in your grid in a desired order but, based on the values shown in your TXT file, the Key fields and their content have no bearing on your question.

There are still some BIG pieces of the explanation which are unclear. If so, then perhaps with still more clarification we will eventually get to what you need.

From your TXT file, it appears as though the subsequent table fields are already populated.

BUT from your most recent post above In the field "costing_details.cost" we need to use a function to calculate the sum (for example unique - det001), it sounds as though the subsequent table fields are not populated and need to be populated using some specific equations.

Lets begin again by clarifying that matter.

Then we can go forward from there.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Here is an example from your TXT file of what is confusing to me.

Table E - Manufactoring Items
quantity
=1.5*area

Is the field Quantity populated with the character string "=1.5*area" or is it empty and you want to populate it with a value which is calculated using that equation?

If you merely want to populate the field(s) with a calculated value, then I might approach it in the following way (note due to the flexibility of VFP, others may use a variety of different approaches):
1. My Form would have various Combo boxes where the user could select which calculation they wanted to run on each Item for each required parameter (Quantity, Area, Cost, etc.).
2. The Form would also have a Grid with the Items listed or where they could be entered.
3. Once the Item was selected and the necessary variety of computations were selected, I'd have the user click on a Compute button.
4. The Form would have various methods added (such as CalcArea, CalcQty, CalcCost, etc.) which would use CASE/ENDCASE to run the appropriate computation based on the user's selections and return a value and then populate the tables accordingly.

But every table would have a common field where its record(s) could be associated with the intended Item from the Items table - possibly something like a field named ItemNo.

If this is off-target from what you need, help me understand better and I'll try to point you in the right direction.

Good Luck,
JRB-Bldr