7 Replies Latest reply on May 11, 2017 7:58 AM by philmodjunk

    Can conditional formatting be used with 3 fields between 2 tables?

    MrsLLM

      I have two tables, Employees (EEs) and Locations.  The tables are joined by District No.

       

      Wanted result:  The calculated address and AKA fields to populate from the Locations table on the EEs table.

       

      Every person works in a specific District.  Every district has a unique District No. and has numerous locations, some have nicknames, which I labeled "AKA".

       

      Action:  Employees can only see and select an address based on their District No.  After an address is selected within the district, the calculated address and AKA fields populate under their name.

       

      Not sure if conditional formatting, scripting or another avenue is better.  FYI:  Not an experienced script writer, yet - still learning Filemaker Pro Advanced 14.  Any help or suggestions are greatly appreciated.  Thank you!

        • 1. Re: Can conditional formatting be used with 3 fields between 2 tables?
          philmodjunk

          I'm puzzled by your post.

           

          I don't see any need for any conditional formatting.

           

          I don't see any obvious need for calculation fields in EE to show data from Locations.

           

          Given the relationship described, you can simply include the address fields from locations on the EE layout.

           

          So please explain more why you need conditional formatting and why you need that calculation field. (There are reasons why you might set up such a calculation, but I don't see that reason yet in what you have posted.)

          • 2. Re: Can conditional formatting be used with 3 fields between 2 tables?
            MrsLLM

            The database is new and I don't know where everyone physically works across the state.  We (IT staff, service techs and myself) need to know the physical address for equipment deployment, servicing and travel purposes.  The employees will not have access to the Locations table to keep uniformity.  The calculated address field will appear on employees info page.  The AKA field is more info on the yard type (construction, electrical, maintenance, etc.) - also not accessible to the employees.

             

            I hope this helps.  Thank you!

            • 3. Re: Can conditional formatting be used with 3 fields between 2 tables?
              philmodjunk

              Sorry, not one bit. If by 'access' you mean that employees will not have access to a locations layout and will not be allowed to edit data in the locations table, this makes sense but does not change my response.

               

              No conditional formatting is required.

               

              No calculation fields are needed.

               

              You can do one of two things to your EE based layout:

               

              1. Use the insert menu to add the address fields as merge fields to your layout.
              2. Add regular field objects from locations to your layout and then use "behavior" options in the inspector's data tab to deny Browse mode access to the fields.

              Both put the data on your EE layout in a form that cannot be modified and which only shows data for the current EE record--just as would a calculation field.

               

              Merge fields make it easy to merge the data from the different fields with each other and with layout text to produce a "smooth flowing" single block of text without any gaps.

              Regular fields are easier to work with when setting up your layout and are just fine for a more "Fill in the blanks form" type of layout design.

              • 4. Re: Can conditional formatting be used with 3 fields between 2 tables?
                MrsLLM

                Thank you, I'll try your suggestions tonight.

                • 5. Re: Can conditional formatting be used with 3 fields between 2 tables?
                  MrsLLM

                  No success yet.  I am still learning FileMaker verbage and how to use FM (efficiently) in designing.  I'll try to explain it better.

                   

                  Only a handful of IT staff and myself will manage the Locations table.

                   

                  Every employee physically works at a location within a district.  The district has several locations that an employee maybe assigned.  There are 13 districts.  (Ex:  District 1 (D01) has 39 locations and an employee works at 1 of the 39).  IT and I want to know where they physically work so we can schedule and provide service in the area better.

                   

                  The full address ("Street Address", "City" and "Zip") should appear as a "drop-down list" or "Pop-up menu" (not sure which is best) for the employee to select.  The address choices should be limited/restricted by the district the employee is assigned.  However, some locations have nicknames ("AKA Location Name") which is an option to also getting the full address. 

                   

                  I thought the conditional formatting would provide the best result. 

                   

                  Thank you for your assistance.

                  MrsLLM

                   

                  No success yet.  I am still learning FileMaker verbage and how to use FM (efficiently) in designing.  I'll try to explain it better.

                   

                  Every employee physically works at a location within a district.  The district has several locations that an employee maybe assigned.  There are 13 districts.  (Ex:  District 1 (D01) has 39 locations and an employee works at 1 of the 39).  IT and I want to know where they physically work so we can schedule and provide service in the area better.

                   

                  The full address ("Street Address", "City" and "Zip") should appear as a "drop-down list" or "Pop-up menu" (not sure which is best) for the employee to select.  The address choices should be limited/restricted by the district the employee is assigned.  However, some locations have nicknames ("AKA Location Name") which is an option to also getting the full address. 

                   

                  I thought the conditional formatting would provide the best result. 

                   

                  Thank you for your assistance.

                  MrsLLM

                  • 6. Re: Can conditional formatting be used with 3 fields between 2 tables?
                    philmodjunk

                    Conditional Formatting is not what you need.

                     

                    The simplest solution is to set up a "use values from field" value list where field 1 is the location ID and field two is the name or address of the location. That field 2 is likely a text field with an auto-enter calculation that combines data from several fields in your location table so that a user can select from the list of location names and/or addresses, but the value list enters the ID. This value list need not be a complete address it just needs to provide enough info so that it's unique and the user can tell which location is which.

                     

                    More sophisticated options that limit the list to locations for a selected district and/or allow a user to enter search text to limit the list are also possible. I'll provide some teaching files on this in my next post.

                    • 7. Re: Can conditional formatting be used with 3 fields between 2 tables?
                      philmodjunk

                      Here are the files that you might find helpful:

                       

                      Adventures in FileMaking #1--conditional value lists

                      Adventures in FileMaking #2--enhanced value selection

                       

                      The first has several different working examples of exactly what you asked for, hierarchical conditional value lists. The second has working examples of a filtered portal that updates as you type.

                       

                      Both files come with detailed documentation on how they are set up and how they work.