7 Replies Latest reply on Jun 18, 2010 1:50 AM by stige

    Conditional Formatting based on data from multiple Fields

    stige

      Title

      Conditional Formatting based on data from multiple Fields

      Post

      Hi There,

       

      Very new forum member here (I've mostly been lurking) and novice, self taught Filemaker user.  I've done what I feel is reasonable due diligence in searching for similar questions but havn't been able to find any answers that suit my particular goals.

       

      My Info: FM Pro 9.0v3, Win XP SP3, Shared, likely will be published with IWP or CWP

       

      The database: It's basically a dispatch system.  The people taking the phone calls and assigning tasks have a pool of up to 20 people to pick from.  There is a field (w/ 20 reps) in which they enter the names of all the staff on duty at that time.  This is on Table #1 (Shift Log), a new record is only created once per shift.  Table #2 (Dispatch Form) contains all the information specific to each dispatch, new records are created for each individual dispatch.

       

      Table #1 has a unique serial number for each record.  When a new dipatch is created on Table #2 there is a related field that associates the  record with the current shift log.  Each dispatch has it's own unique serial number as well.  We generate about 2500-3000 records per week on Table #2 ( I don't know if this is pertinent for any solution, just thought it might be relevant).

       

      The question: I want the name on the list of available staff to change color if they are already on a job.  When the job they were on is closed out I would like the name to automatically change back to the regular text color so that other people looking at the shift Log will know that that person is available again.

       

      The field, mentioned above, w/ 20 reps is on Table #1 and named ::Available Staff.  I would think the conditional formatting should be searching two fields (::Assigned Staff (3 repetitions) and ::Job Status) on all the records in Table #2 where the Job Status is "Open", see which names are "assigned" and then change the color of their name on Table #1::Available Staff to indicate that those people are currently on a job.

       

      I've given this a go, but keep coming up short.  The closest I got was creating a field on Table #2 that showed all 20 reps from Table #1.  I was able to get the color to change on the related field in Table #2 but it would only look at the current record, not all the records for that shift.

       

      I hope my explanation was clear enough.

       

      Thanks for any assistance.

      -d

        • 1. Re: Conditional Formatting based on data from multiple Fields
          FentonJones

          I'm going to say how I'd do this. I would create another table, for "ShiftStaff". It would have one record for each person on a particular shift, with a Shift ID and a Staff ID (or name, but I'd use an ID). The relationship would be from Shifts, based on ShiftID; let's call it "shifts_ShiftStaff".

           

          So, in your case it would have approx. 20 records per shift. I would use this instead of your 20 line text field. It gives each the full status of a record, which makes many things easier. The relationship could have allow creation of related records, so data entry would be much the same as entering in your current text field. Or you could put a button to create a new row. It would appear on a Shift layout as a portal (instead of the text field, looking much the same).

           

          There would be a new table occurrence (TO) relationship from the ShiftStaff table to the Dispatches table (likely a new TO, as your current Dispatches TO comes directly off of Shifts (as it should). Let's name it "shifts_shiftstaff_Dispatches". The relationship would be on the Shift ID AND the Staff ID. There'd be no creation of records (the Dispatch already exists).

           

          You now have the ability to see what a particular Staff is doing on that Shift's Dispatches. In Distpatches you already have a Shift ID and Staff ID (or name); and likely a Closed field (Boolean?). 

           

          In the ShiftStaff portal on the Shift layout, you could conditionally format the Staff Name to change color based on whether that staff has been assigned to a dispatch for the shift, and/or all his/her dispatches had been closed. 

           

          Just how to do that, I can conceive, but I'd have to do it to see exactly what worked best. Questions are how to include the Closed (or calculated "not closed") field, and there may be Refresh issues. It may work better to use a calculation field for the name, in ShiftStaff, which used Text Formatting to color the field, rather than conditional formatting. You could also create "count" fields to show status.

           

          • 2. Re: Conditional Formatting based on data from multiple Fields
            stige

            Thanks Fenton.

             

            I've gotten this part way working with just using the Conditional Formating formula setup.  My issue was that it would only look at one record.

             

            I guess I'm looking for a way to make it expand it's view and look at all records that have an "Open" status.

             

            I actually think I follow the intent of your suggestion and could impliment it but it seems rather laborious and complicated for something I feel like I'm so close to achieving already.

             

            Any more thoughts?

            • 3. Re: Conditional Formatting based on data from multiple Fields
              LaRetta_1

              Using a related table instead of repetitions, as suggested by Fenton, is truly the way to go.  The Invoices demo by Comment (that he suggested) shows how to implement a relationship.  You need to understand this concept and use it otherwise you will keep hitting dead-ends with your information and process.  Using a relationship will save you hundreds of hours of work and relationships are NOT more complex and time-consuming (on the contrary).

               

              UPDATE:  Well I guess I dreamed reading the portion about Comment's demo (or it was on another post).  Basic Invoices demo

               

              Anyone wanting to understand relationships can use this demo to get a clear understanding of how they work.

              • 4. Re: Conditional Formatting based on data from multiple Fields
                stige

                Fenton Jones wrote:

                I'm going to say how I'd do this. I would create another table, for "ShiftStaff". It would have one record for each person on a particular shift, with a Shift ID and a Staff ID (or name, but I'd use an ID). The relationship would be from Shifts, based on ShiftID; let's call it "shifts_ShiftStaff".

                 


                 Okay, I gave this a thourough re-read and this seems very similar to what I was doing when creating conditional value lists.

                 

                Create tables with information.  Link that information to my main table with relationships.  I'm following the concept so far but I have one technical detail regarding our process.  Our staff are not limited to one shift.  We have about 50 some-odd people and due to vacations, sickness, special events, etc. they are rarely only on thier shift.  Flexing schedules and overte are quite common.

                 

                So, if I understand this properly, this would set it up so that when I set my shiftID on my main page it would load all my staff associated with that shift?  The place that breaks down is that the shifts are never consistently the same people.  Unless I misunderstand?

                 

                 


                LaRetta wrote:

                Using a related table instead of repetitions, as suggested by Fenton, is truly the way to go.  The Invoices demo by Comment (that he suggested) shows how to implement a relationship.  You need to understand this concept and use it otherwise you will keep hitting dead-ends with your information and process.  Using a relationship will save you hundreds of hours of work and relationships are NOT more complex and time-consuming (on the contrary).


                 

                Thank you for the link.  I took a look and have saved the file for reference.

                 

                While I have used relationships I fear that I do not truely understand the concept or potential.  Could you provide an example of how they could / would save me time and effort?  This may be one of those questions where the answer seems so obvious to everyone that it's hard to explain, but I'd appreciate any effort you have to spare on my education here.

                 

                Thanks again.



                • 5. Re: Conditional Formatting based on data from multiple Fields
                  LaRetta_1

                  "There is a field (w/ 20 reps) in which they enter the names of all the staff on duty at that time. "

                   

                  Why relationships over repetitions:

                   

                  1) Your current situation ... attempting to apply a (conditional) calculation to Available Staff (20 rep field) based upon Assigned Staff (3 repetitions) and ::Job Status become complex (if not entirely impossible in most cases).  As explained by Fenton, using a relationship will solve it easily. 

                   

                  2) Inability to group data ... If you wish to see a report grouped by staff member, you cannot.  If Bill and Mary share a shift (so are in same repetition) then they can't physically appear as two separate records in your report (first group being Bill's work days) and second group being Mary's work days).

                   

                  3) ODBC (until FM vs. 11) didn't support repetitions.

                   

                  4) Invariably, there is additional information you will want stored besides just who was on shift.  With repetitions, you would then need to add a new field (with matching multiple repetions) to hold this information.

                   

                  5) You hire a 21st staff member so now you must modify your field defition to 21 reps, modify your layouts where that field is displayed and probably modify many calculations you're sweater bricks over to solve (like your current issue).  If using records and relationships, adding new staff is simply adding a new record - no schema changes required.

                   

                  6) Repeating fields were used extensively before the days of the relational model.  They still have a vital place in our toolkits but should be used for developer functionality and not for storing data.

                   

                  7) You will find people every day who are trying to import data from repeaters into relational design to upgrade their solutions.  And 90% of top Developers will advise (strongly) against their use of holding data.

                   

                  I once had it explained like this ... why group things together initially only to break them apart  later (which is much more difficult)?  By breaking 'like' data into finite parts to begin with (records in a related table), you will never be limited.  But you are correct that many of the reasons are more subtle but each time you hit one of those subtleties, you will mutter, "Yep, here's another thing I can't do without a lot of trickery if even possible at all."

                   

                  And mind you ... I'm not against repeaters; in fact, I started the Repeater Society  and the post by bcooney (Barbara) in that thread still cracks me up and it is appropriate that it be posted on FileMaker's website.  Relationships rule our world and rightfully so.

                  • 6. Re: Conditional Formatting based on data from multiple Fields
                    FentonJones

                    stige wrote:

                    "So, if I understand this properly, this would set it up so that when I set my shiftID on my main page it would load all my staff associated with that shift?  The place that breaks down is that the shifts are never consistently the same people."

                     

                    Actually I said very little about adding the staff to the (join table for) the current shift. If you have only a few people (which it sounds like you do), you could just enter them directly in the portal for that shift, using a drop-down of their IDs, showing (only) their names. It would/could look just like your repeats, but be (much) easier to use.

                     

                    It is also fairly easy to move the data in your existing repeats into their own table. It is an option in the 2nd dialog of an Import step, to "split repeating fields into records" (be sure to include the Shift ID). You can create the table at the same time.

                     

                    If this forum supported file attachments, we could fairly quickly do it for you, if you posted an example file. Alas it does not. (It's a bit lame, in my opinion).

                    • 7. Re: Conditional Formatting based on data from multiple Fields
                      stige

                      ^^ Thank you, LaRetta.  Good points, all, and I even understood them!

                       

                      Regarding Fentons first reply - I broke the repeating field up in to 20 individual fields (On Duty_1 thru 20) and created another table (Z_Available Staff) with 2 fields (Staff_Number and Staff_Name).  I am only using a unique serial # in steat of a Shift ID (i.e. - day, noon,  night, etc) because people constantly swap shifts for various reasons.

                       

                      I put the 2nd table in 'table view' and created 1 record for each employee.  I have 131 records.  About 1/2 of these are part time or 'as needed' but I added 'em anyways.

                       

                      The main (TO) of this table has a relation from Staff_Number to the Staff_Number field on the Shift Log table.

                       

                      And now I'm going to sleep  =)