Load credit terms and limits onto customers in bulk

Step 6 of setting up credit limits and payment terms: attach terms and limits to many customers at once, from a file. It follows Steps 3 to 5, which do the same for one customer on screen, and it needs the definitions from Steps 1 and 2 to exist first. GadgetSphere Distribution has about 180 corporate buyers on four terms and perhaps six limits (Meet GadgetSphere).

1. Step 6: load them in bulk, if you have hundreds.

Step 6: Load them in bulk, if you have hundreds

Master Data > Customer Maintenance > Upload Credit Terms and Upload Credit Limits (the Supplier applet has the same two entries)

The outcome: every customer on your agreed-terms list carries the right term and the right limit, without anyone opening 180 records one by one.

These two screens attach definitions to customers. They do not build the definitions. Neither the customer nor the supplier applet has a screen that uploads the term list or the limit list itself, so Steps 1 and 2 stay a job done by hand. With four terms and six limits, that is the right size of job anyway.

Each screen takes one CSV file, has a Sample Format download beside the file picker, and processes the file in the background. The listing shows each file’s Process Status and Error Message. Open a file to see its rows: the reason a row failed is in the row’s Error Message column, and Short Error Message is often empty.

The term file carries, per row, the customer code, the term code, the three Set numbers, the three Add numbers and a status. The limit file carries the customer code, the limit code, a currency, an amount and a status. The supplier files are the same with the supplier code first. Build yours from the sample, in the sample’s column order, and read these before you upload:

• The numbers come from your file, not from the definition. On screen, attaching NET30 copies NET30’s numbers (Step 3). The upload uses the code only to look up the name. The six numbers, or the currency and amount, are whatever your row says. A row reading NET30 with an Add Day of 45 gives the customer a term called Net 30 days that dates their invoices 45 days out, and a row reading LIMIT-150K with an amount of 50000 gives them a limit of 50,000 with a 150K label. A code that is not in your list is not refused either: the customer gets an assignment carrying that code and a blank name. So copy the numbers into the file from the definitions you built in Steps 1 and 2, never from memory.
• Rows are matched on customer and code, so uploading again corrects rather than duplicates. If the customer already has an assignment with that code, the row overwrites its numbers (or its currency and amount) and its status. A row with a different code adds a second assignment beside the first, which is exactly the two-active-limits trap from Step 3. To move 40 resellers from LIMIT-50K to LIMIT-150K in one file, give each of them two rows: the old code with INACTIVE and the new code with ACTIVE.
• An amount that is not a plain number becomes zero, and the row still reports success. 150,000.00, RM150000 or an empty cell all load as a limit of zero. A day, month or year cell that is empty or not a whole number loads as 0 in the same way, so a blank Add Day turns a thirty-day term into “due on the invoice date”. A zero limit is a limit, not the absence of one: your online store, for example, takes the customer’s smallest active limit, so a zero there means nothing can be bought on account. Before saving from a spreadsheet, format the amount column as a plain number with no thousands separator.
• The term upload refuses an Add Day above 31, an Add Month above 12, and an Add Year that is not 0 or four digits. It checks the Add numbers as if they were a date. So NET60, written as Add Day 60, fails with Add Day Must Be One Or Two Digits And 31st Is Largest Date, 60 Is Not Valid. The screen in Steps 1 and 3 has no such limit. Add Month 2 is not a way round it, because two calendar months is not sixty days. Upload the thirty-day customers and attach the sixty-day ones on screen.
• Headings: capitals on the customer term upload, position on the other three. The customer Upload Credit Terms screen reads your heading row by name and fails the whole file with Wrong Columns or Mandatory Columns Missing if it does not recognise a heading. Its check expects the headings in capitals (CUSTOMER_CODE), and the sample downloads with lowercase headings. If a file built from that sample fails with Mandatory Columns Missing, retype the heading row in capitals and upload it again. The customer limit upload and both supplier uploads do not read the heading row at all. They take the columns by position, so a file with two columns swapped loads the wrong values into the wrong fields and says nothing.
• The status must be ACTIVE or INACTIVE, in capitals. Anything else, including active or an empty cell, stops the file at that row and marks it failed. Fix the row and upload the whole file again. Rows that did load are matched and overwritten, not doubled.
• The customer code must exist, or the row fails with Customer Code Does Not Exist (Supplier Code Does Not Exist on the supplier screens).

Most of this was read from the code and has not been tried on a live tenant: the zero amount, the Add Day ceiling, the capitals check on the heading row and the column-by-position reading. Test it before you rely on it. Upload a three-row file: one customer on NET30, one on NET60 and one limit with 150,000.00 typed in the amount. Then open those three customers’ Credit Term and Limit tab and read what landed.

A bulk load does not set the Default Credit Limit and Default Credit Term pointers from Step 4. Nothing sets them in bulk, and a customer whose pointer is empty is invisible to the credit sweep. So this checking stays with a person:

• Whoever prepares the file checks ten rows against the signed credit applications before it is uploaded.
• After the upload, somebody other than the preparer opens five customers from the file and compares their Credit Term and Limit tab and their Main tab pointers with the file.
• They also compare the file’s row count with the rows the upload listing shows as loaded.

What success looks like for this part

The file’s row count matches the rows the upload listing shows as loaded, and somebody other than the preparer has opened five customers from the file and found their Credit Term and Limit tab and their Main tab pointers as the file says. A bulk load sets no Default pointer, so any that were empty are set on screen, as in Step 4. Then run the whole setup’s success check on a few of them.

Common mistakes in this part

Uploading limits straight from a formatted spreadsheet. An amount saved as 150,000.00 loads as a limit of zero, and the upload still reports the row as loaded. Save the amount column as plain digits, then open a few of the customers afterwards.

The rest of the setup’s mistakes are on the front page.

Next: What the numbers actually drive.

Related documentation

• Credit Limits and Payment Terms: the whole setup, with its success check and the common mistakes.
• Customer Maintenance: the upload screens, in the field reference.