4 replies to this topic

[Videoflicks2004]

Hello,



Here is the quote from my EDI tech guy:



"The text file is giving me the same kind of layout, only 5 detail records allowed per header file. This arises the same question, if we have an order coming in that has more than 5 detail lines (each product has its own line, your last order had over 100), then you will have a large amount of files all with the same PO number and header information. If you system allows that and doesn't not over write the PO's with the next one you import that is fine we can do that, however this is not hardly ever the case."



Does anyone have any experience with creating a flat file (comma del import file for MOM) ?? Please send me a response.



The main question is what to do with an order when there is more tha 5 items?



Best,



Videoflicks2004

[KPS]

MOM 4.3 references a field "continued". When the order has more than 5 items the order is continued on the next line but only the fields referencing the products (product, quantity, price and discount) are filled in. Additionally the continued field contains an "X" to show that the items belong with the previous line. If there are more than 10 products then another line is used in the same way.

Let me know if you need more clarification.

[Videoflicks2004]

MOM 4.3 references a field "continued". When the order has more than 5 items the order is continued on the next line but only the fields referencing the products (product, quantity, price and discount) are filled in. Additionally the continued field contains an "X" to show that the items belong with the previous line. If there are more than 10 products then another line is used in the same way.

Let me know if you need more clarification.

Hi KPS,

I've got the file layout down but there is some confusion about the proper format for creating a comma delimited file for more than 5 products . .

Here is an example of a billto (ship to the same) sample export with 6 products ..
First Line One
358857,"","NARCISO","DAVID","TEST CO","123 STREET AVENUE","","TORONTO","ON","M6H4H1"," ","(416) 456-7889",""," ","",""," "," ","","","","","","DN","DN","po 1001","ECO"," ",0.00," ","06/16/06",467799,"1017777.17",1.00,"1028942.4",2.00,"1043834.16",5.00,"1028942.4",2.00,"1036363.16",1.0

Second Line
0,"","","","","","","","",/ /,"IN","","",0.00,"X",5.99,0,12.98,0,0.00,0,0.00,0,0.00,0," ",4.00,"","","034","","","","","",""," ","TITLE FIELD","","","","","","","","","",/ /,"","","","","","","","",0.00,/ / : :,"","","","","","",F
0,"","","","","","","","",""," ","",""," ","",""," "," ","","","","","","","","","ECO"," ",0.00,"X","",0,"1069301.18",10.00,"",0.00,"",0.00,"",0.00,"",0.00,"","","","","","","","",/ /,"","","",0.00,"X",0.00,0,0.00,0,0.00,0,0.00,0,0.00,0," ",0.00,"","","","","","","","",""," ","","","","","","","","","","",/ /,"","","","","","","","",0.00,/ / : :,"","","","","","",F

Now an example of a separate bill to ship to

First Line
358857,"","NARCISO","DAVID","TEST CO","123 STREET AVENUE","","TORONTO","ON","M6H4H1"," ","(416) 456-7889",""," ","",""," "," ","","","","","","DN","DN","po 1001","ECO"," ",0.00," ","06/16/06",467799,"1017777.17",1.00,"1028942.4",2.00,"1043834.16",5.00,"1028942.4",2.00,"1036363.16",1.00,"KNOTTS","DON","VIDEOFLICKS","120 HOME AVENUE","","MISSISSAUGA","ON","L5J1E1",/ /,"IN","","",0.00,"X",5.99,0,12.98,0,0.00,0,0.00,0,0.00,0," ",4.00,"","","034","034","","","","",""," ","TITLE FIELD","","","","","","","","","",/ /,"","","","","","","","",0.00,/ / : :,"","","","","","",F

Second Line

0,"","","","","","","","",""," ","",""," ","",""," "," ","","","","","","","","","ECO"," ",0.00,"X","",0,"1069301.18",10.00,"1017779.5",2.00,"",0.00,"",0.00,"",0.00,"KNOTTS","DON","VIDEOFLICKS","120 HOME AVENUE","","MISSISSAUGA","ON","L5J1E1",/ /,"","","",0.00,"X",0.00,0,0.00,0,0.00,0,0.00,0,0.00,0," ",0.00,"","","","034","","","","",""," ","","","","","","","","","","",/ /,"","","","","","","","",0.00,/ / : :,"","","","","","",F

