Order Import File DefinitionOnce the Customer data has been imported, the Order data may be imported. The order associates a customer with one or more "Plans" so that Invoices may be generated at periodic intervals per the Plan specification. If a customer has multiple active orders, then a line item (row) is required for each order for this customer.
Note that the optional fields for the Order must have empty values (i.e., 2 adjacent commas) and the OrderLineItems start at field number 13. The three fields for the OrderLineItems (fields 13-15) may be repeated for each OrderLineItem. - Navigate to Customers >> Upload Multiple Customers
- Download the Order upload template and fill the details below
| No | Field | Required | Notes | | 1 | CustomerId | C | The BluSynergy assigned customer id. Either this field or the CustomerAccountNumber must be specified to identify the customer that owns this order | | 2 | CustomerAccountNumber | C | The externally assigned customer reference number. Either this field or the CustomerId must be specified to identify the customer that owns this order | | 3 | ContractCode | Y | The Plan code for the order (specifies the subscription plan that will be used for generating invoice charges) | | 4 | OrderNumber | N | Auto-generated if not supplied. | | 5 | OrderStatus | Y | Defaults to 'Active'. Can be one of: 'Draft', 'Provisioning', 'Active', 'Suspended', 'Complete', 'Superseded', 'Canceled' | | 6 | StartDate | N | Defaults to current date if not provided | | 7 | EndDate | N | No termination date if not provided | | 8 | IsAutoRenew | N | Defaults to true | | 9 | PurchaseOrderNo | N | The purchase | | 10 | OrderType | N | Either 'Subscription' or 'Installment'. Defaults to 'Subscription' if not provided | | 11 | PayoffAmount | C | Required for installment orders (i.e., if OrderType is Installment) | | 12 | InstallmentsRemaining | C | Required for installment orders (i.e., if OrderType is Installment) | | | | | | The following fields define the order line items associated with the order and may be repeated for each order | | 13 | Quantity | Y | The quantity must be specified | | 14 | PriceCode | Y | The PriceCode corresponding to the Charge for the Plan. This PriceCode must obviously be one of the charges under the Plan identified by the 'ContractCode' field | | 15 | AmountOrPercent | C | If the PricingType for this PriceCode requires an amount or percent to be entered at the order creation time, then provide a value | | | | |
Notes when working with Excel:
If you wish to left-pad certain numbers with leading zero's (the customer a/c number, or order number for example), then use this formula: =TEXT(A1,"00000000") This creates a eight digit number, then make sure you select the whole column and save the format as "text", else Excel will trim the zeros of when you save it as a CSV file. - See this post for how to prevent dates and numbers from getting mis-formatted when opening CSV files in Excel
Use the Text Import Wizard to specify Text format for certain fields within your import file. Start by opening Excel to a blank workbook and from the Data tab's Get External Data group, click the From Text button.
Figure 12.0 - Get External Data, Text
After clicking From Text, select your text file from the Import Text File dialog and click Open. This launches the Text Import Wizard. There are three pages to complete. The first page is essentially to determine whether the file is to be considered Delimited or Fixed Width. The second page depends upon the first. You will be asked what characters to consider as delimiters or asked to choose the widths of a fixed width file.
Figure 12.1 - Text Import Wizard: 3 of 3
The third page is what is going to help you determine the format of each field. Select your account id field from theData preview at the bottom then select Text from the Column data format list in the upper left. Note that the header of the field in the bottom Data preview has changed to Text. Repeat for any other fields and click Finish in the lower right to complete your operation. |
|
