We'd need to know a lot more about your database design before se can suggest an answer.
In the list view layout, each row is a different record or a group of records?
And the numbers listed are the number of items consumed?
But where do you store the data shown in the other spreadsheet column--the amount on hand?
Hey Phil, thanks for taking the time to respond.
1. In the DB report, each row is a summary of a group of records, i.e "1.20 x 60A--1" ,shows a total of 1 nozzle that is a 1.20 x 60A.
2. Not really, those numbers are based on customers equipment. In other words these are the totals of each (in this case) nozzles that are on all customers equipment. Come September Ist will begin to replace all the nozzles throughout the next few months (and other maintenance items). It's just easier to put together all the items I need and order them in bulk in August.
3. The amount on hand is not in the Database yet. I would think I would need to put this on the database, unstored and maybe add it to the Report, then use a calculation field to get to my number? Something I could not figure out. Basically, in the near future, I will just go to my shop, and truck, add up whats on hand, and enter it into this field.
Also, I don't use the database for billing. I use Quickbooks. Detailed invoices for service are done in the field, and a overview is entered into QB.
If it's too complicated (for me, or for you to explain it to me) I wouldnt want to trouble you about it. Manually (after I enter enter "in stock")it only takes about a minute to copy/paste to Excel, and I only need to do it once a year. Like I said before, this is a learn one, create one, use one database for a single user (me), for my business. I just love FM and what it does.....I just sit around in my free time thinking........"what else can I get it to do". (Addicted?)
Thanks Phil for all your help guidance and support.
2) then they ARE the numbers consumed as far as this report is concerned. The fact that they will be gradually replaced over time isn't relevant to the numbers shown in your report as far as I can tell.
3) you'll need to put them somewhere in order for you to get this system to work all in FileMaker and without having to go through the manual steps that you are now with your spread sheet.
This is a basic "Inventory Control" type database issue and their are two basic approaches. One uses a "transaction log" to compute current inventory levels and to trigger needed restocking orders. The other uses a number field in a Products table (one record for each part listed in your sample report) for the amount on hand with scripts that update the values shown each time a shipment is received or product is removed from inventory.
from what you describe here, it sounds like the transaction log method would work quite well. See this thread for a detailed description of the method: Managing Inventory using a Transactions Ledger
Keep in mind that you don't actually need to call this a "sales invoice", it can simply be your "work order" where you document the parts needed for a given service call.
Thank you (again and again) Phil, for your detail reply and help. I checked out your thread, and will spend some time with it in the next week.