Sage 300 - Import items to populate quote

SOLVED

I want to import items into an O/E quotation. 

I have attempted to export an existing "QTxxxxx" to use as a template, but I get an error message "Can't Append Table. Error: Too many fields defined." even when I limit the requested fields to those in "Orders" and "Order_Details" (deselecting everything related to serial numbers, lot numbers, payments, comments, optional fields).

Background:
Quotes at my company can be iterative, with lots of line items to form a complicated system with inter-related components where one change will often cascade into multiple changes. So, it would be much easier if we could prepare quotes in Excel and import them.

Can you provide a template, suggest tips for the export-as-template approach, or provide an alternative method for importing?

Thank you for your attention and help in advance!

  • 0

    First, create a small export:  Header minimum = ORDUNIQ, CUSTOMER and TYPE. If you want to set your own Order Number, include that field as well; otherwise leave it out so that Sage will assign it.  Detail minimum = ORDUNIQ, LINENUM, LINETYPE, LOCATION, ORDUNIT, ITEM, QTYBACKORD.  That will set the range names, page names, etc.

    To import, set ORDUNIQ to zero on both pages, so that Sage will assign the number on import.  Set TYPE to 4, which is the order type for Quotes.  Fill in everything else, and you have a bare minimum import.  Of course add other fields if you want them, which you can either do in the Excel file or in the original export.

  • 0

    Have you looked at the Copy Orders process in Sage 300?  It's possible to copy orders into quotes or quotes into quotes from one customer to another.  You can even copy just some of the items.  Check it out.

  • 0 in reply to wheumann

    Thank you very much for the detailed support wheumann!

    I created the small export of a single existing quote per your instructions. (It only had one item listed, shown as Line No. 1 in the ui but oddly, exported as LINENUM 16.) 
    set ORDUNIQ to zero on both pages, changed LINENUM to 1, and changed QTYBACKORD to 3.
    Then I attempted to import from the O/E Order Entry window. 
    Recieved the following error: Description: Table Order_Detail_Serial_Numbers does not exist. Please specify an existing table. Source: 11:29:24 - 1075.0.502.1432
    I see the option to Save Script but do not see how to customize which records are being imported.

    Can you please link to further instructions or offer some clarity on the import process?

  • 0 in reply to Dana B. Stidsen

    Thank you for the suggestion Dana. That sounds very useful, though for now it's unclear how we would search for the quote worth copying for a given application. Historically, we did everything in Excel and put the key features of the system into the file name. Of course quoting from Excel presents its own issues as well ;)

  • +1 in reply to Melissa Glidewell
    verified answer

    You actually need to export all pages (maybe not Optional Fields, but all others - I usually do All).  On the pages you don't want, keep the headers but blank out the data.  It looks for that Serial page whether you have serial numbers or not :-(

  • 0 in reply to wheumann

    Thank you again wheumann. I seem to be inching closer to the goal...

    The latest error message reads: Description: Please mark all required fields. Source: 14:08:06 - 1041.0.503.198

    There is no apparent means of marking fields. The only option I can find is by right-clicking on tables listed in the Table column of the import window and that doesn't seem to be a useful option.

    Any suggestions are very welcome.

  • 0 in reply to Melissa Glidewell

    Look for any "Import not ready" in the top box, as shown here:

    Then click on that row, and find any field marked with an asterisk, like Line Number highlighted in my example.  If it is not matched to a field in the Contents column, it is either missing from your import file or the field name does not match exactly.

    AND - in testing this i was reminded that the file I had with QTYBACKORD was not the best; use field name QTYORDERED instead.  Otherwise you get strange warning messages that the backorder quantity was increased.

    -Wendy

  • 0 in reply to wheumann

    Wow Wendy, thank you so much for sticking with me all day! I've successfully imported a quote and taught another person to do so as well. We still have a lot to learn about the details, but this is a really big step.

    Thank you again