3 Replies Latest reply on May 2, 2014 12:37 PM by philmodjunk

    Filling Fields based on value list selections

    mdphillips

      Title

      Filling Fields based on value list selections

      Post

           Sorry if this has been asked before.  I'm trying to create a field calculation to auto fill based on a value list selection from a related table.  I set up a layout for data entry for a base camps.  I have related tables for the camps and base contacts.  I set up the portal section to populate the contacts table and it seems to be working fine.  My next goal is to have the "Current Staff" section auto fill based on selection from the position value field. In plain English what I want is:  If the "Position" value is "Commander" use "FullName" in the Commander field.  Unfortunately I'm still trying to grasp how to say that in filemaker. I would want to do the same for the phone fields.  Any suggestions would be greatly appreciated.

      Layout.jpg

        • 1. Re: Filling Fields based on value list selections
          philmodjunk

               In most cases, you would not store the same information (such as the commander name) in more than one table. If you do, updating that data becomes a problem and failure to correctly do that can compromise the data integrity of your database. That's why you usually take advantage of a relational database to refer to such data in the single record where it is recorded so that any updates (say to correct a misspelled name) only need be made in one field of one record.

               And such a calculation field is usually not needed if it is only used to show data from a related record.

               But I'm not sure that I understand the intended purpose of the middle table here (BoOs). Can you explain its purpose? The name does not provide me with any clue. And on what table did you base the layout you show? New Event or BoOs?

          • 2. Re: Filling Fields based on value list selections
            mdphillips

                 To fully explain is very complicated so here's my best short version.  When our group works an event we set up a base of operations (BoO).  Depending on the size of the event there may be multiple BoOs that we want to track - hence the BoO table. Each of the BoOs will have a commander and 1-2 logistic staff depending on the size of the event. If we have a prolonged event then the staff may change during the duration.  I need to add a status field to show whether the staff is active - just haven't done that yet.

                 Not to sound like a jerk, but many of the end users are gonna be computer "challenged" - some struggle with the spreadsheets we use now.  So my challenge is to utilize the capabilities of a DB but dumb down the interface for our "special" folks.  Since I'm new to dB design I'm not always sure how to achieve that special blend.

                 That being said, in the layout I'm working on the end user can add a BoO and staff without jumping to other tables and that part seems to be working correctly.   The intent of those fields is to quickly get a visual of the current staff and contact info without having to click a button, search a field or navigate away from this layout.  On the history tab my goal is to set up a timeline of sorts for that particular BoO so we can see when it was set up, torn down, and who staffed it at any given time during the event.

                 Does that make better sense?

            • 3. Re: Filling Fields based on value list selections
              philmodjunk

                   Knowing what a BoO is helps quite a lot, but I still am figuring out what you have here and whether or not it will really work. That has to be dealt with first as it can change the form of the answer to the question that you have asked.

                   From what you posted, I will assume that the layout shown is based on the BoOs table occurrence.

                   But I'm not convinced that your relationship between BoOs and Contacts will work in the long run. Isn't it possible that the same contact might need to be linked to more than one BoO? Such as a contact that works at two different BoO's at different times? And maybe different BoO's at different events?

                   That describes a many to many relationship where a contact would need to be linked to multiple BoO records even as a given BoO record needs to be linked to many contacts. This is typically handled by adding a join table not currently present in your current database design.

                   

                        If we have a prolonged event then the staff may change during the duration.

                   You may need another table of "shifts" linked to your BoO table with it linked to different contacts records. Short events would have a single shift, prolonged events would have more than one. That would tie in to your need to show a "history" where you want to know the staff at "any given time during the event".

                   This is what I'm currently guessing may work for you:

                   BoOs-----<Shifts>-----Contacts

                   BoOs::BoO_ID = Shifts::BoO_ID_fk
                   Contacts::Contacts_ID = Shifts::Contacts_ID_fk

                   Your portal on the BoOs layout then becomes a portal to Shifts Where one field in Shifts identifies the shift (time period) that the contact worked and another field in Shifts would show the role (such as commander) assigned to that contact.

                   Shifts, BTW, would serve as the Join table I referred to at the beginning of this post.

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained