5 Replies Latest reply on Jan 22, 2014 6:46 AM by mariusjostfm

    Need direction with design set up


      Hi, I need help with how best to design a database that would be a combination of price lookup, sort of inventory, invoicing and PO system.


      My friend and I recently opened a home remodeling retail store. We realized that Quickbooks is a terrible as a sales tool / quoting software, so I started thinking that FMP would be a much better solution; however, it's been so long that I've used it consistently, I no longer know what would be the best method to choice. I don't know if I should be setting uo lookup tables, conditional formatting, popup menus or what. We carry no inventory, so not having to track for it helps, but because everything is special order, there are so many products available it's impossible to know all their part numbers or prices.


      Here's what I envision:


      A customer wants to know how much a particular carpet costs. I open up the file and click on "Price Lookup." I'm given radio boxes with all the product categories we sell. I click on "Carpet" and Filemaker moves me to the next field and basically says "These are the four carpet companies we carry: A, B, C, D. I click on C and Filemaker basically says, "These are the Product Names available." I click on "Aliso" and Filemaker says ""These are the 12 colors available. I enter quantity and FMP gives me unit price and extended price.


      If the customer likes the price, I either press something like "Add to Quote," "Add to Invoice," or "Create PO," then start the process again by choosing "Price Lookp." This time, however, I choose Tile, which has more options available: Manufacturer, Product Name, Colors, and Sizes. Finally, other products can have invoices created by the manufacturers oridering system, so we'd need less choices: Category; Manufacturer, and some reference to the invoice we already created in that ordering syste,.


      So this has a bit of If-Then thinking to it, but because the choices change per input given, I don't know if it should be Relationships, Portals, Defined Lists, Conditional Formatting, Tables, Drop Down boxes, or whatever.


      Any help is greatly appreciated, as well as suggestions for books or sites for learning,


      A hearty thanks in advance!



        • 1. Re: Need direction with design set up

          Sounds like a job for MagicValueList


          there is a great blog post with links on Kevin Franks site which shows the principles



          This can be extended several levels deep, and with ExecuteSQL now in FPM12 and 13 it is really simple to grab the related records in the next table to make the next list appear once you have made a choice.

          • 2. Re: Need direction with design set up

            Thanks for pointing me in that direction. I have to be honest and say this is waaaay over my head, but I'll try to read some of the hacks listed to see if any of them spur new thinking for me.

            • 3. Re: Need direction with design set up

              I will admit that this is non trivial, but once you have the hang of it there are endless possibilities


              Attached a couple of screenshots from a project with 4 levels deep


              Pick a section from the top, pick a product to reveal its options (some of which are required so have the purple lable), then each option has a list of values....

              Easy, no. Happpy client, yes.

              • 4. Re: Need direction with design set up

                I will try to work on that, but since I'm both client and developer, I can only put so much time into it initially.


                I had an idea that isn't anywhere near as elegant, but I'm curious of your opinion. Let's compare the difference between carpet and tile and how one would go down the options.


                Tile > Manufacturers > Styles > Colors > Sizes Available > MFG Part Number


                Carpet > Manufacturers > Styles > Colors > "Nothing" > MFG Part Number


                What if I didn't create conditional formatting -- bear in mind I might be using the wrong terminology -- but I have the same five boxes pop up? For some of them, there would be something in the Sizes Available box, while others would have a character to express it's empty (or maybe no character).


                I assume the products would be in a separate file/table. Would those be accessed through lookup or portal?


                Thanks for all your help. I used to use Filemaker Pro, but it's like I only had a middle school education with it to begin with!

                • 5. Re: Need direction with design set up

                  Hi Jason,


                  If you want to build on something instead of doing it from scratch:  I am sure it is not allowed to sell here - and this is not my intention. So, let me start by saying that what I ll post now has not been for sale because I am still looking for a way to package this as a runtime solution. However, what you ask for has been developped by many I guess, and I would really recommend that you look around for a good solution which is already there.


                  I just post the link to my website where you will see that what you need has been done. I have done this for a furniture rental company (furniture leasing) and there is not even a price tag for my software. What I would instead offer you is : Check the description online and if you like I can send you the open Filemaker file. Then you have the option to customize. The strenght of the solution is a visual product finder with filtering and a very fast proposal process with a beatiful PDF featuring images of what you want to sell that can be mailed to the client. It is still an fp7 - but switching this to 12 or 13 should be no problem. 



                  (The download and purchase link does not work, also there is no video. Check the screenshots. )


                  I hope this will help.