Chappy.Cole

I don't doubt that you are correct within the realm of tools like FMP or Access, i.e. software that is both an application for developers and for users. However, in my experience with software development using programming languages this problem is non-existent. In that world, the developer uses the language and related tools to create the source code and then transforms the code, via compilation, into the software that is for the user. Since the developer starts at ground zero and builds to a pre-determined set of features there is no additional work to be done. I'm not saying that such development is easy, trivial or even less work than using FMP. I know that I am comparing apples to oranges, but I cannot help myself since I have experienced both worlds.

I probably am not explaining myself thoroughly. My database represents a season of play. The sequence of tables is: -> Events : contains a list of the major events within a season -> Tournaments: contains a list of the individual tournaments, which involves 12 teams (out of hundreds). Altogether these tournaments comprise one season event -> Pools: contains information about the pool-play section of a tournament which is quite different from bracket-play, although both are composed of matches -> Brackets: contains the information about the playoff brackets at the end of the tournaments -> Matches: contains the details of every individual match, both pool matches and bracket matches Also in my database, but not part of the "is composed of" sequence above, are: -> Teams: contains the details of each team in the league - noteworthy is that there is an Age Group field, which is somewhat of a "composed of" factor, i.e. the set of Teams is composed of Age Groups and every tournament is for 1 specific age group. -> Team Seed History: contains the seed number that each team owns before and after each event. Perhaps that was too much information, but I was making the point that my issue regarding the '12 teams in a bracket' was separate from, but similar to the '3 teams in a match' issue. I would like to be able to create layouts which provide information about a bracket. As I said in my previous post, I know that I can achieve my goals with 12 TO's of Teams, but that seems very messy. As an FMP tinkerer (i.e. I currently don't make a living as an FMP developer), but also a Software Engineer, when I feel that something is messy, it makes me suspicious that my solution contains a design flaw. How would you handle the '12 teams in a match' issue?

So, I have been working on a volleyball tournament database. In each match, there are 3 teams involved: 1) Home team ; 2) Away team ; 3) Referee team. Each team in any match will at some point be performing each of the roles, i.e. every team is sometimes the Home team, sometimes the Away team and sometimes the Referee team. My database has a Team table and a Match table. When I am designing a layout to show a summary of a match, I have the need to display information about each of the 3 teams. I know that I can achieve this by having 3 TO's of the Team table, each related to the Match table to the 3 team roles. I have no technical problem with this. However, when the tournament gets to bracket play, I have 12 teams in a single Bracket table record (it could be much more, e.g. 32). Again, I know that I can solve my requirements with 12 TO's of the Team table. My question is: Is there any other, less complex, less bothersome way to manage this relationship issue? Consider that each team in a match or bracket does have a distinct designation, i.e. it is not just another team. I don't believe that a join table will solve my problem...Hmmm... Well, after a little testing I guess a join table might work by putting the attributes that distinguish one team from another within a match, in the join table. So, my tables would be: 1) Team: contains Team_Id, Team_Name, etc. 2) Match: contains Match_Id, Court_Number, etc. 3) Teams_In_Match: contains fk_Team_Id, fk_Match_Id, Team_Role where 'Team_Role' would hold 'Away', 'Home' or 'Referee'. Hey, experts (Comment, are you watching), does that sound like the right (i.e. a sound) approach? Thanks in advance

'Comment' is quite right about my plan...The TO one selects when creating a layout merely establishes the base table the layout displays and the network of connected TO's that are related. It does not, however, use any relationship. Essentially, this is a case of you wanting to have your cake and eat it too, i.e. Cake) All records, active and inactive, exist in 1 table: People Eat it) Layout shows only one type of record based on status without chance of user getting to other type of record. As you know, you can temporarily subset the records via a 'Find' (which the user can alter with another Find or Show All, etc.), or via a portal which uses one of the relationships that I described. Using a portal, although very useful in some situations, changes a lot of behavior for the user, e.g. how to change records, how 'Find' works. etc. I suspect the portal solution would not satisfy your needs. There are various things you could do to achieve your goals, but I think that none will satisfy all your needs, unless you take control away from the user. You would have to limit what features the user has access to (e.g. 'Show All Records'), and write a script, or several (to perform 'Find's and 'go to Layout's). This is one of my chief complaints about FMP...To create a bulletproof (i.e. user-proof) solution, one has to do 10 times as much work as to simply provide the data solutions.

There's one pitfall with the 'Mod ( number, Sign ( number ) )' method: Sign can return 0, which, if it does, Mod would be attempting to divide by 0, which is undefined. Of course this is a special case; however, this is precisely what we must always be worried about, i.e. the special cases! Well, excuse me. I just tried using the Mod & Sign calculation and it correctly calculated the value that scottvaughan wanted, e.g. number = 27.385 ==> calc. = .385 number = -17.129 ==> calc. = -.129 number = 0 ==> calc. = 0 That concerns me because FileMaker is making a non-standard choice for a mathematical function. If one created a calculation that resulted in dividing by zero, most software would display some special value like, "#undefined#" or the software would crash. Why would we want FileMaker to do something different? Sheeesh. I just did some more testing and FileMaker gives a completely incorrect answer when mixing positive and negative: Mod ( -17.129, 1) ==> .871 Sounds like a bug to me.

