11 Replies Latest reply on Sep 11, 2013 11:32 AM by pinchvalve

    Quote Builder Question, If-Then Selection



      Quote Builder Question, If-Then Selection


           Before I do too much work, I am wondering if the basic concept of what I want to do is possible for a beginner to do in Filemaker.  I haven't found anything in all of my searching yet.  

           I want my users to create a quote with FileMaker, but I have to go beyond the basic line item list.  The way our product is built, you choose a part number, and based on that, you may have to enter the weight, dimensions, color, or other parameters. These parameters differ by product.  Then you have to add options to configure the product.  These can be from a drop-down list, but the list of what is available differs by product.  

           I don't even know what to search for to try to find solutions.  Yes, I need more training on Filemaker and I am open to turning it over to a developer, I just want to know if it is even possible.  

        • 1. Re: Quote Builder Question, If-Then Selection

               I found this: 


          Dynamically including/excluding fields--and their associated field labels etc is not for the faint at heart. It can be done if your basic reports all have a similar structure, but it aint a trivial excersize and usually requires a lot of calculation fields that return the contents of a data field if that field is selected for the report and empty if it is not. Then you add another one for the field label set up the same way.

          And often you need to set up a lot of sliding fields to eliminate unused space and that brings in it's own set of issues.

               Sounds like it is possible, but beyond me.  Any other input would be great.  I am thinking ov even linking a "Item 1" field to different description field, but not sure how generic I can make things.  

          • 2. Re: Quote Builder Question, If-Then Selection

                 It sounds like you are actually dealing with multiple issues and I suggest that you break this down and tackle them one at a time.

            1.           On the one hand, you need to be able to compute a cost based on multiple field inputs plus looked up data based on the type of material specified. That is a potentially very complex issue with multiple possible methods available for solving it.
            3.           But you also mention the fact that the lists of possible values in one field will be controlled by a value entered or selected in another. That's something we call a conditional value list and is a much more straight forward piece of the entire puzzle.
            5.           And there could be much more to that that I've missed just from that "first read" of your initial post.

                 Let us know which or both you are dealing with and then please provide as detailed an example as possible of the first issue. Show us some of the inputs you might need to specify to calculate the cost and try to give us some idea as to the potential variation that may occur from one material to the next in producing your quote.

                 Also let us know the structure of your tables or relationships so that we can see if that structure will work with what we might choose to suggest and whether or not a change in that structure may be needed in order to produce the results that you want.

                 With regards to 1. above, I can outline three general approaches to handling that issue, but in each, the devil is in the details and thus I need to know a lot more before I can be specific about any of these:

                 1) Multiple Match fields populated from drop downs match to a single record in a look up table where a unit cost can be looked up (copied) to your record. Then a calculation takes a quantity and multiplies it by this unit price to compute the item price. Example, you specify a piece of Lumber as "Doug FIr", 2" x 4" x 6' and the system returns a cost in board feet or linear feet. Your calculation then uses that value and the total number of 6ft douglas fir 2x4's to compute a cost.

                 2) A unit price is looked up based on the type of material and then a formula is used that combines the different inputs and the quantity to compute the cost. While not realistic for a lumber yard, our 2x4 example used with this method is one where specifying Doug Fir looks up the cost of one cubic inch of material and then the specified L x W x H compute the cost.

                 3) In some complex cases, what you look up from the look up table is not just a unit cost but the very formula required to compute that cost could also be looked up from the table. Thus, you might look up a formula for the area of a circle for one material, the volumne of a cylinder for another while others might be based on cubic volume or linear feet.

                 And such look up tables do not need to match values exactly in all cases. Look up tables and the relationships that link to them can be set up such that values that fall into specific ranges of values all match to the same unit price.

                 I have a whole list of instructional links that I routinely share for those that need to learn more about conditional value lists so feel free to ask for those if you need them.


            • 3. Re: Quote Builder Question, If-Then Selection

                   Great advice, breaking it down.  I have decided to simplify what I want the system to do, and I think that I can get it to work within my skill set.  Then, when the boss is convinced, I can hire someone to take it to the next level.  Filemaker is so amazing, I want to really use it to it's full potential you know?  

                   So question #1.  

                   From a "Quote Main" layout, I click on New record and create a new quote with a unique Quote ID. (Get UUID)  This number will tie the quote to each line item in the relational database.  

                   To add a line item, I have a button tied to a script that copies the QuoteID from the main layout, then opens the "Line Item" layout and pastes the QuoteID there, commits the record, and returns to the "Quote Main" layout.  The line item is then added through a portal.       

                   This works fine, but requires that I have the ID field displayed on each layout.  Not terrible, but clunky.  

                   is there a better way to accomplish copying an ID field from one table to related table?  

              • 4. Re: Quote Builder Question, If-Then Selection

                     And a "conditional value list" seems to be what I am looking for.  Any more info would be appreciated.  That should get me to the next step.  

                • 5. Re: Quote Builder Question, If-Then Selection

                             Not terrible, but clunky.

                       And unnecessary.

                       The copy script steps should be avoided if at all possible and one is not needed here. Use Set Variable in place of copy to set a variable to the value of this field and set field in place of paste to copy the value in the variable into a field in the new line item record. Now you no longer need the field to be visible (and worse, editable) on each layout. Also, your users will be happier now that data they may have copied to the clipboard is not being mysteriously replaced by data copied to the clipboard by your script.

                       And you can do this without any script at all in many cases. If you put a portal to LineItems on your Quotes layout and enable the "allow creation of records via this relationship" option for LineItems in the relationship, you can create new records in the portal without needing any script at all.

                       And here are my links on Conditional Value Lists:

                       There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.

                       The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.

                       Forum Tutorial: Custom Value List?

                       Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                       Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                       Hierarchical Conditional Value lists: Conditional Value List Question

                       Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                  • 6. Re: Quote Builder Question, If-Then Selection

                         I'll read through all of the links (thanks a million!) but I am not 100% clear on the set variable.  When I create a new record, an ID is created automatically with Get(UUID).  That same number has to copy over to the related table so that they match.   Set Variable seems to be a way to create a number and paste it somewhere, but it won't match anything else.  

                    • 7. Re: Quote Builder Question, If-Then Selection

                           The standard script for creating a related record (lineItem) from the context of the parent record (Parent):

                           The script looks like this, but substitute your names for the place holders in the example script:

                           Set Variable [$ID ; value: LayoutTableOccurrence::PrimaryKey ]  ---> instead of copy
                           Go to Layout [ "PortalTableOccurrence" (PortalTableOccurrence) ]
                           New Record/Request
                           Set Field [PortalTableOccurrence::ForeighKey ; $ID ] ----> instead of paste
                           Go To Layout [original layout]

                           This script assumes that your relationship for the portal is: LayoutTableOccurrence::PrimaryKey = PortalTableOccurrence::ForeignKey.

                           Be careful about script triggers specified on either of the two layouts as the change in layouts can trip a number of script triggers and the script they perform pops up in the middle of the above script and this can interfere with your results.

                      • 8. Re: Quote Builder Question, If-Then Selection

                             Thanks for the feedback. I am going to play with this and see what it does.  If I understand this correctly however, the script will create a number (of my choosing) in the PrimaryKey field, then create the same number in the ForiegnKey field.  

                             It seems to me that  will not work for adding multiple line items.  Each time the script is run, the Primary Field will be overwritten and only the last line item from the ForiegnKey table will link back.  I need one number in the table with the PrimaryKey to match multiple lines in the table with eht ForiegnKey.

                             And what happens when I make a new record for the PrimaryKey table?  Won't the next record have the same ID as the last?  

                        • 9. Re: Quote Builder Question, If-Then Selection

                               The script does not change the primary field's value at all. It should be an auto-entered serial number or Get (UUID) and not something that the user can ever edit. You can run this script as many times as you need

                               And remember that with a portal, you can do this in the portal without using any script.

                          • 10. Re: Quote Builder Question, If-Then Selection

                                 I'm afraid that I just don't understand what this script is trying to do, nor can I get it to work.  Too inexperienced with scripting at this point.  

                                 However, getting rid of the script seems to have done the trick.  Without a button (Add Line Item) and a corresponding script, the portal is now working how I want.  If I click on the first field in the portal, and then tab to the next, the ID value from the Parent Table is copied automatically to the matching field in the child table.  So it seems that Filemaker does it automatically, wish I knew that a week ago! 

                                 I was struggling with the button because I saw it on another demo, and it also took the user to the next available record.  Perhaps I should just concentrate on that.    

                            • 11. Re: Quote Builder Question, If-Then Selection

                                   And a big thanks to PhilModJunk for your patience!  (I realize now that I have mastered riding a bicycle, and am asking how to fly the Space Shuttle.)  I am just not at a level to understand anything beyond the most basic functions of FileMaker.   I will go and get some formal training and master the basics before I try to complete this project!