Depending on what you are trying to do exactly it may be different, but assuming the group, item and value would be constant, I would put these in a Products table with another field called ProductID (an autoentered serial number. Then your "Sales" table would have the date and qty of the sale and a SalesID (also an autoentered serial number). With each sale possible having many products and products being sold in many sales, you have a many to many relationship and need a third table that would contain ProductID and SalesID. Your relationship between the tables would be
Products::productID = Jointable::productID
Sales::SalesID = Jointable::salesID
Thanks for your reply. I think you've got me heading in the right direction.
This is for a cash bar. The overall plan is that we enter the total qty of each product sold each day so that we can then generate reports to see how much of a certain product we sold over a certain time period. (Or a summary of all products over a time period).
Group & Item are constant
I (think I) understand and like the 3 table relationship you suggested.
Although once the randomly generated SaleID exceeds the match in the join table how can you reset to zero?
As each record (Item) will have a date is this best done in table view, or is it possible in a more attractive layout with the date only displayed the once. So it looks like this for data entry:-
Item 1 Qty Value
Item 2 Qty Value
or is this only possible as a report once the Data Entry has been done ?
Thanks again for your help.
You would probably want your Qty in the join table as well.
The numbers will never exceed those in the join table since the SalesID in the join table will be entered from the salesID in the sales table. Basically when you create a sales record for each day, you create records in the join table for each of the products sold on that day. So if you sold 3 coronas (productID=1) and 20 vodkas (productID=2) on the day where SalesID =1, there would be two records created in the join table with the ID's being entered from the other 2 tables and the Qty for each being input by the user.
SalesID=1, productID=1, Qty=3
SalesID=1, productID=2, Qty=20
When you look at the sales for that day you can have the layout (based on the Sales table) show the date, then have a portal display the related values from the Join table, giving you a list of the "item - qty - value".
You can add summary fields to the Sales table to calculate and total up the sales for that day.
You will also have the option of looking up product sales by date, so you could track how much you sell of each item. i.e. more beer sales on the weekends, more spirits early in the month or however it goes.
Thank you Mark, I'm almost there !
The way I have it at the moment, the user still needs to physically type in the Item (or productID) in the portal.
I would like the user to create a new record (Date) and for the complete list of items to already appear in the portal. How is the best way to do that, through a script, lookups, or another way ?
I would then probably run a script on record commit to delete all items (ProductID in jointable) were qty. is empty.
Thanks again for your help, it's good to feel I'm making progress, and that I will be able to generate all the reports I need.
Define a value list for items, base it on the values of the Items and productID fields (you can hide the productID so the user doesn't see it) from your product table, then set that field in the portal to be a drop down based on that value list. Then the user can click on that field and select the desired product from the drop down list.
An alternate method would have an entry layout where the portal shows all the Items from the product table, use the "X" instead of the "=" in the relationship for the that portal. Then have the user just enter the Qty, and possibly the price if it varies(note -you would then want the price field in the jointable), for the items sold. I would then have a different layout for the report that has a portal with the "=" relationship to show only those items that have a qty entered or filter out qty=0.
I'm trying to achieve the second / "alternate" method you suggest, but unfortunately I'm just not getting it. I have changed the relationship between the 'Product Table : ProductID' and 'Join Table : ProductID' to 'X' but the portal is not automatically showing the items / productID's from the product table. Is there an additional step I need to take ?
The way I did it is to create another table occurance of product for the X relationship and called it "Product list" (you can just pick random fields for the relationship as it is not a matching relationship).
So you would have Sales::anyfield X Product List::anyfield
and a Sales::SalesID = Jointable::SalesID
and ProductTable::productID = Jointable::productID
Then the portal would be based on the Product list relationship.
I'm still not there. The full list of 'Product List::Item' appear nicely in the portal when I create a new sales record, but when I enter the qty figure into the portal that figure is coppied down to all records in that portal. In the Join Table there is only one record for each date, not one record for each item.
You will need to modify so that there is a separate record in the join table for each product, you will have several join table records associated with each Sales record. i.e. a separate record for each product/data combination. While this may seem a little more complicated in setting up, it has many advantages down the road. You won't have to modify all of your layouts or the structure of the DB if you add, remove products down the line; you will have many more options for reporting sales, by date, by product, etc.
I like the idea of having the bulk of the data in the JoinTable', but in order to autopopulate the portal with the items from the 'ProductTable' you suggested that the portal would be based on the Product list relationship. If the portal is based on the table occurance 'Product List' then it cannot create records in the 'JoinTable'.... or can it ??
And if I have the portal based on the 'JoinTable' it does not list all the items from the product list without any user input. ie such as the user typing in the ProductID.
Am I trying to achieve the impossible or am I just misunderstanding ?
One option is to set up a portal to all products and set it up as a "click list". Use the X operator in your relationship for your portal so that all products are listed.
You can then format the fields in this portal as buttons that run a script to add/remove the clicked item to/from the join table: If you use conditional formatting on layout text in the portal row to indicate which items are currently selected, you no longer need to include a portal to the join table on this layout.
Or you could have 2 portals, one being a click list as Phil suggest above, when the user clicks the item from the product list, it will show up in the join table portal where they can enter the Qty and price info for the sales that day.
Got it !!! I had a look at your 'Enhanced Value Selection' file, which works a treat with the click list, I have kept the 2 portals as Mark suggested so once the user becomes more accustomed to the productID's (PLU's) they may find it quicker to just enter them directly.
Thank you both for your help, it is greatly appreciated !!