ID867 - Update Technicians' Min Stock Levels from BEI Tech Optimization Overview & Sample:

Subscribe to ID867 on our website and indicate the recipients for any updates posted, we will email them a list of records processed. We typically schedule this to run every 30 minutes. It will only process new records imported in the steps below that have not been previously processed.  

BEI’s Technician Territory balancing tool has the ability to report back to you the optimum car stock your technicians should be carrying based upon the technician's assigned equipment AND the national parts usage CBC AND the equipment's volume. 

**Please see spreadsheet link at bottom of this post

To utilize the BEI Predictive Car Stock, you must use the BEI Territory Mapping Tool (the mapping tool currently works best from the Chrome Browser).

Login to BEI

                Territory Mapping (under reports)

                Select their pre-determined Work Area

                (click NEXT, then SUBMIT)

                Now under Reports

                Generate Car Stock

                Input your email address and select one or more technicians

                The report is emailed with an attached file (.csv)


Within 5 minutes or so BEI will email a link where you can retrieve the ZIP file.

**FYI the zip file name does not match the file name inside.

You will receive the zip file containing a .csv file for each technician requested.  You should open each file in Excel and review BEI’s suggested car stock.  They are working with data, but like most optimization tools, it is not infallible and you should make any changes that you feel do not make sense.  A pair of eyes on any report  information (BEI, Juice, DGI, Compass, etc.) is worth 10,000 lines of programming code.  Once you make any changes/deletions/adjustments:

  • Highlight and ‘copy’  all the rows of data from that spreadsheet in columns A thru C


  • Open the  CEOJuice_BEI_TechMinUpdate revxxx.xlsm spreadsheet and on the worksheet tab ‘BEITechMins’, paste the contents starting at column E  [PartNumber] thru G [StockAmt]. This spreadsheet is attached at the bottom of this post.


In the Warehouse column, you’ll need to put in the warehouse number from e-automate that you want to update, making sure that you copy that warehouse number into column D for all the rows that have BEI data in them.

  • In the same spreadsheet, go to the [Settings] tab, and enter in ‘CEOJuice’ in the Company DB Name, and your e-automate SQL Server (Server) name.


  • Return to the BEITechMins Tab and press the ‘Insert Button’ at which point you will prompted for a valid user ID login and password for the SQL Server!


  • After you enter your user ID and password, press ‘OK’ and if your login is valid for SQL Server, the spreadsheet will process the data into our CEOJuice database table so that it can be processed by ID867 (this process runs on whatever schedule you specified - hourly, daily, etc.) and you will not see any changes to your technicians' minimum car stock levels until the task has run and the recipients specified in the subscriptions have received the email report with the results).


  • You should see ‘Posted’ in the Status column for all rows which means the data was written into our CEOJuice temporary table.
  • When the task runs (based upon your requested schedule,  the default every is 30 minutes), an email (sample below) will be sent to whomever is listed on the alert.




Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request


Please sign in to leave a comment.
Powered by Zendesk