# Thread: Quick Calculation Question

1. ## Quick Calculation Question

Hi All

I have a parts form built on a simple part details table named table_part.

On this form I have a calculated field that looks at my orders tables and tells me how much the part has been ordered. See code below.

Code:
`tablesum("table_shipping_parts","trim(num) = "+quote(table_part->part_num),"qty")`
The orders tables layout looks like this.

Table_Orders -> Table_Shipping -> Table_Shipping_Parts

I want to modify the filter on the calculation to exclude orders that have not been shipped. On the Table_Shipping table there is a field called shp_date in which a date is entered when the order is shipped.

I've tried using the below addition but it does not work.

Code:
`tablesum("table_shipping_parts","trim(num) = "+quote(table_part->part_num) .And. isnotblank("table_shipping->shp_date"),"qty")`
How can I modify the filter to include this?

2. ## Re: Quick Calculation Question

Tablesum() only allows dealing with one table (itself) in the filter. So it is possible to filter for records where a field in the table equals some external value but it is not possible to filter for records where the value in another table equals (etc) some other value. Also, there is no setsum() where you can sum over a set of tables.

You should be able to get what you want with set.external_record_content_get() and use *TOTAL() on the result.

*TOTAL(set.external_record_content_get("setname.set",......................))

3. ## Re: Quick Calculation Question

Hi Stan

Attached is a sample of the tables I'm working with.

I've tried using your suggested code but have been unsuccessful.

Can you make a further suggestions?

4. ## Re: Quick Calculation Question

Will look in the morning.

5. ## Re: Quick Calculation Question

Looks like this works for me.

Code:
`*total(set.external_record_content_get("set_report_shipping_parts.set","table_shipping_parts->qty","","trim(table_shipping_parts->num) = "+quote(table_part->part_num)+" .And. isnotblank(\"table_shipping->shp_date\")"))`

6. ## Re: Quick Calculation Question

Thank you so much for your help Stan!

7. ## Re: Quick Calculation Question

I know I'm a bit late on this, but I had to look this up in my old code, because I knew I had solved this problem before.

In order to get a set to update a value, I created a script as follows:

t=table.open("year")
t.fetch_first()

while .not. t.fetch_eof()
dim yvalue as n=0
yearindex=alltrim(t.Yearmaker)
s=table.open("baseballcards")
query.filter="yearmaker=yearball"
qry=s.query_create()
yvalue=tablesum("baseballcards","yearmaker = '" + yearindex + "' .and. own='X'","price")

t.change_begin()
t.value=yvalue
t.change_end()
s.close()
t.fetch_Next()
end while
t.close()t=table.open("year")
t.fetch_first()

while .not. t.fetch_eof()
dim yvalue as n=0
yearindex=alltrim(t.Yearmaker)
s=table.open("baseballcards")
query.filter="yearmaker=yearball"
qry=s.query_create()
yvalue=tablesum("baseballcards","yearmaker = '" + yearindex + "' .and. own='X'","price")

t.change_begin()
t.value=yvalue
t.change_end()
s.close()
t.fetch_Next()
end while
t.close()

The dbf "year" is the main dbf in the set while "baseballcards" is the child. They are related by "yearmaker". In any event, I just run a quick loop to go through the records, find the ones I own in the child dbf, and add up the price if I own it, then post that total to the main ("Year") dbf.

Hope this helps or gives another possible solution.

Charlie

8. ## Re: Quick Calculation Question

Tanks for the code Charles!

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•