1 Reply Latest reply on May 31, 2016 9:22 AM by mikebeargie

    List of records from Two Different Tables




      I have two tables with the following fields:

      (TBL 1) Staff Activity (Fields) SA ID, UserAccountName, UserID, Client ID, Client Name, Date, Time, ActivityType


      (TBL 2) Staff Non Client Activity (Fields) SNCA ID, UserAccountName, UserID, Date, Time, Activity Type


      In Table 1 appointments(activity type) offered to the clients, the date and time of appointment are entered and it keeps the users ID for each.


      In Table 2 appointments (activity type) that the user has but that are not directly related to a client are entered, for example, meetings or training, the date and time.  This also keeps the Users ID for each.


      What I'm trying to do is have a view that lists the records from the two tables, essentially creating a diary view.  So it would look as follows:


      31/05/16 - 9:00 - Assessment - Client Name (Table 1 Info)

      31/05/16 - 10:00 - Team Meeting (Table 2 Info)

      31/05/16 - 12:00 - Appointment - Client Name (Table 1 Info)

      31/05/16 - 2:00 - Appointment - Client Name (Table 1 Info)

      31/05/16 - 3:30 - Training (Table 2 Info)


      It's not really a Join in SQL that I want, is it?? as in I don't want to add information from one record in one table to another record in the second table.  It's list each record from both tables where the User ID and Date is the same.  Is it possible to do this?

        • 1. Re: List of records from Two Different Tables

          In filemaker you can't really display records from a JOIN statement without using the virtual list technique. That IS the method that is used to combine records from two tables into a single display.


          What's probably more suitable is to create a single table of appointments, because they're essentially the same thing, with a few differences.


          Having both sets of records stored in the same table, separated by a "type of record" field, is probably more conducive to what you need to do.