Alpha Video Training
Results 1 to 8 of 8

Thread: Quick Calculation Question

  1. #1
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default 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. #2
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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",......................))
    Last edited by Stan Mathews; 06-27-2013 at 04:06 PM.
    There can be only one.

  3. #3
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default 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?
    Attached Files Attached Files

  4. #4
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default Re: Quick Calculation Question

    Will look in the morning.
    There can be only one.

  5. #5
    "Certified" Alphaholic Stan Mathews's Avatar
    Real Name
    Stan Mathews
    Join Date
    Apr 2000
    Location
    Bowling Green, KY
    Posts
    25,119

    Default 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\")"))
    There can be only one.

  6. #6
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Quick Calculation Question

    Thank you so much for your help Stan!

  7. #7
    "Certified" Alphaholic Charles Hoens's Avatar
    Real Name
    Charles Hoens
    Join Date
    Sep 2000
    Location
    Princeton, NJ
    Posts
    1,170

    Default 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. #8
    Member
    Real Name
    Melvin
    Join Date
    Nov 2011
    Posts
    133

    Default Re: Quick Calculation Question

    Tanks for the code Charles!

Similar Threads

  1. Quick Calculation Question
    By ADCO Service in forum Alpha Five Version 11 - Desktop Applications
    Replies: 6
    Last Post: 12-13-2012, 02:59 PM
  2. quick help with a calculation
    By hov333 in forum Alpha Five Version 10 - Desktop Applications
    Replies: 51
    Last Post: 08-24-2011, 01:35 PM
  3. Another Quick Question
    By Rick Canady in forum Alpha Five Version 5
    Replies: 9
    Last Post: 01-27-2004, 04:24 AM
  4. Quick Question.
    By Jamin Dunivan in forum Alpha Five Version 4
    Replies: 1
    Last Post: 04-25-2002, 08:52 AM
  5. Quick - Quick Filter Question
    By forskare in forum Alpha Five Version 4
    Replies: 2
    Last Post: 05-08-2001, 10:08 AM

Bookmarks

Posting Permissions

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