14 Replies Latest reply on Dec 23, 2011 7:52 AM by mikefromnewmexico

    NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11

    mikefromnewmexico

      Title

      NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11

      Post

      Hello, I'm new to the forums and hopefully someone can shed some light on my needs and issues.

       

      I handle concert merch for a touring rock band and am trying to build a database that replicated what I have been doing in Excel. In Excel, I am spot on and have spent countless hours building something I am quite proud of but now we are doing so much volume that if a link breaks or Excel hangs (I'm using Mac OS X), valuable data is lost and I have to spend alot of time backtracking to cut and paste. to update info.

       

      My scenario is:

      We have shirts in various sizes where unit cost can vary depending on if we get a volume discount from distributor.

      Each show is an invoice in and of itself as one show = one invoice and each invoice may not carry the full inventory at any given time.

      Invoice contains columns Returns (from previous show), pull/issue, add1, add2 (where if you have to add stock through course of night) and then closing inventory. Returns+pull+adds-returns=qty sold and inventory should be adjusted as such. Returns should rollover to next invoice i.e. Monday's returns go to Tuesday's starting balance.

      In excel I also tracked statistical data which averaged qty sold each night which would give you a rough idea of what stock you should pull based on projected attendance.

      as far as inventory projections, I normally enter projected atendance and based on what I feel a product may sell (% of sales) determines what the initial tour order will be.

      While it may seem quite rudimentary to some of you more knowledgable folks, I'm having a problem breaking out of the Excel mentality. Merging my excel isn't an option due to the way the sheets were built.

      Hopefully I haven't made this confusing but because we are done for the year and starting 2012 with a new tour and new product line, I feel it is the best time to make transition.

      ANy help or ideas would be of great assistance.

      I played with the bonsai starter and while that is pretty much in line with what I'm looking for, it still doesn't address the adds and returns issue and the actual settlement solution which is a different post in its entirety as a reverse charge mechanism is used and sometimes we retain tax and credit card fees, sometimes not and some shirts are legitimate charitable items tax exempt etc.....

      I have provided an example of a tour projection for a UK Tour

      Thanks for your time.

       

      Preview_of_%E2%80%9CUK_PER_HEAD_FALL_2011.xls%E2%80%9D.pdf.jpg

        • 1. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
          philmodjunk

          Given that you started with the "bonsai starter", what tables and relationships do you have set up at this point?

          Are you familiar with this very simple invoicing demo file? http://fmforums.com/forum/showpost.php?post/309136/

          You've described some parts of your system without enough detail for those not familiar with your procedures to know what your currently need and how you set it up in Excel to do it.

          Please describe how you use your "add" columns as they don't show in any of your screen shots. I would guess, that this has to do with managing inventory, but would like a detailed picture before I make any suggestions.

          • 2. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
            mikefromnewmexico

            With the Bonsai I have just replaced their customer data with mine as well as a few inventory items keeping all of their tables and relationships intact.

             

            The screenshot I provided is how my pre-tour forecast looks: not an actual settlement sheet

            For the actual working day sheet in excel the columns look like:

            Header (Date and venue)

            A)Item Name

            B)Size

            C)Returns

            D)Pull

            E)Initial Issue (C+D)

            F)Add

            G)Add

            H)Total In SUM(E:G)

            I)Comps

            J)Damaged

            K)Out

            L)Sold (H-I-J-K)

            M) Price

            N) Total (L*M)

             

             

            I will look at the inventory Demo and play with that.

             

            Settlement (invoice) is a little more complicated on a different sheet but provides:

            Total Gross Sales:

            Total Sold by design with a sub of % sold by design i.e the 44 XS of the 200 Black Tees sold was 24% of all show sales today

            Gross then carries to bottom for a settlement summary which provides:

            Gross Sales

            Tax (which is different for each locale) calculated as 

            =(SUM(Gross/(SUM(1+Tax))-Gross)*-1) so if you had a gross of say 11,682 the tax backed out is 661.25.

            The reason the tax is backed out is due to a shirt sold at $25 includes tax to make transaction quick and to where you don't deal with coins

            Adj Gross which is Gross-Tax

            Venue percentage which varies but let's say it's 20% so next figure would be:

            Amount to venue: 20% of adjusted net

            Payable to Artist is Adjusted Gross - Amount to Venue

            There are other minor calculations to be factored in like staffing, bootleg security etc but I'm just trying to get a handle on getting started. thanks

            • 3. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
              philmodjunk

              Excel lends itself easil to setting up a grid of rows and columns and calculations easily span both columns and rows to compute the needed results.

              FileMaker does not make such a set up as easy as Excel. It has strengths in other areas--including the ability to generate summary reports that are grouped by categories to provide sub totals as needed by your users.

              Given the differences, I recommend you not try to perfectly replicate your original excel layout as that will limit your design and require a lot of extra design work on your part to make it happen. That said, it can be done if you really need to. In this case, filtered portals can provide separate columns for each of the different transactions that might be recorded to update inventory.

              I suggest that you treat inventory management as you would deposits and withdrawels from a bank account.

              Transactions that reduce your inventory (sales, comps, damaged, Shrinkage) are logged as "withdrawels" and Transactions that increase inventory: (Pull, Add, Returns ) be logged as "deposits". Your Current "balance" will then compute the totals you have on hand for each item. You'll also need to distinguish not only between the Color/Art work on the shirt, but the shirt's size as well in order to break down your inventory counts for each size shirt.

              Here's a basic outline of a table that can track inventory transaction by transaction. Not only does it provide you with an up to the minute count of what you should have in inventory at the current moment, this table also gives you an indication over how levels have changed over time--which can help you set re-order levels.

              TransactionTimeStamp  (Date and time item was added to / removed from inventory--can be an auto-entered creation time stamp.)
              ItemID (UniqueID of each shirt type/size sold)
              Description (Text field identifying the type of inventory change (Sale, Return, Pull, etc.)
              QtyIn (Number, count of this item added to inventory)
              QtyOut (Number, Count of this item removed from inventory)
              cBal (QtyIn - QtyOut , get's correct postive or negative value for computing a running balance)
              sBalance (Summary, Total of cBal, running total, restart totals when grouped by ItemID)

              If you remove items from inventory at time of sale, You can use this table as your line items table with QtyIn used to count the number of Items sold on each line of your invoice. Other layouts can be used, as needed, to log other inventory changes.

              • 4. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                mikefromnewmexico

                Great thanks, I'll organize what you suggested and report back.

                • 5. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                  mikefromnewmexico

                  Okay thanks for the tips. Things are going well and my data is doing what it is supposed to do.

                   

                  One question is how would I move my sizes into their own table to make invoice read a little cleaner.

                   

                  Right now the invoice body looks like

                  Black shirt - XS - 6

                  Black Shirt - S - 12

                  Black Shirt - M - 14

                  etc......

                  which after 6 designs looks cluttered and generates a 3 page invoice.

                   

                  I would like for it to look

                  Black Shirt 

                   XS - 6 | S - 12 | M - 14 etc

                  White Shirt

                   S - 4 | etc

                   

                  right now I have the sizes in a size/serial no. field

                  Just trying to prevent redundancy and letting clean lines and less clutter complement the database invoices

                   

                  Than ks in advance

                  • 6. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                    mgores

                    If you set up your layout to only list each style once the for the size info create a calculation field,

                    SizeList = Substitute ( List ( Product::size/serial no. ); ¶ ; " | " )

                    place that SizeList field in place of your size/serial no. field and it should list them like  XS - 6 | S - 12 | M - 14 

                    Though you would have to make sure your invoice line had enough room for the longest possible string there.

                    • 7. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                      mikefromnewmexico

                      Thanks. I will try that. Makes sense!

                       

                       

                      • 8. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                        mikefromnewmexico

                        So things are going well and I'm using the starter invoice template and when I go to add the second item to the invoice, the product ID field cannot be selected. all the other fields can but just not product id which, according to the inspector, should be able to be edited with a pull down window from line tems::product id.

                         

                        I have not changed the default settings, field settings or relationships.

                         

                        • 9. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                          mikefromnewmexico

                          Disregard, I discovered that the left edge of the furthermost field (Product ID) was a hair over the actual specified border of the portal....good to know. It was impossible to see until I zoomed in

                          • 10. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                            mikefromnewmexico

                            So i'm having an issue with the  calculation to move the sizes into a horizontal row. The calcualtion you gave me did clean up the layout very well but I'd still like them to read as a "row" Thanks

                            • 11. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                              mgores

                              SizeList = Substitute ( List ( Product::size/serial no. ); ¶ ; " | " )

                              should replace the returns in your list with  |   and make them appear on a single row.  Did you put that calculation in line with the description field?

                              • 12. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                                mikefromnewmexico

                                I'm sorry maybe I'm misunderstanding you. I made a new field in products called SizeList and put the calculation in verbatim (filed position is located in body part) which resulted in the above /result/picture

                                • 13. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                                  mgores

                                  you would want that field to be in the same summary part as your main category (T-Mens EYE Black) so that the List ( Product::size/serial no. ) part of the expression will get all of the sizes.  In the body part it is just getting that size from ones that are the same size.

                                  • 14. Re: NEWB HAVING ISSUES MOVING FROM EXCEL TO FMP PRO 11
                                    mikefromnewmexico

                                    I must be doing something wrong : still doesn't work but now that the boss wants to see the re-order level and not just on hand the way the formula is portraying data is a huge help.

                                     

                                    I really do apporeciate the help - thanks and have a good holiday