10 Replies Latest reply on Sep 7, 2012 11:12 AM by philmodjunk

    Incosistent Script

    TurtleKoala

      Title

      Incosistent Script

      Post

      I have two tables, Employee and Projects. The Employee table is guaranteed to contain on record per employee, while the Projects table can contain any number of records per employee, including zero. I also have a Global table with global fields gFirst and gLast. I have three relevant layouts.

      The first is a layout based on the Global table with a place to enter an employee's first and last name in gFirst and gLast respectively. There is also a button that runs a script which generates a report using one of the other two layouts.

      The second layout is based on Projects. This is the report that I would rather use, but given that not all employees have projects, I cannot use this for all employees. This is the purpose of my third layout.

      The script to generate the report first enters browse mode. Then it performs the following check.

      If[IsEmpty ( FilterValues ( List ( Projects::First Name ) ; Global::gFirst ) ) or IsEmpty ( FilterValues ( List ( Projects::Last Name ) ; Global::gLast ) )]

      If the check succeds I generate the report based on Projects. If it fails, I generate the report based on Employees. For some reason, the first time I run the script it always generates the report based on Employees, but when I run it again it correctly generates the report based on Projects.

      What is happening?

        • 1. Re: Incosistent Script
          philmodjunk

          I can only assume that you have a relationship that matches to all records in Projects. Perhaps one like this?

          GlobalsTable::anyField X Projects::anyField

          If you have that relationship, make sure that you commit recors before doing this test. You can commit records with the script step of the same name. You can also make a quick check first to see if that is the issue by clicking a blank area of your layout after entering your name data but before running the script.

          If you do not have such a relationship, please let us know what relationship you are using.

          • 2. Re: Incosistent Script
            TurtleKoala

            I tried clicking in blank space before running the script and it didn't work, but I should probably add the commit records script step anyway.

            I'm guessing my issue has to do with the relationship thing. First, what does the X mean? Also, my Global table is not linked to any other table. I'm essentially just using is as a place to store user inputted variables.

            • 3. Re: Incosistent Script
              philmodjunk

              X is the cartesian join operator. You can double click a relationship line in Manage | Database | Relationships to open a dialog where you can change the default = operator to a different one such as this.

              I guessed you might have such a relationship because otherwise, the list functions you are using make no sense.

              I suggest the following relationship between your GlobalsTable and your Projects table:

              Global::gFirst = Projects::First Name AND
              Global::gLast = Projects::Last Name

              Then your If step calculation can be simplified to:

              If [ IsEmpty ( Projects::Last Name ) ]

              Note: using names like this can be a bit problematic since names aren't always unique and people change their names. Also, the idiosyncratic way people spell names can make data entry errors very easy.

              I'd define a serial number field in your employees table and use a value list to look up the ID number for that Employee instead of matching on two name fields like this. You can then use a relationship such as:

              Global::gEmployeeID = Projects::EmployeeID

              For small numbers of employees, you can use a value list of EmployeeID's and their full name to select a value for gEmployeeID. For larger lists of names, you can use a script supported method such as that used in this demo file (I'm including links for both .FMP12 and .FP7 file formatted copies of the same file):

              https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
              https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7

              • 4. Re: Incosistent Script
                TurtleKoala

                First, I realize that using a surrogate key would work better, and I have explained that to my boss, but he wants to use people's names. This is because the people who will be entering records into the Projects table don't know the employee ID numbers and he doesn't want them to have to look them up.

                As for the other point, I think I may have been unclear. This is what I thought should be happening. My projects table has a field called Projects::First Name and another called Projects::Last Name. I thought that List ( Projects::First Name) would list all of the values in that field across all records. I was then filtering that list for the value that had been entered into the Global::gFirst field by the user before running the script.

                For example, if there were three records with first names Bob, Joe, and Steve, and the user entered Steve into the Global::gFirst field, then I though it would do this.

                IsEmpty ( FilterValues ( List ( Projects::First Name ) ; Global::gFirst ) )

                becomes

                IsEmpty ( FilterValues ( "Bob" "Joe" "Steve" ; "Steve" ) )

                becomes

                IsEmpty( "Steve" )

                becomes

                0

                Is this not what would happen?

                • 5. Re: Incosistent Script
                  philmodjunk

                  First, I realize that using a surrogate key would work better, and I have explained that to my boss, but he wants to use people's names. This is because the people who will be entering records into the Projects table don't know the employee ID numbers and he doesn't want them to have to look them up.

                  What I have suggested does not prevent that. In fact, I have suggested two different ways that you can use names to make an ID number based approach work for you. These methods can keep the ID numbers total hidden from your users, they need never know you are using ID numbers instead of names.

                  I thought that List ( Projects::First Name) would list all of the values in that field across all records. I was then filtering that list for the value that had been entered into the Global::gFirst field by the user before running the script.

                  Yes, but list doesn't work that way and that's why I am suggesting a different approach. (actually two approaches).

                  List ( table::FieldName )

                  will just return the value of FieldName of the current record if "table" is the current layout's table occurrence. If "Table" is not the current layout's table occurrence, it will return a list from all records in table that are related to the current record on your layout. If the relationship is invalid or there is no relationship to that table occurrence, it returns an empty list.

                  • 6. Re: Incosistent Script
                    TurtleKoala

                    I guess I didn't understand how the List function works. Now I am confused why the script worked when I ran it again. I guess I have to fix this, so it doesn't really matter, but it is still bugging me.

                    Also, there are a large number of employees, so I tried looking at the .FP7 version of your file (I have Filemaker 11) but when I opened it I got a message saying that it isn't modifiable whenever I tried to use any of the features.

                    • 7. Re: Incosistent Script
                      TurtleKoala

                      I added the relationships that you suggested and I still seem to be getting the same problem.

                      To test if things were updating correctly I tried adding Projects::Last Name to my Global layout. I found that even after entering a new first name and last name in gFirst and gLast, Projects::Last Name was not updating its value. It would finally update after I ran the script, but this seems like the wrong behavior. Is it possible that my database is not set to commit records on field exit?

                      • 8. Re: Incosistent Script
                        philmodjunk

                        As a test, I downloaded from the above link to my desktop and it opened fully accessible. Either it got munged during the dowload and needs to be downloaded again or you downloaded to a folder location whose permission settings are not letting you modify the file.

                        Exiting a field does not commit the record. Try clicking the layout background before running the script. If that works, all you need do is add a commit records step to the beginning of your script.

                        • 9. Re: Incosistent Script
                          TurtleKoala

                          I am stupid. The layout that I thought was based on the Global table is actually based on the Employee table. I think that means that the reason that it doesn't work the first time is that I could be on any random employee record without knowing which one the first time I run it, but when I run it again, I've done a find for that employee as part of the script, so the next time I am definitely on that employee's record, and list behaves the way I wanted it to. Again, I am stupid.

                          I'll try redownloading the file or downloading it to a diffferent place on my computer. If I can't get it to work, it's not a huge deal. I think I get how to what you were talking about with looking up the idea in a value list, but am I correct in thinking that if they spell the lookup value incorrectly then the ID will not be set properly? I assume that I could create a message box to inform them that there is a problem if I go this route.

                           

                          Thanks for all your help.

                          • 10. Re: Incosistent Script
                            philmodjunk

                            The demo file uses an auto-complete enabled list of names (something that's not possible if you use the ID field as the primary field in your value list), so it filters down as you enter a name keystroke by keystroke. That doesn't help with every possible spelling idiosyncrasy, but it catches quite a few.