Warehouse Management - Customized for Business Central

Our client, Books for Less (BFL) is a large, discount, bookseller with warehouses in the US, but selling internationally as well as domestically. They needed a Cloud solution like Microsoft Dynamics 365 Business Central, but it had to be customized to meet this bookseller's unique requirements. This implementation required extensive software development using Application Language (AL), embedded within Business Central.

PDG Consultants also chose to partner with Insight Works - Warehouse Insight to help control the Barcode printing, Scanners, Bin Locations, Receiving, Put-Aways, Picks, Movements, etc.

This document will describe many of the required, complex, customizations. These could not have been done with "low code" methods.

Custom Fields

Custom fields were added to the Item card to bring in specialized information from the legacy system SBM (Small Business Manager / GP)

The important fields had to be changed to a type “Dropdown” in order to avoid the tens of thousands of spelling errors that were in the SBM system.

The legacy data had to be cleaned up so that there was only one unique entry per custom field

The following 2 slides will describe the Dropdown custom fields.

Custom fields were added to the Item Card: Category1, Category2, Category3, Publisher, Author, Book Format, Cover Link Image, Date Entered into the system, First Date Received, Last Date Received, Last Date Sold, Case Pack, Item Default Variant.

Data Tables and List Pages

Data Tables were created with List Pages (using the Application Language (AL) in Business Central) so that the data in the dropdowns would have unique values. Much data cleaning needed to be done.

Before the lists could be created all the legacy data about the book (ISBN) had to be converted to the single dropdown value.

For instance: the Publisher might be “ALGONQUIN BOOKS”. However, it might show up in thousands of entries as “Algonquin”, “Algonquin Pub.”, “Algonkwin”, etc. Only one value could be chosen and all items (ISBN’s) for that publisher would now say “ALGONQUIN BOOKS”.

For instance: there were over 18 different versions of the Book Format: “PAPERBACK”. Most were misspellings: “Paapperbook”, “Paper”, “Paperback Book”, “Paper Book”, etc.

Same problem with Category 1 and 2, Author, and Book Format

Pictures of the Book Covers

Pictures of the book covers are stored in the ISBN Database, which is a public database requiring a subscription, which BFL has.

A special formula is used to convert the ISBN to a Cover Link URL in order to retrieve a picture of the cover (into Business Central) from the ISBN database.

Initially, PDG used an integration tool called SmartConnect to insert the book information from SBM to Business Central. But the information in legacy system did not have the image. Therefore, we created a special formula shown in the screenshot below to bring in the Cover Link URL from the ISBN database. The trick is to understand how the public ISBN database stores the images. It starts with "https://images/isbndb.com/covers/ " and then a hash of the ISBN number which always follows the same pattern but ends with ".jpg". See the CONCAT command in the slide below for this formula.

Now when the books are added to the Inventory Item table, we run an after-hours system job which fills in the Cover Link URL, based on the ISBN and the formula below.

Variants

Variants here refer to the book’s condition.

There are only 2 possible conditions “MARKED” or “UNMARKED”.

A “MARKED” book will have a small red dot on the bottom edge of the pages. It signifies less than perfect condition. The Variant designation will be a blank field.

An “UNMARKED” book does not have that red dot and we consider it “MINT” condition. The Variant field will say “MINT” and it will mean “UNMARKED”.

BFL may have the same ISBN in both conditions. It depends upon the purchase receipts. So the Item Card can only guess the Default Variant.

We created a system job which updates the Item Card at the time the book is put away in a bin. If the packer indicates it is “MINT”, then our job updates the Item Default Variant field on the Item Card. Same goes if it is “MARKED”. It will be a blank field on the Item Card.

Only the SO CAR warehouse uses variants in the system. Not the NJ Warehouse.

Shortcut to the Bin Contents window

A shortcut to the Bin Contents window was added to the Item Menu on the Item List screen.