Your skill level says, "Expert", so I suspect that I am missing something as I ponder your issue... Anyway, it sounds to me like your solution is: 1. Define 2 global variables: gActive, gInactive. 2. Establish their values to whatever you use for your field Active, i.e. you set them to 1 and 0 respectively. 3. Create 2 Table Occurrences of your People table: 3.1 Active_People 3.2 Inactive_People 4. Create 2 relationships to your original (or any other) People table occurrence using the gActive field from the Active_People table to connect to your Active field and the gInactive field from the Inactive_People table to also connect to your Active field. 5. Create 2 layouts: 5.1 Active People: In the 'Create a Layout/Report' screen select the Active_People table occurrence from the "Show Records From:" pop-up list. 5.2 InActive People: same process as above but select the Inactive_People table occurrence If you do that, then the layouts will always, only show either Active or Inactive people. This is a self-join relationship using a global variable as a pseudo-primary key. Using global variables in this fashion is a convenient way to allow the user to select subsets of records which meet a certain criteria. For example, you could create just 1 global variable (perhaps gSelectedStatus), put it on a layout which you display to the user to allow the user to select which group of records they wish to examine. On the layout, the global variable field is defined to show a pop-menu with a value-list which is defined to show all the unique values in the selected field (in your case, Active). After the user selects a value, they would have to click a button on the layout which would be defined to "Go To Layout..." In this scenario, since 1 global variable can be assigned to either "Active" or "Inactive", you only need 1 table occurrence and 1 relationship (instead of the 2 that I described above), to display whatever subset of records the user selected. Hopefully all that is understandable, as opposed to confusing.

Ok. After further tinkering I think I have a more refined inquiry regarding my problem. If one has a group of tables like my Continent->Country->State->City example, where all Country records relate to exactly 1 Continent record, and so on down the line, is there a standard rule for the use of key fields? What I have now is: --> Continent::continentId --> Country::countryId, fk_continentId --> State::stateId, fk_countryId --> City::cityId, fk_stateId However, another possibility that came to mind, and would solve my problem, is: --> Continent::continentId --> Country::countryId, fk_continentId --> State::stateId, fk_countryId, fk_continentId --> City::cityId, fk_stateId, fk_countryId, fk_continentId The reasoning is that when I need to access data from, let's say, the State table and some other table that is only related to the Continent table, I have fk_continentId in each record of the State table which enables me to make the various relationship connections that I need. I see the first method as a cascade-like set of tables, each with a primary key field and all but the starting table (Continent) with exactly 1 foreign key field relating that table backwards to it's superior table. The second method, although it would solve the problem that I am having, seems unclean to me due to the "extra" foreign key fields. Is is common-practice/safe/normal to have multiple foreign key fields in a table? This brings me to my final comment. "Comment" (the "name" of the user who commented on my first post) said that he/she thought that my example did not meet the Set theory criteria. I would like to know how you ("Comment") would characterize my example of Continent-County-State-City? What about it do you think is not consistent with Set theory?

The most obvious thing - which I think is in error - is that you have the "Go To Portal Row: Select: Next: Exit after last" in the loop *before* any processing steps. Unless you intend to skip the 1st portal row, the "go to next" step ought to be immediately before the "End Loop" step. The other things that "appear" to be in error is the absence of any step which adjusts the amounts remaining after applying a payment. If I'm right about this, then your script would apply the lessor of the client's check amount or the invoice balance, to every row in the portal except the 1st. Since you are ranked "Advanced", I doubt that I have debugged your problem, but you never know. Chappy

