Quick Calculation Question

[ADCO Service]

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.

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.

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?

[Stan Mathews]

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",......................))

[ADCO Service]

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?

[Stan Mathews]

Will look in the morning.

[Stan Mathews]

Looks like this works for me.

*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\")"))

[ADCO Service]

Thank you so much for your help Stan!

[Charles Hoens]

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

[ADCO Service]

Tanks for the code Charles!