1 Reply Latest reply on Mar 18, 2012 2:18 PM by philmodjunk

    Linking records from a populated table (Help with relationships)

    TomTilmant

      Title

      Linking records from a populated table (Help with relationships)

      Post

      I'm just started learning filemaker pro and have been going through the online video training for the past few weeks.   I'm starting a project where I want to assign soccer fields for our club.    The fields have different availability times so when I get a game request, I need to look up which fields are available for the game.    So I'm trying to understand how I can get a list of available fields and times for a requested date and select  one that will be assigned to the game then that field/time becomes unavailable from the list for the next game.  Also if a game gets change to another field and time, that the site comes available in the pool. 

      I have created two tables which are:

      Table  = games  Fields = game_number, game_date, site, time

      Table = site Fields = date, time, site, game_number

      I populated the site table with date, fields and available times

      I know how to create a new record with a link, my problem is that the site table already has data so I need to understand how to make the one to one link when selected by the games table.  Now, I'm not sure if I need to set my relationship by game_number or date.  If I set the relationship by date, then I can create a popup that shows all the games in the site table, but I'm not sure how I link the selected record to the games table and prevent it from being seen again.  If I set the relationship the game_number, it still will show the popup but I still don't know how to link.  

      My popup displays all records from the site tables.  First field is time and second field is site

      I appreciate any guidance and help.  

        • 1. Re: Linking records from a populated table (Help with relationships)
          philmodjunk

          What you are asking ends up pretty sophisticated for a newbie to tackle. I haven't spelled out every single detial either so take a look at this and be prepared to use Post A Answer to post follow up questions.

          This setup:

          Games>-----Fields    (>--- means many to one)

          Games::FieldID = Fields::FieldID   (Fields::FieldID is an auto-entered serial number)

          would seem to be the basic relationship to schedule a game for a specific field.

          The catch here is that checking for availability like you describe would require an additional, separate relationship that checks a given combination of Field, date, and time for availability. You can create additional relationships in Manage | Database | Relationships by using the duplicate button (two green plus signs to duplicate one of the table occurrence "boxes" found on this tab.

          The following method assumes that all field reservations are made for the same length time interval. It does not assume that all reservations start with the same pattern of intervals. (One game can start right on the hour, another at 15 after, a third 30 minutes after, etc.)

          I suggest defining two fields, TSstart, TSend of type time stamp in games. These record the date and time a game starts and ends. TSend should be a calculation field defined as:

          TSstart + //put length reservation time period in seconds here

          Choose TimeStamp as the return type. Define a serial number field: GameID. Then define this relationship:

          Games::TSstart > GameReservations::TSstart AND
          Games::TSstart < GameReservations::TSend AND
          Games::GameID ≠ GameReservations::GameID

          GameReservations is a new table occurrence of Games that you create with duplicate button I mentioned earlier.

          Now define a calculation field, cReservedList as:

          List ( -1 ; List ( GameReservations::FieldID ) )

          Select text as the return type. Be sure to select "Games" from the context drop down at the top of the specify calculation dialog.

          Now you can set up this relationship:

          Games::cReservedList ≠ AvailableFields::FieldID    (AvailableFields is a new occurrence of Fields.)

          With these relationships, you can put a portal to AvailableFields and when you enter a date and time in TSstart, and the portal will display all available fields. A conditional value list can use the same relationship such that you can select a field from a drop down list of available fields.