calc field question

[Tim Kiebert]

Re: calc field question

Bill,


In the filter the left side of the equals is the the field or combnation of fileds that you want to compare to in the specified table. In your case you want to compare the Location_Code field in the Inventory table. So you simply say Location_Code = .

The right side of the filter is the value that you want to check the left side for. In your case you want that value to be that which is in the Location_Code field in the parent table of your form. If that value is a character type it needs to be in quotes. So if that value is 'A1' then the filter would be Location_Code = "A1". But you want to specify the value as a result of the Location_code field. You need to build this filter by adding the the value in Location_Code to the first half. Also the whole thing has to end up in quotes so it should like this - "Location_Code = 'A1'"

"Location_Code = " + Location_Code would give you "Location_Code = A1"
Since A1 is not in quotes the function will be looking for some variable called A1, which it won't find. So you have to get the A1 into quotes.

You could do this: "Location_Code = '" + Location_Code +"'" which results in this "Location_Code = 'A1'"
or you could this "Location_Code =" + quote(Location_Code) which gives you the same.

So the quote function is just to get the quotes right. Also check out s_quote() which is used for other data types.

What you place in the filter parameter of the function has to evaluate to something that the function can use against the specified table. So, as you noticed "Location_Code = Location_Code" when applied to the table will always be true because the you are simply comparing the field to itself.

When specifying the filter you place the actual final bits that you want the function to use in quotes. ie "Location_Code =" All the variable bits that you want to tack on to that filter you have to add to the parts already in quotes.
The expression knows that it needs to use the second Location_Code from the parent form not because it is in the quote function but because it was outside of the quoted section. The builder provides a clue by the color of the text. In mine the quoted parts are grayish and the the items that will be evaluated and used to build the filter are black.

Hopefully this helps and doesn't confuse.

[[email protected]]

Re: calc field question

That helps a bunch! Thanks so much for taking the time to explain it to me.

Bill Belanger

[Tom Cone Jr]

Re: calc field question

If you add a couple more records in the inventory table using 'A5' as the location code, ie second parent record you will see that your code for currentcases totals all the records rather than break on the parent. In your version both instances of location_code are refering to the inventory table. So you are comparing location_code to itself which will always be true.

Thanks, Tim. I see that now. The testing I did here prior to posting proved a tad inadequate. A corrected example, showing all 3 approaches is attached.

-- tom

[G Gabriel]

Re: calc field question

Why not avoid the traffic, the confusion, single quotations, double quotations, right and left...etc:

tablesum("child","child->Link_fld 
=current(Link_fld)","child->fld_to_sum")

[Tom Cone Jr]

Re: calc field question

Why not avoid the traffic, the confusion, single quotations, double quotations, right and left...etc:

Sounds nice. Doesn't work. Too bad, would be very useful.

In testing here the CURRENT() function is evaluated correctly only for the record that has focus when the form opens. When you navigate to a new parent table record this value does not get re-evaluated automatically. So, as was true in that part of my example that I got wrong, the computed sum of linked child table field values is correct for the first parent table record only.

[G Gabriel]

Re: calc field question

In testing here the CURRENT() function is evaluated correctly only for the record that has focus when the form opens.

If so, it's a bug. It should recalculate on fetching. I used current() in other contexts and it always worked but not in the context of tabelsum(). Actually, I don't use tablesum() that much since it is not the ideal function in terms of speed.