11 Replies Latest reply on Aug 16, 2010 9:47 AM by philmodjunk

    Conditional Value List in Portal - I'm not getting it :(

    RandyS_1

      Title

      Conditional Value List in Portal - I'm not getting it :(

      Post

      Greetings,

      I'm using the Invoices Starter Solution in FM 11 to create a database for a dance studio. The analogy being, customers are students, products are classes. The wrinkle is that the classes are seasonal and when I want to select a product (class) from the line item portal on the invoice it shows a list of all products (classes). I just want to show the ones for the current season.

      So I have created a "Season" fields in classes database with values like this. "2009-2010", "2010-2011".

      I have set up a "Current Season" field in the User Preferences table and it is set to "2010-2011".

      When I create a new invoice I auto enter the current season into a new Season field in the Invoice table. I may not need to do this but I've been trying different relationships to filter the class list to the current season.

      On the Invoice layout the portal shows the Line Item values linked by the product ID.

      The value list drop down list on the Product ID uses the "Related Products" relationship.

      Well I'm not quite getting the connection between the relationships I setup and the conditional value list thing in the portal. I've been going through other forum posts on this and I apologize but I'm just not "getting it".

      I just want my value list to be the class names from the current season. Do I need to do something like alter the Related Products relationship to include an extra condition where, ie Season = Current Season?

      Any help is much appreciated.

      Cheers,
      Randy

      PS: Do you know where I can find an example of how this type of conditional list is built on the fly using calculations in the Advanced version?

        • 1. Re: Conditional Value List in Portal - I'm not getting it :(
          philmodjunk

          Answering your PS first, Both advanced and regular versions of filemaker pro handle value lists in the same manner. Set up correctly, a change in the "category" field used in the relationship will immediately result in a different set of values in the conditional value list--which is how I read "on the fly" in your post.

          The key to getting a conditional value list working correctly depends on the one part you didn't describe about your value list setup: When you use the "show only related records" option, you also have to specify a "starting from" table occurrence. (A table occurrence is one of the "boxes" found on your relationship graph in Manage | Database | Relationships.)

          Think of conditional value lists this way: If you can get a portal to list only the desired values on your layout, then the same relationship can be used in a conditional value list so that they list the same values you see in the portal. Normally, the "staring from" table should be the table occurrence specified in "show records from" in Layout Setup... though there are exceptions and this can be one of those exceptions.

          In your case, you have Season in one table that is separate from both invoices and the line items records in your portal. That will work if you have a relationship linking Invoices to user preferences and you specify user preferences as the "starting from" table occurrence. You'll also need a relationship linking user preferences::Season to Products::Season. I'd use a relationship between user preferences and invoices that uses the X operator instead of = so that any invoice will match to any record in your user preferences table.

          • 2. Re: Conditional Value List in Portal - I'm not getting it :(
            RandyS_1

            Thanks PhilModJunk,

            I kind of get it but the light hasn't quite gone on yet in my head. I'm waiting anxiously for that "ah-ha" moment as I try to implement what you've said as well as what I've culled from other posts.

            One think I keep thinking is kind of the opposite of what you said.

            "If you can get a portal to list only the desired values on your layout,  then the same relationship can be used in a conditional value list so  that they list the same values you see in the portal"

            ... is that if I can get a conditional value list to only show the classes for that season I wouldn't have to worry about what the portal is filtering for for the line items.  So long as the portal is linked properly to the Related Product IDs  (or class IDs as I have changed it), then that should be fine. Know what I mean?

            I guess it all comes down to a relationship anyway.

            Cheers,

            Randy

            • 3. Re: Conditional Value List in Portal - I'm not getting it :(
              philmodjunk

              Your last sentence is key.

              What I was trying to say was that the same relationship that enables you to put a portal that only lists records for products in the specified season is exactly the same relationship you'd use in the value list setup to list only the values for the specified season.

              In fact, before we had conditional value lists, this was pretty much our only options short of a plug in for implementing a conditional value list.

              Here's a demo file, I played with to check my assumptions before I made my first response to your question:

              http://www.4shared.com/file/VunrcSr7/SepTableCondValueList.html

              Note how the portal and value list on Main show exactly the same list of values. You can use the Preferences layout to change the group of values that appear in both. If you check the design of the value list, you'll find it uses the same relationship as the portal.

              • 4. Re: Conditional Value List in Portal - I'm not getting it :(
                RandyS_1

                Thaks PhilModJunk,

                I get this file :)

                I need to modify it such that the popup with the classes for the current season is in the invoice portal showing the line item records. The popup retrieves the Class ID and populates the rest of the fields in that row. Recall that I'm using the Invoice Starter Solution in FM 11.

                I'll let you know how it turns out. I'm off to a cottage this week so you may not hear for  a week.

                Cheers,

                Randy

                • 5. Re: Conditional Value List in Portal - I'm not getting it :(
                  RandyS_1

                  Hi PhilModJunk,

                  Thanks for your sample. I see how your file works but I can't reproduce the same in my file where portal creates line items in the invoice that are filtered for the category, or in my case the season. I'm trying to inject a line items table in your sample to see if I can isolate the parts I need in my other fp7 file.

                  I've uploaded to my file to my server for reference. (I tried the free 4shared.com service but its free service is a slow upload. 0.1KB/s, but I'll post that link when its done. The starter solution is  beefy compared to your sample.)

                  http://filemaker.greatbear.ca/dancestudio_sample.fp7 ... 1.7 MB
                  http://filemaker.greatbear.ca/dancestudio_sample.zip ... 0.3 MB

                  Recap: I started with the Invoices Starter Solution and added a Seasons table and a Current Season field to the User Preferences. When I create a new invoice I auto-enter the current season value in the Invoices::Current Season Field.

                  From this point on I want the drop down list of classes in the portal rows to only show the classes for the current season. Pretty sure you understand all that but I'm repeating myself to make sure it makes sense. :)

                  This file is  a bit messy since I've been smashing away on it, sorry about that, but there are 2 seasons of class data to work with.

                  Cheers and TIA,
                  RandyS

                  • 7. Re: Conditional Value List in Portal - I'm not getting it :(
                    philmodjunk

                    I'd already downloaded the sample from your earlier link...

                    There were several things not set up quite right in your file.

                    In my sample, the preferences table was located between invoices and Lineitems so the value list could be set up correctly. You'd need a second relationship and table occurrence to implement the line items portal.

                    Main (invoices)---x-----preferences-----Lineitems

                    Your file makes things easier by setting up a field that auto-enters the current season and we can use that in a simpler arrangement to set up the value list.

                    I made a new table occurrence of Products, Related Products for Season and linked it in this way:

                    Invoices::__fk_season = Related Products for Season::season

                    I then when to the Invoices layout and modified the Class ID value list used with LineItems::ClassID so that it was set up like this:

                    Column1: Related Products for Season::ClassID //you had LineItems::ClassID and this is the wrong field for this
                    Column2: Related Products for Season::calc_name
                    Include only related values starting from invoices //this option was not selected in your copy
                    Show only values from second field

                    For testing purposes, I formatted __fk_season with the seaons value list so I could easily pick different seasons and see just the classes for that season appear in the Class ID value list.

                    • 8. Re: Conditional Value List in Portal - I'm not getting it :(
                      RandyS_1

                      Hi PhilModJunk,

                      Or should I call you Phil? Anyway, the solution seems obvious now but I'll chalk that up to being rusty in  Filemaker which I haven't used since version 7. When you throw in  multiple table occurances and portals and lists it starts to get kind of  abstract.

                      I have it working now, thanks a ton for your speedy responses. Now I can start cleaning this mess up and massaging it into a reall app.

                      Thanks,

                      Randy

                      • 9. Re: Conditional Value List in Portal - I'm not getting it :(
                        philmodjunk

                        Here's a link on table occurrence's that may help you sort things out a bit. Since you are already familiar with filemaker, you may want to drill down to the second article which discusses how filemaker table occurrences are misnamed as tables throughout the system--yet using them corrrectly is vital to getting the desired results.

                        http://forums.filemaker.com/posts/60a91f2dd7  Oops, wrong link. See:  

                        Tutorial: What are Table Occurrences?

                        • 10. Re: Conditional Value List in Portal - I'm not getting it :(
                          RandyS_1

                          Thanks for the help. I checked that link about TO but it goes to a thread about "Easier, Better Script Posting". You must be multi-tasking.

                          Cheers,

                          Randy

                          • 11. Re: Conditional Value List in Portal - I'm not getting it :(
                            philmodjunk

                            I have a little table of "useful" links hidden in my Known Bugs database. I must have copied from the wrong record. Here's the link:  

                            Tutorial: What are Table Occurrences?