11 Replies Latest reply on Dec 13, 2010 12:19 PM by philmodjunk

    Auto-populate new Estimate with standard Items

    FranzLang

      Title

      Auto-populate new Estimate with standard Items

      Post

      Hello,

      I just built myself a database to produce Estimates more rapidly than before.

      Each Estimate can contain multiple Items, so I added a Join Table to link the two tables (which I assume is right).

      I am quite happy with it so far and it works quite well :-)

      There's only one thing left:

      Each new Estimate I create usually contains a handful of standard Items, i.e. Items that I use repeatedly in almost all my estimates. It is quite tedious to add all these Items manually each time I create a new Estimate.

      Now is there a way to fill a new Estimate with those standard Items at the click of a button?

      When I click the button, I want the Estimate to be populated with references to Items #1, #2, #3, #4, and #5. 

      I figure that this should be done with a script but I lack the experience to do this by myself. I have already tried a couple of things, but to no avail.

      Can anybody help? What would a script have to look like?

      Thanks a lot...

        • 1. Re: Auto-populate new Estimate with standard Items
          philmodjunk

          Here where I work, we process hundreds of customers a day that bring in used beverage containers for recycling. Since 99% of our customers bring in some combination of 4 typical types of containers, our invoices are "pre loaded" with these four items to speed people through the line. Sound familiar?

          I'm assuming this structure to your system:

          Estimates----<LineItems 

          With the two tables linked by an EstimateID serial number defined in Estimates.

          A script to create a new estimate might look like this:

          New Record/Request
          Set Variable [$EstimateID ; Value: Estimates::EstimateID}
          Freeze Window
          Go To Layout [LineItems]
          New Record/Request
          Set Field [LineItems::EstimateID ; $EstimateID ]
          Set Field [LineItems::ItemID ; 1 ] //Specify whatever value or values you want pre loaded in your LineItems portal

          // repeat for each line item record you want "pre loaded" in your estimate
          Go to Layout [original layout]

          Note: This script requires modification each time you determine that you need a different set of LineItems created with each new estimate. We didn't want to do that with my employer's set up, so we use a more sophisticated script to step through a set of records from a related table to create each new line item record. That enables us to change these preset entries simply by editing this table.

          • 2. Re: Auto-populate new Estimate with standard Items
            FranzLang

            Hey Phil,

            Thanks for your help!

            What you describe sounds indeed very familiar to me. It's the exact same thing here. About 80% of my clients want an Estimate with the same 5 LineItems.

            The script you posted looks promising, I will try to implement that in the next couple of hours.

            Are you sure that

            Estimates----<LineItems 

            is the best structure? Since each LineItem can have several Estimates (and vice versa) I figured that I need a Join Table as well:

            Estimates----<LineItem_per_Estimate>----LiteItems

            No?

            Feel free to correct me here, I'm still new to FileMaker.

            Thanks...

            • 3. Re: Auto-populate new Estimate with standard Items
              philmodjunk

              It's just a difference in terminology.

              I should have posted:

              Estimates>----<LineItems>----Items

              LineItems is a commonly used name for this type of join table.

              (And you can add a field to Items that can be used to designate the items you want auto-loaded into your portal each time if you modify my script to find those records and loop through them to generate the pre-loaded line item records.)

              • 4. Re: Auto-populate new Estimate with standard Items
                FranzLang

                OK, i see what you meant.

                I've been trying to implement your script but I failed miserably. To be honest I don't even know how to hard code the script. I have only buttons and "options" in my version of FileMaker.

                I think we can safely ignore the first lines of your script as I usually use FileMaker's "New Record" button to create a new Estimate. All I need is a button "Populate Estimate with Standard Items".

                I have added a new field "Standard_Item" to my Item table as you suggested. It can be checked or unchecked (I am using a checkbox here).

                Can you give me another hint on how to implement this loop thing? I have done some loops in PHP but I can't seem to get it done in FileMaker. Thanks a lot.

                • 5. Re: Auto-populate new Estimate with standard Items
                  philmodjunk

                  You need to use Manage | Scripts to write this script. In manage scripts, you'll find a button labled "new" that you can click to start a new script. After you write the script, you can add a button to your layout and set it up to perform this script.

                  I'd get the basic script working first and then upgrade it with a loop once it is working.

                  Note: we put a number in our field. This number not only designates it as a preloaded item, but specifies the order in the portal where we want it to appear.

                  • 6. Re: Auto-populate new Estimate with standard Items
                    FranzLang

                    Hey Phil,

                    your "basic script" works like a charm, so thanks for that.

                    Using a loop seems more elegant though, so I am currently trying to do that. This is what I've got so far:

                    ----------------

                    New Record/Request

                    Set variable [ $EstimateID; Value:Estimates::EstimateID ] 

                    Freeze Window

                    Go To Layout [ “LineItems” ] 

                    Set variable [ $int; value:0 ] 

                    Loop (Start)

                         New Record/Request

                         Set Field [ LineItems::EstimateID; $EstimateID ]

                         Set Field [ LineItems::ItemID; $int + 1 ]

                         End Loop If [ $int = 5 ] 

                    Loop (End)

                    Go To Layout [ “Estimates"]

                    ----------------

                    This works only partially though. For some reason the script gets into an endless loop and produces thousands of LineItems with ItemID 1. I have to hit Escape in order to stop it.

                    What am I missing here?

                    • 7. Re: Auto-populate new Estimate with standard Items
                      philmodjunk

                      make this small change and let me know if you can't figure out why the change is necessary:

                        New Record/Request
                        Set Variable [$int ; Value: $int + 1 ]
                        Set Field [ LineItems::EstimateID; $EstimateID ] 
                        Set Field [ LineItems::ItemID; $int ]
                      End Loop If [ $int = 5 ] 

                      • 8. Re: Auto-populate new Estimate with standard Items
                        FranzLang

                        Wow, that's it. Thanks a lot. Didn't see the wood for all the trees...

                        One last question: I normally use FileMaker's "New Record" button to create a new record. Is it possible to tie this new script to that button? Or will I have to create a custom separate button for this purpose?

                        • 9. Re: Auto-populate new Estimate with standard Items
                          philmodjunk

                          If you look at my original script, you'll see that the first thing it did was create the new estimate record. If you wan to select New/Record from the Records menu or press the keyboard shortcut for new record, this can be done but only with FileMaker advanced.

                          In FileMaker advanced, you can define a custom menu that perform this script in place of the standard new record action. Then, on any layout where you specify the custom menu in place of the default menu, New Record will perform your script for you.

                          • 10. Re: Auto-populate new Estimate with standard Items
                            FranzLang

                            OK, thanks, Phil. I am lucky enough to own a copy of FileMaker Advanced but it's probably easier if I stick with my own custom made button.

                            • 11. Re: Auto-populate new Estimate with standard Items
                              philmodjunk

                              Custom Menus are pretty easy to set up.