5 Replies Latest reply on Mar 17, 2011 4:05 PM by philmodjunk

    Need help with Conditional Value Lists (I think.)



      Need help with Conditional Value Lists (I think.)


      Hello FileMaker friends,

      I used to be an Access/MSSQL database developer so I sort of know my way around databases. I've had to dust off my skills but I used to be a natural! :)

      When I made the transition from PC to Mac I've been so happy with the general user-friendliness of Mac products.

      With the exception of FileMaker. (I have version 10)

      I am having a really hard time figuring out what seemed really simple in Access. This is making my brain melt because I haven't had this issue with any of the other migrations I've made. Usually Mac programs are by far more superior in the user-friendliness department.

      So I'm hoping that this is a situation where the Mac version is just so much easier that it's going right past me.

      I'm trying to create a very simple database with 2 tables.

      1) CLIENTS
      2) VISITS

      Table 1 is names and identifying information for my clients.
      Table 2 is detailed information about each one of their visits.

      It's set up to be relational via a unique Client ID.

      When a client comes in for a visit, I want to be able to:

      - look up the client
      - see a list of their most recent visits
      - add a new visit record

      This would have been super simple in Access but I've been stuck all day trying to figure it out. I think I am supposed to use a Conditional Value List and I set one up but I can't seem to be able to make it work the way I want, which is to select the name from a drop down and then see a list of related values from the VISITS table.

      I know a Portal technically does this, but I don't want it in a table format like a portal does.

      I just want the drop down at the top of my layout and I want the visit detail to be in a FORM format where I can tab through the various Visit records filtered by client and also easily add a record right there. When I'm with the client, I need to be able to scroll back through previous visits and also add the current one.

      Am I missing something simple?

      I am hoping you can either help me to figure this out or perhaps point me in the right direction or give me the right terms to use so I can continue my searching. So far I keep running into dead ends.

      I know this is probably just my own learning curve and I really appreciate any help I can get.

      Thank you so much!!


        • 1. Re: Need help with Conditional Value Lists (I think.)

          I think in most cases it is not terribly difficult; it's just that you don't know how to do it. Which doesn't really help either. 

          For the drop-down list you talking about creating a Value List, filtered via the client_Visit relationship (which you already have), choose a unique Visit ID (some options, but must be unique), starting from Client. 

          But I don't really get why that would better than a scrolling portal (could be only 1, 2 rows tall, sorted, showing the latest on top). And navigation to go to the Visit records in a list, with more fields visible.

          Most (some) of us would set the above up as follows (basic). Do a Find in a Client List view, find your Client. Or some other mechanism to find/show the client. Switch to Form view (nothing else required). See the Visits in a short portal, sorted descending by a timestamp field.

          Put navigation button(s) to the related records, both above and in the portal (same script step or script). Either go to the related records directly, in form view, to show more detail, or "pop up" the details in a new windows (which should then be closed). 

          Create a (short) script to create a new Visit (so you don't to try and enter it into the portal, which would not work well).

          Or set up a portal to show 1 Visit at a time, a portal with a filter, which is based on a drop-down or portal choice. Basically, if the way you want to see the data makes sense (and it usually does, perhaps with some modification), then you can do it, and it should not be too hard. 

          But it's hard for us to decide just what that layout would be, since it isn't our layout, and there's some different ways to do this, depending on your design and data flow. I favor the short portal over a drop-down any day, for something like Visits (or appts., etc.).

          • 2. Re: Need help with Conditional Value Lists (I think.)

            What if you had a 'Main Menu screen', with a simple search box for the Client's name?  You start typing the client's name and the portal shows all the Clients starting with 'S', then 'Sm', then 'Smi' as you type 'Smith'.  When the list is short enough for you to see the particular 'Smith' you want you click on the name and it takes you to a list layout of all their previous visits, sorted from most to least recent.

            You click on any visit you want and it takes you to a form view of that visit's details.  From there you can click forward, back, or return to the list to jump-start soemwhere else. At any time on that form view you can create a new visit record for that client.  All of those actions are 1-click.

            You could show on the Main Screen the listing of the client's visits as soon as you click on the name.  If the visit details are usually short, you need never even leave that screen - the portal can contain the date and time, the reason for the visit, the contact they saw, and if you hover the mouse over it it can flash up the details of the meeting (as a tool-tip, so it will be there for 10 seconds, that's why I said the details had to be short.  Or you could deliberately create a '10-second summary' as part of your meeting's record.)

            If that's what you want it would be pretty simple to do. (Can you confirm, btw, if Vs 10 has Script Triggers?)

            • 3. Re: Need help with Conditional Value Lists (I think.)

              Ah, thank you so much for responding. My apologies for waiting so long to respond ... I got pulled off in another direction for a little while.

              But I'm back now and ready to get back on this. I'm getting a little closer to figuring this out ... but no avail just yet.

              Okay. Seems like what I want to do at this point is:

              1) Find the client via the CLIENTS layout 

              2) Be able to click a button that takes me to a VISITS layout that has filtered the records to only the records of the found CLIENT (or, give me a blank record to start a new one if they haven't been seen before)

              Seemed easy enough, like I should just be able to find the CLIENT and then click a button that sends me over to the related records on the VISITS layout.

              So I created a button with a script for "Go to Related Record" and filled out the options to go to VISITS layout. Only, when I try it, nothing happens. 


              I'm still surprised that these basic functions seem so difficult to figure out. I feel like it should all be much easier. I'm not trying to do anything fancy! This is actually a really simple database.

              Any ideas for how to do this (find a client, click a button and go to the related VISITS on another layout), that would be most excellent and very much appreciated. 

              Also, if there are any websites or resources you know of that will help me learn FM, that would be great. The help menu hasn't been super helpful for me and learning FM doesn't seem to work naturally with my brain.

              Thank you so much,


              PS As far as I can tell, V10 does have script triggers.

              • 4. Re: Need help with Conditional Value Lists (I think.)

                Go To Related Record will not take you anywhwere if there are no related records in the related table.

                Do it this way:

                Set Variable[$$ClientID ; Value: Clients::ClientID ]
                IF [Not IsEmpty ( Visits::ClientID ) ]
                    Go To Related record....
                    Go To Layout [Visits]
                    New Record/Request
                    Set Field [visits::ClientID ; $$ClientID]
                End IF

                Note: a global variable (the two $$) isn't really needed for this, but you might want to use a global variable here, and then set up an auto-enter calcualtion on the Visits::ClientID field to auto-enter the contents of that variable. If you do so, you won't need the set field step in this script and any new record you create while on the visits layout will automatically be linked to the current client record on the clients layout.

                • 5. Re: Need help with Conditional Value Lists (I think.)

                  Oh yes, you might want to read this article on Go To Related record:

                  The Complete Go To Related Record