3 Replies Latest reply on Jul 11, 2013 8:27 AM by philmodjunk

    One Report on Two fields based on Value Lists

    bokenrosie

      Title

      One Report on Two fields based on Value Lists

      Post

           I'm using a value list to track clothing size for individuals we are inviting to a party.  

           I have two fields called:

           Attendee Sweater Size & Guest Sweater Size

           inside there I store values from:

           a Value List called Sweater Size

           Mens-S
           Mens-M
           Mens-L
           Mens-XL
           Womens-XS
           Womens-S
           Womens-M
           Womens-L
           Womens-XL
            
           Now I need to know how many of each size to order 
            
           I tried a report but it did not come out anywhere close to what I expected.  Do I need to add a calculated field?

        • 1. Re: One Report on Two fields based on Value Lists
          philmodjunk

               The best option would be to restructure your tables/relationships so that sizes are recorded in the same field, but in different records for both attendees and guests. A simple related table can be set up for that purpose.

               Without that option, reporting the total count of sizes for each person attending--wheter invited or as guest, becomes difficult to impossible because the same record can contain size info for at least two different sizes.

          • 2. Re: One Report on Two fields based on Value Lists
            bokenrosie

                 I assume you mean a related table?  If so, how do I ensure a relationship between the names on the list and their records in the size table?

                  

            • 3. Re: One Report on Two fields based on Value Lists
              philmodjunk

                   To quote my previous post: "A simple related table can be set up for that purpose."

                   To get a clearer idea of the tables/relationships/layouts I might use for such a solution see this demo file that I use to illustrate many to many relationships: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

                   It is based on these tables/relationships:

                   Contacts----<Contact_Event>------Events

                   A record in Contact_Event is created for each person slated to attend a given event. ID numbers from Events and Contacts are used to link in info about the attendee and the event they are expected to attend. A field in Contact_Event can be added and used to identify whether a given attendee was invited, is being brought as the guest of an invitee or is an employee/volunteer scheduled to work at that event. A field in Contacts can record the clothing size data.

                   And a self join between Contact_Event and a new Tutorial: What are Table Occurrences? of Contact_Event can be set up to link an invited individual to the person that will be bringing them to the event.