Let's break this down further...

Second Example First Line:

First Line

• 358857,
• "",
• "NARCISO",
• "DAVID",
• "TEST CO",
• "123 STREET AVENUE",
• "",
• "TORONTO",
• "ON",
• "M6H4H1",
• " ",
• "(416) 456-7889",
• "",
• " ",
• "",
• "",
• " ",
• " ",
• "",
• "",
• "",
• "",
• "",
• "DN",
• "DN",
• "po 1001",
• "ECO",
• " ",
• 0.00,
• "",
• "06/16/06",
• 467799,"
• 1017777.17",
• 1.00,
• "1028942.4",
• 2.00,
• "1043834.16",
• 5.00,
• "1028942.4",
• 2.00,
• "1036363.16",
• 1.00,
• "KNOTTS",
• "DON",
• "VIDEOFLICKS",
• "120 HOME AVENUE",
• "",
• "MISSISSAUGA",
• "ON",
• "L5J1E1",
• / /,
• "IN",
• "",
• "",
• 0.00,
• "X",
• 5.99,
• 0,
• 12.98,
• 0,
• 0.00,
• 0,
• 0.00,
• 0,
• 0.00,
• 0,
• " ",
• 4.00,
• "",
• "",
• "034",
• "034",
• "",
• "",
• "",
• "",
• "",
• " ",
• "TITLE FIELD",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• / /,
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• 0.00,
• / / : :,
• "",
• "",
• "",
• "",
• "",
• "",
• F

Second Line

• 0,
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• " ",
• "",
• "",
• " ",
• "",
• "",
• " ",
• " ",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "ECO",
• " ",
• 0.00,
• "X",
• "",
• 0,
• "1069301.18",
• 10.00,
• "1017779.5",
• 2.00,
• "",
• 0.00,
• "",
• 0.00,
• "",
• 0.00,
• "KNOTTS",
• "DON",
• "VIDEOFLICKS",
• "120 HOME AVENUE",
• "",
• "MISSISSAUGA",
• "ON",
• "L5J1E1",
• / /,
• "",
• "",
• "",
• 0.00,
• "X",
• 0.00,
• 0,
• 0.00,
• 0,
• 0.00,
• 0,
• 0.00,
• 0,
• 0.00,
• 0,
• " ",
• 0.00,
• "",
• "",
• "",
• "034",
• "",
• "",
• "",
• "",
• "",
• " ",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• / /,
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• "",
• 0.00,
• / / : :,
• "",
• "",
• "",
• "",
• "",
• "",
• F

IMPORT.DBF FIELDS

IMPORT TABLE
The IMPORT table is a temporary table, not a permanent one, and therefore
does not have relations to the
other MOM tables. This table is imported into MOM, or exported from MOM,
during the use of the
Import-Export Module. When information in this table is imported, it’s just
as though the order were
entered manually; MOM sorts the description out among the many tables
governing orders. We have
included the field descriptions in this manual so that you can get a better
understanding of what information
should be included, and why.

Field Name T W Name
1 CUSTNUM N 10
The billing customer number. In most cases, this is left blank for
importing. It should only be filled in when the order entry system is
working off the same MOM table as your MOM system, and can
access the correct MOM-assigned customer number.
2 ALTNUM C 15
The customer number that has been assigned by the other system or
web package used in order entry. Note that this is a CUSTOMER
number, not an order number--that's taken care of by Field 32 below.
3 LASTNAME C 20 Billing address last name.
4 FIRSTNAME C 15 Billing address first name.
5 COMPANY C 40 Billing address company name.
6 ADDRESS1 C 40 Billing address first address line.
7 ADDRESS2 C 40 Billing address second address line.
8 CITY C 20 Billing city.
9 STATE C 3 Billing state or province.
10 ZIPCODE C 10 Billing zip code or postal code.
11 FOREIGN C 1 X or Y if billing address is outside of your country. Blank
or N indicates
a domestic address.
12 PHONE C 14
If this is a US address, the format is (xxx) xxx-xxxx. Note the space,
hyphen, and parenthesis for a total of 14 characters. If this is a foreign
address, the special formatting requirement is dropped, but there is
still a 14-character limit.
13 COMMENT C 40 This information writes to the COMMENT field in the CUST
table, and
shows on the customer's general tab of the main information screen.
14 CTYPE1 C 1
This corresponds to the first customer type, a single character, located
on the "General" tab in the customer's main information screen in
MOM.
15 CTYPE2 C 2
This corresponds to the second customer type, two characters,
located on the "General" tab in the customer's main information
screen in MOM.


