7 Replies Latest reply on Aug 7, 2009 8:17 AM by philmodjunk

    Filemaker newbie needs help quick!!



      Filemaker newbie needs help quick!!


      Sorry to ask what is probably a dumb question but I need to make a database in FMP urgently for use on Monday!


      I can set up the database and (in fact ) import an access database into it, no problem.


      But I need to set up the equivalent of an Access form (plus a query in fact) to enable input to the database as follows: 


      Database has two tables:










      Table_1 has a one to many relationship with Table_2 based on Equipment_id.


      What I need to be able to do is to input an equipment_id which then reports back the Description and enables me to set up a new record in table_2 with the test results


      There are about 600 items in table_1 with about 1200 entries to be done in Table_2. (Table_2 also has the last 5 years worth of results in but that doesn't really matter for Monday! - I have a lot more time to think about report generating).


      Any ideas?


      Thanks very much!!!




        • 1. Re: Filemaker newbie needs help quick!!

          I speak "Access" as well as "filemaker".


          Look up "Portal" in FMP's online help. A portal is very similar to a Sub-form in MS Access. If you know how to set up a Sub-form in access, a portal in filemaker should be fairly easy to create.


          Other Access to FMP suggestions:


          In Access, you create a SQL expression as the record source for your form and can control what records appear on your form by manipulating the SQL expression. In Filemaker, you select a base table in Layout Setup... and then perform a find or Go to related record operation to pull up a subset of the total records in this table as a "found set". This found set is your record source for the form and you can control this record source by performing different finds and sorts to change what records are in the found set and how they are sorted.


          You add fields from related tables to an Access record source by include a "join" expression to that table in your record source SQL expression. In Filemaker you define the "join" in File | Manage | relationships and then simply add the field from the related table to your layout.

          • 2. Re: Filemaker newbie needs help quick!!

            Yes, I'd already got most of that, in fact.


            It's how to perform the "find or go to related record operation" that I need to know!



            • 3. Re: Filemaker newbie needs help quick!!

              Finds can be done either by hand or by script. The basic steps are the same:


              enter find mode

              enter search criteria in various fields

              perform find

              sort the records if you need them sorted.


              GTRR (go to related record) is done via script and must be used with care to avoid undesirable results.

              Define your relationship in File | Manage | Relationships

              Use GTRR in a script specifying the relationship, the layout you want to use to display the results, whether you want only the records related to your current record or all the related records of all the records in your current found set...


              Where GTRR can be dangerous is that there are two circumstances where it will fail silently (script execution is not halted and no error message is automatically displayed.) Instead, the script continues execution as though the GTRR step doesn't exist with potentially disastrous consequences for your data. You can easily trap for both situations but you have to create specific script steps to identify and handle these situations:

              1. There are no related records
              2. You triggered GTRR from the bottom blank portal row of a portal that uses the same relationship

              To trap for these situations, one trick is to use:


              If [get(lasterror) ≠ 0]


              immediately after the GTRR step.


              In your case, I'd read up on finds, sorts and GTRR in the help file. I've included the above info on GTRR as it is not in the help file.

              • 4. Re: Filemaker newbie needs help quick!!

                That wasn't really the answer I was looking for, but in the meantime I've solved the issue myself.


                For the record (and for anyone else interested) the solution is to create two scripts.

                One as an "on entry" to the equipment_id field - simply "enter find mode"

                a secondly as an "on exit" from the equipment_id field "go to field (equipment_master::equipment_id)"


                You can then type the equipment_id number in, press return and the rest of the layout including the portal populates itself with the appropriate info and you can add rows to the portal in the normal way.


                I haven't bothered with any error trapping - so entering an incorrect value (or a nil value) in the equipment_id field might have unpredictable results but I will investigate that in the morning!



                • 5. Re: Filemaker newbie needs help quick!!

                  It may not be what you were looking for but it was what you asked as far as I could tell :smileywink:


                  You'll need to take a closer look at your second script trigger. When you press the enter key next to the number pad, you'll cause filemaker to perform the find and return to browse mode and this is why it works. However, if the user presses tab, or the return key that's part of the Qwerty section of your keyboard, it may still leave you in find mode.


                  Here's a way to "tweak" your approach:


                  Create a global field and put it in the header of your layout. If practical, you can set it up to be drop down listing equipment Id's. Put a button next to it or set an On exit script trigger to perform the following script:


                  Enter Find mode []

                  Set field [equipment_master::equipment_id ; equipment_master::globalField ]

                  Set error capture[on]

                  Perform find []

                  set error capture [off]


                  This will do the same thing without the above issues in your current set up. You can also now enter the Equipment ID field and edit that value if necessary where currently, each effort to edit the field drops you into find mode.


                  • 6. Re: Filemaker newbie needs help quick!!

                    I don't, with respect, think it was what I asked for, but never mind.


                    Yes, you do appear to be right - evidently filemaker does not consider every possible exit from a box to be an on exit event - stupid in my view, but still.


                    I don't actually think it's than much of a worry in this particular case - I'm running it on a laptop and it does consider the laptop enter key to be an on exit event (it will only be running on a laptop) and the tab key is far enough away physically for it not to be a problem either. This has to be live for Monday as I said at the outset so I don't propose to worry about it unduly at present.


                    Maybe when this year's testing season is over I will investigate further (I have to get it to print a big report as well) but I should have the time to spare when I get to that stage.




                    • 7. Re: Filemaker newbie needs help quick!!

                      The on exit event is consistent in filemaker. Each of the following user actions will trigger an on exit event trigger for a script (with *qualifications given).


                      Tab Key*

                      Keypad Enter*

                      "Qwerty" Enter* (mac "return" key)

                      Clicking with the mouse outside the bounds of the current object.


                      *If enabled in Field/Control | Behavior, the default is usually "Tab".


                      The problem is that your On Exit script is not what's actually performing the find. Filemaker's Find tool is set up so that when you are in find mode and press "enter" it's the keyboard shortcut for Perform Find. That's why pressing the enter key works for you.


                      I am trying to warn you that if you exit the field without pressing enter, you will still be in find mode as the On Exit script you describe does not perform the find.

                      In addition if you need to interact with the contents of the Equipment ID field, you'll be unable to do so as you automatically enter find mode every time you enter the field. Thus, you won't be able to copy the id number to your clipboard or edit the ID number if it is a mistyped number (Of course it's probably an auto-entered serial in which case never mind that last issue.)


                      I'm suggesting a modification that will avoid those problems.


                      Glad it works for you, but I think in the long run, you'll want to change things a bit due to the issues that I'm describing.