top of page

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.


Advanced Warehousing: Inventory Adjustments (continued again)


A Warehouse Item Journal is done to Register the missing 40 pieces


Advanced Warehousing: Inventory Adjustments (continued again)


The printed Warehouse Register shows the negative adjustment against Bin # W-04-0006 and the positive balancing adjustment into the Warehouse Adjustment Bin # W-11-0001



Advanced Warehousing: Inventory Adjustments (continued again)


The Warehouse Item Journal adjustment didn’t change the total Inventory Quantity On Hand.




Advanced Warehousing: Inventory Adjustments (continued again)


Because the Warehouse Item Journal didn't change the total inventory, an additional journal (an Item Journal) is called to synchronize the basic inventory with the Advanced Warehouse Inventory. It is an automated process




Advanced Warehousing: Inventory Adjustments (continued again)


In the above case, enter the Item No. into the Item Journal, with the Quantity. Specify the Entry Type as a Negative Adjustment.

Then use the Actions->Functions->Calculate Warehouse Adjustment menu to synchronize the Warehouse count.



Advanced Warehousing: Inventory Adjustments (continued again)


Oddly enough, the Item Journal won’t let you enter a Location Code manually. You need to run the Calculate Warehouse Adjustment Function first. That creates another record with the Location Code inserted. Then you need to delete the original line, with the blank Location Code. Then Post the Item Journal.



Advanced Warehousing: Inventory Adjustments (continued again)


After the Item Journal is Posted, the Adjustment Bin is zeroed out for its previous balancing entry.




Advanced Warehousing: Inventory Adjustments (continued again)


You may remember from the second slide, that the Inventory On Hand was 1370. After both the Warehouse Item Journal and the Item Journal (and the recalculate function) the quantity was reduced to 1330.



Advanced Warehousing: Inventory Adjustments (continued again)


From the Item Card for this title, we can go to the Related->Warehouse->Bin Contents Menu to view the contents in Bin # W-04-0006. It is now the true count of 200, instead of 240.




Advanced Warehousing: Inventory Adjustments (continued again)


The Adjustment Bin has been cleared of stock for that title (101 Techniques Oil, ISBN 9781438001975)




Advanced Warehousing: Inventory Adjustments (continued again)


Search the Related->History->Ledger Entries from the Item Card for the title "101 Techniques Oil" and you will see the minus 40 adjustment.




Advanced Warehousing: Inventory Adjustments (final slide)


You can get to the Item Ledger from the Item Card as shown in the prior slide. Here we see the latest negative adjustment of -40. In fact, the entire history of the item shows it originally came from Bridgeton. Was transferred out of Bridgeton by our own logistics (our truck). Was received in SO CAR. Shipped 10 copies. Found a shortage in the bin and made the necessary adjustment.




Barcode Labels – Printing for all bins in South Carolina Warehouse


2-D (Data Matrix) barcodes were generated for all the bins in the South Carolina Warehouse. The 4x6 labels were large enough to be scanned from 6 feet by the forklift driver, as he/she was doing a Put-Away. All the Bin Labels for the entire 3 or 4 level section were on the eye-level cross-beam, so that the driver could scan the contents for the top-level Bin without leaving the forklift.


We set up a routine that could print thousands of labels at a time, in sequence – matching exactly all the Bin Locations in the warehouse.


The scanners were set up to match the Put-Away document with the Bin Location. This recorded the contents of the Put-Away as now in that Bin, once the Put-Away was Registered by the driver.


Now using the scanner to view the Bin Contents, showed whatever had been placed on the pallet inserted into that Bin location.


Thanks to Insight Works' free barcode software, this process was easy.







System Jobs and their explanation


Many custom system jobs were created using Application Language (AL). Some run nightly after hours or at multiple times throughout the day.



BFL (Books for Less) split into 2 companies to more easily manage day-to-day


We created the Accounting Company as a separate company from the Warehouse Company. For financial reporting, they both can be combined as one company. For day- to-day management they are better separated.


In Business Central we name the Accounting Company “Books for Less”. The Warehouse Company is named “Books for Less Whse”.


They both have the same customers, vendors and inventory catalog (same list of ISBNs); although Accounting lists the Inventory as non-inventory. This way a sales or purchase invoice can be easily modified in Accounting without being constrained by having to reconcile to inventory counts.


The sales and purchases do occur in the Warehouse Company. One of our developers wrote an extension in Application Language (AL) which copies one or more Posted Sales Invoices from the Whse Company to the Accounting Company as an Unposted Sales Invoice. In addition he re-generates the Warehouse Shipment from a PDF and sends it as an Excel file attached to the Unposted Sales Invoice.



Sending a Posted Sales Invoice from the Warehouse to Accounting




When the Warehouse has fully processed a Sales Order, created a Shipment (Packing Slip) and a Packing Manifest, then the Sales Order can be Posted in the Warehouse Company and sent to the Accounting Company. They use the “Send Sales Invoice to Accounting” Action link. They pick one or more invoices to send from the dropdown. An invoice can only be sent once. The system will prevent duplication.


When the Accounting Company wants to Receive one or more Sales Invoices, they use the “Receive Sales Invoice Data” Action link.



Accounting receives the Posted Sales Invoice as an Unposted Sales Invoice


We send an Unposted Sales Invoice to Accounting, so they can verify and correct any missing or incorrect quantities or prices.


The Warehouse Company does their best to check the Posted Sales Invoice against the Packing Slip, but this checking process occurs again in Accounting Department.


Because the Inventory Items in the Accounting Company are “non-inventory”, then there are no restrictions to changing anything on a sales line. Corrections can easily be made.


