4 Replies Latest reply on Jun 2, 2014 10:47 AM by Scottiet

    Need a simple solution!

    Scottiet

      Example.JPGHi

       

      I am relativly new to FM13 and have attended the basic training courses.

       

      Although i understand various points and applying them to a new database i have run into one of those 'Moments' that i cannot seem to see past, i'm sure it is simple but i need a nudge in the right direction before i proceed.


      Here is the scenario:

       

      We have to record a Video conference event, this has a unique ID issue to it

      Within this event we have the requirement to record each VC UNIT in attendance (Value List)

      Each VC UNIT needs to associate the number of participants and identify them by their ROLE (Value List) (1 Selection I.E. Dropdown list)

      In turn each ROLE has to record the SECTOR (Value List) (Can be Multiply selections I.E. CheckBox)

       

      I just cant seem to establish what tables should be set up and what relationships there should be?

       

      I have attached a picture hopefully to make it easy to understand.

       

      If anybody could give me a solution or a direction it would be very much appreciated.

       

      Thank you.

        • 1. Re: Need a simple solution!
          erolst

          Hi Scott –

           

          welcome to FileMaker – and this forum, of course!

           

          Having said that: what you need is not a simple, but a correct structure, that is, one with a correct data model as per your description. It should be as simple as possible, but as complex as required …

           

          According to your description, that would probably comprise the following tables in this structure:

           

                                                                       People

                                                                          |

                                                                         ^

          VideoConferences --< VCUnit --< Attendants ( >-- Roles (or just as an attribute in Attendants) [ --< Sectors ; a child table of Roles or Attendants?? ]

           

          This data model allows you to …

           

          • store a video conference

          • add any number of units to each conference

          • add any number of attendants (a person with a role) to each unit (of every conference)

          • add any number of sectors to …well, your napkin format specs say 'Role', but in the model I suggested above that that ‘role' would probably be played by 'Attendants'

           

          Hard to say without knowing what a “sector” is.

           

          Does this give you a clearer picture?

           

          Scott Taylor wrote:

          In turn each ROLE has to record the SECTOR (Value List) (Can be Multiply selections I.E. CheckBox)

           

          I would try to very clear about what a sector is and what you need to do with a Role's/Attendant's sector values once you've entered them.

           

          For data entry, a check box field is (or seems to be) a terrific tool, but it can be very problematic if you need to summarize the multiple values that will be stored in it, or want to perform other kinds of batch processing on them.

           

          For such a scenario, related records (as shown above) are always the better choice – and the more so you if you want to be able to say more about any 'sector' than just that such things exist in your database.

          1 of 1 people found this helpful
          • 2. Re: Need a simple solution!
            Stephen Huston

            Some of these level need to be related records, but others are simply attributes (fields in the related record).

             

            Can you clarify what Sectors and Roles are part of, the Event or the Unit or something else?

             

            You might have a setup like either of these, depending on which attributes are tied 1-to-1 to which records:

            • Event -> Sector -> Unit. with various attributes at each level, or
            • Event -> Unit -> Sector. also depending on which attributes go with which.

            It sounds to me like Role is an attribute of a Unit, but your specialized terms are not clear enough to me to be certain which attributes go which record level.

             

            Attributes (fields) are anything which has a 1-to-1 relationship with the records in that table, so sorting out the difference between Record levels and their attributes is the real key to defining your data structure.

            1 of 1 people found this helpful
            • 3. Re: Need a simple solution!
              Scottiet

              Thank you for such a quick response.

               

              So as an example of the data to be captured would be something like the following:

               

              Video Event ID (Unique)

               

              Participants/Presenters (Probably from a look up table that you can add names too)

               

              Video Unit ID (This would be all of the physical sites that are part of the conference, static value list)

              Witin each Video Unit we would need to capture:

               

              Role (One selection from a drop down value list)

              Example: Audiolgist

               

              Sector (can be multiply Job Sectors that are linked to the Role)

              Example: Health and Developmental Services

               

              Number of Participants (This would simply be the number of participants that are in that role, this number will be totaled later)

               

              So, it should look something like this:

               

              Event ID           Presenters                                VC Unit               Role                         Sector                                        # Of Participants

              12345               John Smith, Paul Jones             Toronto               Audioligist                 Health, DS Services                         2

                                                                                                                     Dentist                       Health, Oral Care                             4

                                                                                                                     Social Worker            DS Services, Mental Health             1

                                                                                         

                                                                                          Ottawa                Dentist                       Oral Care, Health                             3

                                                                                                                     Direct Support           Justice, Health, Social                      2

               

                                                                                          London               Manager                    Health, Justice                                  1         

                                                                                                                     Speech Worker         Health, Mental Health                        2

               

              Potentially, an event could contain 2 or 100 VC Units, the same for the ROLE associated with the VC Unit.

               

              I'm still learning scripts etc, and believe i would have to also create some form of 'Loop' to enable each record to be created?

               

              Hope this helps visualise the information i need to capture.

              • 4. Re: Need a simple solution!
                Scottiet

                Thank you for such a quick response and the Welcome

                 

                I look forward to being able to contribute more and more as i learn.

                 

                So as an example of the data to be captured would be something like the following:

                 

                Video Event ID (Unique)

                 

                Participants/Presenters (Probably from a look up table that you can add names too)

                 

                Video Unit ID (This would be all of the physical sites that are part of the conference, static value list)

                Witin each Video Unit we would need to capture:

                 

                Role (One selection from a drop down value list)

                Example: Audiolgist

                 

                Sector (can be multiply Job Sectors that are linked to the Role)

                Example: Health and Developmental Services

                 

                Number of Participants (This would simply be the number of participants that are in that role, this number will be totaled later)

                 

                So, it should look something like this:

                 

                Event ID           Presenters                                VC Unit               Role                         Sector                                        # Of Participants

                12345               John Smith, Paul Jones             Toronto               Audioligist                 Health, DS Services                         2

                                                                                                                       Dentist                       Health, Oral Care                             4

                                                                                                                       Social Worker            DS Services, Mental Health             1

                                                                                           

                                                                                            Ottawa                Dentist                       Oral Care, Health                             3

                                                                                                                       Direct Support           Justice, Health, Social                      2

                 

                                                                                            London               Manager                    Health, Justice                                  1         

                                                                                                                       Speech Worker         Health, Mental Health                        2

                 

                Potentially, an event could contain 2 or 100 VC Units, the same for the ROLE associated with the VC Unit.

                 

                I'm still learning scripts etc, and believe i would have to also create some form of 'Loop' to enable each record to be created?

                 

                Hope this helps visualise the information i need to capture.