7 Replies Latest reply on Jul 21, 2011 9:13 AM by philmodjunk

    Trouble with scripts



      Trouble with scripts


      Thanks in advance for any that take a crack at this question. I have a lawn care business and I'm trying to get set up with FMPro 11 Advanced. I have the database created with all my clients and their information, each with a different contracted price and payment due date. Here are my questions: (1) I'd like to have each individual record set up to automatically roll over from month to month, but as I said, each needs to roll over at different times. I have it set up now (with Conditional Formatting) that if a customer does not pay their bill (via a "Paid" Check Box set) by the contracted due date, the payment due date and a "Missed Payments" field highlights in red. The "Missed Payments" field also shows a "1" if payment is not received by the due date, but only a "1", no matter if 2 or three months behind. I'd like for it to actually calculate how many months the customer is behind instead of showing a "1" each time. I'd also like the amount due to multiply each time a payment is missed. For example, if a customer's contracted price is $125/mo and a payment is missed, I'd like for the amount due to display $250. (2) If a customer has missed a payment, or is a few days late, but soon pays their bill, I'd like to be able to click the "Paid" Check Box set and the "Missed Payments" counter to go back to "0" along with the payment due date rolling over to the next month. For example, If the "Missed Payments" counter is showing a "1" and the "Payment Due Date" field  is showing 7/14/2011 and highlighted in red, and I then click the "Paid" Check Box set, I'd like the two fields not to be displayed in red anymore, the "Missed Payments" counter to go back "0" and the "Payment Due Date" to then display 8/14/2011 (Move forward by 1 month). (3) I have a table with set-up with all the services I offer each with their own Service Item Number. On my main table, I'd like to be able to set up a Check List that doubles a record of when and exactly what the work was done. I've added a button in the layout to "Begin Checklist", but so far all it does is insert a Time Stamp when I click it. I'd like to be able to create the Time Stamp, then enter the Service Item Number, which automatically displays the service itself, instead of the number. And this one is purely cosmetic, but I'd only like there to be one Service Item Number to be visible to begin with, after I enter the first Service Item Number, another field below it becomes visible, and so on. I'd like all this to back up to my database somehow, which I'm sure FMPro will take care of that automatically. I apologize for the length here, but I'm a new comer to FMPro and I know absolutely nothing about scripting. Again, any help anyone out there can give me is greatly appreciated. Thanks again.


        • 1. Re: Trouble with scripts

          Before you start scripting, you need to make sure that you have tables and relationships setup that properly support your business.

          What tables have you defined and how have you related them to each other?

          • 2. Re: Trouble with scripts

            Haven't gotten into the relationships too much. FMPro is a lot to handle all at once, but so far I have two tables, my main table, which houses the following:

            -Last name

            -First name





            -alternate phone


            -contract price

            -payment due date


            -payments missed

            -paid (check box set)

            -service item number (mainly troubleshooting with this one)

            and a photo container. The only other table I have is called "Services", which lists my services in one field and the service item number in another field. I have the relationship between the two of them set so far with an equal relationship between the "Services" field from the "Services" table to "Contract Price" and "Service item number" in my main table. I realize these relationships probably don't make any sense, but I've been trying to learn by doing instead of watching instructional videos on YouTube.

            • 3. Re: Trouble with scripts

              You'll need quite a few more tables and relationships to do what you want. And you'll need to invest in some time studying up on FileMaker and databases. There are training tutorials and books available you can use for this purpose.

              First add a customer ID field to your customers table (The first table you have here.) Define it as an auto-entered serial number. If you have existing records already entered, go to customer layout where you've placed your customer ID field, select show all records, put the cursor in the field and use Replace Field Contents from the Records menu to assign a serial number to all your existing customer records. Click the option that updates your auto-enter settings so that the next new record will receive the next serial number in the series. You'll use this field a lot to link customer records to other tables in your database.

              Then you'll need the following tables:

              Invoices (one record bills one customer for that month's service, among other things, this will simplify handling unpaid bills as the sum of all unpaid invoices for a customer will tell you how much they owe you.)

              LineItems (A portal to this table in Invoices will enable you to list all the different services performed for a given month's bill. Note that this enables you to bill a customer differently each month should your rates change or the services performed change for that month.)

              ServicesAvailable (You seem to have this table. In most invoicing examples, this table is called Products.)

              Payments. one record in this table represents a single payment received from a given customer. It can be a payment for several invoices or even part of one invoice.

              Payment_Invoice A special "join" table that enables you to link multiple payments to one invoice and also multiple invoices to one payment.

              That's quite a lot isn't it? But it's what you need to manage your business here. You might check out the invoicing starter solution for ideas in how to set some of this up. You can also examine this much simpler demo file created by Comment for how to set up the basic invoicing. In both cases, think of the products table as your "services offered" table.


              • 4. Re: Trouble with scripts

                I'll probably be coming back from time to time to ask some more questions while I'm working on this. i have a smaller question this time; I was wondering how I could create a button to navigate me to another table. From my "client information" table, I want to make a button that takes me to a different table where I have a contract written up. I have the image I want to be used as the button already in the layout, I just can't figure out the "Button Setup" part of it. I want to click on the button and it take me to the contract I have written up in preview mode (in a new window would be fine too)...and possibly have a "Print Setup" dialogue box pop up since I've named the button "print contract". Again, thanks for all previous answers and thanks in advance for this one as well.

                • 5. Re: Trouble with scripts

                  Every table should have at least one layout for it in your database. FileMaker automatically creates the first one and you can always add more layouts to the same table if you need them. To "go to another table", you go to a layout based on that table. Thus, the go to layout step will do the job, but you may need additional steps to pull up the specific record or group of records that you need when you go to that table. Thus, if you are on a client record for "John Smith", a script performed by your button can take you from John Smith's client information record to the Contracts layout and also pull up the specific contract record that belongs to John Smith.

                  • 6. Re: Trouble with scripts

                    Since my last post, I've gotten it to go to the related records and I have in the script for it to switch to Preview Mode then open the Print dialogue box, but when it enters preview mode, the record displayed is the first one of the list and record count in top left corner shows a question mark. I can manually scroll through the records via the next record and previous record buttons and once I scroll through all of them, the record count goes back to normal. But when I run the script again, same thing.

                    Also, I realize this is a tall order to ask, but I've got no clue how to set up a "Line Item" table. As usual, any and all help is greatly appreciated and again, thanks for the previous responses.

                    • 7. Re: Trouble with scripts

                      In preview mode, the "book control" and associated numbers show page numbers rather than record numbers. Until you drag the slider to the last page of the previewed report, FileMaker does not compute the number of pages needed to preview, print or PDF the found set of your current layout. If you want to see the numbers first off and you are using a script to enter preview mode, add

                      Go To Record/Request/Page [Last]
                      Go To Record/Request/Page [First]
                      Pause/Resume Indefinitely

                      Immediately after entering preview mode (Don't select pause for the Preview mode step).

                      Here's a simple invoice--<LineItems>----Products demo file created by a person who goes by the name of Comment:  http://fmforums.com/forum/showpost.php?post/309136/