9 Replies Latest reply on Apr 26, 2013 11:44 AM by user22497






           Hi, I'm new to FileMaker but not to databases but I'm developing an app in FM and need help.

           I have 3 tables, the first one has the fields:

           Code, Description and Notes (where code is the primary key and there's a relation of one to many with the field Code in the second table). It's records can be

           Code      Description               Note

           001         Traffic Violation        Client on Fault

           002         Parking Violation     Any relevant note here

           The second one:
           Code, Subcode, Description (Each code from the first table have several subcodes in this table). Records:
           Code     Subcode    Description
           001        001             Speeding less than 30 miles over the limit
           001        002             Speeding more than 30 miles over the limit
           001        003             Speeding more than 50 miles over the limit
           002        001             Parked after authorized time
           002        002             Parked before authorized time
           The third one:
           Code, Subcode, County, Prices. (For each code and subcode there are diferent prices depending on the County). Records:
           Code        Subcode      County                 Price
           001           001                Miami-Dade        $20.00
           001           001                Broward               $15.00
           001           002                Miami-Dade        $25.00
           001           002                Broward              $10.00
           002           001                Miami-Dade       $5.00
           002           001                Broward              $5.00
           And so on...
           What I want to fulfill is that the user can get into de prices in the third table, depending on what he/she previously selected from each prior table. That is, first it has to select a Code from the first table (let's say "001" was selected), and then the system must only show from the second table all the subcodes for the code="001" . The user selects one here (let's say subcode "002" was selected) and finally the user is taken to the third table where the system shows the County prices for code="001" and subcode="002" for the user to select the price associated with the county where the infraction took place.
           I know I'm asking for a lot but any help would be greatly appreciated 

        • 1. Re: Help!

               Using the three tables as described would suggest the following relationships:


               Table1::Code = Table2::Code

               Table2::Code = Table3::Code AND
               Table2::SubCode = Table3::Subcode

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

               But it's not clear to me what user interface you have in mind here and that could, in turn require a different data model.

          • 2. Re: Help!

                 Thanks for your replay. As to the user interface I would like to have the code, subcode and prices in the same windows, that is the first one. The layout corresponding to the first table.

            • 3. Re: Help!

                   Please describe that in more detail. Please indicate specifically, how this layout will be used.

                   Are you, perhaps going to use this layout to document the codes and sub codes for a ticket and compute the total cost of the violation?

              • 4. Re: Help!

                     Exactly. That layout is that of the clients with a portal to the tickets. I want the user to be able to surf trought the clients and see all information regarding to their tickets in the same window, and it seems that's not easy to do in FileMaker

                • 5. Re: Help!

                       It's actually quite easy and the relationship structure is the same as what I would use even if setting this up in a different rdbms. But we do have to get a bet creative with the county info to pull up the correct subcode fees by county.


                       clients::__pkClientID = Tickets::_fkClientID
                       Tickets::__pkTicketID = TicketDetails::_fkTicketID

                       TicketDetails::_fkCode = SubCodes|CountyCode::_fkCode AND
                       TicketDetails::cCounty = SubCodes|CountyCode::County And
                       TicketDetails::_fkSubCode = SubCodes|CountyCode::__pkSubCode

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

                       A layout based on Tickets can use a portal to TicketDetails to list the subcodes and records in this table can look up the fees from the sub codes table.

                       cCounty is the one odd bit here and there are several approaches you can test out. YOu don't want to have to manually enter the county into each record of ticket Details.

                       Your options for getting a value into cCounty automatically:

                       1) define cCounty as a calculation field that copies the value of Tickets::County
                       2) define cCounty as a text field that auto-enters the value of Tickets::County
                       3) Use Set field from a script performed via a script trigger in a required portal field to copy the value into a text field.

                       This doesn't provide every detail, but hopefully that's enought to get you started.

                  • 6. Re: Help!

                         Ok, thanks a lot for you answer. I don't know if you or someone else can answer the following question but I think it will spare me a lot of time with FM so hoping maybe someone can help, here it is:
                         I worked with Visual FoxPro where you defined work areas and openen tables in them. Every time you go to a workarea you were automatically working on the defined table and the active record in it remained active all the time unless you moved to another record. This mechanism allowed to get information from any table no matter if related or not and you could field a form with info from any table in the system without moving away from than form or layout as they are called in FM. Is there a way or an approach to work like that in FM or the work phylosophy have to be completely changed?

                    • 7. Re: Help!

                           In Filemaker to open a table you have to go to that layout, you can keep the user from seeing that layout by using Freeze Window before you go to that layout.

                      • 8. Re: Help!

                             Every Layout is "hardwired" to a specific table occurrence--a "box" found in Manage | database | Relationships. Each Tutorial: What are Table Occurrences? the refers to a specific table on your tables tab in Manage | Database. This cannot be dynamicly altered.

                        • 9. Re: Help!

                               Thanks a lot for your help, both of you have given me very important clues to understand FM.