3 Replies Latest reply on Nov 11, 2015 2:56 PM by Extensitech

    Value list from a subset of the same table?


      I did a lot of FileMaker work about 18 years ago but have been stuck in Access since then. Now I am trying to reproduce the functionality of a database I have in Access.


      This part of it is an inventory, keeping track of which lab computers are where. I have a table with computers, and one with desks, and then one where computers are assigned to desks, with fields for the start and end dates of the assignment (so we can maintain the history of where they have been).


      When recording a new assignment, I would like to have dropdowns for both the computer and desk fields which shows only those computers/desks which are not currently in an active assignment (end date is still blank). But I can't figure out how to create a value list based on a filter of another field in the same table.

        • 1. Re: Value list from a subset of the same table?

          Create a second table occurrence of the same table on the graph. Link it such that you'll only see those without end dates (you may need a calculated flag, since you can't match on blank). Create a value list based on this new TO, showing only related records from the original TO.


          This is pretty brief, so let us know if there are parts you're not familiar with, and we can elaborate.


          Chris Cain


          • 2. Re: Value list from a subset of the same table?

            Thanks for the quick reply Chris. I have added a calculated field that figures if the assignment is over (end date is nonblank) and that is working, but I'm stuck on the link that will make it show only those entries. I added a dummy field to the desks table and added the criteria of the calculated field not equaling the dummy field, but it shows the full list whether I say the two have to be equal or have to be not equal.

            • 3. Re: Value list from a subset of the same table?

              OK, I'm gonna make up some names for things, so bear with me.


              Your current table I'll call "Computers". That's the one your layout is based on.


              Your join table I'll call "Assignments". I presume you're showing a portal where Domputers::primaryKey = assignments::computerKey.


              In both the "desks" and "computers" tables, it sounds like you have a numeric (boolean) field I'll call "Open" equal to "Isempty ( End Date ) ", where you've unchecked the box that says "do not evaluate if all referenced fields are empty". That'll equal either 1 for open or 0 for in use.


              I'm also assuming you have a constant calculation field that always equals 1. I call mine "_1" so that's what I'll call it here.


              From the Computers, when entering Assignments in the portal, you'll want to select a desk from a drop-down of active Desks.


              (From your "Desks" layout, you'll do the same thing, but you'll be showing a portal of assignments for this desk, and a drop-down of active computers.)


              Now, create a new table occurrence in the graph based on the "Desks" base table. I'm going to call this "ASN_DSK_open" because I'm a lazy typist. Create a relationship between "Assignments" and "ASN_DSK_open" where Assignments::_1 = ASN_DSK_open::Open.


              Create a value list called "ASN_DSK_open", which shows values from the ASN_DSK_open table occurrence (name, key, whatever you want to show in your drop-down) , but only show related records, starting with Assignments.


              Put that value list on the desks field on the assignments portal on your computers layout.


              So... does any of that need clarification? Did I miss, or misunderstand, something?


              If this isn't all you need (and I won't be surprised if more clarification is needed by one or both of us) it would probably be a good idea to post screenshots of your layout (in layout mode) and the relevant part of your relationships graph. This'll not only help me, but might allow someone else to spot things I'm missing.




              Chris Cain