It saves about 6 clicks to find the Bin Contents table, which shows where the book is, what condition and # of copies in each bin.

If you use a portable scanner and scan the barcode on any book, you can immediately see the Bin Contents window and can find any book that has been put away in a bin. There are 30 million books in the bin systems, so it helps to know where each title is - instantly.

Item References

An Item Reference is used as a cross-reference ISBN for a book. Some publishers use a formula to label the outside of a carton with the ISBN of all the books that are inside that carton. That number on the outside is not the ISBN. So when we scan the carton, we won’t find the match in our inventory; unless we calculate a value for the Item Reference field.

Notice in the screenshot below that the Reference No ends in “478”, while the actual ISBN ends in “471”.

Notice also on the bottom screenshot that the Reference Type is “Bar Code”. We created another formula in the Insight Works software to tell the scanner to find the book based upon the standard conversion formula.

A system job was created to fill in the item Reference field for each item based on the formula on the following page.

The following 2 slides show more information about this.

Item References (continued)

This slide shows the formula to convert the book's ISBN to an Item Reference (a cross-reference that associates the ISBN barcode on the exterior box to the ISBN barcode on the rear cover of the book.). Insight Works allows set up of Barcode Rules, where a "Regular Expression" was used to translate and associate the Item Reference with its ISBN number.

Fetching full information about hundreds of books, just by having an Excel list of ISBNs

ISBN Request Staging List is a custom extension that calls the API from our subscription to the ISBN Database; passes a list of ISBN’s and returns data about each ISBN, such as Title, Author, Publisher, Cover Image, MSRP, Format, etc.

The user can pass a long list of ISBNs and get data returned for all. This return data is exported to Excel and then imported into SmartConnect to add the titles to the Business Central Inventory.

This data can be then used to insert new titles into the BC Item Inventory.

The Customized Item List in Business Central

The Item Card can only show one ISBN at a time. The Item List is a list of all Item Card information.

Our developer put it all a one-page scrolling list, so the user could easily export this List-Page to Excel to obtain the Weekly Inventory Report.

Many of these columns were added as custom fields, so the standard BC system would not expose them in a list unless the list was customized.

The customized Weekly Inventory Report - now downloadable "real time"

Below is the Weekly Inventory Report downloaded to Excel from the Item List on the prior page.

The Qty. Available was filtered to not equal zero.

There is logic behind this report, in that the Qty. Available is reduced by the Qty. on BFL Purchase Orders, since they take longer to process and the books are not available to be sold for another month perhaps.

The beautiful part about this report is that it is always available for a salesperson to download – directly from the Item List. It used to take a full day for a database consultant to fill in the missing information and do the calculations for this report. Now the calculations are done within Business Central – Real Time.

The key to an accurate report is to continually update the Item Card of each ISBN as a Put-Away is Posted or with a System Job in the evening. What was the latest case-pack? The latest Variant? The Last Date Sold?,

All Business Central system jobs that PDG Consultants created were done with Application Language (AL).

Data Integration

Data integration was accomplished originally by SmartConnect.

There were 27 different integration maps written to import or update data into Business Central.

We often exported data from the legacy system using SQL. Cleaned the data using SQL. Exported the clean data to Excel and then imported the Excel data into Business Central using SmartConnect maps.

SmartConnect Java Script functions

Often the data needed to be modified with SmartConnect. The following slide will show some of the Java Script functions to control the length of data or other properties so that Business Central would accept it as valid.

The incoming data needed to match related tables in Business Central. For instance: when importing an ISBN, the vendor for that ISBN must match a vendor on the list of vendors in the BC system. Because of inconsistent entries in the legacy system or misspellings, we had to create a conditional formula to correct the input to match the BC vendor list.

Or to truncate the Title to 100 characters max; or to truncate the Author to 30 characters max.

PopDock Reports

