1 2 Previous Next 22 Replies Latest reply on Oct 22, 2013 10:10 AM by philmodjunk

    Conditional Fields With A Catch

    GarthBeast

      Title

      Conditional Fields With A Catch

      Post

           Hi all,

              I'm trying to create a conditional field with a catch.  Basically my company works with many different creative professionals with different titles (directors, producers, editors,etc).  I have my filemaker database setup so I can sort by title, but I want to be able to add a "Preferred" field, so we know which person in each title group we prefer to work with.  I'm wondering if I can setup a conditional "Preferred" field based on title, that will also only allow one person in each field to be preferred?  In other words, if we decided to change X director to preferred then the old preferred director, Y will automatically change to not preferred?

           I hope this makes sense and thanks for your help

        • 1. Re: Conditional Fields With A Catch
          philmodjunk

               This seems pretty easy to set up, but the devil is in the details and we need more before we can suggest a solution.

               What tables are you using for this data. If more than one table, how are they related?

               If you have a single table, does that mean that you have multiple fields in each record for this information and you need to specify a single field from that set of fields? And will that single field from the set be just for a given record or over multiple records?

               Is this a file used by only one user at a time on a single machine or is it hosted over a network?

               And if it will have multiple users, will the preference specified apply to all users or could they be unique for each user?

          • 2. Re: Conditional Fields With A Catch
            GarthBeast

                 I'm only using 1 table for this data, 1 contact list.  Yes, I have multiple fields in each record and I need to specify a single field from that list. I have a field called "Primary Discipline," here I write in (not a pulldown list) whatever creative title the person holds (director, photographer, etc).  For each of the titles I want to be able to specify a single person as a primary hire...ie I may have 40 directors in my contact list but I want to be able to specify 1 as a "First Call", and I want to be able to do this for each title (so first call for producer, photographer, etc).  The thing I need help with is creating a conditional field so that will only allow 1 first call designation for each job title.  So, when I designate a new person as a first call director, for example, whoever was the previous first call director will automatically lose that designation.  Hopefully that makes this problem a little clearer for you.  I appreciate your help!

            • 3. Re: Conditional Fields With A Catch
              philmodjunk

                   The design of your database is still not clear. Why do you have multiple fields in one record? Why do you hand enter the title instead of using a value list? (this leaves you open to problems caused by incorrectly typing the title...)

                   What I think you have specified is that if you have say "Director" in any one of several fields and you want to be able to designate that record as the preferred "director" choice over all the records in your table.

                   But the multiple fields in one record for this greatly complicate that and should not be necessary if you properly design your tables. To suggest an alternate design requires understanding why you currently are using multiple fields in one record for this.

                   And these key questions are still not answered:

                   

                        Is this a file used by only one user at a time on a single machine or is it hosted over a network?

                   

                        And if it will have multiple users, will the preference specified apply to all users or could they be unique for each user?

              • 4. Re: Conditional Fields With A Catch
                GarthBeast

                     Sorry Phil, maybe I'm just not correctly understanding what you mean by "record" in relation to how many fields I have.  What I mean is that my database obviously has many different fields (Name, location, title, rate of pay, etc), but there is only one field for title called "Primary Discipline".  

                     The reason I have chosen not to add a value list yet is because about 75% of the database entries have been imported from excel spreadsheets and there are so many different titles and ways of writing each title (Producers Assistant is sometimes written PA, among others) that I need to spend some time cleaning up how the different titles are written in the thousand or so entries I have so far.

                     As for the key question, this is a file used by only one user at a time on a single machine.

                     Thanks again for your help and sorry for being unclear.  My grasp of Filemaker lingo isn't strong at this point. 

                • 5. Re: Conditional Fields With A Catch
                  philmodjunk
                       

                            there are so many different titles and ways of writing each title (Producers Assistant is sometimes written PA, among others) that I need to spend some time cleaning up how the different titles are written in the thousand or so entries I have so far.

                       Since you want to select one preferred record for each such title, anything I suggest will not work well if at all until you get that data cleaned up and consistent. I recommend formatting the field as a drop down list with a value list of the standard titles you want to use. This won't prevent importing data with different text into this field and you can then use the value list to quickly change the title to one of the standard forms you want to use.

                       And I suggest that you learn how to use this drop down list to perform finds to find all records of a given title and then how to use Replace Field Contents to update them all to a different value in a single operation as that can save you many hours of cleaning up your data.

                       

                            As for the key question, this is a file used by only one user at a time on a single machine.

                       But will that always be the case?

                       What I am about to suggest will work with multiple users should you need to transition to that in the future, but only specifies one set of "Prefernces" for the entire database. If you find you need different sets of preferences for different groups of users, this approach will need modification.

                       Add a new table, named "Titles", to your database with at least these fields:

                       Title   (Text; Validate for Unique Values only)
                       PreferredContact (number)

                       Define the value list that I mentioned at the beginning to use the values from Title of this table.

                       Since I don't know the names of your existing table and the fields you need for this may or may not exist, you'll have to add fields where needed and use your names for tables and fields in place of mine:

                       I'll name your existing table "Contacts". It should have a field that auto-enters a serial number to uniquely identify each record in the table. I'll name it __pkContactID in this example. If you do not have such a field, add one and use Replace Field Contents to assign a serial number to this field for every record in your table.

                       Define this relationship:

                       Contacts>------Title

                       Contacts::Primary Discipline = Titles::Title

                       Enable "allow creation of records via this relationship" for Titles in this relationship.

                       To select a given contact as a "preferred" contact over all records with the same exact text in the Primary Discipline field, put a button on the Contacts layout that performs this one line script:

                       Set Field [Titles::PreferredContact ; Contact::__pkContactID ]

                       A conditional format expression can be set on a field, button or layout text to change its appearance to show that the contact has been selected as the preferred contact for that discipline. The expression: Contacts::__pkContactID = Titles::PreferredContact.

                       Conditional format expressions can make layout text appear/disappear, change color or text style. Fields and buttons can change style, text color or fill color so you have  a number of options. You can even place an X inside a box drawn on the layout and use conditional formatting to make it visible only if the contact is a preferred contact. This text can then be set up as the button so that clicking it creates a "look and feel" like that of clicking a check box, but selecting one contact for a given discipline will automatically deselect any previous selection.

                  • 6. Re: Conditional Fields With A Catch
                    GarthBeast

                         Thank you so much Phil!  You have been a huge help

                    • 7. Re: Conditional Fields With A Catch
                      GarthBeast

                           Phil,

                             I wanted to thank you again, the Preferred Contact button works perfectly.  I am, however, having a new issue: I've setup my drop down list to display the values Titles::Title per your instructions, with the Title setup (Text; Validate for unique value only).  However, now when I try to use the drop down menu to choose a title it gives me the message ""Title" is defined to contain unique values only.  You must enter a unique value."  In order to enter a title into this field I've had to create a new field that displays the values from Contacts::Primary Discipline.  When I enter a title into this field it automatically mirrors onto the Titles::Title field.  Is there a way to make it so I can use the drop down list for Titles::Title without having to first enter a value into Contacts::Primary Discipline?

                           Thanks again for your help 

                      • 8. Re: Conditional Fields With A Catch
                        philmodjunk

                             You wouldn't set up Titles::Title with this drop down list. you'd set up your original field, Contacts::Primary Discipline with this drop down.

                        • 9. Re: Conditional Fields With A Catch
                          GarthBeast

                               Gotcha, that fixed it.  Thanks

                          • 10. Re: Conditional Fields With A Catch
                            GarthBeast

                                 Another question:  Is there a way to have a pop-up alert if there is already someone designated as a Preferred Contact?  IE if there is already someone of that title that has been designated Preferred Contact a window will pop-up and say "Are you sure you wanted to replace X person as the Preferred Contact" or something to that effect?

                            • 11. Re: Conditional Fields With A Catch
                              philmodjunk

                                   You can add steps to the one line script:

                                   If [Titles::PreferredContact ≠ Contact::__pkContactID // Different contact is already selected ]
                                      Show Custom Dialog [ "A contact for this discipline has already been selected. Change the preference to this new contact?"]
                                      If [ Get ( LastMessageChoice ) = 1 ; // Ok (button 1) was clicked ]
                                         Set Field [Titles::PreferredContact ; Contact::__pkContactID ]
                                      End If
                                   Else
                                      Set Field [Titles::PreferredContact ; Contact::__pkContactID ]
                                   End IF

                                   and if you add another Tutorial: What are Table Occurrences? of contacts to get this:

                                   Contacts>------Title----Contacts|Preferred

                                   Titles::PreferredContact = Contacts|Preferred::__pkContactID

                                   The custom dialog could then refer to the selected contact by name or data from other fields in the contact record:

                                   Show Custom Dialog [ Contacts|Preferred::Name & " Is selected as the preferred contact for this discipline...

                              • 12. Re: Conditional Fields With A Catch
                                GarthBeast

                                     I'm having some trouble with this line of the script:  " If [ Get ( LastMessageChoice ) = 1 ; // Ok (button 1) was clicked ]"...When I try to enter it I get a message saying: "An operator (e.g. +, -, *,...) is expected here and highlights this part of the line: "; // Ok (button 1) was clicked."

                                      

                                Thanks again for your continued help

                                      

                                      

                                • 13. Re: Conditional Fields With A Catch
                                  philmodjunk

                                       Oops. Remove the semi-colon as it should not be there.

                                       The // Ok (button 1) was clicked, BTW, is just a comment added to the calculation to explain it. You can leave it out if you prefer.

                                  • 14. Re: Conditional Fields With A Catch
                                    GarthBeast

                                         Hey Phil,

                                          

                                              I can't seem to get the warning/dialogue box to appear when changing preferred contacts.  This is what my script looks like:

                                         If [Titles::PreferredContact ≠ Contacts::CONTACT ID MATCHING FIELD]

                                              Show Custom Dialog: ["A contact for this discipline has already been selected.  Change the Preferred Contact?"]

                                               If [Get (LastMessageChoice) = 1]

                                                   Set Field [Titles::PreferredContact; Contacts::CONTACT ID MATCHING FIELD]

                                               End If

                                         Else

                                              Set Field [Titles::PreferredContact; Contacts::CONTACT ID MATCHING FIELD]

                                         End If

                                          

                                         Do you see anything that should be changed?

                                          

                                         Thanks again

                                    1 2 Previous Next