13 Replies Latest reply on Jan 11, 2014 6:19 PM by philmodjunk

    Starting Over

    Striker3070

      Title

      Starting Over

      Post

           I just took a volunteer position that requires me to track other volunteers, activities and what volunteers attend what activities.

           Since they have been using an excel spreadsheet, and the sheet they handed me has 240 volunteer names on it, and only a few hundred events, I have the opportunity to do a DB with an interface for data entry.

           Lets start at the beginning, will the relationships work for what I need?  Is there any recommendations as far as doing it differently?

           GOAL; (1)Track Volunteers (2)Track the different activities (3) Track what volunteers attend what activities. (4) Produce reports monthly/yearly on miles volunteers drive and number of activities they attend. (5) finally eventually move to mobil iPad syncing.

           Issues: Volunteer_ID is formatted as "US12345" No quotes.  Perhaps this little group thought they would expand beyond the US, not sure why that is there.

           Using, and new to MAC and FMP 13

      relationship.png

        • 1. Re: Starting Over
          philmodjunk

               The basic relationships look fine. The US isn't a major issue but I wouldn't do it that way, the ideal primary key should be unique, never ever changed and have no special "encoded meaning". That "US" appears to violate the last rule.

               The only other issue that I see is that you have some sort of wrong field option specified for VA_ID. There should be "crows feet" at the end of that relationship line. Check for an auto-entered serial number or unique value validation rule and remove it.

               You may find this demo file on many to many relationships a useful source of ideas: https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7

               If you are using FileMaker 12 or newer, use Open from the file menu to open the file and convert it to the newer .fmp12 file format.

          • 2. Re: Starting Over
            Striker3070

                 Because I did not like the US in the member ID, I created an auto incrementing serial number field to use as a Key field, and imported the excel sheet, however the Key field did not get populated.  Is this normal on import?

            • 3. Re: Starting Over
              philmodjunk

                   It is normal if you did not select the option to enable auto-enter options during the import. There's a small dialog that appears just before the actual import starts where you can select this option.

                   But you can also use Replace Field Contents with the serial number option to update your imported records and thus avoid having to re-import.

              • 4. Re: Starting Over
                Striker3070

                     I created a layout on volunteers table with Volenteer_ID and Full name, getting the ID from a value list that has both fields.  The ID field populates from the drop down list, but Full_Name does not.  What do I need to do to populate both fields based on one selection?

                      

                     Thanks

                • 5. Re: Starting Over
                  philmodjunk
                       

                            I created a layout on volunteers table

                       Why would you use such a drop down on the Volunteers table? There should only be one record in Volunteers with any given ID so using such a drop down on a layout based on that table makes no sense as it would be used to create multiple volunteer records with the same ID.

                  • 6. Re: Starting Over
                    Striker3070

                         Umm, OK< I guess I was thinking of that as a find feature.  I'll try removing the list and adding a portal to volunteer activities and see how that goes then,

                          

                         Thanks

                    • 7. Re: Starting Over
                      philmodjunk

                           You could set up such a find feature, but there's more to such a feature than just setting up a drop down list on the field. You could for example set up a global field with such a drop down and use a script to take that data and perform a find. But I'd just use a global text field and set the drop down up with a value list of volunteer names--I wouldn't use the ID number for finding them.

                           Here's a thread with many different scripted find examples--many of which start with data selected/entered in a global field: Scripted Find Examples

                      • 8. Re: Starting Over
                        Striker3070

                             When you say global text field, is this some sort of variable set to text.  If so will it act like an array?  Like dimensioning a variable in VB.  I'm looking through the examples you sent, but struggling a little.  Not recognizing the naming convention.  Not any kind of Hungarian notation I am familiar with!!

                             But I like the challenge, and appriciate your responses.

                        • 9. Re: Starting Over
                          philmodjunk

                               "Global" is a storage option you can select for a field while in Manage | Database | Fields. You can select the field there and click the options button or you can just double click the field definition to open up the same dialog.

                               A field with global storage stores the same value no matter what record or layout you are currently on. It does not turn the field into an array. The key detail for using it with scripted finds is that data entered into a global field while in browse mode is still accessible when you or a script enter find mode. Thus, you can enter or select data in some global fields, perform a script and the script can use the data in the global fields along with set field steps to set up find criteria and perform the find. (Put a regular field on a layout. Put a global field next to it. Enter data into both fields. Enter find mode. Note how the regular field now appears to be empty but the data in the global field is still there.)

                          • 10. Re: Starting Over
                            Striker3070

                                 I created a LO on the Volunteers table with a portal to volunteer activities, buy when I hit new record, it adds it to the Volunteers, not the volunteer activity.  If this is where the lo should go, I guess I need a button to add a record to the volunteer activities.

                                 Since I couldn't get the above to work, I tried the opposite approach.  I created a LO, based on volunteer activities with volunteer ID and full name and a portal to volunteer activities.  That view seems to list all records, not just the ones for the ID.  I assume I am just missing the place I need to filter those records based on volunteer ID.

                                 which approach is better?

                            • 11. Re: Starting Over
                              philmodjunk
                                   

                                        I created a LO [Layout] on the Volunteers table with a portal to volunteer activities, buy when I hit new record, it adds it to the Volunteers, not the volunteer activity.

                                   Which is exactly what it should do. There are two methods for creating new portal rows in the portal:

                                   1) If "allow creation of records via this relationship" is enabled for the portal's table occurrence, you need only scroll the portal to expose a blank row after the last existing portal row and start entering data in to the fields in your portal. FileMaker will create the related record for you and link it to your current volunteer record. This is the simplest "newbie friendly" way to make this work for you.

                                   2) Many developers don't want to make users scroll the portal to the end each time they want to add more portal rows. They then specify a sort order for the portal that lists the newest portal rows first and add a scripted button to the layout that adds a new related record in the portal when the button is clicked. There's more than one way to script this, but a commonly used method freezes the window, copies the current volunteerID to a variable, changes layouts to the portal's layout, creates a new task record, uses set field to copy the ID from the variable to an ID field in Tasks and then returns to the original layout.

                              • 12. Re: Starting Over
                                Striker3070

                                     Umm very interesting, I may like the scripting language after I get used to FMP object names.  Seems like the same way I would do things in VB.  Set screen updating to false, change cursor to hour glass, make my changes in the background, set screen updating to true, change cursor back to normal.

                                     So seems like I need to create a LO based on Volunteers, with a portal to volunteer activities based on a table instance that binds the foreign key in activities to the primary key in volunteers.

                                     I'll do some googling, but is there a quick example of a script for adding a new record in the portal?

                                • 13. Re: Starting Over
                                  philmodjunk

                                       But in FileMaker, you do not have to unfreeze the window. This will happen on its own when the script completes.

                                       Freeze Window
                                       Set Variable [$ID ; Value: Volunteers::__pkVolunteerID ]
                                       Go to to layout [ "activities" (Activities) ]
                                       New Record/Request
                                       Set Field [Activities::_fkVolunteerID ; $ID ]
                                       Go to Layout [Original Layout]

                                       This is not the only way for a script to create a new related record. The main draw back to this method is that it can trip a number of script triggers that might be set on either layout. When a script trips a script trigger, the current script is paused until the trigger controlled script completes. This "interruption" by a trigger controlled script can keep the original script from producing the expected results.