I'm not an expert; however, I have been a professional software developer and I have used Filemaker Pro since version 3 up through 7. I created my own version of your scenario (making assumptions as necessary) and I was able to achieve your goal - sort of. My solution has 2 tables: 1) Employee ; 2) Safety. However, there are a total of 8 Table Occurrences and 6 relationships. Essentially there are 2 Table Occurrence Groups (TOG's): 1) the Employee table connected to the Safety table with 3 separate relationships via the 3 fields in Employee that each hold a copy (i.e. foreign key fields) of the Safety table's id field. This TOG enables the creation of a layout which shows Employee table records and uses the relationships to select the desired Safety Activities for an employee. 2) the Safety table connected to the Employee table with 3 separate relationships via the id field in Safety to each of the 3 foreign key fields in the Employee table. This TOG enables the creation of a layout which shows Safety table records and contains 3 portals using the 3 relationships to show the name of each employee that is assigned to a safety activity. In my solution, I created 3 fields in the Employee table to hold the Safety Assignments for that employee. This limits you to a maximum of 3 assignments per employee. By the way, the choice you make regarding how to handle multiple safety assignments per employee affects your database design significantly. Notice how each of the TOG's that I described feature 3 relationships connecting to the 3 foreign-key, safety activity fields in the Employee table. There are 2 other ways that I know of to handle the many-to-many relationship that you have: 1) Use a repeating field in the Employee table to record the safety activities assigned to an employee. *BAD* DO NOT DO THIS! Even in very simple scenarios, this feature is a major headache and requires solutions that do not adhere to good software practices. 2) Use a 3rd table to make the connections between the Safety table and the Employee table. I believe that such a table is referred to as a "Join Table". This table would have (at a minimum) the following fields: > foreign-key Employee Id > foreign-key Safety Id Then you link each of your tables to this join table via the primary key in each table. So, each record in the join table represents a connection between an employee and a safety activity. With this solution, I believe that you would only need: > 3 tables: Employee, Safety and Employee_Safety > 2 relationships: ------> Employee::Id <==> Employee_Safety::fk_employeeId ------> Safety::Id <==> Employee_Safety::fk_safetyId Of course, you would have to perform some other steps to completely setup the scenario, but these are the essential elements. See my attached file "SafetyEmployee_JoinTable.fp7". The big benefits of this setup are that you could assign any number of safety activities to any one employee (with my first solution which stuck to your original 2 tables, you are limited by the number of foreign-key fields you create in the Employee table), and any number of employees can be assigned to any one safety activity (also possible even with my 2 table solution), and you have a much simpler database design/relationship graph. Also, with the addition of a date field and a status field (active/inactive) in the join table, you could have the ability to track the history of the safety activity assignments over time. I hope this helps. If one of the real experts who monitor these forums responds to this thread, they will probably have more professional, clean and evolved solutions. Chappy Safety_Employee.fp7.zip SafetyEmployee_JoinTable.fp7.zip

I have a scenario that which requires at least 4 tables which can be represented as such: Continent (which is a superset of) Country (which is a superset of) State (which is a superset of) City I would like to be able to create relationships, value lists and layouts that enable the user to view the information from the top (Continent) all the way down (to City) (succeeded here already) *AND* from the bottom (City) all the way up (to Continent) (failed here). My failure is not that I cannot figure out how to accomplish the task, but rather that the solutions that I can come up with seem flawed. My instincts tell me that there must be a clean method that I am currently unaware of. If there's a white paper or some such document that explains this scenario with good examples, that would be great. Thanks, Chappy

I attempted to open a FP version 5 file, which has a password for full access priveleges, using FP version 7.0v3. FP7 prompted me for the name to save the old version under and where to save the converted file. Once I'm done with those, it asks for a username and password. I enter what I know to be the username and password that I originally used, and it rejects it. Other possible factors are that, since the last time I worked on the FP5 file, I have upgraded our OS to Mac OS X 10.4.7. Also, my hard drive crashed and I had to take it to a specialty place to get the files recovered. Most files were recovered fine and all the other FP5 files - which do not have passwords - which are in the same directory, work fine. Any known problems with this conversion process? Any hints on recovering from this problem?

Thanks for the welcome! Thanks for your reply also. I'm already glad I joined this forum community. It's possible that I might need some of the other tracking fields that you mentioned, but for now, I just want the basic feature of customer info, issue description, current status and history. Thanks for the ideas and help. Chappy Cole

I have occasionally wanted to track the history of some bit of data. For example, right now I want to create a db which contains the following fields: -> Customer Name -> Customer Phone -> Customer Issue -> Current Issue Status -> Current Issue Status Date -> Current Issue Status Description -> Issue Status * -> Issue Status Date * -> Issue Status Description * in which, the last 3 fields with the '*'s, can repeat endlessly. In other words, as time goes on, for any customer issue, the status will change some unknown number of times. I would like to put together a db that handles this in such a way that it is easy to design a layout that allows a user to "walk" through the history of the issue. My one thought on the matter is to do the following: > Make 2 db's: 1) Customer Issue Db; 2) Issue Status Items > In the 1st db, define a field as an automatically serialized number which is the key field for relating this db to the 2nd db. > Then use relationships with "allow creation of related records" and portals to allow multiple entries in the 2nd db to share the serial number from the 1st db. Will my idea work? Any other thoughts?

I started using databases *after* learning about relationships and being a programmer. So, I avoid repeating fields entirely. Since this feature has persisted through 5 generations of FM that I have used (3-7), I guess it has some use. Can someone give me an example or 2 of a good use of repeating fields?

Is it possible to encapsulate a group of FM files - i.e. tables - into a coordinated, single point of entry, application? That is, is there a way to have a group of related FM files connected so that the following things are true: > There is only one entry point into the "application", i.e. the first file & layout the users see is always the same > Permissions and other security features can be estabished at the application level, i.e. not on a FM file by file basis > Scripts can be shared across all files in the application without have to re-write them for each file > Some variables can be defined as global to the entire application I have only used basic (i.e. not Developer) versions of FM, which are clearly not capable of any of these features. I am wondering it the Developer version provides some of these "glue" features that would enable me to produce something like an application, as opposed to a group of files which, although related via relationships stored internally in each file, have no apparent connection from the outside.