I was trying to figure out how to make a report or layout that I can use to reorder inventory. I would like to either make this off an existing report, adding calculations based on a formula, then subtracting that from the amount in stock (a number I manually enter), or off of a layout.
Some additional info, the inventory is not tied to sales, but to the equipment I need to replace on an annual service (nozzles, filters, pump strainers, pump screens. All this information is in the database and shows in a report (see screenshot). The top of the screen shot shows the report layout, the middle is an actual report, and the bottom is exactly what I would like it to do (in an Excel spreadsheet.
Right now I Export the report to a one Excel, the copy/paste columns C & D to the Excel sheet shown, Enter the amount of stock on hand in column E, then let the calculations in F & G create the order amounts. Then repeat for the other inventory items.
Things I've tried (unsuccessfully):
1. Creating additional fields on the report to try the calculations like the spreadsheet, no luck.
2. Go to the layout the information in on, and tried adding some additional fields and looping script. (yikes).
3. Combinations of 1 & 2.
I'm sure this is possible and I was hoping someone can point me in the right direction.
ps. sorry for the long post.