9 Replies Latest reply on Sep 20, 2011 7:39 AM by philmodjunk

    Easiest way to set up an invoice



      Easiest way to set up an invoice


      I'm using the FM solution "Invoices" for my database, but I'd like to improve it a little. 

      In the Invoice form, it uses a portal to show the products, although the way to select the products is by selecting the product ID #. That works for a small database with few products, but when there are many (thousands...) that can take a while...

      I was thinking about changing it so that we can type in the name (or part of the name) in the "Name" field and then be able to select the one we want. And also create a button on the "Products" form to add the product to the current invoice without changing the layout.

      I need a little help on how to set that up...

        • 1. Re: Easiest way to set up an invoice

          Take a look at this demo file: http://www.4shared.com/file/plr_jbkk/EnhancedValueSelection.html

          It demonstrates several scirpt enhanced methods of working with value lists of large numbers of items. The demonstarted methods all work with a line items portal so you can see how this can be set up.

          Feel free to ask questions if some part of the implementation of one of these methods does not work for you.

          A simpler to implement option is to use a conditional value list to reduce the number of items listed in the portal's drop down list to just the items that are a member of a given category. You select the category from a value list in field 1 and then the drop down of items purchased is limited to just the items from that category.

          • 2. Re: Easiest way to set up an invoice

            Awesome thanks! 

            So I did the first example, and it works... kind of. I think the problem comes from how the portal was set up on the solution in the beginning. 

            I can successfully search by name and select the product I want in the drop down, but when I click on it, it creates a blank line, it doesn't pull the information from the line item table. 

            The only way it works is still from selecting the product ID #. I think it's because it was the only field set up as modifiable (apart from quantity, price, etc.).

            What do I need to change so that it works either by selecting the ID# or the name?

            • 3. Re: Easiest way to set up an invoice

              Take a look at the script and the script parameters specified in the script trigger on the drop down. (If that's what you mean by the "first example".)

              I'd guess that a needed value is not being passed in the list of parameter values needed by that script.

              • 4. Re: Easiest way to set up an invoice

                Sorry I think I didn't express myself correctly, I meant the first table (on the left beside the text), not the search bar. 

                Or maybe I misunderstood you, I don't see a script used for the 2-in-1 field.

                • 5. Re: Easiest way to set up an invoice

                  You have a drop down list outside the portal that auto-completes on the description field.

                  You have the same drop down list inside the portal.

                  Then you have two "search" portals at the bottom of the screen that use different pattern matching methods that control which items are listed in their respective portals.

                  All of them use script triggers.

                  In the case of the drop down lists, the "Script trigger badge" is hidden behind the description field that has been placed in front of the actual drop down field. Enter layout mode in the demo file, right click the drop down arrow and select script triggers to see what trigger was specified for that drop down and what parameter is being passed to the script by the script trigger.

                  • 6. Re: Easiest way to set up an invoice

                    Wow I have a hard time with scripts, they're very new to me... Why do you need the gSearchField3 exactly? 

                    Being able to add a product just typing in the name in the name field (or Description in your case) is enough for me, but I see that you used it in the script.

                    • 7. Re: Easiest way to set up an invoice

                      I was setting up each of these techniques to handle two key issues:

                      1) partial matching text entered by the user to text in the description fields

                      2) correctly handling cases where the text specified by the user matches to more than one record in the products table.

                      In the drop down lists, partial matching is handled first by enabling auto-complete on the drop down list. This option produces a nice 'self limiting' list of names. If I tried to set this up on the typical LineItems::ProductID field, FileMaker won't allow auto-complete. If I use a relationship based on the product description instead of the idea, I am no longer using the ID's and lay myself open for issues due to using names instead of unique IDs. (Think what error could happen if you enter "Apple" if you had a product named "Apple" and another named "Applesauce" or when a product description has to be changed...)

                      Partial matching is further handled by the script as it performs a find for all records where the description starts with the same text. If you have just one matching record, it pops up in your portal, if you have several it lists them with complete descriptions so that the user can pick one. This also handles cases where the user is not fully sure what the description is and choose to enter a key word instead.

                      • 8. Re: Easiest way to set up an invoice

                        Thanks for clarifying this for me. Does that mean I have to have the search field in my invoice layout?

                        I think I managed to modify the script to my database, although there is one part I couldn't figure out. How do you name an object ("LineItemPortal")? 

                        Thank you.

                        • 9. Re: Easiest way to set up an invoice

                          To name an object, you enter layout mode, select it and then type the name into the name box in the top of the inspector's position tab.

                          To use the methods in this demo you need a search field. Please note that in the drop down list versions, this field is the one formatted as a drop down list and hidden behind the related field from line items so that it provides the user with the same look and feel as if they are simply using an auto-complete enabled, text based drop down list.