3 Replies Latest reply on Nov 30, 2010 11:25 AM by philmodjunk

    Newbie question

    AntonTeterine

      Title

      Newbie question

      Post

      Hi all,

      I have new developer question, which might be really easy or might be quite complicated to deal with.

      I have table Registrants where I have number of records (over 100) of people registering for event. They choose different options (activities such as skiing, snowmobiling etc) for 7 days for AM and PM. So I have bunch of fields named in this fashion: Day1_AM_act_name, Day1_PM_act_name etc. Each of this fields stores name of the chosen activity.

      I also have options for those activities, and there might be as little as one or as many as 10 options users select. Therefore I have fields for each day, each time slot and each option named as: Day1_AM_opt0 - Day1_AM_opt9, Day1_PM_opt0 - Day1_PM_opt9 etc. These options are presented as integer numbers 0, 1, 2, 3 etc.

      I now need to represent them as names, rather than numbers on one of the layout. So skiing 0 would represent say private lesson etc.

      I have created Activities table, which has act_name field which has name of the activity and opt_0 - opt_9 fields which represent names of those options.

      What I need to do is output value of the field opt_# from Activities table where act_name equals Day1_AM_act_name and # is value from Day1_AM_opt0 into the field named Day1_AM_opt0_name

      After that I will duplicate fields Day1_AM_opt0_name into ***_opt1_name etc.

      I hope this makes sense. If there is an easier way of manipulating data I would appreciate suggestion.

      Thank you!

      Screen_shot_2010-11-30_at_10.57.33_AM.png

        • 1. Re: Newbie question
          philmodjunk

          If there is an easier way of manipulating data I would appreciate suggestion.

          That's the key question here as your current design would require making a very large number of relationships to your activities table, one for each option field.

          Instead, look up "Portals" in FileMaker help. You should define a table, Registered_Activities, separate from Registrants to be used for logging each planned activity. A record in Registered activities would log a single activity for a specified registrant and time so you would only have one field in this record to link to the activities table in order to display the activity name.

          Registrants---<Registered_Activities>----Activities (---< means one to many)

          • 2. Re: Newbie question
            AntonTeterine

            Thank you very much for suggestion. At this point we have over half of people who already chose their options, so half data is already there. The other half have not. Data input is done through website, which uses complex JavaScript and PHP to feed information into database as well as pulling data out to allow people to log back in and make changes. 

            Based on this, unfortunately, separating data into another table is not an option. 

            What about a suggestion on calculation?

            Logically it should be

            Registrants::Day1_AM_op1_name = Activities::opt_ + "Registrants::Day1_AM_opt1" from record where Activities:act_name = Registrants::Day1_AM_act_name

            • 3. Re: Newbie question
              philmodjunk

              I don't think you have a reasonable option but to add a related table. There are ways to script the transfer of data from your current file into the related table.

              Your calculation field will hit the same limitation as far as I can see.

              If I've analyzed your original post accurately, you have 20 options per day for 6 days ( the 6 tabs that I see). That's 120 different options fields, and that in turn requires 120 different relationships, one for each option field. See how ugly that's going to be?

              I suppose you could script a lookup, but this will be far from the best approach for implementing this. Both the 120 relationships and a scripted lookup would be far from ideal design choices.

              This isn't just an issue for setting up a relationship to activities in order to show the name. Any reports where you list the scheduled activities, any searches for all registrants who have signed up for a given activity that can appear in multiple option fields will hit similar issues that can't be solved short of either a table redesign or incredibly cumbersome work arounds.