When Accounting is satisfied the sales invoice matches the true Packing Slip, then Accounting sends the Unposted invoice and bills the customer. The customer receives the invoice and often will make corrections to the count. When the customer and BFL agree on the corrections, BFL changes the invoice and Posts it. Doing this in the Warehouse Company would not be easy or even possible, since any change on a sales invoice would affect the corresponding shipment, packing list, and inventory. Since the Accounting Company uses "non-inventory" items, changes are not restrictive. There can be 700 lines on a given invoice, each line being over 1000 books. Mistakes happen and are easily adjusted.



Accounting sends the Invoice to customer with Packing Slip


The next slide will show the Packing Slip in Excel format, which can easily be changed to match the changes on the Sales Invoice.


Please realize the complexity of this customization. The Posted Sales Invoice from the Warehouse Company gets sent to the Accounting Company as an Unposted Sales Invoice. The pdf packing slip in the Warehouse company gets streamed as an Excel file to the Accounting Company, so the packing numbers can be adjusted at the point of shipment or revised again after the end customer receives and counts the shipment. There is no way a low-code solution could create this complicated extension. It had to be done with Application Language (AL).




Vendor Default G/L Distribution Account - customization


In Great Plains (Microsoft Dynamics GP) expense purchases (not inventory, but rent, electricity, legal expense, vehicle expense, marketing, etc.) are all done as simple, one line, Payables Transactions. The Vendor Card in GP has been set to a default G/L Distribution account. When a bill arrives for an expense (not COGS) the payables entry is quick, because the bookkeeper need not search for the G/L account. It populates the Payables Transaction Window automatically from the Vendor Card.


In Business Central expense entry is advised to be done with a Purchase Invoice, specifying it as a G/L account on the line instead of an inventory item. Moreover, the G/L Account assigned to the vendor has to be remembered by the entry clerk. There is no easy way to set up default G/L Distribution Accounts per vendor.


PDG Consultants created a quick way to enter expense purchases for Business Central.

  1. Add 2 fields to the Vendor Card

    1. A Dropdown field for the Default G/L Balancing Account (which looks at the G/L)

    2. A read-only field of the Account Description field based upon the G/L Account chosen.

  2. Add logic to the Purchase Journal that will auto-populate the Balancing Account field based on the setting on the Vendor Card.


We captured the Default Distribution accounts for vendors in the legacy system and imported them to Business Central.


However, we first had to add 2 new fields on the Business Central Vendor Card to hold this data.



Entering Vendor Bills – Automatic Insertion of G/L Distribution Account


Vendor bills (other than purchase of goods for resale) are entered quickly in a one-line Purchase Journal. A full purchase invoice doesn’t need to be created.


Because we linked this vendor to a Default G/L Distribution Account on the Vendor Card, then our system will automatically populate any journal line with the correct account once the Vendor Number has been entered (in this case “A-1 Fire”).


Notice that the entry is negative. That is because it is a Credit to the Payables Account of A-1 Fire balanced by a Debit (Positive entry) to the Expense Account 71000, Office Expense. However, Microsoft has recently corrected this requirement to enter vendor purchases as negative. Just enter them as postive numbers now. Microsoft will switch the signs as needed.






Check Remittances – Lacking in Business Central


Business Central is not able to print more than 10 lines on a check stub. If the check is paying more than 10 invoices, Business Central uses multiple checks to display the applied invoices and voids all but the last one.


Notice it took 2 checks to pay the 14 invoice/credit lines (11805 +11806). 11805 was voided and just used for the stub.


Using Application Language (AL) PDG created a remittance report for Business Central. See second slide below.





Custom Check Remittances – Added to Business Central


Here is the revised check paying all 14 invoices with a list of all 14 on the attached Remittance. There could be 50 or more applied invoices and this would still work – printing only one check and one Remittance .


Microsoft has been asked for this feature. They estimate the earliest would be 6 months. Well - our customer’s Accounts Clerk couldn’t wait; so our terrific developers wrote it especially for her - using Application Language (AL).





Historical Sales stored on Microsoft Azure and accessed using eOne PopDock Reporting


We created a Data Lake on Microsoft Azure to hold large data sets.


The one we see here are all sales invoices from 2006 to March 18, 2022 - for all customers, for 13-digit ISBN’s beginning with “97”.


We are viewing it in a data tool called PopDock, which can connect to any Cloud data provided you are authorized with a security key.


The entire data set we loaded is 1.6 million rows. Far too big to look at in Excel; so it must be filtered to manage the view. A filter was set for:

• Customer Number like “Plum”

• Category starts with “CHILD”

• Description contains “Mouse”

• Available greater than zero






Historical Sales stored on Microsoft Azure – (filter for Edward R. Hamilton – All)


Here is a filtered set of data for all of Edward R. Hamilton’s sales since 2006 and sorted by descending document date. It is 21,368 lines from all invoices in the period.


The export to Excel is shown in the next slide.



Historical Sales for Edward R. Hamilton – exported to Excel


Hamilton’s sales since 2006 are available in Excel.


We don’t just show each invoice line, but also the current availability in the warehouses, current sell price and current list.



Historical Data imported directly into Business Central and merged with live data - real time.


PDG Consultants improved upon the Historical Sales Data Report above, by bringing in 6 years of Historical Sales Data and merging it with real time sales data, moment to moment. All the records are now in Business Central and combined with the current transactions. There are not 2 places to look and a much faster report now that we are using Business Central's accelerated indexing functions from Application Language (AL).



Thank you for working your way through this documentation. Hopefully, you will see the real power of Business Central is its potential to be molded or customized to fit any complex, proprietary, business process - using Application Language (AL).




Recent Posts
Archive
Search By Tags
Follow Us
  • Facebook Basic Square
  • Twitter Basic Square
  • Google+ Basic Square
bottom of page