PopDock is a new data tool from eOne Solutions. They created the "SmartLists" inside Small Business Manager and Great Plains. Now they have a similar tool for Business Central. It is incredibly useful.

We created various lists:

Ship Lines

Receipt Lines

Put-Away Lines

Sales Order Lines

etc.

These lists can be filtered to find where an item is in the warehouse. It may not be in a bin, but instead on the floor somewhere in a Put-Away or a Pick that has not yet been Registered or Posted.

Cross-Docking in Business Central was customized for Books for Less.

The next 2 slides show the cross-docking process - relied on heavily by our client. It had to be customized with additional windows and logic for Business Central to satisfy the fast-flow receipt to ship process for BFL. The Application Language (AL) gave us access to the inner workings of Business Central, which would not have been possible with a low-code method.

Steps for Warehouse Receiving for Cross-Docking

·Open Warehouse Receipt

·Clear Qty to Receive

·Find Item (ISBN) from incoming P.O. (Warehouse Receipt)

·Enter Qty to Receive for just that item

·Filter Item No. column to just that ISBN

·Calculate Cross-Dock

·If Qty to Cross-Dock > 0, then Open Cross-Dock Opportunities Window

·Assume the Qty to Cross-Dock was filled in by the system to 400

·Assume there are 3 sales orders requiring copies of this book.

·Pick one and enter the Qty to Cross-Dock in line with that sales order in the Cross-Dock Opportunities window.

·Clear filter

·Sort on Qty to Receive Column, descending to ensure that only one item is being received and cross-docked.

·Post this warehouse receipt and create Put-Away Document

·Go to Put-Away, auto-fill Qty to Handle, make sure the Placement is in a Cross-Dock Bin, and Register Put-Away.

·Go back to the sales order to which you assigned the Cross-Dock quantity.

·Look for the Warehouse Shipment that belongs to this Sales Order.

·(Continued on next slide)

Cross-Docking in Business Central (continued)

·(See the slide below)

·Create a new Pick from this Shipment Document.

·You will find your newly cross-docked items on this pick – along with other items, some of which may not be available for picking (no Take Bin).

·Delete all lines on the Pick for items which have a blank Take Bin.

·Keep for this Pick only those items which are able to be picked. You should see one or more items which can be picked from a Cross-Dock Bin.

·Don’t Auto-fill Qty to Handle.

·Send the Picker out to fetch those items from that Pick number.

·When the Picker (forklift driver) is using the scanner, he can register the pick as soon as he enters the quantity picked for each item on the Pick and scans the bin from where it was picked.

·Repeat this process to use Cross-Docking to fulfill another sales order for another customer

·Go back to the warehouse receipt and open it.

·Clear Qty to Receive

·Find Item (ISBN) from incoming P.O. (Warehouse Receipt)

·Enter Qty to Receive for just that item

·In this case enter 68, (since I know that one sales order needs that quantity of this ISBN).

·Filter Item No. column to just that ISBN

·Calculate Cross-Dock

·The Qty to Cross-Dock will be >0, since you had typed “68” for Qty to Receive and then Calculated Qty to Cross-Dock. The system faithfully filled in the Qty to Cross-Dock as the same as the Qty to Receive

·Open Cross-Dock Opportunities Window

·In the Qty to Cross-Dock column enter 68 adjacent to the Sales Order to Which it applies.

Cross-Docking in Business Central (continued again)

·(See the slide below)

·After Posting the Warehouse Receipt for this Quantity to Cross-Dock; and after Registering the Put-Away for the Cross-Dock of the 68 pieces to Sales Order 10180 for customer Junior Page, we can go to that Sales Order. Then we can find the one Warehouse Shipment for that Sales Order (or create a Warehouse Shipment if there is none). Remember: “Only one consolidated warehouse shipment per sales order!”

·From that Warehouse Shipment we can:

Create a new Pick from this Shipment Document.

You will find your newly cross-docked items on this pick – along with other items, some of which may not be available for picking (no Take Bin).

