Alpha Video Training
Results 1 to 8 of 8

Thread: Sales Order/Work in Progress

  1. #1
    Member
    Real Name
    David L. Aery
    Join Date
    Dec 2004
    Location
    Missouri
    Posts
    336

    Default 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. #2
    "Certified" Alphaholic Tim Kiebert's Avatar
    Real Name
    Tim Kiebert
    Join Date
    Jul 2004
    Location
    Geelong, Victoria, Australia
    Posts
    2,785

    Default

    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.

  3. #3
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,626

    Default

    Quote 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.

  4. #4
    Member
    Real Name
    David L. Aery
    Join Date
    Dec 2004
    Location
    Missouri
    Posts
    336

    Default

    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

  5. #5
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,626

    Default

    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.

  6. #6
    "Certified" Alphaholic
    Real Name
    Cal Locklin
    Join Date
    Mar 2000
    Location
    S.E. Michigan
    Posts
    5,763

    Default

    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 at 06:42 PM.

  7. #7
    Member
    Real Name
    David L. Aery
    Join Date
    Dec 2004
    Location
    Missouri
    Posts
    336

    Default

    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

  8. #8
    Member
    Real Name
    Mark Liermann
    Join Date
    Jun 2000
    Location
    Wisconsin
    Posts
    141

    Default 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.

Similar Threads

  1. Work Order Form
    By Tony Agnone in forum Alpha Four Version 6 and Prior
    Replies: 1
    Last Post: 08-09-2005, 05:44 AM
  2. Sales tax calculation for in state sales
    By Jim Gatton in forum Alpha Five Version 1
    Replies: 2
    Last Post: 01-04-2005, 12:11 PM
  3. Work order to invoice
    By Rodney Efird in forum Alpha Five Version 4
    Replies: 2
    Last Post: 07-30-2001, 02:44 PM
  4. By customer or by sales order number
    By Greg Fong in forum Alpha Five Version 4
    Replies: 3
    Last Post: 07-06-2001, 01:20 PM
  5. Work Order Database
    By James Peterson in forum Alpha Five Version 4
    Replies: 0
    Last Post: 10-17-2000, 05:31 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
  •