13 Replies Latest reply on Sep 22, 2011 1:02 PM by LennonRichardson

    Newbie

    LennonRichardson

      Title

      Newbie

      Post

      Hello, I'm new to Filemaker Pro, and Databases in generall.

      I have a table that has the fields

      Date, Group, Item, Qty Sold, Value Sold. Which looks something like this :-

      1 Sept  Beers  Corona      10  50

      1 Sept  Beers  Heiniken    20  100

      1 Sept  Spirits  Vodka      10  80

      etc..

      I would like on one page (form?) for the user to be able to enter the date just once, for Group & Item to auto populate (from a value list or a related table ??). So that the user can then enter the Qty & Value sold on one page, makeing it quick and easy for them to tab between the fields filling in the data.

      What is your advise on the best way to achieve this ?

      Thanks !

        • 1. Re: Newbie
          mgores

          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

          • 2. Re: Newbie
            LennonRichardson

            Hello Mark,

            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:-

            Date
              Group
                 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.

            • 3. Re: Newbie
              mgores

              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.

              • 4. Re: Newbie
                LennonRichardson

                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.

                • 5. Re: Newbie
                  mgores

                  Lennon,

                  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.

                  • 6. Re: Newbie
                    LennonRichardson

                    Hello Mark,

                    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 ?

                    Thanks.

                    • 7. Re: Newbie
                      mgores

                      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.

                      • 8. Re: Newbie
                        LennonRichardson

                        Hi Mark,

                        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.

                        • 9. Re: Newbie
                          mgores

                          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.

                          • 10. Re: Newbie
                            LennonRichardson

                            Hi Mark,

                            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 ?

                            Thanks.

                            • 11. Re: Newbie
                              philmodjunk

                              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.

                              • 12. Re: Newbie
                                mgores

                                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.

                                • 13. Re: Newbie
                                  LennonRichardson

                                  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 !!