Delete all lines on the Pick for items which have a blank Take Bin.

Keep for this Pick only those items which are able to be picked. You should see one or more items which can be picked from a Cross-Dock Bin.

Don’t Auto-fill Qty to Handle.

Send the Picker out to fetch those items from that Pick number.

When the Picker (forklift driver) is using the scanner, he can register the pick as soon as he enters the quantity picked for each item on the Pick and scans the bin from where it was picked.

·Let us repeat this process for the same item (9781627797245).

·We go to the same Warehouse Receipt WMS-R-000104

·Find our 100 more copies of this same book (9781627797245).

·Set the Item filter on the Warehouse Receipt to that Item.

·Enter 100 in Qty to Receive column.

·Calculate Quantity to Cross-Dock.

·See 100 in the Quantity to Cross-Dock column.

·Click on that column to open the Cross-Dock Opportunities window.

·See only 2 remaining orders for which to assign those 100 books.

Notice that the prior order for 68 from customer Junior Page and Sales Order 10180 is not in the Cross-Dock Opportunities window any longer, because it has been put away to Cross-Dock and Picked from Cross-Dock. Once it has been Picked and the Pick Registered, then that quantity is removed from Cross-Dock Opportunities

Purchase Receipts (customization): Added the Customer No. to the Cross-Dock Opportunities Window

In the Purchase Receipts we modified the Cross-Dock Opportunities window to now show the Customer for the Sales Order.

Cross-Docking is the process of moving goods from the Receiving Area directly to the Shipping Area without putting the goods into a warehouse bin first.

While the system did specify the Sales Order Number, it did not specify the Customer. In a busy warehouse, this can lead to mistakes.

We programmed the Customer Number into the Cross-Dock Opportunities Window so the Warehouse people could ensure the goods were moved to the correct order in the Shipping Area.

Purchase Order "Samples" customization

A Samples column was added to the Purchase Order Lines section.

While receiving a P.O. the warehouse crew is asked to pick samples of each title so they can be shipped to the sales office in New York for display to buyers.

Since there can be up to 700 lines on a P.O., we made it easy to Autofill with one click.

A menu Action was added to Autofill the “Samples to Pull” column from the “Samples Needed” column.

A menu Action was added to Autofill the “Samples to Pull” column from the “Samples Needed” column. There is also a check box in the column to the right which indicates if samples have been pulled already for that line. This receipt has been partially received so the “Samples Pulled” column has already been checked. Of course, the logic for this type of customization can only be done with Application Language (AL).

Purchase Receipt - Samples Table Updated

The vendor must be paid in full. We cannot deduct samples. Therefore, we recalculate the quantity received for the ISBN to include the Samples.

Samples Table will be updated for only those lines with Quantity to Receive greater than zero and # Samples Pulled greater than zero. An Update entry is made to the Samples Table increasing "# Samples Pulled" for that ISBN and modifying the Date field and the “From P.O.” field..

Warehouse Receipt - New Unit of Measure and New Variant

2 Columns were added to the Warehouse Receipt:

• New UOM

• New Variant

The original P.O. may not have specified the Case Pack nor the Variant correctly. Therefore, the

Receiving crew will specify the Case Pack in the “New UOM” column and the Variant in the “New Variant” column.

When a Warehouse Put-Away is created from the Warehouse Receipt, these changes will be recorded for that ISBN in that quantity put away in its bin.

When the Put-Away is Registered, then a real-time system job PDG created which runs a Warehouse Item Journal to adjust out the wrong Variant or wrong Case Pack.

Sales Orders - Sales Option choice of MINT books only

An Option Set was added to the Sales Order which allows the user to fill the Variant Code column depending on what condition the customer requires. Some will accept MINT (Unmarked) books only. Some require MARKED books only. Most will accept ANY condition. The warehouse may have copies of the book in both Variants (MINT and MARKED) and will combine the books to fill an order where ANY is the option.

