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

Sales Order/Work in Progress

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

    Sales Order/Work in Progress

    I'm not sure how to get started with this.
    I have been using A5 about a year. Have made a few working db's.
    I am not a programmer, but would like to learn and have learned a lot from this forum. I learn a lot just trying to convey my problem/question.
    Many times I have found my answer merely by typing out my question.
    I am not a beginner beginner. I know how to make sets, queries, reports, lookups. Put buttons on forms. I use the genies. Code I don't have a grasp on that. Have copied some from help and modified a little to work in my db's.

    I use Quickbooks for my accounting, invoicing, sales orders, but it lacking in customizing to fit my needs. I export to Excel, then import to A5. A5 has been a big help in managing my data. I tried the QODBC but it is way too complicated! I imported tables, tried some SQL queries. I even did an ADO operation to changed data in Quickbooks, but it is easier to export to Excel and import to A5 than to try to automate that with QODBC.

    I am looking for some general direction on how to set up this type of db (SO/WIP).
    We receive several SO's a day with several different items.

    So I need a report/WO (Work Order) to show all of the items needed.
    Next, there may be 3 to 4 operations/phases for each item. Then at the end of the day, I need to enter what quantity was completed for that operation.
    Tomorrow I need a WO showing what is needed, less what was completed, plus new SO's.

    I have made the SO set, header table and detail table, with a 1 to many relationship. Now I am stuck. How do I post the Date, SO number, Customer, and the details of the order? To what kind of table and/or set?
    I have an understanding on how to post in field rules and the post operation.

    Now in reading my own question, it looks like I need to post the quantity to the item table, instead of trying to tie it to the customer. Then when I have the Items complete, pull from Item inventory table to fill Sales Orders.
    But I still don't know how to "Post" from a 1 to many set.

    I guess I would need an item inventory table for each operation/phase?

    Any thoughts on how to get started with this would be greatly appreciated!
    David
    A5V12.3
    Win7, 64 bit
    Qbks 2018 Enterprise
    QODBC R/W

    #2
    David,

    There's lots of ?s there and I'm not qualified to answer all of them. I am not a DB professional either and am still learning but the best advice I can give at this point (just passing on that which I have received) is to turn of the computer, grab a pencil and design the DB on paper first. Take the time to write out your work flow. Like you have started but in a bit more detail. It is a bit like writing an outline for an essay.
    We receive several SO's a day with several different items.

    So I need a report/WO (Work Order) to show all of the items needed.
    Next, there may be 3 to 4 operations/phases for each item. Then at the end of the day, I need to enter what quantity was completed for that operation.
    Tomorrow I need a WO showing what is needed, less what was completed, plus new SO's.
    If you haven't been introduced to the subject of normalisation it would pay to spend an hour on your favourite search engine and do abit of research. It can be a very involved and complex subject but just to understand the basics is a big help.

    I guess I would need an item inventory table for each operation/phase?
    You may (depending on what other info you wanted storedthat related to that procedure), however you could get a way with one table that had a field for each process. you would adjust the value for each field depending on how many of each item has completed that process.

    Thats all from me for now. Have to go and do some real work(apologies to all you pros out there:) )
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

    Comment


      #3
      Originally posted by David

      I use Quickbooks for my accounting, invoicing, sales orders, but it lacking in customizing to fit my needs. I export to Excel, then import to A5. A5 has been a big help in managing my data. I tried the QODBC but it is way too complicated! I imported tables, tried some SQL queries. I even did an ADO operation to changed data in Quickbooks, but it is easier to export to Excel and import to A5 than to try to automate that with QODBC.
      David

      Have you worked with the ADO import genie to import from Quickbooks to A5 via QODBC? It's very straight forward and works really well.

      Please let me know what you find so complicated in using QODBC with A5. Perhaps there is a fundmental issue that is missing that we can help you with.

      When you referenced an ADO operation, did you mean an ADO import. I don't know of an ADO operation that is currently available in A5. Perhaps in the Client/Server version which isn't yet in beta....
      Al Buchholz
      Bookwood Systems, LTD
      Weekly QReportBuilder Webinars Thursday 1 pm CST

      Occam's Razor - KISS
      Normalize till it hurts - De-normalize till it works.
      Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
      When we triage a problem it is much easier to read sample systems than to read a mind.
      "Make it as simple as possible, but not simpler."
      Albert Einstein

      http://www.iadn.com/images/media/iadn_member.png

      Comment


        #4
        Thanks Tim for your thoughts. Sounds like back to the drawing board is a good idea. I did a search on Normalize and I think I understand the basis. I have seen this subject before, but I went back and studied some more. In A5 I use sets to relate data rather than enter the same data in 2 or more tables.
        Thats about all I know about the basics.
        Code:
        Quote:
        I guess I would need an item inventory table for each operation/phase?
        Code:
        Quote:
        You may (depending on what other info you wanted storedthat related to
        that procedure), however you could get a way with one table that had
        a field for each process. you would adjust the value for each field depending
        on how many of each item has completed that process.
        That's the way I am doing now.
        I have a single table as attached.
        This table was made in a hurry just to fill an immediate need.
        The buttons are queries to find the record that needs to be updated.
        I use to do this in Excel and is easier, but with A5 I can do a lot more and thats what I'm trying to learn. I try to do everything in A5 and not use Excel.
        I think where I am having trouble is using an embedded browse I get lost with all of the fields. When I query the to a single record that seems to reduce the chance of input error.


        Thank you Al. I use the ADO import and imported several tables from QBKS, Customers, Vendor, Item, ItemInventory, ItemAssembly, ItemAssemblyline.
        That worked fine. Then when I tried to import Sales Orders from QBKS, I got lost with the info that I needed. I used a SQL example from the QODBC website and I got all of my Sales Orders. I know I needed to filter out the closed SO but ran out of time or lost interest because I didn't have enough knowledge to keep going. Then I opened the interactive window, then I clicked on the New Script using Action Scripting Editor, then clicked on Add New Action, then Display Xdialog Form based on ADO/ODBC data.

        I selected the Inventory Items and the fields I wanted displayed and checked the box to customize the form. I'm recalling this from memory so I may not have all of the steps. Then I had a form with the inventory items. I could change prices and it would update in QBKS. I couldn't figure out what to do here. What I wanted to do was to import my Inventory Item table, which I did. Then I wanted to query Items numbers and change prices based off of last cost. Then update that data back into Quickbooks. Now I am not trying to accomplish this in 5 minutes just trying to figure out how to do this. I couldn't find a way to import data to Quickbooks. Now my 2nd chance on the trial version of the QODBC has ran out. So now I think my time would be better spent learning A5 and phase out using Quickbooks all togeather. I need to keep track of a lot more data that QBKS won't do. But If I thought I could make the QODBC to work, I would buy it.


        Thanks again for your thoughts. I'm still open for suggestions.
        David
        A5V12.3
        Win7, 64 bit
        Qbks 2018 Enterprise
        QODBC R/W

        Comment


          #5
          David

          Writing back to Quickbooks is done via ADO/Xbasic code. In the future there will be another way to write back.

          There is an example of writing back in the Alpha newsletter and in the talk that I gave at the 2005 Alpha conference.

          The talk is available for downloading from:

          http://www.bookwood.com/learningcenter.html

          You certainly have to make a business/value decision about interacting with QB or rewriting the parts that you need in A5. Ease of using the interface is one of the decision factors.....
          Al Buchholz
          Bookwood Systems, LTD
          Weekly QReportBuilder Webinars Thursday 1 pm CST

          Occam's Razor - KISS
          Normalize till it hurts - De-normalize till it works.
          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
          When we triage a problem it is much easier to read sample systems than to read a mind.
          "Make it as simple as possible, but not simpler."
          Albert Einstein

          http://www.iadn.com/images/media/iadn_member.png

          Comment


            #6
            Here is my recommendation about "interfacing" between QB and A5:

            Don't.

            OK, maybe that's a bit strong but here's the basis of what I'm saying. Maybe that will make it clearer....

            Using QODBC, or anything else, to pull info from QB to A5 in order to generate new/better reports is fine. The problems start when you try to take transaction info from A5 and enter it into QB. Accounting is a very complex issue and there are many restrictions on what can be entered into QB and HOW it has to be entered. Even the specific account names in QB must be spelled EXACTLY the same in A5. And, you must balance every debt amount(s) with equal credit amount(s). Building generic routines to accomplish it is virtually impossible. Even building specific routines can be extremely time consuming. And heaven forbid you should every change the account names or what type of payment/charge goes into which account.

            I've had rather strong requests from 3 customers now to use A5 to "manipulate" QB data. In each case I've either sat down with the customer and his accountant to discuss the issue or just told the customer to discuss it with his accountant. In all cases the accountant was quick to agree with my stance -- if the details are stored in A5, leave them in A5 and just create an end-of-month report that summarizes the info and let someone make a ledger entry in QB by hand. If anyone needs to know the details, they can always get them from A5. (One ledger entry such as $2500 in income from carport rental is much faster than entering 100 carport transactions along with all the details for each transaction.)

            The reason the accountants agree - they've been through it before and know that there are a LOT of issues when you try to import transaction data from outside any accounting package. Besides, what's the point in duplicating the information?

            In one case I did create a routine that enters ledger entries into QB. It was a situation where there were often dozens of entries in A5 and they wanted to copy each of these transactions into QB. After many trials and much discussion (this was also one of my first attempts at interfacing with QB), we finally decided that we would restrict it to entering a ledger total to QB rather than the individual transactions. Since this particular routine can require opening up to about 60 different companies in QB and making a ledger entry for each one, the routine itself takes anywhere from 1 to 3 hours to run. (The time seems to depend more on the server - how busy it is or what it's doing - than how much A5 data is being handled. The same routine takes about 30 minutes on my stand-alone computer.) You can imagine how long it would take if done by hand. Because of this huge time savings, they were willing to pay the couple thousand $$ that it cost. Most people won't gain enough advantage to make something like that worthwhile. Even at that there are still problems getting QB to open the next company file and occasional problems with data that wasn't entered or was accidentally entered twice.

            In fact, if you read most of the stuff about QODBC either in the QB website or the Alpha info, it talks far more about "getting data from QB to build custom reports" than it does about "copying transaction data into QB".

            Just my 2 cents.
            Last edited by CALocklin; 06-17-2006, 05:42 PM.

            Comment


              #7
              Thanks Al,
              Sorry its been so long getting back to this thread. I had job this week that has kept me away from computer things.

              I have been through your presentation and I don't how to do X-basic or even know how to ask how. I am giving up on QODBC. I still would like to learn. I guess I need to buy the book X-Basic for Everyone. Would this be the best place to start learning x-baxic?

              I do appreciate your thoughts.

              Thanks CAL,
              Here is my recommendation about "interfacing" between QB and A5:

              Don't.
              I don't.:)

              Thanks for you thoughts. I will at a later date have more details of what I am trying to accomplish and what is giving me trouble.

              David
              David
              A5V12.3
              Win7, 64 bit
              Qbks 2018 Enterprise
              QODBC R/W

              Comment


                #8
                Re: Sales Order/Work in Progress

                If you are looking to design a database (which would include needed sets, views, etc.,) the first thing that you need too do is decide what the primary database needs to accomplish.

                If you try to make it be all things for all people, it will end up being useful for no one. Secondly, after deciding what it is to be used for, you need to understand what information you need to get out of it. Once that is estahblished, then you need to visualize/conceptualize how the data is arranged/stored etc.

                I am skipping a lot of info here, to make the concept easy to grasp. If you were to say that writing a program was a 10 step process, then the actual programming/debugging starts around step 7. Unless you have extensive experience designing systems, almost always you will discover differing ways to do the same thing. Some simpler and some not.

                PS: It is also a known that the original data layout you come up with will rarely look anything at all like the final concept you adopt.

                Comment

                Working...
                X