8 Replies Latest reply on May 19, 2011 11:36 AM by philmodjunk

    Realational Help

    JamesBransom

      Title

      Realational Help

      Post

      Hi,

      With my trial period about to expire, I made the plunge and have my very own license. Now I'm committed, but not at all sure that I''m going to get my head around all this stuff.  I get the advantages of relational design for storing information in one location and drawing it together through relationships and my current attempt works well for viewing my data once they've been stored in their various tables.  Data entry, on the other hand, is causing me all kinds of headaches.

      Say I have a set of tables related as follows:

      Countries::pk_Country_ID ---> States::fk_Country_ID||States::pk_State_ID ---> Cities::fk_State_ID||Cities::pk_City_ID ---> Destinations::fk_City_ID

      The Destinations table is necessary because, while all cities are destinations, not all destinations are cities (i.e., Disneyland, Grand Canyon National Park,...).

      The primary interface is laid over the Destinations table and I would like to be able to:

            1.  Retrieve and view, but also perform data-entry through this layout.

            2.  Automate data entry, linking the relationship chain (destination -> city -> state -> country) according to the current

                context (If the current record relates Durango -> Colorado -> USA, adding to City should be CityName -> Colorado -> USA)

            3.  Automatically create a destination record when a city record is created, each with the same Name-field contents.

      I've been trying to script this, but linking in a new city entry up the relationship chain from the perspective of the destination layout is...well, yeah,... I don't know what it is except confusing.  Am I going about this in the wrong way?

      Thanks for any advice, including reading lists!

        • 1. Re: Realational Help
          rjlevesque

          Need to hire a developer? LOL

          What exactly are you asking?

          • 2. Re: Realational Help
            rjlevesque

            This seems like you should be able to choose destination via a drop-down (value list) and then script to auto-populate the remaining fields based on decisions/choices made on your form. Have a screen shot? A snippet of code?

            • 3. Re: Realational Help
              philmodjunk

              One thing you can do to simplify your design is to remove the City table and just use the destination table in for cities and destinations. This can work even if you want to list a group of destinations for a given city. I suggest this because your post describes cities as a special kind of destination.

              • 4. Re: Realational Help
                JamesBransom

                Ok, let me see if I can be more clear

                The destinations layout is the main focus of this interface.  There are Businesses and People associated with (below?) Destinations in Portals on the layout.  I say "below" as opposed to cities, states and countries, which are above destination in the relationship chain (I don't know if that's convention, but it seems to me that it ought to be.)

                My problem is that a State has lots of cities and I thought that I would save some time (or, rather, spend that time tryng to learn how to use this %#$@$% FileMaker software!) through automation. Rather than setting the fk_State_ID field manually everytime I add a new city, I would like that to happen automatically according to my current context.  So if I'm adding Colorado cities, I only have to type the city name and, on verification or save, a script (or some other magic) would set the city's state ID to Colorado for me.  When I move on to another State's cities, that state becomes the new context.  If that new state needs adding, then I'd like that state's country ID to be set the current country context in the same manner.  I was thinking that this could all be done from the destinations layout without switching (or appearing to switch fro the user's perspective) layouts.

                I hope I made some sense.  Thanks for listening.

                • 5. Re: Realational Help
                  philmodjunk

                  This can be done with auto enter settings, global variables and some script triggers so that when you create a record in a child table such as city, the ID of the current state can be entered into the new record.

                  Example:

                  Write a script such as:

                  Set variable [$$StateID ; value: States::StateID ]

                  Use the OnRecordLoad trigger on the states layout to run this script everytime you change records on this layout.

                  Now define an auto-enter calculation in cities::StateID to enter $$StateID with each new record in Cities.

                  You can also use a script trigger to update the $$StateID variable from your cities layout, but you have to do this differently or a new record will clear the variable before the auto-enter can copy the ID value from it. An OnObjectSave trigger set on the Cities::StateID field can be used so that if you change the stateID for your current cities record, it will also update the variable.

                  • 6. Re: Realational Help
                    JamesBransom

                    You just brought me a major step along!  Thank you.  However (from Script Triggers Reference):

                                "FileMaker Pro does not pass information regarding which record activated the script. You may want to use

                                additional script triggers to capture that information."

                    Right now, no matter what happens in the Cities table, the script in the States OnRecordLoad always sets $$StateID to the first record (Arizona).  Any more hints?

                    • 7. Re: Realational Help
                      JamesBransom

                      ...or maybe you already answered that last question...?  Working on it...

                      • 8. Re: Realational Help
                        philmodjunk

                        Right now, no matter what happens in the Cities table, the script in the States OnRecordLoad always sets $$StateID to the first record (Arizona).  Any more hints?

                        Well that's how it should work shouldn't it? At least that's how I set this up for you. If you want a different state ID loaded into the variable from this layout, you'd change records on this layout.

                        Keep in mind that if you used a portal from the states layout to create and enter City records, none of this extra scripting and auto-enter calculating would be needed as the portal would handle it all for you.