16 CTYPE3 C 4
This corresponds to the third customer type, four characters, located
on the "General" tab in the customer's main information screen in
MOM.
17 TAXEXEMPT C 1
If this customer has tax exempt status, indicate with an X or Y so that
MOM does not calculate tax on the order if the address is in a state
where your company has a presence. Leave blank or type N to
indicate if this customer does not have tax exempt status.
18 PROSPECT C 1
If this entry is not an order, but rather a catalog request, indicate with
an X or Y. In that case, there is no need to complete any of the
subsequent fields besides field 22, the source of the catalog request,
or field 55, PROMOCRED, to place a promotional credit in the
prospect's record. Leave blank or type N to indicate if this entry is an
order.
19 CARDTYPE C 2 Credit Card type. This entry will be checked against the
valid list of
credit card types setup in Merchant Accounts.
20 CARDNUM C 19 Credit Card Number. Spaces are permitted. Dashes are not
permitted.
21 EXPIRES C 5 Credit Card Expiration date. The format should be MM/YY, even
for
Year 2000.
22 SOURCE_KEY C 9 The source key for this order or catalog request. Will be
matched
against source codes set up in MOM.
23 CATALOG C 2 The catalog code for this order, to be sent if this is to be
a catalog
request rather than an order. Matched against MOM catalog codes.
24 SALES_ID C 3
The sales ID for the order setup in Maintain User ID. Very often if this
is a web order or an inbound telemarketing order, MOM users set up a
single identifier code (like "WEB") in the Maintain User Security
Profiles section of MOM, and then default all imported orders to that
Sales ID. Make sure when you set this up that "Salesperson" is
checked to true.
25 OPER_ID C 3 The Operator ID for this order setup in Maintain User ID.
Same
information immediately above.
26 REFERENCE C 10 A Purchase Order number or check number for this order.
27 SHIPVIA C 3
Designated shipping method for order set up in Maintain Shipping
Methods. Default method that is set up in Import-Export setup is used
if this field is left blank.
28 FULFILLED C 1
If this order has been filled outside of MOM and therefore does not
need to deduct inventory from the shelves, indicate with an X or Y. In
this case, no processing paperwork will be generated, although it still
must be processed through Order Processing in order for MOM to
view the sale as complete. Leave blank or type N to indicate that this
order still needs to be filled in normal MOM processing.
29 PAID N 9
Designates that this dollar amount has already been paid. Indicating
an amount causes Mail Order Manager to generate an accounting
entry showing pre-payment of this amount.
30 CONTINUED C 1
If this record is a continuation of the prior record, indicate with an X or
Y. What is this field used for? You'll notice below that there is space
for only up to 5 products per import record. What happens when there
are more than 5 products in an order? The information for these
subsequent products must be contained in records immediately below
the first record. The first record for the order has nothing in this
Continued field (i.e., it should be left blank), and any subsequent
records for the order have an X or Y in this field. Upon import, MOM
will then flow the records together. For Continued records, there is no
need to repeat any of the address or payment information (and in fact
you MUST not repeat the AMOUNT field or the SHIPPING field), only
the product and pricing information that is specific to the product. In
fact, it's often easier, programming-wise, to simply ignore
PRODUCT02, PRODUCT03, PRODUCT04, and PRODUCT05-
instead, have as many IMPORT records as you have line items.
Complete PRODUCT01/QUANTITY01 for each record, and all line
items subsequent to the first one.
31 ORDER_DATE C 8 The entry date of the order. The format is MM/DD/YY.
32 ODR_NUM N 8
For importing, it is the order number assigned by the order taking
company. This number is imported into the ALT ORDER # field in the
M.O.M. database and can be used for order lookup, although it cannot
be seen from within the order proper.
33 PRODUCT01 C 20 The 1st product code on the order. Please see discussion
in Field 30
above for more information.
34 QUANTITY01 N 8 The quantity of this product being ordered.
35 PRODUCT02 C 20 The 2nd product code on the order.
36 QUANTITY02 N 8 The quantity of this product being ordered.
37 PRODUCT03 C 20 The 3rd product code on the order.
38 QUANTITY03 N 8 The quantity of this product being ordered.
39 PRODUCT04 C 20 The 4th product code on the order.
40 QUANTITY04 N 8 The quantity of this product being ordered.
41 PRODUCT05 C 20 The 5th product code on the order.
42 QUANTITY05 N 8 The quantity of this product being ordered.
43 SLASTNAME C 20 Shipping address Last Name. If the bill-to is the same as
the ship-to,
leave these shipping address fields blank.
44 SFIRSTNAME C 15 Shipping address First Name.
45 SCOMPANY C 40 Shipping address Company Name.
46 SADDRESS1 C 40 Shipping address first address line.
47 SADDRESS2 C 40 Shipping address second address line.
48 SCITY C 20 Shipping address city.
49 SSTATE C 3 Shipping address state or province.
50 SZIPCODE C 10 Shipping address zip or postal code.
51 HOLDDATE D 8 The date until which MOM will hold the order before
processing. This
is optional, of course, and is a processing hold, not a shipping hold.
52 PAYMETHOD C 2
Payment method for the order. CC=Credit Card; CK=Check; IN=Open
Invoice; CO=COD. If this field is blank and there is credit card number
for this record, MOM will assume it's a credit card payment; otherwise,
it will look to the default payment method established in the Define
Settings for Import-Export Module.
53 GREETING1 C 35 1st Box label greeting (Optional).
54 GREETING2 C 35 2nd Box label greeting (Optional).
55 PROMOCRED N 8 Dollar value of promotional credit.
56 USEPRICES C 1
If this is marked X or Y, then MOM will use the next 10 fields to assign
prices and discounts to the items being imported. If it is blank or N,
then MOM will assign the prices to the line items as they are entered
into MOM. Most MOM users importing from the web or an inbound
telemarketing company mark this X or Y because it ensures that the
price that the customer saw or was quoted is ultimately the price he or
she pays. If there is a discrepancy between MOM and this amount,
you don't want to risk dissatisfied customers.
57 PRICE01 N 9 The price to assign to the 1st product code.
58 DISCOUNT01 N 2 Corresponding discount expressed as a percentage (0-99).
59 PRICE02 N 9 The price to assign to the 2nd product code.
60 DISCOUNT02 N 2 Corresponding discount expressed as a percentage (0-99).
61 PRICE03 N 9 The price to assign to the 3rd product code.
62 DISCOUNT03 N 2 Corresponding discount expressed as a percentage (0-99).
63 PRICE04 N 9 The price to assign to the 4th product code.
64 DISCOUNT04 N 2 Corresponding discount expressed as a percentage (0-99).
65 PRICE05 N 9 The price to assign to the 5th product code.
66 DISCOUNT05 N 2 Corresponding discount expressed as a percentage (0-99).
67 USESHIPAMT C 1
If this is marked X or Y, then MOM will use the next field, SHIPPING,
to assign the shipping charges to the order. If it is blank or N, then
MOM will calculate the shipping charge if you have the Automatic
Shipping Calculations Module.
68 SHIPPING N 8 The shipping charges to assign to the order if the
USESHIPAMT field
is marked X or Y.
69 EMAIL C 50 Customer's e-mail address.
70 INTERNETID C 32 Order number assigned to the order by SiteLINK when the
customer
"checks out" of your web store. SiteLINK only.
71 COUNTRY C 3
Country code for the billing address on an order. Country codes
correspond to M.O.M. country codes (001,002, etc.) If empty, the field
defaults to the code for the country you selected for your business
address in Maintain Global Parameters.
72 SCOUNTRY C 3
Country code for the shipping address on an order. Country codes
correspond to M.O.M. country codes listed in this manual. If empty,
the field defaults to the code for the country you selected for your
business address in Maintain Global Parameters.
73 PHONE2 C 14 The billing customer's alternate phone or fax number.
74 SPHONE C 14 The shipping customer's phone number, if there is a separate
shipping
address.
75 SPHONE2 C 14 The shipping customer's alternate phone or fax number.
76 SEMAIL C 50 The shipping customer's e-mail address.
77 ORDERTYPE C 6 The order type of the order (usually WEB in this case)
78 INPART C 1 The invoice part (populated in Export, not in Import)
79 GREETING3 C 35 Third line of greeting/gift message
80 GREETING4 C 35 Fourth line of greeting/gift message
81 GREETING5 C 35 Fifth line of greeting/gift message
82 GREETING6 C 35 Sixth line of greeting/gift message
83 TITLE C 40 Title of bill-to customer
84 SALU C 6 Salutation of bill-to customer
85 HONO C 6 Honorific (suffix) of bill-to customer
86 EXT C 5 Phone 1 extension of bill-to customer
87 EXT2 C 5 Phone 2 extension of bill-to customer
88 STITLE C 40 Title of ship-to customer
89 SSALU C 6 Salutation of ship-to customer
90 SHONO C 6 Honorific of ship-to customer
91 SEXT C 5 Phone 1 extension of ship-to customer
92 SEXT2 C 5 Phone 2 extension of ship-to customer
93 SHIP_WHEN D 8 Date to ship item
94 PASSWORD C 20 SiteLINK password assigned to customer
95 CUSTOM01 C 240 Custom 1 field
96 CUSTOM02 C 240 Custom 2 field
97 CUSTOM03 C 240 Custom 3 field
98 CUSTOM04 C 240 Custom 4 field
99 CUSTOM05 C 240 Custom 5 field
100 INTERNET L 1
101 RCODE C 3 MOM 4.3 and higher only. Return code from MOM export.
102 APPROVAL C 10 MOM 4.3 and higher only. Approval code from authorization.
103 AVS C 2 MOM 4.3 and higher only. Address verification system code.
104 ANTRANS_ID C 30 MOM 4.3 and higher only
105 AUTH_AMT N 11 MOM 4.3 and higher only. Authorized amount.
106 AUTH_TIME T 8 MOM 4.3 and higher only. Authorization time.