This Option setting of “ANY” requires complex logic and splitting lines on the Sales Order if the line requires both MINT and MARKED books to be filled. This complex customization in Business Central can only be done with Application Language (AL).

This order requires MINT Only, so the warehouse can only ship books in MINT condition.

Sales Order – Sales Option Choice of “ANY” Variant condition

This Sales Order was set to the “ANY” option.

Once the user clicks the Reset Sales Option Action button the Variant column will be filled with the Variant which exists. If there are no MINT books in the warehouse, then the Variant Code column will be blanked out (meaning “MARKED”).

You can see the Quantity Available for MINT matches the Variant Code setting. Same logic for MARKED (blank field), where there are no MINT copies available, only MARKED.

It isn’t shown here, but if the total quantity cannot be filled by either Variant alone, but requires some MINT and some MARKED, then the sales order line is split and an additional line is added with the opposite Variant.

Warehouse Shipment customizations – Added Short Column and option to cancel Backorder

Once the Picker brings the Items Picked to the Shipping Area, the lines on the Shipment are checked. Sometimes books were Picked but were damaged; or the Picker recorded them as Picked, but the full Picked Quantity never reached the Shipping Area. For this occasion, a Qty. Short column was created. Entries into this column will cause the Qty. to Ship column to recalculate. It will also show as a Backorder, when the Shipment is Posted.

On the Sales Order, the user has the option to Cancel Backorders, since these books are after-market sales and not vitally necessary for the customer.

Packing Slip customization– will show unfulfilled order quantities as Back-Ordered Quantities

The Packing Slip will show the difference between the Original Quantity and Shipped Quantity as “Back Ordered”. This can be converted to Qty. Canceled from the Sales Order window. An Action button was added to the Sales Order, which will do this conversion automatically.

Sales Order customization - Cancel Back-Ordered Quantity

Pressing the “Cancel Back Ordered Quantity” button will move the Back-Ordered quantities to the Canceled column on the Packing Slip, when it is reprinted. It will also reduce the billed quantity on the Sales Order. The customer cannot be charged for items not shipped.

Sales Shipment- Reprint of Packing Slip after canceling Back-Orders

Reprinting the Packing Slip (Shipment Report) now shows no Back Ordered quantities, only Canceled quantities.

Notice the one line where there was a shortage of 2 from the Picker, that shows as Canceled. These 2 books might have been damaged or the Picker miscounted them; or perhaps the Shipping Clerk lost them during the Packing and Labeling process.

Sales Order – Custom Shipping Manifest Worksheet and Report

A Packing Slip confirms what has been received into the Shipping Area. To be loaded on the trucks a Shipping Manifest is needed. An Action was added to the Process Menu of the Sales Order to create that worksheet.

The Shipping Department counts cartons, labels them, puts them on pallets and checks against the Packing Slip.

Custom Shipping Manifest Worksheet completed

The Shipping Manifest Worksheet List is completed by the Shipping Department. The cartons are labeled and put on Skids. The total pieces per line is calculated by the worksheet:

# Ctns x Case Pack

Once the worksheet is complete a paper or Excel report can be created from another menu.

Sales Order - Custom Shipping Manifest Report

This is the pdf or paper Shipping Manifest.

It specifies the number of cartons and the case pack per carton, with a total # of pieces for each ISBN.

This should match the Packing List and the Sales Order, when it is posted as a Sales Invoice.

Posted Sales Invoice (custom report) matches the Packing Slip and Manifest

The final product of all this is the invoice to the customer – which matches the Packing Slip and the Shipping Manifest.

Transfer Orders – Moving goods from SO CAR warehouse to NJ

Transfer Orders had to be customized because the system can’t compare the stock requirements between multiple warehouses unless we add some logic.

The system won’t know to fill an order with MARKED books if they run out of stock with MINT versions or vice versa.

