Alpha Video Training
Results 1 to 14 of 14

Thread: Stock Control

  1. #1
    Member
    Real Name
    David Corcoran
    Join Date
    Oct 2001
    Posts
    218

    Default Stock Control

    I have a database based on the alpha sports invoice module. I want to extract from two different child data file both on sets of one - many relationship.

    My question has any one got ang sugestions on how I construct the set so that I can get all the items from the "out" field in "invoice_Items" and all the items from the "in" field in "Stock_rcv_items". I set up a new set with the parent "products" and the other two as child with one to many relationship but cannot design a repor that will give me a summery of the fields "out" and "ins" for each individual "Product_Id" but it does not work. The purpose of this report is to give me to give me a result of how much we bought and invoiced for each product over a given period. Hope someone can help.

    Kind regards
    David

  2. #2
    Volunteer Moderator Peter.Greulich's Avatar
    Real Name
    Peter Greulich
    Join Date
    Apr 2000
    Location
    Boston, MA
    Posts
    11,642

    Default Re: Stock Control

    Assuming that:
    • all 3 tables have the same product_id field
    • on a form you can see the 3 tables with the correct records (this is just to make sure your records are properly linked)
    then...

    create a report with 2 subreports (one for each child table) in the report's detail section.

    That should work.

  3. #3
    Member
    Real Name
    David Corcoran
    Join Date
    Oct 2001
    Posts
    218

    Default Re: Stock Control

    Thanks Peter I stupidly tried using a sub form in the detail but used the product_id,description and outs from the invoice_items fields and and the sub form with the ins field based on stock_rcv_items. Thank you again.

    Kind regards
    David

  4. #4
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: Stock Control

    As Peter suggested:

    Make a set with 3 tables

    Master table is Product, with key field of item #.
    1 to many on Child 1 is items_in, with key field of item #
    1 to many on Child 2 is items_out, with key field of item #

    You can then create a report of items in Product with calculated fields
    that sum the child records for both in and out.
    See the DBSUM() & TABLESUM() in the XBASIC Reference Guide.

  5. #5
    Member
    Real Name
    David Corcoran
    Join Date
    Oct 2001
    Posts
    218

    Default Re: Stock Control

    I am really struggling with this report I will try and layout how I have constructed it.
    Set
    Product --- Invoice_items one – many by Product_id and also Stock_rcv_items one – to many also linked by Product_id
    I designed a report based on this set as per attached screen shot. The selection of records for each of the sub reports is date field in each table i.e.Date>=date_value(2007,01,01) with no selection of records in the main report.
    Now what I get if I run the report is a load of pages with nothing and then every ninth page either the header and one number in the middle of the page. That is what happens when I have it grouped by Product_id.
    If I delete the group I get
    Product code description
    1 6 etc.
    the product code and description only appears on the first line without any outs or ins beside it. It continues to the end of the page then skips 5 pages and repeats the same throughout the report.
    The product file has over 5,000 products and both the other files have over 100,000 records for that period that I am selecting. I hope I have explained correctly and hope either of you can tell me where I am going wrong.
    Kind regards
    David

  6. #6
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: Stock Control

    David,

    Remove the group!

    Put the product_id from the master (Product) in the detail section.
    Then you can make two calculated summary fields, one for Stock received
    and one for Invoice items.

    Example for Invoice Items is
    C_sum_inv = TABLESUM("INV_DETAIL.DBF", "INV_DATE > CTOD("01/01/2007", "QTY")

    Please note, this requires that the dates are in the stock and invoice detail records.

    If they are in the headers, then your set relationship is greatly complicated.

    As an alternate, you can create summary tables before running the report
    by using the operations tab to create summary db's for stock recieved and items shipped within that date range. Then you would need another set that links Product to the summary child tables. The OnInit script in the report could be used to automate the summaries.

  7. #7
    Member
    Real Name
    David Corcoran
    Join Date
    Oct 2001
    Posts
    218

    Default Re: Stock Control

    Melvin,

    I tried your suggestion with the following expression in the calc field tablesum("invoice_items.dbf",","invoice_date>ctod("01/01/2007","out") but get invalid expression. Is the ctod to filter the records from that date and then it sums the "out" field. Howerver the correct names for the fields are in the expression above so maybe there is something wrong with the syntax.

    Kind regards
    David

  8. #8
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Stock Control

    David,

    Seems like you have an extra arguement for the tablesum function--


    tablesum("invoice_items.dbf",","invoice_date>ctod("01/01/2007","out")

    Get rid of the comma and quote mark in red and see if that helps.
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  9. #9
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: Stock Control

    David,

    My bad. :(

    There was an extra comma in the expression.

    Here's the correct one.

    tablesum("invoice_items.dbf","invoice_date>ctod("01/01/2007","out")

    Note: It is always best to consult the Alpha Five Reference guides to verify
    formulas.

  10. #10
    Member
    Real Name
    David Corcoran
    Join Date
    Oct 2001
    Posts
    218

    Default Re: Stock Control

    Mike,

    I edited that and it still gives invaled expression.

    David

  11. #11
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: Stock Control

    Please use the reference guide, the interactive code editor and make sure the field names are correct.

    I do not have access to A5 while I am working.

    If you need further assistance, then you will need to post a copy of your database with this thread so I and others can work with actual structures.

  12. #12
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: Stock Control

    David,

    another shot in the dark. Try


    tablesum("invoice_items.dbf","invoice_date>ctod("01/01/2007")","out")
    Mike
    __________________________________________
    It is only when we forget all our learning that we begin to know.
    It's not what you look at that matters, it's what you see.
    Henry David Thoreau
    __________________________________________




  13. #13
    "Certified" Alphaholic Melvin Davidson's Avatar
    Real Name
    Melvin Davidson
    Join Date
    Apr 2000
    Location
    Parker, CO
    Posts
    1,197

    Default Re: Stock Control

    Just out of curiosity, are you using the original invoice_items table that came with Alpha Sports, or did you add the date fields to it as I suggested.

    The expression will not work unless there is actually a field called INV_DATE
    in invoice_items.dbf.

    Please post a copy of your database so we can review it.

  14. #14
    Member
    Real Name
    David Corcoran
    Join Date
    Oct 2001
    Posts
    218

    Default Re: Stock Control

    Thank you all for your help I could not get ctod to work as the filter so I used { } instead and this worked.

    Kind regards
    David

Similar Threads

  1. Stock Control
    By dcorcoran in forum Application Server Version 8
    Replies: 1
    Last Post: 08-23-2007, 09:21 AM
  2. stock control
    By dcorcoran in forum Alpha Four Version 6 and Prior
    Replies: 0
    Last Post: 08-22-2007, 06:54 AM
  3. STOCK CONTROL TEMPLATE
    By Nev Kent in forum Alpha Five Version 5
    Replies: 2
    Last Post: 09-29-2004, 04:48 PM

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
  •