4 posts in this topic
This has been a well discussed topic however I have struggled to find a direct answer to:
Should the 'currenty qty' field in my stock table be a calculation that refers to related line items, or should it be modified via script when related line items are added/deleted to Invoices?
I have looked at Todd Geist's excellent Inventory Transactions tutorial and am very excited about implementing it for this and other functions. For one it would mean I could remove a constant server script that hides/unhides stock items (from the stock list that appears when users are creating line items) depending on whether the quantity is zero or not. To be honest I'm a bit surprised that the portal that is used to display stock items that have a quantity above zero can't just refer to the calculated quantity (I'm guessing i opted for the server script method because the alternative was too slow or it was unable to see the quantity given that it was an unstored calculation). I believe that in general things would be sped up too (at the moment there are just 3000 stock records and 9000 line items).
However I am partly confused as looking at FM15's Invoicing starter solution it would appear that a calculation field is used.
Which path should I take?
First-time setting up a complex (or what I think is complex) database, and really could use some help. The idea is to be able to create an inventory of Computers, Peripherals, Software, and Software Licenses, (and where each is installed/located), along with the Purchase Order information for each item, and the Vendor for each P.O.
Here's how I sort of see it breaking down, but I'm confused as to how to setup the tables and relationships:
One Computer can have many Purchase Orders associated with it.
One Computer can have many Vendors/Manufacturers/Sales Reps associated with it.
One Computer can have many pieces of Software installed on it
One Computer may have many Peripherals
One Computer can have many Change Log entries associated with it.
One piece of Software (software title) can be installed on many Computers
One piece of Software can have many Licenses associated with it
One piece of Software can have many Purchase Orders associated with it.
One piece of Software can have many Vendors/Manufacturers/Sales Reps associated with it.
One piece of Software can have many Change Log entries associated with it.
One License can be installed on only one Computer at a time
One License can have only one piece of Software associated with it.
One License will have only one Purchase Order associated with it.
One License can only have one Vendor/Manufacturer/Sales Rep associated with it.
One License can have many Change Log entries associated with it.
One Peripheral can only be installed on one Computer at a time
One Peripheral can only have one Purchase Order associated with it.
One Peripheral can only have one Vendor/Manufacturer/Sales Rep associated with it.
One Peripheral can have many Change Log entries associated with it.
One Purchase Order can have many Licenses, Peripherals, and/or Computers associated with it.
One Purchase Order can have only one Vendor/Manufacturer/Sales Rep associated with it.
One Purchase Order can have many Change Log entries associated with it.
One Vendor/Manufacturer/Sales Rep can have many Purchase Orders associated with them.
One Vendor/Manufacturer/Sales Rep can have many Computers associated with them.
One Vendor/Manufacturer/Sales Rep can have many Peripherals associated with them.
One Vendor/Manufacturer/Sales Rep can have many pieces of Software associated with them.
One Vendor/Manufacturer/Sales Rep can have many Licenses associated with them.
One Vendor/Manufacturer/Sales Rep can have many Change Log entries associated with them.
Change log is: A manually-created list of notes for any changes, upgrades, problems, maintenance events, etc. performed for the entire department. Needs to be filterable/sortable by Date, Computer, Peripheral, Software, License, P.O., Vendor, and/or type of event. Event types would be from a manually-created value list.
Would be great if I could have a layout specifically for entering these events. Also, not sure how I would go about entering events if one event affects multiple computers or peripherals at the same time. I might have to just make duplicate entries (one for each piece of equipment) to link an event to each piece of equipment it affects?
I've begun setting up tables for:
- Installations (join table joining everything?)
- Vendors/Manufacturers/Sales Reps
- Purchase Orders
- Change Log (not sure what fields I need in this)
I'm also trying to figure out the Primary Keys and Foreign Keys, how the relationships should work, and how I can create a layout for let's say Computers that will show me:
- Computer info (computer name, location, hardware specs from the Computers table)
- P.O. number for Computer (from the P.O. table)
- List of installed licenses (including software name/version, license number, vendor, and P.O. associated with that license) (portal to the Licenses table, filtered by ComputerID?)
- LIst of installed peripherals (including the type of peripheral, manufacturer, name/serial number, vendor/P.O., date of install) (portal to the Peripherals table, filtered by ComputerID?)
- Change Log list for that computer, sortable by date and/or type of change. (portal to the Change Log table, filtered by ComputerID?)
And a layout for Software that will show me:
- Software name/Version
- Type of Software (standalone software, software plugin, etc.)
- List of Licenses associated with this Software (including software name/version, license number, vendor, and P.O. associated with that license) (portal to the Licenses table, filtered by SoftwareID?)
And a layout for Vendors/Manufacturers/Sales Reps that will show me:
- Vendor Contact info (sales rep name, phone, email, website, etc.)
- List of P.O.s associated with this Vendor, sortable by date, and expandable to see the individual items within that P.O. (such as license numbers and where those licenses are installed, or peripherals and where those peripherals are installed)
If anyone has any advice, I sure would love the help. Thanks.
I am looking for solution for quicklube point of sale
The main requirements are:
- Customer Database
- Car Database & history record : what they serviced , when , what invoice they pay , etc.
- Employee Database : their wage , bonus per car they do , etc.
- Cash Drawer
- Thermal receipt printer and normal printer with auto switching.
- credit card (360 plugin or similar) - via mag stripe reader or manual entry
- split payment (ie $10 in cash, rest on card, etc.)
- Returns, either by entering invoice # or returns without invoice. return as cc refund, or cash
- Gift cards tracking (just a unique barcode on preprinted card)
- Sales tax y/n for customers for customers we enter all data for.
- quick sales without needing customer info
- sales tax reports ( we can talk about it later , but not so important right now ).
- inventory management - of course
- bundling on sales. Ie enter a SKU and all items in that bundle are listed on invoice but the bundle price is not the same as the sum of the individual prices.
- quantity discounts ie $1 each or three for $2.75 but can mix and match different pre determined SKUs for the bundle. Think favors of candy, each with a unique SKU.
- fast item entry - either type SKU and hit return or use barcode
- no mouse on basic sales- tranaction including payment can be done with keyboard - OK to use macro keys on keyboard. Complex things like returns, can use the mouse, just not the basic store sales tranaction as a mouse is too slow.
- multiple SKU per item - hey it happens when vendors change barcodes on us
- Open solution
- Daily register balance reports
- Reports not so critical as we can build as needed
If you have or know of a robost FMP POS solution please contact me here , or pm me
I'm trying to create a simple inventory system of digital music (downloads) sales so that licenses can be properly tracked. We have to purchase a license for each song sold.
I'm having trouble with one part of the reporting.
I receive a CSV from the vendor that lists 2 types of transactions: single track (song 1, song 2, etc) and complete album (Album 1 which is made up of song 1, song 2, etc.)
I can successfully report totals on the single tracks using a Summary field in the table, but I'm unsure of the best methodology on how to include the songs sold when a complete album is reported.
I have 2 tables at this point: Digital Downloads which contains all the data imported from the vendor (and will be added to every couple of weeks). Important fields are the Artist, Album, Song, and Quantity Sold. Second table is Albums (will be updated as new albums are recorded). Fields are Album Name, Track Name, License Purchased.
The single report I have created has a header with the Artist, A sub-summary by song (trailing) with the Song and Quantity Sum fields, and a footer with the date. There is a simple script on the front end of the report that sorts the records by Artist and Song and opens the report layout.
When an album is sold, the CSV has an empty Song field, so I can certainly find albums by omitting records that have anything in the song field.
Is this a portal solution, where each instance an album displays the songs that it contains? If so, how do I add those results to the other summed songs?
Or is this a temp table solution where each time the report is run, the applicable songs are added depending on the album sold?
This is my first time using this forum so please excuse any mistakes..
I have setup a standard pos system with DB for invoicing a customer, inventory and purchasing inventory from Maker. The DB is setup in such was so that I have a layout for Invoicing the customer and within the invoice are the line items for each product that I carry in the inventory. The Inventory is defined with barcode, description, prices and most importantly stock. The purchase order is setup the same way as such so the i have layout with purchase order sheet, then within are the line item with barcodes, description etc... Now, the problem that I am facing is that i am using calculations to keep track of the stock where as if i order 1 item then 1 item gets added and if I see that item 1 gets deducted etc.. now the problem is that after certain length of time... I see stock for items that I didn't order. It will just appear out of the blue as stock....
I am totally stumped and urgently need of help.
A POS system with wrong information on stock on hand is useless...
Looking for any suggestions or comments.