1 2 Previous Next 18 Replies Latest reply on Apr 10, 2015 4:04 PM by philmodjunk

    Help with formula functions so Filemaker auto-fills text fields...

    DaveHord_1

      Title

      Help with formula functions so Filemaker auto-fills text fields...

      Post

      I could really use some help with formula functions, and getting my database to use text values in two separate boxes to fill out a third box. I have the following lists as part of my database:

      Client List – basic contact information
      Vehicle List – list of vehicles owned by each client, linked via Client ID#
      Event List – List of events I run (Event ID code and Name)
      Event Signup – linked via client ID, this sheet lists the Event ID they’ve signed up for, the assigned car number for that event and the role of the client in the car (Driver, CoDriver, Passenger)

      I am attempting to create an event sheet listing all cars, and this is where I need the formula’s that I have ZERO idea on how to create properly. Lets say I have an event next month called April 2015. I would like to create a list which shows me driver and codriver, sorted by assigned car number. I think I need to do the following:

             
      1. Create a new list, called “April 2015”.
      2.      
      3. Setup my fields so that I have Car number, Driver Name, CoDriver Name, and Passenger

      If I start a new record in the this list, I would like to be able to type “1” into the car number field and have the following happen:

      If “EventID” in the “Event signup” list = "April 2015"
      AND “assigned car number” in the “Event Signup” list = "1"
      AND “role in the car” in the “Event Signup” list = "Driver"
      THEN use the text from “First Name” field from the appropriate Client ID in the Driver Name field.

      IF “role in the car” in the “event signup” list = CoDriver
      THEN use the text from “First Name” field from the appropriate Client ID in the CoDriver Name field.

      IF “role in the car” in the “event signup” list = Passenger
      THEN use the text from “First Name” field from the appropriate Client ID in the Passenger Name field.

      How would I go about doing this? I definitely have more questions regarding formulas, but if I can learn this one I can experiment a bit and that might just teach me all I need. If not I can follow up with another question.

      Thanks in Advance,

      -Dave

        • 1. Re: Help with formula functions so Filemaker auto-fills text fields...
          DaveHord_1

          Hmmm, is there a resource which has more basic information on how to do calculations in fields?

          I'm looking for filemaker to do what I think are basic AND / IF functions.

          If box A = ___ AND box B = _____ THEN put information from Client ID in Box C.

          ...but I have no idea how to get this to work, and can't seem to find anything related enough to get started in the tutorials.

          -Dave

          • 2. Re: Help with formula functions so Filemaker auto-fills text fields...
            philmodjunk

            I looked at your original post and decided not to respond. Here's why:

            What you are looking for will largely be affected by the basic design of your system and that was not something that I could infer from your original post. Couldn't tell what a "list" represented in your original description. When I read that, I couldn't tell if a "list" meant a table, a found set or a value list and simply didn't have the time then to ask the needed questions in order to get a complete picture of what you wanted to accomplish.

            To respond to your last post will illustrate the problem: First, there are no "boxes" in FileMaker, only fields and variables. Presumably, you mean:

            If Field A = ___ AND field B = _____ THEN put information from Client ID in field C.

            But are Fields A, B and C fields in the same table or different tables? Fields in the same record or different records?

            Even with those answers, you might use a calculation field, a data field with an auto entered calculation or a script to do what you describe.

            • 3. Re: Help with formula functions so Filemaker auto-fills text fields...
              DaveHord_1

              Hi Phil,

              Thanks for help, and apologies for not having a clear description with the correct terms. I’ll do my best to come up with a clearer picture of what I’m trying to do.

              Currently my database as three tables:

              Client Table – Names, allergies, contact information. Each client is given a unique client ID by Filemaker pro.
              Events Table – A list of events I run, each event is given a unique Event ID manually by the user.
              Event Signup Table – A table used to keep track of which clients are doing which events. This table has four fields: Event ID, Client ID, Assigned Car Number, Role in Car. The assigned car number is a manual number entered by the user. The “Role In Car” field is a value list using checkbox selections with three options.

              I will then create a new table for each event that I run. This table, like a classroom list showing students who’ve registered, should show me all my clients who have registered for a particular event. The image attached is a single record from my “April Event 2015” table. For the sake of keeping this somewhat easy, lets focus only on the “Driver First Name” field.

              When creating a new record in this table,  the user will manually enter the car number into the “Car Number” field (in the attached example, Car Number ‘0’). At that time, I would like Filemaker Pro to do the following in the Driver Name Field:

                     
              1. Search the Event Signup Table for all records using the “April Event 2015” Event ID.
              2.      
              3. Narrow those results by entries using value “0”, found in the “Assigned Car Number” field in the “Event Signup” table.
              4.      
              5. Further narrow those results by entries using the “Role in Car” field found on the Event Signup table. In this case by the first checkbox selection which is labeled “Driver”.
              6.      
              7. At this stage Filemaker Pro should have only one entry on the Event Sign Up table which matches the above criteria, and it should then take the associated Client ID number, and use the Client Table to copy the value in “Driver First Name” from the client table into the “Driver First Name” in this table we’re working on.

              In theory, once I learn how to setup the “Driver First Name” field on this specific event table, I can then setup each of the other fields found above ruled line so that upon entering the Car Number “0” all my contact fields fill out automatically.

              So my dumbed-down example in my second post should actually be:

              If Field A = ______ and Field B = ______ AND Field C = _____ THEN Get Value ____ from Field D. 

              Or, in other words. In the “Driver First Name” field of my “April Event 2015” Table I need the following ‘calculation’ (not sure if calculation is the right word):

              If “Event ID on Table Event Signup” = ‘April Event 2015’
              AND “Car Number on Table Event Signup” = ‘0’
              AND “Role in car on Table Event Signup” = ‘driver’
              THEN Get “Driver First Name value from Client Table” and place it into the Driver First Name field on this table.

              -Dave "I can rebuild a Porsche gearbox without trouble...but this has me stumped!"

              • 4. Re: Help with formula functions so Filemaker auto-fills text fields...
                philmodjunk

                Ok but you don't need to add any such  tables. Your event sign up table can serve to list all participants for any given event. A portal to event sign up placed on the Event layout would also list the participants for that event. 

                • 5. Re: Help with formula functions so Filemaker auto-fills text fields...
                  DaveHord_1

                  Hi Phil,

                  I don't follow? A portal will list participants for that event...but it won't organize them by car number. Additionally, in my image showing "Car 0" you'll note there is a line separating the persons in the car, and the hotel information below. This hotel information is specific to the event and car number, thus my plan to add an additional table for the specific event. 

                  I'm happy to entertain other ideas on how to do what I want to do, but it seems to me using the calculations function in Filemaker would be the easiest option. I just don't understand how to get it to do what I need it to do, and I can't find a tutorial or instruction section that is even remotely close to what I need.

                  Thoughts?

                   

                  • 6. Re: Help with formula functions so Filemaker auto-fills text fields...
                    philmodjunk

                    A portal is just the simplest way to list participants. It is far from the only way to list this information.

                    A layout based on the event sign up table can also be used, just perform a find or use Go TO Related records to pull up just the records for one event. There are several options that would make it possible to group participants by Car. A self join to another occurrence of the sign up table is one, a related table of cars, is another A list view summary report  with a sub summary layout part is another.

                    Trust me, you really, really don't want to define a different table for every event just to list data already entered into another table. This creates all kinds of problems for you as the developer.

                    • 7. Re: Help with formula functions so Filemaker auto-fills text fields...
                      DaveHord_1

                      Thanks Phil. One last thing to consider, and why I was creating a new table for each event:

                      The hotel information in my graphic above is specific to the event. So you may sign up for my April event as Car 6, and you're travelling with your best-friend / car nut buddy. For that event you select a two-bed room, and your buddy is allergic to cats so needs an allergy safe room. When you sign up for my June event, however, you bring your wife. So now you want a one-bed room and have no allergy issues. So this information is always specific to the eventWhich is why I thought a new table was appropriate?

                       

                      • 8. Re: Help with formula functions so Filemaker auto-fills text fields...
                        philmodjunk

                        If data is specific to the event-participant pairing, that becomes data that should be recorded either in fields that are defined in the join table or in an additional table that is also linked to that join table. You still would not need and should avoid setting up specific tables for each event.

                        And then a layout based on the join table still becomes the best way to report this data in either situation.

                        Note: each layout is based on a specific table. If you created a new table for each new event, you would have to keep on adding more and more tables and recreating more and more layouts to use with them--that's not a good idea!

                        • 9. Re: Help with formula functions so Filemaker auto-fills text fields...
                          DaveHord_1

                          Thanks Phil. Unfortunately I just can't wrap my head around how to make this work without finding a course and/or paying someone to assemble my database for me. Bento is not (was not) sophisticated enough to do what I need, and Filemaker Pro is so advanced the learning curve seems impossible. First software in 25 years I haven't been able to crack. 

                          I've reached the point of frustration where I'm throwing in the towel, I just don't understand enough about databases to grasp the method you're trying to help me with. Even if I do make a major database error by using new tables for each event, at least I would have data I could import into a new, better, database once I learn enough. We're talking about a business that does six events per year...not a crazy amount of data. 

                          Sucks, I thought I had it dialled except for one simple stumbling block. 

                          • 10. Re: Help with formula functions so Filemaker auto-fills text fields...
                            philmodjunk

                            From here, I don't see where the "disconnect" is occurring for you....

                            6 events a year is still 6 different layouts and (potentially) 6 different sets of relationships as well as discrepancy issues when it becomes necessary to correct a data entry error.

                            • 11. Re: Help with formula functions so Filemaker auto-fills text fields...
                              DaveHord_1

                              Hi Phil,

                              Okay, so after some breathing time I went back to this with fresh eyes. What I did was create a new Layout, based on my Event Signup Table. The new layout (Event Sign Up Detailed) will hopefully show me all the information I need on a per-car basis. I'll attach an image below, but I have the following working:

                                     
                              • Event ID & Car Number are working correctly (example, I have two car zero's in two separate events)
                              •      
                              • The Vehicle ID field is now pulling up the correct vehicle information via a portal from my Vehicle Table. This is matching correctly with each event. (example: car zero in event one used vehicle V10, car zero in event two used vehicle V8...all are assigned via a portal in the client page)
                              •      
                              • The Hotel information (which, at the moment is being held in a table called "Hotel Info" is all linking correctly via Event ID and Car number. 

                              So I'm stoked. I apologize for getting frustrated, I can't imagine how annoyed you were on the other end! Be patient with me...we've almost got it all working, in as few tables as possible!

                              Here is the final bit that I am stuck on - Getting my "person" portals to work correctly. In the "Event Sign Up" table, a client ID is assigned to both an Event ID and a Car Number (that part should be obvious as to why). I then have a Value List field (Role in Car), with three possible check boxes. This identifies whether the person is a Driver, CoDriver or Passenger in the car.  (See first image)

                              Now, back to my newly created "Event Sign-up Detailed" layout, I can't figure out how to get the Portal to filter out by the Role in Car value list. So for my top portal, the Driver, I have the portal results set as "Filter Results". I am then using the following filter:

                              Client List::Client ID = Event Signup::Client ID  

                              I believe I need an "AND" command that then filters those results by the Role in Car, but can't seem to figure it out. I would then use the AND command to adjust the second portal to the CoDriver value, and the Third portal to the passenger value. 

                              ...am I getting closer?! :)

                              -Dave

                               

                               

                              • 12. Re: Help with formula functions so Filemaker auto-fills text fields...
                                DaveHord_1

                                Here is the second image of my new layout

                                • 13. Re: Help with formula functions so Filemaker auto-fills text fields...
                                  philmodjunk

                                  I'm not and wasn't annoyed. I never assume that just because something is easy for me, that it should be easy for someone else.

                                  You are getting closer, but it's the car records and the records for individuals that still is the knotty part of this problem. Linking hotel info for a car only makes sense if all people riding in that car will always link to the same hotel record. If A hotel books up and 2 people from Car 1 have to be put up at Hotel A and the other person has to stay at Hotel B, you have a problem.

                                  To me, you have this basic set of tables/relationships:

                                  Car----<Car_People>-----People----<People_Event>------Event
                                                                                                           v
                                                                                                            |
                                                                                                        Hotels

                                  My assumptions behind this particular data model are that a car may have different people in different events and that you only want one record for any given car that is driven in at least one event. But this is not the only option. You might find it simpler to create a new Car record each time a group of people associated with that car registers for an event, it depends on how much info about the car you need to record.

                                  • 14. Re: Help with formula functions so Filemaker auto-fills text fields...
                                    DaveHord_1

                                    Thanks for your continued help. 

                                    I'm going to clarify cars and car numbers, so that we're both on the same page with my business terminology:

                                    A "Car" (or better yet, "Vehicle") is an automobile owned by a client. Some clients own three or four, and occasionally they sell them amongst each other. I have a Vehicle table so that the needed details about each are stored in one table, and I can move ownership from one person to the other as needed. I like to have a database of cars, as I occasionally get contacted about a need for a movie or TV shoot. Having the Vehicles table means I can quickly search for a car type, and find the owner.

                                    A "Car Number" is what I assign to a Vehicle for each event. The car number (actually worn on the side of the car during the weekend) is our designated ID code for a booking on that particular event. The accounting, guest names, hotel preferences and any event-specific information is all related to the Car Number. Each event starts with a fresh list of cars, and if you were registered in two you might be Car 1 today, and car 63 next month.

                                    The Hotel record is unique for each event. You might register for my April event with your wife, and want one king bed. But for May, you register with your Brother as the CoDriver and now you need two queen beds. So having the hotel record 'linked to the Car Number' for each event seems to make the most sense to me. 

                                    Attached is an image of my table setup as it currently stands, which will hopefully help you out in your diagnosis!

                                     

                                    1 2 Previous Next