6 Replies Latest reply on Jan 14, 2009 10:32 AM by davehebb

    Value list?  Related fields?

    davehebb

      Title

      Value list?  Related fields?

      Post

      I have a complicated (for me - I'm a newbie to databases in general, and FMP in particular) question - I need to create two separate fields that can be independently searched, but are somehow "attached" to each other.  I have 3 basic types of text fields, all of which are related to "trade shows" that each client attends.  They are grouped by city, each city having its own separate list of shows.  So, for example, the list of shows in NYC might have a list of 10 separate shows, each client may attend 0-10 of these shows .  Here's where it gets complicated. Each show is done 1-5 times a year, and should also be listed by month - each show has it's own schedule, so there should be a related but separate field titled "month", which would be conditional.  So, for example, if a client attends the "Shoes and Accessories" show, there might be an option to select 1-4 months, such as Jan., Apr., Jul. and Sep.  That same client might also attend the "Women's Fashion" shows, which might be held only in Jan. and Sep. (different conditions.)  I think I can figure it out so far, but here's where I'm lost - I need to be able to "attach" the date separately and specifically to each show, perhaps as a checkbox or dropdown list, but I need to be able to do a Find for all shows in a particular month.  So, for examply, if I have each show listed, with a "sub category" field of the dates, I need to be able to search all date fields across all shows!  I need to find all clients that go to any show in Jan., for example.  Also, across cities, which adds a third dimension to it all.... I'm so over my head here, it makes my brain hurt.... I've been using the VTC tutorials, but there is nothing like that in there, or if there is, I fail to see how to make it work. 

       

      I'm not really confident with scripting and calculations, but I might be able to muddle my way through it if I understood the big picture of how things are related....  Anybody have a clue?

        • 1. Re: Value list?  Related fields?
          Jens Teich
             Missing table?

          I assume you have two tables yet: clients and trade shows. You need a third: attendees!

          Jens

          • 2. Re: Value list?  Related fields?
            davehebb
              

            You're already way ahead of me ... I only have one table, "Contacts", which has all the records of individual clients/companies.  I have a field on this table called "shows" which is a check box field of about 9 shows.  Now, I need to break it out to include 1 or more "months" for each show.  I could have the months as a separate table, and then I guess to validate which months are available for each particular show, I could make some kind of script (or calculation I think) that would only give the correct month options for each particular show.  So, assuming I can figure out how to do that, the questions are:

             

            1) do I need a separate table for "months", or can I just use a defined list and what's the difference really? - I need the option to be able to add new "months" and "shows" as they may change in the future.


            2) How do I display each show as a separate entity, each with  its own "sub set" of months and still be able to search for all clients that attend ANY shows in the month of Feb.?

             

            3) Is the "attendees" table you refer to populated by a calculation of some kind?  I can't really understand how the "attendees" is relevant without "months" somehow attached.

             

            4) Is this really as complicated as it seems to be?

             

            Here's kind of what I envision it to look like, with  ( ) to show and empty check box, and (x) to show a filled one (just for one city - I can create a tabbed layout to include the other cities)

             

            Client #1:

             

            (x) Show A     ( ) Jan.

                                 (x) Mar.

                                 ( ) Jun.

             

            (x) Show B      (x) Feb.

                                 (x) Jun.

                                 (x) Aug.

                                 ( ) Oct.

             

            ( ) Show C      ( ) Jan.

                                  ( ) Aug.

                                  ( ) Dec.

             

             

            Client #2:

             

            (x) Show A     ( ) Jan.

                                 (x) Mar.

                                 (X) Jun.

             

            ( )  Show B     ( ) Feb.

                                 ( ) Jun.

                                 ( ) Aug.

                                 ( ) Oct.

             

            (x) Show C     (x) Jan.

                                  (x) Aug.

                                  ( ) Dec.

             

             etc. etc. - you can see how ugly it will look, not to mention the complications of having each have it's own set of months that refer to the same table of months... or maybe it makes sense to you, I'm just lost....

             

             

            I had this idea that this kind of "nested data" would be a common task, but I have no idea how to approach it, nor do I even know how it should be displayed... As I've stated before, I'm seriously new and in over my head - act like you're talking to a 10 year old child...

             

            • 3. Re: Value list?  Related fields?
              Jens Teich
                 Tables: clients, attendees, months, shows

              table client:
              ID: 1, Name: Joe
              ID: 2, Name: Mary

              table shows:
              ID: 1, Show: test show A
              ID: 2, Show: test show B

              table months
              ID: 1, ID_Show: 1, Month: 2009-01 - Show A in Jan 2009
              ID: 2, ID_Show: 1, Month: 2009-05 - Show A in May 2009

              table attendees
              ID: 1, ID_month: 2. ID_client: 1 - Joe visits show A in Jan 2009

              Jens








              • 4. Re: Value list?  Related fields?
                davehebb
                  

                Okay, That was way over this 10 year-old's head, but I think I got a little bit more understanding - but I'm not sure how to do several things:

                 

                - the _kp and _kf relationships (I think I sort of have it, but not totally)

                 

                - creating the value lists and showing them as related fields from the other tables

                 

                - The attendees table - is this just a related fields table with a portal to the 3 other tables?  Are "attendees" records where I actually see which client goes to which show on what dates?  Is it all just in one portal per record?  I'm really confused....

                 

                Anway, I attempted to implement the 3 table relationship with other table occurrences ( I write this as if I know what I'm talking about, but I don't really...) and I'm gonna try to attach the file, but I can't see how - I guess I'll just upload it and post a link to the file online somewhere...

                 

                Here it is:

                 

                showtest.fp7

                 

                Please lemme know what you think - feel free to edit, but please tell me what you did!

                 

                 

                 

                • 6. Re: Value list?  Related fields?
                  davehebb
                    

                  :robotsurprised:  Oooooh that made my head explode....in a good way, I suppose.  So much to learn, so little time.  It looks to me like I have inadvertently created a multi-tiered table, but not as ONE table, but as four.  I'm not sure that that will matter much, but I'm really just blundering my way through this.... I still don't quite get how to actually see the data and edit which "show" can occur on which "months", and then how to tie that to each "client" record.  Also, how do I display all that in straightforward manner?  A portal?  Should it be locked to not allow editing, as described in the White Paper?  It seems that this is getting way beyond me, and even the White Paper itself seems to be referring to things that I don't totally understand.  Maybie I'm actually a pre-newbie... with just enough knowledge to be dangerous.:smileywink:

                   

                  Anyway, any further input or suggestions would be greatly appreciated!