Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Stock Control

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    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
    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.
    Peter
    AlphaBase Solutions, LLC

    [email protected]
    https://www.alphabasesolutions.com


    Comment


      #3
      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

      Comment


        #4
        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.

        Comment


          #5
          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

          Comment


            #6
            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.

            Comment


              #7
              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

              Comment


                #8
                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
                __________________________________________



                Comment


                  #9
                  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.

                  Comment


                    #10
                    Re: Stock Control

                    Mike,

                    I edited that and it still gives invaled expression.

                    David

                    Comment


                      #11
                      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.

                      Comment


                        #12
                        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
                        __________________________________________



                        Comment


                          #13
                          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.

                          Comment


                            #14
                            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

                            Comment

                            Working...
                            X