12 Replies Latest reply on Aug 4, 2010 3:24 PM by ShaunG-B

    How do I pull one record from a portal when it's not the first or last record listed?

    ShaunG-B

      Title

      How do I pull one record from a portal when it's not the first or last record listed?

      Post

      I am using FileMaker Pro 9, and having trouble here.

      I am trying to design a layout that shows all the people who attended a particular gallery exhibit.  Gallery exhibit information is stored in the Gallery Table, and personal information (name, phone, etc.) is stored in the Master table.

      Master Table is linked to Gallery Exhibitions Table through unique ID code Client_ID. The relationship is not sorted.

      In the Master Table, there is a tab that displays records from Gallery Exhibitions through a portal, which is sorted by the Exhibit_Name field, which is a drop-down value list.

      In the layout I have designed, ideally the Exhibit Name will display in the header as a searchable drop-down value list, and then the main body of the layout is one line long which displays the name, email and exhibit participation record for each person.

      The body of the layout works -- if I search for an event in the Exhibit_Name drop-down list, it will display the names of all people who attended the event.  

      However, the issue is that all the information being pulled from the Gallery Exhibitions table is not displaying the way that I want it to.  The exhibit_name record in the header is displaying data from the first exhibit record of the first person listed.  If that person's most recent exhibit was not the one I did the search for, then the Exhibit_Name display in the header is incorrect.  The same is for the exhibit participation -- the field will display the first record from the portal, not the record relating to the event that I searched for.

      Does this make sense?  Any ideas about what I need to change in order to display the correct fields from the portal?

      Thank you ~ Shaun G-B

        • 1. Re: How do I pull one record from a portal when it's not the first or last record listed?
          philmodjunk

          Sounds like you need another table between exhibits and people since the same person can attend more than one exhibit.

          People---<Attendance>---Exhibits

          People::PeopleID = Attendance::PeopleID
          Exhibits::ExhibitID = Attendance::ExhibitID

          On your exhibit layout, you can place a portal to attendance and add any fields from people to this portal to show the people who attended that exhibit and any information about them you need to display. Any information specific to attending that one exhibit can be stored in fields in the attendance table.

          Here's a demo file of the concept, it matches contracts to companies instead of people to exhibits but the concept is exactly the same:  http://www.4shared.com/file/PLhjErzu/Contracts_to_Companies.html

          • 2. Re: How do I pull one record from a portal when it's not the first or last record listed?
            ShaunG-B

            Thanks Phil, I'm trying to re-create this example you sent.  I'm unsure how to insert the attendance table in between the already existing master and exhibit tables... when I tried to do so, it made me create an "exhibits 2" table that was a copy of the original exhibit table.

            Any chance you'd be able to give me a bit more explicit (and simple) instruction on how to set this up?

            • 3. Re: How do I pull one record from a portal when it's not the first or last record listed?
              philmodjunk

              Exhibits 2 is not a table, it's a "table occurrence". It refers to the same data source table as exhibits. (Data source tables are what you find listed on the table tab in Manage | Database and table occurrences are the boxes shown on the relationships tab. While an entry on the tables may have the same name as a box on the relationships tab, they aren't really the same thing.)

              You'll need to either delete the existing link between exhibit and master or use a second table occurrence of exhibits just as filemaker has created for you. If you choose to use the table occurrence, however, you then have to refer to exhibits 2 in the "show records from" box in layout setup... for your exhibits layout if you also want to place a portal to attendance on it.

              • 4. Re: How do I pull one record from a portal when it's not the first or last record listed?
                ShaunG-B

                OK... well, I can't delete the existing link between exhibit and master because it's used for other purposes besides this new layout I'm creating.  So I'll try to use a table occurrence.

                I need to leave for the day so I'll come back with more questions soon.

                Thanks for your quick responses. ~ Shaun GB

                • 5. Re: How do I pull one record from a portal when it's not the first or last record listed?
                  ShaunG-B

                  Alright, I've been working on this again today and it's a lot more complicated than I thought! I can't seem to get it to work, even though I have been trying to emulate the sample you sent me.  So I'm writing out what I want to display, and the steps I've taken to try to mirror the sample you sent... please take a look and advise!

                  What I want this Exhibit Attendance Layout to display:

                  In the header of the page: Exhibit_Name, Exhibit_month, Exhibit_year (all from Exhibits table)

                  In the body of the page: A list of people who attended the exhibit, including First_name, Last_name, email, phone (from Master table) and Exhibit_code (from Exhibits table)

                  Here are the steps I understand I need to do (and their parallel in the Contracts_to_Companies sample you sent), and the places where I have questions.

                  1. Create a new table "Exhibit_Attendance" (similar to Contract_Company).  Create fields Client_ID and Exhibit_ID in that table (similar to Contract_ID and Business_ID in Contract_Company).

                  2. Create a relationship between "Exhibit_Attendance" and "Master" through the Client_ID (similar to relationship between Contract_Company and Contracts).

                  3. Create a relationship between "Exhibit_Attendance" and "Exhibits2" – which is created by filemaker automatically when I try to create a relationship with the original Exhibits table (similar to relationship between Contract_Company and Companies).

                  4. Create a new layout for Exhibit Attendance in the Master table. In Layout Setup, choose "Show Records From" the Exhibits2 table. (is that correct? Or should it display from the original Exhibits table?)

                  5. To display the exhibit name, month and year in the header, if I follow the sample you sent (this is similar to displaying CompanyName in Contracts layout in Contract_Company table), it appears that I should take the following steps for each field:

                  a. Create a new value list (in Master table? Or in Exhibit_Attendance table?) for Exhibit Attendance Name. Value List Name is Exhibit Attendance Name, Source is From Field, Values are from specify field -- "Exhibits2::Exhibit_ID" and "Exhibits2::Exhibit_name"

                  b. Insert a portal to Exhibit_Attendance table.

                  c. Place a field in the portal line, select to display Exhibit_ID from Exhibit_Attendance table; display as a Drop-Down List, display values from Exhibit Attendance Name. (this is not working… when I click on the field it is not editable and no drop-down list appears.  What am I doing wrong here?)

                  d. Repeat for exhibit_month and exhibit_year.

                  6. To display name, email and phone in the body of the page:

                  a. Insert a portal to Master table.

                  b. Place a field in the portal line, and select name (or email or phone) field from Master table. (is this correct?)

                  7. To display exhibit_code in the body of the page:

                  a. Create a new value list (in Master table? Or in Exhibit_Attendance table?) for Exhibit Attendance Name. Value List Name is Exhibit Attendance code, Source is From Field, Values are from specify field -- "Exhibits2::Exhibit_ID" and "Exhibits2::Exhibit_code"

                  b. Place a field in the portal line next to name, email and phone.

                  c. Select to display Exhibit_ID from Exhibit_Attendance table; display as a Checkbox set, display values from Exhibit Attendance Code. (this displays as <no values defined>)

                   

                  Please, I’d really appreciate any advice you have for how to correct this and make it work.  Thanks ~ Shaun

                  • 6. Re: How do I pull one record from a portal when it's not the first or last record listed?
                    philmodjunk

                    3. per my original directions, you'd use Exhibits2, but hang on as I see this last post, there's another option that may work better for you.

                    5a) value lists aren't created in tables, they're defined in files. Do you have more than one file or all tables in the same file?

                    c) did you enable "allow creation of records via this relationship" for Exhibits2 in the relationship linking it to exhibit_attendance?

                    d) why would you put exhibit month and exhibit year in the portal? Aren't those fields the same for all attendees? If so, you should define those fields in exhibits and place them in the header.

                    6. simply place the email and phone fields from the People table in the exhibit_attendance portal.

                    7. I don't see why you need any exhibit code field on this layout at all. This should be an auto-entered serial number and if you define the relationship right, entering data in the portal will automatically copy the exhibit code from exhibits and put it in the matching field in exhibit_attendance.

                    Now I mentioned that there may be a different layout setup that works for you. If this layout is for data-entry only, keep it as specified, you'd use your one portal to exhibit_attendance to log the attendance of each person to a given exhibit. If you want this for printing out a report, a list view layout can be created based on exhibit attendance with the header fields selected from exhibits 2. You'd perform a find on this layout to find all attendees for a given exhibit. This layout will let you print a list of any number of attendees without having a portal keep some from being printed if the whole list doesn't fit in the portal.

                    • 7. Re: How do I pull one record from a portal when it's not the first or last record listed?
                      ShaunG-B

                      Great suggestions... before I go further in trying to make this work let me just answer some of your questions so you can tell me whether I should keep going in this direction or use one of your other suggestions.

                      First to clarify: The only functions of this list will be to search for a list of contacts who attended a particular exhibit (or who curated or contributed artwork, etc), and to print out that list.  All data entry will happen through the main layout on a gallery exhibitions tab that links into the exhibits table through a portal.

                      5a) This database is all in one file with multiple tables. So I get it, I'd just create the value list in the same place as all the others, which is what I did (i guess that one was obvious!)

                      5c) nope! Forgot to enable "allow creation of records via this relationship" -- I suppose I should do that?

                      5d) exhibit month and exhibit year are connected to each exhibit, not to the attendee. I thought to put these in the portal with exhibit name because otherwise as of now both fields pull data from the first exhibit record of the first person listed, the same way exhibit name does.

                      6. ok.

                      7. Exhibit code is how my client has set up a way to track a person's participation -- whether a person was an attendee, a curator, an artist, etc. It is not a number code (and shouldn't be called code in my opinion, it should be called participation, but that's what they've been using for years before I got here) but is actually an editable checkbox to indicate how a person participated in the exhibition.

                      • 8. Re: How do I pull one record from a portal when it's not the first or last record listed?
                        philmodjunk

                        5c, but of course, but now we don't need a portal at all.

                        5d) there should be only one exhibit record for a given exhibit period. All exhibit attendee records should link to this same record for a given exhibit's attendance. "first record" makes no sense as the relationship should be linking to just one record in exhibit and these fields, via exhibit2, should be in that table.

                        7) gotcha, in that case since each exhibit could have a different code, this field should be defined in exhibit_attendance and placed in the portal with a value list format.

                        Since this is a "find and print layout", I'd definitely use a list view layout based on exhibit attendance with the related fields from exhibit2 placed in the header and not use any portal. Everywhere I've said "put it in the portal"  should read "put it in the layout body" instead.

                        However, you will need a data entry layout with this portal for logging attendance in the first place.

                        • 9. Re: How do I pull one record from a portal when it's not the first or last record listed?
                          ShaunG-B

                          5c) I guess that's good news that we don't need a portal... but we still need the relationship between exhibits2 and exhibit_attendance, correct?  So should I select "allow creation of records..." in that relationship or not?

                          5d) I agree this should be structured differently, but the way it is now, there are some exhibits 

                          7) do you mean that this exhibit_code field should be defined in the exhibit_attendance table as well as in the original exhibits table? There is already a field tracking this information, and it is defined in the Gallery_Exhibitions table, so it's not a matter of creating a new field for data entry but instead it's a matter of displaying data already entered into this field.  

                          There is already a data entry layout with this portal where attendance is logged. This new list layout is enabling them to see that information in a different way.

                          So I think you're saying now that for this list view layout, I should go to Layout Setup and select Exhibit_Attendance next to "Show Records From" is that correct?  The scary thing that happens when I do that is that all of a sudden it tells me there are no records in the entire database.  They reappear when I change the "show records from" back to gallery_exhibitions or Master... yikes!

                          • 10. Re: How do I pull one record from a portal when it's not the first or last record listed?
                            ShaunG-B

                            You know, I am realizing that this problem of displaying information on a view list layout that pulls from a portal into another table extends into other areas of this database.  I am wondering if there is some setting on all the portals, or some step I am missing when I set up a list view layout.

                            For example, there is a simple Donor Summary List, which should list just the name and total donations amount for each person.  It does list the correct amount next to the correct person, but it will list some people multiple times.  For example, if Jean has made 5 donations of $100 each, it will list Jean with a total donation amount of $500, and it will list that 5 times. 

                            Do you think there is some step I am missing when I set up these list views? Or some setting that is incorrect in the portal setups?

                            • 11. Re: How do I pull one record from a portal when it's not the first or last record listed?
                              philmodjunk

                              5d I have now idea how you have this now. It shouldn't affect you going forward, it's just that you won't need the duplicate data.

                              7) basic rule of data normalization, never ever store the same data in more than one place except for the key values used to link records in relationships. Since this field identifies the "role" of a given attendee to a given exhibit, this field should be defined only in the join table where you have one record for one person attending one exhibit. This makes no sense to have this in Gallery_Exhibitions as you could have many people attending the exhibition and they won't all have the same code--at least that's how I understood your previous post.

                              There may already be a data entry layout, but you will have to change it to support the new table structure. The format that will work is the portal approach I've described.

                              The number of records shown are the number of records in the layout's specified table via the table occurrence you've selected in "show records from" it isn't telling you the total number of records in the database--only the number of records in the current layout's data source table. If you haven't yet created any records in this join table, it will show zero records. You will need to populate this table with records logging  exhibit attendance before things will look right, that takes us back to the new data-entry table I am telling you to create.

                              Also note that changing a layout's table occurrence will affect every field already placed on the layout. You will have to double click them one at a time and re-specify which table occurrence they come from before they will correctly display data. You may find it less confusing to create a new layout from scratch.

                              • 12. Re: How do I pull one record from a portal when it's not the first or last record listed?
                                ShaunG-B

                                Alright, thanks for helping me inch closer to a solution.  Unfortunately I ran out of time to resolve this today, and will be away from this project for a few weeks.  I look forward to figuring this out when I get back to work on it.

                                Thanks again for your patience and willingness to talk through this... you'll certainly be hearing from me again! ~ Shaun