6 Replies Latest reply on Nov 19, 2012 7:13 AM by tchung

    need help define relation

    tchung

      Title

      need help define relation

      Post

           need help on defining a relation for

           tableb
           fields: location, cust1, cust2, cust3, cust4
           with following data

           location | cust1 | cust2 | cust3 | cust4
           --------------------------------------------------
            a            | 08:00 | 08:30 | 08:40 | 09:00
            b            | 07:00 | 07:10 | 07:40 | 10:00
            ...           | ...         | ...         | ...       | ... 

           tablea
           fields: custname, location, time

           how to do a relation from table a to table b for time, so

           when tablea:custname=cust1 and tablea:location=a, field tablea:time auto populated with value of 08:00, or
           when tablea:custname=cust4 and tablea:location=a, field tablea:time auto populated with value of 09:00, or
           when tablea:custname=cust2 and tablea:location=b, field tablea:time auto populated with value of 07:10, and so for

           <enter>     | <enter>  | <auto from relation>
           custname | location | time
           ------------------------------------------
            cust1        | a             | <auto> 08:00
            cust4        | a             | <auto> 09:00
            cust2        | b             | <auto> 07:10
            ...     | ...      | ...

           thanks

        • 1. Re: need help define relation
          johnhorner

               hi tc... assuming i understand your existing solution correctly i have a few suggestions for you.

               as a general comment, i would highly recommend giving your tables meaningful names (e.g. "timeslots", "locationschedule", "appointments", etc).  this is an all but universal practice.  while it is easy enough for you to keep track of with only 2 tables, it will very quickly become extremely difficult to manage as the number of tables grows.

               to accomplish what you are asking is not difficult.  first, you need to create a relationship ("tablea_TABLEB", for example) between tablea and tableb such that: tablea::location = tableb::location.  then, for the auto-enter calculation simply set up a "case" statement to obtain the correct time:

               case(

               tablea::custname = "cust1" ;

                    tablea_TABLEB::cust1 ;

                

               tablea::custname = "cust2" ;

                    tablea_TABLEB::cust2 ;

               ... etc.

               )

               however, one of the key advantages to a relational database is that it prevents the need to enter or store data twice.  you are trying to take advantage of this to avoid entering the data twice, but i would go a step further and avoid storing it twice.  you don't need to store "time" in "tablea" because you can simply display it from "tableb" where it is already stored.  to do this you would need to change the structure of your database.  tableb would need three fields: location, timeslot_ordinal, time (i use the name "timeslot_ordinal" because it is easier for me to remember what this field means rather than cust1 as that makes me think of a customer's name).  you can call the fields whatever you want though. so rather than having one record in this table per location, you would have one record in this table for each possible timeslot, at each location.  one advantage to this is that you can now easily add or delete timeslots to your schedule by simply creating or deleting records.  whereas before you were constrained to 4 timeslots and if you wanted to make a change, you had to actually change the structure of the database by adding fields.  once, you have this in place, you can create a relationship between tablea and tableb such that tablea::location = tableb::location AND tablea::timeslot_ordinal = tableb::timeslot_ordinal.  then you don't need a "time" field in tablea at all.  simply place the field "time" from tableb using the relationship tablea_TABLEB (tablea_TABLEB::time).

               this is just the first thing that came to mind.  i don't have much experience with calendar or scheduling solutions but you can probably find a template within filemaker that has similar functionality and see how they set it up.  my main point is that there is probably a better way to structure your database to make it more flexible, more efficient, and more powerful.  it is worth spending some time up front to think about the overall design before proceeding with a structure that may not be the best to accomplish what you need.  for example, you may want to be able to show a list of available timeslots for all locations on a given date, or for a single location on a given date.  or perhaps you may want to actually track the customer by name to be able to see their history.  these things can all be done 

               good luck!

          • 2. Re: need help define relation
            philmodjunk

                 Matching by names is also problematic. You can easily have two customers with the same name and customers also change their names. Plus it's easy to mis-enter a customer's name due to the idiosyncratic way people spell their names. All of the above will create issues for you when you match records in one table to records in another by name. It's better to use a table of customers to assign each a unique serial number and then use that serial number to link customer information to records in other tables.

            • 3. Re: need help define relation
              tchung

                   thank you for your pointers and suggestions.

                   will try and update.

                   tc

              • 4. Re: need help define relation
                tchung

                     yes - doing the 'correct way' (not the case thing) is much better and correct.

                     but - 'my people' wants to see the data shown as the original tableb layout. will find a solution for that later. if not they just have to live with this.

                     thanks again for explaining and suggestion.

                • 5. Re: need help define relation
                  philmodjunk

                       It is possible to use one row portals to arrange data from multiple related records into columns.

                  • 6. Re: need help define relation
                    tchung

                         ok - thanks