1 Reply Latest reply on Aug 15, 2011 9:26 AM by philmodjunk

    Help with Calculations

    NathanApps

      Title

      Help with Calculations

      Post

      Hi Everyone,

      I'm working with an existing databse that has staff names and room numbers. Simplified, it looks like this:

      Name Room
      John 1
      Henry 2
      Mary 1
      Susan 3
      Becky 4
      Mark 5
      Fred 1
      Jamie 5

      What I would like to do is to make a "room report" that lists all the rooms, how many people occupy each room and who the people are that occupy a particular room. 

      Something like this:

      Room Occupants Occupant Names
      1 3 John, Mary, Fred
      2 1 Henry
      3 1 Susan
      4 1 Becky
      5 2 Mark, Jamie

      Could someone point me in the right direction? I've had limited experience with Filemaker Pro but extensive experience with Excel. 

      I've worked out how to add fileds that do calculations but am unsure where to go from here.

      Thank you,

      Nathan

       

        • 1. Re: Help with Calculations
          philmodjunk

          This isn't something that your Excel experience will help you much in setting it up.

          Define a "Rooms" table where you have one record for each room. Use Manage | Database | Relationships to create this relationship:

          Rooms::RoomNumber = Occupants::RoomNumber

          "Occupants" is what I am calling your current table here.

          Create a list layout based on Rooms so you have one row for each record and you can sort these by RoomNumber to get the correct order.

          This calculation field, defined in Rooms, will provide you comma separated list of room occupants:

          Subsitute ( List ( Occupants::Name ) ; ¶ ; ", " )

          List will produce a list of the Names separated by returns. The substitute function then replaces the return characters with a comma and space to eliminate the returns.