Notice the Variant Code column on the Sales Order is blank. That means “MARKED” books are being sold.

Note the 3rd title, “Roberts Rules of Order…”. There are no copies of it in the NJ Warehouse. There are many copies of it in SO CAR, but only 97 are MARKED, so the balance of 103 will need to be transferred as MINT.

Our developer extended the Business Central Transfer Order logic to solve these problems. This was done with the Application Language (AL).

Please see the following slide.

Transfer Orders – Intelligence added to correctly transfer only what is needed

Transfer Orders were customized, so they transfer only what is needed for the linked Sales Order (which we added).

We needed 1000 copies of Soul Survivor, but there were already 771 in NJ. So only 229 needed to be transferred.

There were 200 copies of Robert’s Rules on the Sales Order, but none were in NJ. The Sales Order says “MARKED” (blank), but there are only 97 MARKED copies in SO CAR Warehouse. The balance of 103 need to be transferred as MINT.

Without the customizations of linking the Sales Order directly to the Transfer Order and adding logic to look up what is available in both warehouses and in what Variant – the system will not calculate the correct quantities and variants to transfer.

When the Transfer Order is Put Away, adjustments need to be made programmatically

The NJ Warehouse is not set up to have a Receiving Area. However, the books do not go directly into the bins. They first stop at a bin called “TRANSFER”, which represents a Receiving Area. All the lines should have their Bin Code filled as “TRANSFER”. An Action button was added to fill all the Bin Codes with “TRANSFER”.

The NJ Warehouse is not set up to recognize MINT Variants. It is a legacy system from 17 years ago. Therefore, we can Put Away the last line as MINT, but need to run an Item Journal in the background to change its Variant to MARKED (blank). When we refer back to the Sales Order, the entire quantity Robert’s Rules is being sold as MARKED (blank). Therefore, it needs to show in the warehouse as MARKED (blank).

Yes, this is complicated, but remember we are imposing an Advanced Warehousing system on the South Carolina Warehouse (SO CAR) and need to communicate with a Basic Warehouse in NJ in order to do a Transfer and Sale from NJ of books from both locations (NJ and SO CAR). Application Language (AL) in the hands of a skilled software developer can accomplish the most complex of scenarios.

Sales Order Line Variant Code must match the inventory in the bin

Here we have the line for Robert’s Rules requiring 200 copies of MARKED (blank) Variant Code from the NJ Warehouse.

The NJ Warehouse has the 200, but not all of them are MARKED. We need to run a custom system job to convert 103 MINT in the TRANSFER bin to 103 MARKED. Then the order can be fully picked and shipped.

We have a job (to convert all MINT books in NJ) that runs every few hours, but in this case the user wants to Pick this order right away – so we have a “right now” job too. You can see that on the next slide.

Sales Order – Custom Job to convert MINT to MARKED in NJ

When the user clicks the “Process Reg…Jersey Location”, it runs an Item Journal deducting the 103 MINT that were there and adds them back as MARKED (blank variant) and then sums up the total of 103 + 97 to 200.

Now the Sales Order in NJ can be picked complete.

Sales Order can now be Picked and Shipped

The Sales Order can now be Picked completely and brought to Shipping – which the user designates as “TRANSFER 1”. It was set up for Cross-Docking to make it easier to combine Transfer from SO CAR with existing books from NJ.

Sales Order with Transfer Complete: Invoice, Pack Slip, and Shipping Manifest

The slide below shows a composite of the Posted Sales Invoice, the Packing Slip, and the Shipping Manifest - all compared to show the same numbers on each document.

Advanced Warehousing: Inventory Adjustments Flow Chart

Below is a flow chart showing our client how to make adjustments to inventory for shortages in a bin.

Advanced Warehousing: Inventory Adjustments (continued)

A shortage is discovered in Bin W-04-006 for this item. Note that the total quantity on hand for this item is 1370.