1 2 3 Previous Next 41 Replies Latest reply on Nov 5, 2010 8:55 AM by philmodjunk

    Invoice Template problem



      Invoice Template problem


      Hi, I am totally new to Filemaker as of a week ago so please excuse my ignorance. I Am setting up a database for a friend's company that still takes down new orders on scraps of paper!!

      Anyway the Invoice template looks to have enough functionality for what we need + a few changes I have already made. Now despite my trying, I cannot fathom this out.-- When creating a new invoice, clicking into either  of the Ship To or Bill To object boxes, the customer name list is displayed via the pop up menu. I just want to search this list as I can see once it runs into the hundreds it would take some time to find the choosen customer. The list is populated via a value list specifying CustomerID & Full Name from the Customers table. Once the customer is selected from the list all the address details are automatically loaded.

      Currently the only way I can see to search the list is to scroll up/down with the arrow keys.

      Tried conditional formatting in the object box using date but that doesn't work.

      Ideally I would like the current customer only inputted into the Invoice form without the need for the list, perhaps by a script box on the Customer Record layout.

      Any help with this would be greatly appreciated, thankyou.

        • 1. Re: Invoice Template problem

          Hi Colin Fielder,

          check "Auto-complete using value list" - this option will reduce the list by typing the first letters..

          • 2. Re: Invoice Template problem

            Hi Josef,

            And thanks for the reply

            Because the control style is pop up menu, auto complete using value list is not an option & only becomes ones when the control style is changed to drop-down list, but even then it is greyed out.

            How hard can it be to place a button on the Customers page saying "New Order" that opens up the Invoice form & inserts those customers details alone into the form? Well I'm finding it quite hard, managed to create a simple script to go to the Invoice form, create a new record & open up the customers pop up menu list, but it's the whole list & I cannot seem to filter it down.

            All new invoices/orders originate from the Customer form, do this would be more sense.

            Thanks in aticipation, Col

            • 3. Re: Invoice Template problem

              Also, Auto-Complete does not work when the column 1 value is a number such as Customer ID.

              It's possible to add a button that switches to a layout for customers, allows you to find an existing customer record and then a button on that layout can take the customer ID number from that record and put it into the current Invoice Record.

              Note: I haven't checked layout and field names in the Starter solution for this, make sure you use the actual table, layout and field names from the file when creating these scripts:

              Button on invoice runs this script:
              Go To Layout [customer layout//use the name of a layout in your file]
              Enter Find Mode[]

              That's it, you see the system switch to the other layout and enter find mode. You then enter a name or partial name and press Enter (on the number keypad) or click the Perform Find button in the status area. You may find one customer, no customers or several customers with similar names, depending on the search information that you enter.

              Button on the customer layout runs this script:
              Go To Layout [Invoices]
              Set FIeld [ Invoices::customerID ; Get ( ScriptParameter ) ]

              When you add this button to your layout, you specify the name of the custom table's customer ID number as the parameter passed to the script.

              If you really want an auto-complete based value list of names for this, it can be done, but requires quite a bit more scripting and layout design. Here's a tutorial on how to do this:  Tutorial: How to use an auto-complete drop down list when selecting records related by ID number

              • 4. Re: Invoice Template problem

                Cheers Phil for the in depth explanation.

                Yes I like the button on the Customers record layout running a script to fill in the current customer invoice details.

                However I can only get thus far...

                Go To Layout [Invoices]

                Set Field [ Invoices::Ship To customerID   (the invoice field for the customer name).

                But I can't see the Get ( ScriptParameter ) ] to add to it??  Is this somewhere in the Calculated Result options?



                • 5. Re: Invoice Template problem
                  1. Enter layout mode and double-click the button to bring up button setup...
                  2. With "Perform Script" highlighted, click the Specify... button.
                  3. In this new dialog, you'll see a box at the bottom labeled "optional script parameter". Click the Edit button next to it.
                  4. Select the name of your CustomerID field from your customer info table by finding and double clicking it's name in the field list.


                  Now the value of this field will be passed as a script parameter to the script so that Get ( ScriptParameter ) will return this value.

                  • 6. Re: Invoice Template problem

                    Hi Phil,

                    Thanks for that it all works a treat. Everything now goes through the customer layout- New orders, find orders etc.

                    If you see this post again I now have a couple of other questions...

                    1. On the invoice layout I have a discount edit box. The date formatting is set to Percentage. When I place 20% in the box it enters 2000%. to get 20% I need to enter .20. Fiddled with all the settings, but no luck. Any ideas?

                    2. On the products layout each item has a notes area that I would like to use & copy along with the item when it is selected on the Invoice layout. The Notes box on the Invoice layout is displaying data from Related Products::Notes. Now this works fine for the first item, but a new item overwrites the first.

                    3. The Invoice layout has a Portal setup to Line items. Obviously as the line items increase this list will get quite long. What I would like is to click in the box & before the item list is displayed a catagory list is first shown from which line items can be selected from each catagory to which they are listed. Make sense?

                    As ever any help with this is much appreciated.






                    • 7. Re: Invoice Template problem

                      1) Here's a trick I sometimes use with number fields formatted to display as percent:

                      1. Find the field's definition in Manage | Database | Fields and double click it.
                      2. Select the Auto-enter tab and use the calculated value option to enter this calculation: If ( Self > 1 ; Self / 100 ; Self ).
                      3. Clear the "do not replace existing value..." option and dismiss the dialogs by clicking OK until you're back on your layout. With this set up, if you enter 20 or 0.20 you get 20% entered into your field.


                      2) On that layout, you have an Invoice Record and a portal to the LineItems table. Into which table do you want to copy the note? If you copy it into the lineItems table, you can have a note for each item in the portal--though you'd need to redesign your portal and invoice print layout (if you want the note printed) to accomodate this.

                      3) What you are describing here is called a conditional value list where the value selected in one field controls what subset of values will appear in another field's value list. Here's a knowledge base article on this concept. If you still can't get this to work after reading this article, feel free to post back here with your questions on it.


                      • 8. Re: Invoice Template problem

                        Thanks Phil,

                        The discount % is now working fine

                        I just need the note details for the appropriate ordered product to find their way onto the invoice print layout so it does not need to go near the portal on the Invoice layout. Thought adding them first to the Invoice Note field would be easiest. Is there a way for each ordered item the nore details can be directly input to the print invoice?

                        I have looked at the link but it is hard to relate to my tables. Each product also has a catagory field & its this that should be the first drop down list in the line items portal.

                        Would I need to first create a new value list based on the catagory field? The Line Items & Related Products tables are related via Product ID

                        Many thanks


                        • 9. Re: Invoice Template problem

                          It would be simpler to set this up to lookup the note field into the lineitems table. You can then add this field to the body of your invoice print layout if you can make room for it. Then all such notes will be printed next to the item that looked it up.

                          Appending the note data to a combined text field in invoices like you describe would require scripting and could get a bit complicated to set up.

                          • 10. Re: Invoice Template problem

                            Hi Phil,

                            I just split up the notes box ito multiple lines & tied each line to a value list with the product notes in. Where required the drop down notes can be added to the invoice.

                            I need to add a simple chart for marketing options. Basically there would be 5 or 6 different options along H axis of a bar chart & I would like the vertical be display over time the proportion of each option, perhaps over the last 3 or 6 months. Any help would be welcome to save me trawling through the help files.


                            Many thanks,


                            • 11. Re: Invoice Template problem



                              I have added a pie chart which works fine. However on the found set from Date Created, I would like the option of the last 3 months records but don't know what calculation needs to go after the // current date character


                              Many thanks,



                              • 12. Re: Invoice Template problem

                                Here's a calculation for computing a date 3 months in the past:

                                Let ( D = Get ( CurrentDate ) ; Date ( day ( D ) ; Month ( D ) - 3 ; Year ( D ) ) )

                                If the current date is very close to the end of the month, this may produce a date that looks wrong in some cases as not all months have the same number of days. You may want to compute a date 3 months earlier than the 1st of the current month.

                                Let ( D = Get ( CurrentDate ) ; Date ( 1 ; Month ( D ) - 3 ; Year ( D ) ) )

                                • 13. Re: Invoice Template problem

                                  Thanks Phil,

                                  With this you don't need the // before the calculation?

                                  Also could I do somethign similar for 4, 6, 12 weeks?



                                  • 14. Re: Invoice Template problem

                                    Get ( currentDate ) returns the current date which is what // does when placed on a layout. You can't use // in a calculation, only as layout text placed directly on the layout.

                                    To get dates a specified number of weeks in the past, you subtract the equivalent number of days from the current date.

                                    Get ( CurrentDate ) - 28, for example computes a date 4 weeks in the past.

                                    1 2 3 Previous Next