6 Replies Latest reply on Feb 4, 2010 1:09 PM by philmodjunk

    Value List that changes as items are used up

    Dubba_C

      Title

      Value List that changes as items are used up

      Post

      Sorry if this has been posted before, but there are so many entries for value lists that don't help I gave up searching.

       

      Here's the deal: I have a db with four tables: people, locks, lockers, and assign. All but assign have unique primary keys for each record.  Assign will be for assigning locks and lockers to students. It was easy enough to set up the relationships that group the data together in assign. The hard part is providing a UI that is resistant to user error. To wit, I want the lock and locker number entry fields in the assign table to offer (using value lists?),  only locks and lockers that are unassigned. In other words, the value list changes (shrinks) as locks are used up. For example if locks 101, 102, 103, and 104 are in the locks table but assign already has used 101 and 102 then the list that pops up for the next entry in assign will offer only 103 and 104.

       

      I finally got a prototype to work by creating a calc field ("taken") in locks that uses a lookup to see if that lock number is already present in assign. If not, then another field in locks ("available") populates itself with the lock number, and that field is used to create the value list that is on the entry layout in assign. 

       

      The problem is that the lookup doesn't refresh and therefore keeps offering the same selection of locks even after some have been used. I tried making the lookup an unstored value to force it to refresh the lookup but the value list needs to work with indexed fields. I also tried manually refreshing the lookup, but got "There are no fields that look up values based on the field "taken". Okay, so I tried refreshing from the lock number in the assign table (where the lookup is looking). Same alert. The only way I can get it to refresh is by going into the field definition and changing something then changing it back.

       

      So is there a better way to do this? It doesn't seem like rocket science to me, but then I've failed so far... :smileytongue:

        • 1. Re: Value List that changes as items are used up
          Steve Wright
            

          I think this is what you are looking for.

           

          http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=15325&query.id=181703#M15325

           

          See the last two posts in particular 

          • 2. Re: Value List that changes as items are used up
            Dubba_C
               Thanks, SW. Picking it apart to see how it works. Looks promising. I'll post back when I've digested it and (hopefully) succeeded.
            • 3. Re: Value List that changes as items are used up
              Dubba_C
                

              It worked partially. The filtering by location works very well, but the value list is complete again on the creation of a new record. I can't have this. We have almost 800 lockers and locks and, though it would be cool to put all 800 on one record so we could do it semester-by-semester that would be cumbersome to say the least. I need a value list that will look at all the locks/lockers that are out across all records and populate the lists with what is left in the respective tables.

               

              Next I'm going to try creating another lock table that will have records deleted when a lock goes out and added back when a lock comes back in. Then a script will create the value list from that table each time a click is made on the entry point. I thought I'd post back here first, though, just in case there is any comment on this. Not sure it will work, but here goes!

               

              More later... 

              • 4. Re: Value List that changes as items are used up
                philmodjunk
                  

                I haven't clicked the link Mr. Vodka posted and am only responding to: "I'm going to try creating another lock table that will have records deleted when a lock goes out and added back when a lock comes back in."

                 

                Instead of deleting and created records, just add a field that records whether the lock is "in" or "out".

                 

                A calculation field like this: If ( LockStatus = "In"; LockID ; "")

                 

                could then be used in a value list that will only list those locks that are currently "in".

                • 5. Re: Value List that changes as items are used up
                  Dubba_C
                    

                  Thanks, PhilModJunk. I had tried you solution before, but there was the complicating factor of a second occurrence of the lock table related to the transaction table by location because there are two sets of locks (for two locker rooms). The value list has to include only related values starting from the transaction table by location, then select the unused ones after that to build the value list.

                   

                  The answer was to do it the old fashioned brute-force way: Do away with the second lock table occurrence (related by location) and create two value lists by calculation, one for each room in the locks and lockers tables. For example, in the locks table the personkey field is populated (or not) like this:

                   

                  if(room=2;if(personkey>0;"";locknumber);"")

                   

                  Once that was in place your idea essentially worked. I just had to do the old script tango to add, delete, and update entries in the transaction table (copy keys and data into global fields and use them to search and replace in the lock and locker tables). I had been hoping for something more elegant but this solution will hold for now.

                   

                  Thanks for the help guys! 

                  • 6. Re: Value List that changes as items are used up
                    philmodjunk
                      

                    You might want to check out this thread even if you've already read it. If you combined both tricks you should be able to get what you want without the "brute force" approach that isn't as flexible if you later have to add a third location. I'd use the calculated field to list available locks and a relationship to filter the list by location. (You could also set up the relationship so that in/out status AND location were part of the relationship and avoid all the calculation fields.)


                    Custom Value List?