1 2 Previous Next 23 Replies Latest reply on Apr 11, 2012 3:33 PM by PhotoBuffoon

    Value List that dynamically omits records

    PhotoBuffoon

      Title

      Value List that dynamically omits records

      Post

      Hi,

      Here is what I'm trying to do:

      I have a coniditonal value list with names of people in it based on the group I select in another field.  This value list is in a layout that has fields that represent where each person is in a group photo e.g. front row position 1 = front first left.  

      As I select a name and enter it into each field I want the next position I select to omit the record that has already been used so that as you enter names there are less and less to choose from thereby making it quicker and quicker as you fill fields.

      In a nutshell I want the value list to dynamically omit records based on the populated values of the postion fields in my layout.

      Is there a simple way to do this?

        • 1. Re: Value List that dynamically omits records
          philmodjunk

          Define a calculation field, cSelectedList, as:

          List ( "x" ; front first left ; front second left ; ... etc )

          So that all fields where you define this value list are are included in this list. Select text as the return type.

          Modify the relationship on which your conditional value list by adding this pair of match fields to the relationship:

          YourLayoutTable::cSelectedList ≠ YourValueListTable::ValueField AND

          "x" in the list functions ensures that the field is never empty so that you get all values for your group when you haven't yet selected a value. In place of valueField refer to the field your value list specifies as field 1.

          • 2. Re: Value List that dynamically omits records
            PhotoBuffoon

            Thanks PhilModJunk!  That worked like a charm!

            • 3. Re: Value List that dynamically omits records
              philmodjunk

              Note that this specific solution assumed that all selections made were in fields of the same record. If each selection had been made in a different record, a different approach would be needed to produce the exclusion list of selected values.

              • 4. Re: Value List that dynamically omits records
                PhotoBuffoon

                Hi PhilModJunk, 

                I have found that now I am moving into testing phase that when I import new records and then create a new record in my layout that the value lists are coming up with nothing in them?  The old ones work if I modify them to a new Group name from the imported data but whenever I create a new record and select the group name I'm not getting any names come up?

                 

                Any ideas on what might be causing this?

                 

                Cheers.

                • 5. Re: Value List that dynamically omits records
                  philmodjunk

                  What is the exact relationship you have defined for this?

                  From what I recall, you need a matching record in the values table for each record in your data entry table. I would guess that there is no matching record in the values table for your imported records. Please note that if your relationship uses an auto-entered serial number, that field may be empty for all your imported records unless you enabled auto-entry options when performing the import.

                  • 6. Re: Value List that dynamically omits records
                    PhotoBuffoon

                    Hi PhilModJunk,

                    Please find screen shots so you can see what I have done with relationships and fields.

                    • 7. Re: Value List that dynamically omits records
                      philmodjunk

                      Other than to show me no serial numbers are involved this doesn't change what I said in my last post. You won't see values in the value list unless there is a record in Player Data that matches the record in Group_Names. You'll need to examine the data in these two fields to see why there is no matching record.

                      • 8. Re: Value List that dynamically omits records
                        PhotoBuffoon

                        Hi PhilModJunk,

                        Thanks for your patience.  Basically the data I'm using is dummy data.  The value list in my layout for team/group name is derived from the player data table and for every player in the table there is a team they belong to. Because I added dummy player data for every team I know there are entires for all.  

                        Secondly, it appears that if I delete relationships and then redefine them the same way It will work for the current record but not for any new records after that.  This makes me think this must be something more to do with the calculation than the relationship and that maybe I don't have it setup properly to calculate more than once or something?  What are your thoughts?  Please see screen shot of calc and settings FYI.  I have tried ticking and unticking do not evalute if referenced fields empty to no avail.  When I go to a new record, as you pointed out it appears to be calculating that there are no record matches however, I can assure you there are.

                        Thanks again for your patience with me! 

                        • 9. Re: Value List that dynamically omits records
                          philmodjunk

                          if I delete relationships and then redefine them the same way It will work for the current record but not for any new records after that.

                          Any new records in which table? What values are you entering into which field fo this new record?

                          • 10. Re: Value List that dynamically omits records
                            PhotoBuffoon

                            Apologies, please ignore my comments regarding deleting relationships as I have not been able to reproduce this.  Se below screen shot of layouts with and without the cSelected match field.  As you can see there is certainly data there but it does not appear when the cSelected match field is set.

                            • 11. Re: Value List that dynamically omits records
                              PhotoBuffoon
                              /files/b2935b9ec8/Relationship.png 606x579
                              • 12. Re: Value List that dynamically omits records
                                PhotoBuffoon

                                With cSelected Match Field

                                • 13. Re: Value List that dynamically omits records
                                  philmodjunk

                                  You have to look at the data in both tables and compare the values in both pairs of fields to see why it's not working here. From here, I'm still guessing at what I'm seeing. This layout lists "Group Names" in Show Records From when you access layout setup...?

                                  Is the top left field that shows "team 11", the Group_Name field?

                                  If you perform a find for records in Player Data, for "Team 11" (assuming that's the group name field), what values do you find in Player_Full_Names of the records thus found?

                                  • 14. Re: Value List that dynamically omits records
                                    PhotoBuffoon

                                    Hi PhilModJunk,

                                    Yes I have alooked at data in both sets of tables please see attached screen shot of data in the Player Data table with the team name.  As you have seen,

                                    - The Group_Name Field in the groups Names Layout is a match field to Team Name field in the Player Data Table.

                                    - In this screen shot you can see that I do have data in the team field and the Player_Full_Name Field which is a calcualted field joining the First_Name & Last_Name Fields in the Player Data Table.

                                    - I have reproduced the issue where I got a record to work.  It was by editing the specified fields in the Value List.  Existing records that previously didn't show values do however, if I create a new record in the Group Names Layout it does not work.

                                    1 2 Previous Next