[CDI Fulfillment]

Dave - I took your examples and plugged them into my MOM to see what they would look like.

The second example, with separate bill to and ship to, looks just about perfect. The only thing I could see wrong with that is that you didn't have the "useshipamt" field populated with an X, but did have a shipping amount in the "shipping" field. Minor detail.

The first example has one major problem that I can see. Between your "holddate" and "paymethod" fields, you have an extra field populated with "/". This is throwing off the rest of the fields in the file, moving them one position to the right.

One other thing that I am always cautious of is populating the "custnum" field. When that is populated, it doesn't matter what data is in your bill-to name and address fields, MOM uses the address info for that Customer Number. If the customer number listed does not exist in MOM, you will get an error on the exception report that the customer number does not exist. In addition to this error, that order will not have any address assigned to it.

Hope this helps a little.

[Videoflicks2004]

Hi,



Thank you for the help! There has to be more than enough info on this page to help my developer out.



Best,



Videoflicks2004

[lily]

Your wealth, fame and temporal power will shrivel to irrelevance.
It will not matter what you owned or what you were owed.
Your grudges, resentments, frustrations, and jealousies will finally disappear.
So, too, your hopes, ambitions, plans, and to-do lists will all expire. The wins and losses that once
seemed so important will fade away.
It won't matter where you came from, or on what side of the tracks you lived.
It won't matter whether you were beautiful or brilliant. Your gender, skin color, ethnicity will be
irrelevant. (wow gold,)
So what will matter? How will the value of your days be measured?
What will matter is not what you bought, but what you built; not what you got, but what you gave.
What will matter is not your success, but your significance.
What will matter is not what you learned, but what you taught.
What will matter is every act of integrity, compassion, courage and sacrifice that enriched, empowered or
encouraged others to emulat oe your example.
What will matter is not your competence, but your character.
What will matter is not how many people you knew, but how many will feel a lasting loss when you're gone.
(wow gold,)
What will matter is not your memories, but the memories of those who loved you.
What will matter is how long you will be remembered, by whom and for what.
Living a life that matters doesn't happen by accident.
It's not a matter of circumstance but of choice.
Choose to live a life that matters.