1 2 Previous Next 21 Replies Latest reply on Jun 25, 2012 3:59 AM by liza

    Crosstab Item allocation for POS

    liza

      Hi, is it possible to do something like this in Filemaker (see attached excel file)?

       

      I'm making a POS, and before delivering items to different stores, I want to make an Item Allocation, to plan what quantity per item will be allocated to different stores. So for example I have 50 pieces of Item1 in the warehouse, and I'm going to distribute it to 3 stores, I want to be able to encode in Filemaker how many pieces of Item 1 I'm going to allot for Store1, 2 and 3, and I should be able to see the balance how many pieces I have left in the source store, after allocating.

       

      After making the allocation, I want to have a script that will generate delivery slips to the stores indicated in the Item Allocation. So if there are 5 items that were allocated in Store1, I run the script and it will create a delivery slip going to store 1, and deduct the quantities from the warehouse.

       

      I'm sure the 2nd part is possible, but I just can't figure out how to do the Item Allocation part, in an excel-like format so that it's easy for the user to see the starting quantity and the balance. I'm not even sure if this is possible? Hope someone can help me out.

       

      Thanks and regards

      Liza

        • 1. Re: Crosstab Item allocation for POS
          erolst

          Hi Liza,

           

          here's a little example database with a basic delivery slip / report layout, just to show you that FM is

          the best tool for this kind of job. ;-)

           

          There is (of course) room for optimization, especially if I knew a bit more about your workflow (like,

          do you work from items or from stores?). Also the script triggers haven't been tested extensively

          in a business environment …

           

          Let me know what you think.

           

          Oliver

          • 2. Re: Crosstab Item allocation for POS
            liza

            Hi Oliver,

             

            Thanks for your sample! The basic idea is here, but I would like to do it in a crosstab format, with the item as rows, and the stores as columns.

             

            The POS I'm working on is for a clothes retail shop, so each item is composed of the Style and Size.

            Example:

            Item 1: Style 1 (small)

            Item 2: Style 1 (medium)

            Item 3: Style 1 (large)

            Item 4: Style 2 (small)

            Item 5: Style 2 (medium)

            Item 6: Style 2 (large)

            ..and so on

             

            So say Style 1 will have 50 pieces for each size (small, medium and large), for a total of 150 pcs. I will then allocate the 50 pieces of each size to the different stores. So to answer your question above, I'm working from Item, and not from the Store.

             

            I would like to have the crosstab format because I would like to be able to see all the items in each style together while I'm allocating, so that I can see the whole picture for that certain style: where are the 150 pieces going exactly? So that for Style 1, I can see in one screen how many of the small, medium and large I'm allocating for each store.

             

            Thanks and regards,

            Liza

            • 3. Re: Crosstab Item allocation for POS
              liza

              Or could it be possible to do this using a portal?

              • 4. Re: Crosstab Item allocation for POS
                erolst

                Let me look into this; I'll get back to you.

                • 5. Re: Crosstab Item allocation for POS
                  comment

                  liza wrote:

                   

                  I would like to have the crosstab format because I would like to be able to see all the items in each style together while I'm allocating, so that I can see the whole picture for that certain style: where are the 150 pieces going exactly? So that for Style 1, I can see in one screen how many of the small, medium and large I'm allocating for each store.

                   

                  Filemaker is not a spreadsheet and it's not flexible in the horizontal direction. IOW, if you require a column for each store, then adding a store will require modifying the layout - i.e. the intervention of a developer in something that should be entirely in the user's province.

                   

                  That's not to say a tabular view is impossible - for example, you could use filtered portals as a workaround. However, you need to consider if it's worth the extra effort, given the above limitation.

                  • 6. Re: Crosstab Item allocation for POS
                    erolst

                    Michael Horak wrote:

                     

                    liza wrote:

                     

                    I would like to have the crosstab format because I would like to be able to see all the items in each style together while I'm allocating, so that I can see the whole picture for that certain style: where are the 150 pieces going exactly? So that for Style 1, I can see in one screen how many of the small, medium and large I'm allocating for each store.

                     

                    Filemaker is not a spreadsheet and it's not flexible in the horizontal direction. IOW, if you require a column for each store, then adding a store will require modifying the layout - i.e. the intervention of a developer in something that should be entirely in the user's province.

                     

                    That's not to say a tabular view is impossible - for example, you could use filtered portals as a workaround. However, you need to consider if it's worth the extra effort, given the above limitation.

                    You can work around that horizontal inflexibility by putting the sizes on the x-axis and make the stores scrollable, assuming that new sizes will only be added infrequently, if at all, while stores may come and go. But you're right in that this is easier to do in a spreadsheet, because even in this arrangement, at least one additional utility table is needed to provide the blanks in the crosstab view, and a new size would require layout modification.

                    • 7. Re: Crosstab Item allocation for POS
                      comment

                      erolst wrote:

                       

                      you can work around that horizontal inflexibility by putting the sizes on the x-axis

                       

                      Only if you assume that each style has exactly the same sizes - or, more precisely, the same number of sizes. Of course, you could just go ahead and build a layout of 5 columns and leave 2 of them empty when the product has only 3 sizes. Or build several layouts and switch between them as required. As I said, it's a matter of balancing the costs against the benefits.

                      • 8. Re: Crosstab Item allocation for POS
                        erolst

                        Liza -

                         

                        as you probably have gathered from following the discussion between Michael and me, FM is not a spreadsheet, and a bit impeded when it comes to horizontal display. A crosstab report is certainly possible, but unless you put the more static elements on the x-axis (the horizontal one), dynamic generation would be quite difficult. If you switch the dimensions, it becomes easier; see my screenshot.

                         

                        It still means a lot of work to implement this, not the least because a crosstab can and will have empty cells, and portals show related records which are not empty.This means you either have to insert empty records in your data table, which is not a good idea, or use a table with virtual records, where you can mix real records and those which only serve formatting purposes.

                         

                        As Michael said, you need to balance cost against benefit. If you're interested in a solution like the one illustrated, you can contact me via PM.

                        1 of 1 people found this helpful
                        • 9. Re: Crosstab Item allocation for POS
                          usbc

                          I can't help but chip in, again, on this topic.

                          Horizontal portals would be such a huge help for a lot of our work and chill the need for customers to go outside native FileMaker for these chores.

                          If you agree, ping FMI.

                           

                          Chuck

                          • 10. Re: Crosstab Item allocation for POS
                            erolst

                            Why stop at horizontal portals?

                             

                            What I really wish for (and, strangely enough, never have heard from someone else) is a portal grid - enter desired number of rows and columns (actually, horizontal and vertical cells) and specify if Across first or Down first (like when printing multiple columns). You can simulate it with the existing vertical-only portals, but maintaining it is really a drag.

                            • 11. Re: Crosstab Item allocation for POS
                              liza

                              Hi Oliver and Michael,

                               

                              I do understand I'm probably asking for something not native to filemaker. But this seems like something that will be a big help to our users if I'm able to incorporate it in our system. I was hoping even if I can't get the exact same format as using a spreadsheet, a workaround could be possible.

                               

                              Oliver I'm interested in the screenshot you sent, it looks like something I can work with. The style, sizes and the stores are all there in 1 screen, which seems good enough for our purpose. Will send you a pm.

                               

                              Thanks a lot,

                              Liza

                              • 12. Re: Crosstab Item allocation for POS
                                MarcDolley

                                Further to Michael's comment that FileMaker is not a spreadsheet, you need to stop thinking of what your doing as a simple grid of rows and columns. FileMaker is so much more than that. Unfortunately, too many FileMaker users migrate from Excel and try to make FileMaker act like it. It doesn't and hopefully never will (although I agree that horizontal portals are long overdue).

                                 

                                Can I suggest you stop trying to adapt your old Excel model into FileMaker and start looking at the entire process as if your Excel solution had never existed. You'll almost certainly end up with a much more powerful and flexible solution.

                                 

                                Regards

                                Marc

                                • 13. Re: Crosstab Item allocation for POS
                                  comment

                                  liza wrote:

                                   

                                  I was hoping even if I can't get the exact same format as using a spreadsheet, a workaround could be possible.

                                   

                                  A workaround is definitely possible - it's just a lot of work. For starters, have a look at the files posted here:

                                  http://fmforums.com/forum/topic/71836-getting-more-out-of-filtered-portals-in-version-11/

                                   

                                  and here:

                                  http://fmforums.com/forum/topic/73368-which-relationship-is-faster/#entry347135

                                  1 of 1 people found this helpful
                                  • 14. Re: Crosstab Item allocation for POS
                                    liza

                                    Hi Marc,

                                     

                                    I definitely agree that I'll be having a much better solution with Filemaker than the old excel model I'm using, even if I'm not able to incorporate this spreadsheet. But if I'm able to get some ideas for workarounds here, that would already be a big help.

                                     

                                    Regards,

                                    Liza

                                    1 2 Previous Next