3 Replies Latest reply on Aug 20, 2013 11:27 AM by philmodjunk

    Just a few questions to start with



      Just a few questions to start with


           I'm looking to migrate from bento so am in the process of trialing FMP.  I'm building a database with 2 main table, Customers (main Fields are Price, frequency of work, balance and next due date) and then Transactions (fields are Date, Price,Tips, Extra work, Payment amount Total and Balance. Total and Balance are calculation fields. Total is Price Tips and Extra work, Balance is Payment - Total).

           I'm building a customer form with a Portal to transactions.  One of the fields in the customer table is entitled Job Price and I wanted to insert a button that auto filled some of the next line in the portal such as date and Price possibly even having another button to auto fill if they have paid.  I've had some success but it only ever seems to fill out the first line of the Portal.

           I also want to add up the Balance Fields in the Portal and display the Answer in the Balance Field in the Customer form.  I thought that this would be straight forward but doesn't appear to be so.

           Lastly I wanted to create a button that adds the duration to the currant date to tell me when a customer is due again.  I was wondering if it is possible to make a button multifunction so that it performed 2 or 3 jobs at once.


           Thanks in advance for any help Simon.

        • 1. Re: Just a few questions to start with

               You haven't described any relationship or match  fields bewteen customers and transactions. That's the first thing you need--something like this:


               Customers::__pkCustomerID = Transactions::_fkCustomerID

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

               Presumably, you have this already though your names may be different from mine. (But please don't match records by the customer's name.)

               If you want a button to perform a script that modifies fields in a specific row in your portal, put the button for that inside the portal row. Then, the mouse click that performs the script also selects and puts the focus on the portal row that you want to modify.

               To get a total of a field in Transactions for a given customer, define a calculation field in Customers with this expression:

               Sum ( Transactions::Balance )

               Set Field [ Customers::DueDate ; Get ( CurrentDate ) + Customers::Duration ]

               will update DueDate to the next date due provided that your system clock is accurate and Duration is a number field that records the duration as the number of days.

               When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.


                    is possible to make a button multifunction so that it performed 2 or 3 jobs at once.

               It's possible, but when you click that button, how will the script know which function to perform? Clicking the button could pop up a custom dialog with up to 3 choices or you could use a pop up menu/drop down list of options with a script trigger in place of your button. The script performed by the script trigger can check the drop down/pop up field's value to determine which function was requested.

          • 2. Re: Just a few questions to start with

                 Thanks very much, I'm starting to make progress.

                 Is it possible to attach a saved find to a layout, so that a specific layout would only ever return the results of a find and not the whole table.

                 Also I've noticed a glitch with a button in a portal, I have it set to Insert a Calculated Result into a field, the calculation is the contents of a field in the parent table.  So the parent table is Customers and the Portal is Transactions.  What happens is when I click on the button in the portal the result goes into the target field, however when I move onto another field or another button in the portal the target field returns the value of 1.  I have to click the button twice to make the desired result stay.



            • 3. Re: Just a few questions to start with

                        Is it possible to attach a saved find to a layout,

                   Not really. FileMaker is not designed to support that approach. You can set up a script that performs a find when you first enter the layout. And you can use custom menus (requires FileMaker Advanced) to limit the user's ability to bring up other records on that layout.

                   Or you may want to use a portal to list those records so that you can use a combination of relationship and portal filter to control what records are listed in the portal.


                        Also I've noticed a glitch with a button in a portal,

                   I think you need to describe that in more detail. I know of no reason why it would do that, but all the insert steps are best avoided unless there is no alternative to use and in this case you can use set field instead of insert calculated result. (Insert steps require that the target field be physically present on the layout and if you don't specify a target field, the insert step modifies whatever field as the focus. (And clicking a button does not move the focus out of the field.)