Filtering a Calculated Field

[MIKE LYON]

Hi folks,

I am trying to design a calculated field for a 1-Many link
table to show the total value of the fields (for 1 orderno)
where another field equals "YES".
IE.

If (Returns->Unit_Adj_Pl="YES") then total the value of Returns->Unit_Value (where the above is YES dont include any with NO or different).

I know how to total the group of fields up

Total(Returns->Unit_Value,GRP->Manufactures,GRP->Returns)

and I know how to check if field says YES

If (Returns->Unit_Adj_Pl="YES")

But I dont know how to join the 2 into 1 script/expression
to only total up the values where the other field shows YES?

Any help would be much appreciated.

Mike

[Stan Mathews]

RE: Filtering a Calculated Field

As a shot in the dark:

Why don't you change your group expression to

Total(Returns->Unit_Value,GRP->Manufactures,Returns->Unit_Adj_Pl="YES") )

[Stan Mathews]

RE: Filtering a Calculated Field

Sorry, cut and paste problem:

Total(Returns->Unit_Value,GRP->Manufactures,GRP->Returns->Unit_Adj_Pl="YES")

[MIKE LYON]

RE: Filtering a Calculated Field

Thanks for reply,
I tried what u said stan but it comes up with
'Argument is incorrect data type error'

Any other suggestions?

Thanks
Mike

[Tom Cone Jr]

RE: Filtering a Calculated Field

Mike, what's the context for the calculated field? Is this a field rule in a table? If so, the fields in the other table will not be visible to the expression.


Is it a calculated display variable on a form? If so, the form must be based on the set which contains the tables referenced in the expression.

[Thomas Henkel]

RE: Filtering a Calculated Field

Is this in a report or on a form?

In reports I use a simple 2-step calculation.

calc1 if(Returns->Unit_Adj_Pl="YES",Returns->Unit_Value,0)
calctot total(calc1,GRP->Manufactures,GRP->Returns)

Basically, if the indicator is on, use the value, else set to 0
total up the calc.

Hope this helps.

Tom

[Stan Mathews]

RE: Filtering a Calculated Field

Sounds like your Returns->Unit_Adj_Pl might be a logical field (Yes/No). If so you can't test for "Yes" (character value). You have to text for Returns->Unit_Adj_Pl = .T.

At least that's what it seems from the error message.

[MIKE LYON]

RE: Filtering a Calculated Field

Thanks again everyone for replying,

Sorry it is a character field length 3 chars

The selection for Returns_Unit_Adj_Pl is done by a drop down list box with 2 option YES or NO (via field rules).

The Calculated field is on a form (generated by form) which is in a set covering all the relevant tables of which 'returns' is the child of 'Manufactures' on a 1-Many link.

Hope this points to the solution?

Many thanks
Mike

[Tom Cone Jr]

RE: Filtering a Calculated Field

Mike, is the link in your set a simple one field expression, or is it a compound expression, designed for example to order to the display of child table records?

Next, have you considered using Dbsum() or TableSum() instead of Total(). They would not depend on current grouping or ordering arrangements in your set... or so it seems to me.

-- tom

[MIKE LYON]

RE: Filtering a Calculated Field

Hi Tom,

Dbsum or Dbtotal works a treat except for 1 thing (yes theres always one) it works out the value correctly but for the whole
table not just the current Quote_No.

To make dbsum work I had to build an index so I tried to filter out other quote nos but it doesnt allow me to cross reference another table?

we are almost there I feel so dont let me down.

Thanks mate for your help so far

Mike

[Tom Cone Jr]

RE: Filtering a Calculated Field

Mike, why not use a filter expression that includes both the "Yes" value you need, and the "Quote_No" ?

-- tom

[Ohlen Cartmell]

RE: Filtering a Calculated Field

You can have dbsum summarize the child records for each parent. Create this type of calculated field at the set level. Create an index for the child table that indexes by the linking field. The calculated field will contain the sum of the values in the child table.

dbsum("child.dbf","quote_no_by_link",quote_no,"fee")

where child.dbf is the child table, quote_no_by_link is the index, quote_no is the linking field, and fee is the field you want totaled. The only drawback is all child records must be saved to the child table for the expression to calculate accurately.

Hope this helps.

Ohlen

[MIKE LYON]

RE: Filtering a Calculated Field

Thank you thank you Ohlen and Tom and everyone for you invaluable help in this matter the nightmare (for me and probrably you) is now solved.

To let you know I created a index in the child.dbf (Returns)
which ordered on Quote_No (linked field with set) and had a filter expression - Unit_Adj_PL="YES"
Then a calculated form field with the following expression -
DBSUM("Returns.dbf","Unit_Adj_P",QUOTE_NO,"Unit_Rtn_Value")

This worked a treat and have yet to find an inaccurate value
so fingers crossed.

If by any chance this does have potential problems please let me know (although you will send me to certain nervous breakdown by doing so).

Thanks again with this kind of support online A5 is second to none.

Mike