1 2 Previous Next 16 Replies Latest reply on Jan 26, 2013 8:39 AM by JamesCarr

    Trouble With Value List

    JamesCarr

      Title

      Trouble With Value List

      Post

           I have confused mysef.

           I have a solution that sets up seating arrangements. I start out on a main page table and mark one of 3 fields men women and couples with an "X". The second table is a Names Table. Each record in the Names Table has the same three fields as the Main table plus a name field that is a calculation of First Name and Last Name(text result). For each record in the Names Table There is an "X" in either the men or women field and an "X" in the couple field if necessary. The three fields are related. The third table is for the actual seating containing multiple name fields (name 1, name 2 and so on. These are related to the Names Table through the calculated name field. The value list I want to create is a list of names based on selection of the main table. If I mark main table men with "X" I only want to show those names in the names table taht are marked accordingly. Same with the women and couples. I have tried and tried to make this work but just cant seem to get it.

           Thanks in advanced

           Jim 

        • 1. Re: Trouble With Value List
          philmodjunk

               Please describe the structure of your third table. What does one record in this table represent?

          • 2. Re: Trouble With Value List
            JamesCarr

                 The third table has multible name fields for assigning names to tables. It has a field for table number so I can use the name fields to select a name for table seating. I have a relationship for each name field to the name table using multible  tables. For  each name field I want a value list showing a list of names based on the type of function men only or women only or couples

                 Does this make sense?

            • 3. Re: Trouble With Value List
              JamesCarr

                   PS I think it is a conditional valulist but confused on hoe to set it up

              • 4. Re: Trouble With Value List
                philmodjunk

                     I think you would be better off to have one record for each table or even one record for each seat at each table than individual fields for each seat at each table.

                     The fields for your Names table would appear to be these:
                        FirstName
                        Lastname
                        cFullName
                        Men
                        Woman
                        Couple

                     Do you use one record or two for a "couple"?

                     And what fields make up the first table besides Men, Woman and Couple?
                     What does one record in this first table represent?
                      

                • 5. Re: Trouble With Value List
                  JamesCarr

                       You are correct for the name table each record for the name table has one name the fields for men women have an "X" depending on who it is. The couple field would have an "X" if the name will be used in a couple senario. EX: John (1 record) could be paired up at the table with Jane(1 record) or someone else. What I am trying to do is basically sort the names table to provide a value list when one of the 3 choices are selected. There are 200 records in the name table so I was trying to get the list shortened by the choice I select.

                       In the third table I wanted to use the "sorted" value list for each seat. Each table would have up to 6 seats and I could use the value list to assign the people to the table.

                  • 6. Re: Trouble With Value List
                    philmodjunk

                         You didn't answer this:

                         And what fields make up the first table besides Men, Woman and Couple?
                         What does one record in this first table represent?

                         I'm trying to get a clear enough picture of what you have to be able to describe a solution. A conditional value list is indeed a likely choice for what you want, but that, in turn, requires a functional relationship that will match to the right records...

                    • 7. Re: Trouble With Value List
                      JamesCarr

                           The first table has the 3 fields men women and couples. My thought was to select one of these fields and create the list according to the selection. I have set up various relationships between the main table and the name table to no avail. I have used men to men couple to couple and women to women. I even add an extra field to both and had that field related to each other but that did not work

                      • 8. Re: Trouble With Value List
                        philmodjunk

                             I'm not at all clear on the purpose of this first table. Your second table, names lists each person you need to seat at a table. The third table documents the actual tables and seating.

                             But what purpose is served by this first table?

                             What does one record in this first table represent?

                             And please note that linking records by names is usually quite problematic as names are not unique. You can easily get two people to seat that have exactly the same first and last name.

                             Ps: I think you'll also want to set up something with the Names table so that you can link the two records that form a given couple.

                        • 9. Re: Trouble With Value List
                          JamesCarr

                               I was using the first table as a starting point to select if it willbe an all mens or all womens group or couples. You are correct about the names but i received the names from someone and they are what they are I guess. The couples could change so linking them would not work.

                               Here is what I would like to happen. From the main menu select "men" and only see the men from the names list and so on. I hope this helps.

                               Jim

                          • 10. Re: Trouble With Value List
                            philmodjunk

                                 The fact that this data comes to you from an outside source as a list of names does not mean that you can't assign serial numbers to each name record in order to help select the correct person should you get two people with the same name.

                                 I think that you can dispense with table 1 then. And your "couple" linkage is something that can easily be updated from event to event--but it also relies on some sort of info as part of your intial registration process that identifies the current "link" between two members of a couple. But you'll need that info in any case or there will be no way, as far as I can tell, to determine that John Smith should sit next to Mary Smith because they are attending your event as a couple.

                                 In table 3 define 1 global field and give it a value list of "couple, Man, Woman". (A global field as "global storage" selected as a field option.)

                                 In your names table, replace your separate fields with a single checkbox field with the same three values. You can use a validation rule on this field to keep a person from selecting both "man" and "woman" for the same record if you want. But you need all three values in one field.

                                 Then you can set up this relationship:

                                 Table 3:GlobalField = Names::YourCheckBoxField

                                 If you already have a relationship between table 3 and Names, you can create a new occurrence of Names to use for this relationship.

                                 Then you can define a "use values from a field" value list to list Names from the Names table. Select the "include only related values" option and specify Table 3 as the "starting from" table.

                                 You can now select Man, Woman, or Couple in Table 3::globalField and your value list will update to list only Men, Woman, or "couple" records.

                                 And this last value list can be set up to enter a serial number from a serial number field defined in Names with a relationship used to include the person's name from the name field--this is much easier to do if you do not have separate fields for each seat, but instead have one record for each seat at each table---filtered portals can then be used to show how is sitting at each table--even on a "map" layout the reproduces the physical layout of the room.

                                 Note: this value list is a "beginner level" approach to the problem. It is best suited for relatively short lists of values. There are more sophisticated methods for finding and selecting a value that make working with longer lists of values much easier to work with.

                            • 11. Re: Trouble With Value List
                              JamesCarr

                                   I will give it a try and let you know

                                   Thanks JIm

                              • 12. Re: Trouble With Value List
                                JamesCarr

                                     Phil

                                     I tried the method you sugesseted and it worked. It was a little clunky. I think I made this harder than it was supposed to be. After I did what you sugested I tried another method. I still used the main table for setup. I created a field that is a popup list containing the thrtee fields "men woman Couples". I went to the name table and added a check-box field with the same values. I changed the name field to a calc based on the new check box field i called it master group, so when master group = group type of the record the name field gets calculated last name & first name. In order for this to work i built a script to set the master group from the data on the main table. Then using a replace step I just change the master group to whatever I selected on the main page. The value list came out with only the names that were calculated.

                                     I really appreciate your help with this

                                     Thank you

                                     Jim

                                • 13. Re: Trouble With Value List
                                  philmodjunk

                                       What made it a "little clunky"?

                                       What you describe would, I think, have the same limitations as the more typical conditional value list with the added complications of doing a Replace FIeld Contents and a new limitation that if you share the database over a network, two different users cannot do this at the same time as the replace field contents performed by one user would interfere with the results wanted by the other.

                                  • 14. Re: Trouble With Value List
                                    JamesCarr

                                         I hadn't considered that problem. I will stick to your sugesstion.

                                         Thank you

                                         Jim

                                    1 2 Previous Next