Neville310

Sorry to bother with my neophyte questions... Several years ago, I developed a fairly extensive database on another platform. It handled value lists mainly through a lookup table. One could add multiple fields through queries and set the data source field independent from the list displayed value. This technique became handy for saving abbreviations and displaying descriptive text in forms and reports. The query would form the basis for the drop-list; and the drop-down control would have its source value set to the appropriate column. The simplest explanation relates to a State lookup table. StateID => CA StateName => California The drop down control box would display California, yet when the user selected a value, the drop down control would save CA. This technique is fairly straight forward in Filemaker (even for a newcomer). My question revolves around multiple layouts and utilizing a more descriptive text in one layout, which has more details about the status; and the abbreviation in another layout. My objective relates to tracking phases in a project. It would be like multiple check boxes tracking progress; yet the other layout would display the full descriptive status for all phases. In other words, the status could have ten steps summarized into in progress or done. Or the following stages below. RS => Ready to Start NA => Non Applicable IP => In Progress DN => Done So IN PROGRESS is anything not DONE while the other layouts has a detail status; like ordered, received, verified, packaged, shipped, and receipt confirmed. Presently, a spreadsheet serves this purpose helping me get from start to finish. I hope to develop a similar layout in Filemaker. One approach would be to create a calculation field, which I have less familiarity with calculations; Should it be a calculation in the value list, table field, or form control? The other approach would involve using multiple fields; one for the descriptive status; another one for the status abbreviation. Then tie it together with a table occurrence. Again, both techniques require me to shift my mindset about value list for Filemaker. Please let me know if anybody has crossed any tutorials or databases utilizing these two techniques. Any help pointing me in the right direction would be appreciated.

I would like to install Filemaker 8 and begin familiarizing myself with the new features. But we still use Filemaker 7 and can not risk full implementation until Filemaker 8 is battle tested in our scenario. How does Filemaker 8 install with Filemaker Developer 7? Do both installs run without conflict? How you deal with Filemaker 8 take over the *.fm7 files? Let me know if you have any suggestions migrating solutions to Filemaker 8. It does not seem complex since Filemaker 8 uses the *.fm7 format. But you never know.

How do you create a tab portal or single page portal view for browsing multiple address records for a contact? Ideas on creating a layout similar to the business tracker sample, where an additional tab appears with each address. In other words, the contact layout would allow two or more addresses in a non scrolling portal . Navigation arrows scroll through portal records. The tricky part is the interface. I need a tutorial on creating this layout with a complex many-2-many relationship. A sample database would help, yet a tutorial or book as a quickstart guide would be ideal.

I am sure this topic has been covered in this forum, yet the search feature has not led me to the information. Does anybody have a tutorial for creating a complex graphic interface in Filemaker? This tutorial would not focus on creating the graphics and UI theory. It would focus on the tables, fields, and scripts behind a user interface solution. It represents a quick start guide for creating a database. The tutorial would form easy to follow steps for a beginner to intermediate level. It would explain all the container fields and their placement on the layout. It would cover primary & secondary tabs; check boxes, highlight masks, sort graphics, and slider controls. Several sample Aqua databases have helped me, yet reverse engineering is not as ideal as tutorial. Let me know if you can help.

Please let me know if anybody has seen a salary history database. The database would track job descriptions and various salaries for positions. Each job description should include multiple rates ranging from low to high quotes. It would classify jobs in several ways like union, non-union, flat, daily, hourly, etc. It should calculate various salaries based on hourly, daily, or flat rate in conjunction with the number of hours worked. In addition, a salary calculator would help as well. Reports should display all high rates for a department or all rates for a job description. My initial thoughts revolve around two tables; one table represents the job description; and the second table presents these rates. I just need a start point for ideas. Screenshots or a working model would be ideal. This database is not a human resources solution. It serves to track rates across similar positions when the titles have small deviations. It has more in common with a project management solution.

Now, I have a better understanding about Filemaker language, yet remember it does not mean that it represents my native tongue. I have been evaluating the suggestions thus far and have not made any decisions. Translating these suggestions based on previous database experience remains the biggest challenge for me. I have many small stupid questions about comprehension. Do you mean this or that? I wish the chat module worked. Let's start with the basics. We all agree that the tables will include the following. Contact Company Project Addresses Communication Points Activities For the most part Contacts remains the body. I don't understand what Fenton means by a Branch table. The Company Join table has a field for Company Type, which designates the company as corporate, distribution, production company, etc. The Company Address Join table contains an Address Type field designating the location as branch, location, etc. This organization structure keeps three levels of tables. The hard decisions remain linking them; and naming the primary and foreign keys. Table occurrences and TOGs are a new concept for me. Please specify when you refer to a TO, the original table, or a join table. I need an Access -> Filemaker dictionary. Filemaker developers seem to have a different approach to naming primary and foreign keys. Filemaker Developers name the primary key ContID and the foreign key, recordNumber. What do you call a table that has a primary key and foreign with no join table? Let me know if a naming convention exist. Here's my hack approach. TO_Cont ->TO_joinContComp -> TO_ContComp TO_Cont ->TO_joinContAddr -> TO_ContAddr TO_Cont -> TO_joinContComPt ->TO_ContComPt TO_Cont ->TO_joinContProj -> TO_ContProj TO_Cont ->TO_joinContAct -> TO_ContAct The limitation in relational structure is that addresses and phone numbers are not tied together without an additional TO_joinContAddrComPt. This path presents a challenge in Contact layouts since its several levels deep. The Activity table poses several considerations as well. Presently, my database has many2many relationships. Activities may be better suit for a one-to-many relationship. In addition, they may include a company, project, etc. I am still working on a better approach for it and feel that the main starting points (Contact, Project, and Company) should be addressed first. One attachment shows the relational structure in a text documents. It serves as a quick diagram. The second attachment contains the database with an extensive relationship graph. This illustration is the starting point for me; and does not represent the best approach. The activity table has not been thoroughly thought out. The layouts are mess. I am still fumbling with the graphics and organizing data in a tab interface. Filemaker 8 with tab controls would be handy at this point. Filemaker 7 will do for now. I need help on the interface for displaying multiple addresses and phone numbers. I would like to create a tab interface for the addresses like Business Tracker; the scrolling portal could be cumbersome. Does this objective seem overly ambitious? Let me know you thoughts. JobOrg_TableOccurrences.txt JobOrg_1.02.zip JobOrg_TableOccurrences.txt

Based on Fenton comments, a hierarchy approach from the contact record seems doable. But his approach will not eliminate the need for many2many relationships. Here are point of views necessary. For a Contact, list all companies For a Contact list all projects For a Company list all contacts For a Company list all projects For a Project list all companies For a Project list all contacts For a Contact, Company, or Project list all activities Now, comes the tricky part. The database serves as a networking tool. I need to keep track of emails, phone calls, and submittals for follow-up. An activity table may address this objective. Some hard decisions need to be made like how to link the activity table to the Contact, Company, and Project table. Should the activity table have foreign keys from Contact, Company, and Projects table? Or should the Contact, Company, and Project contain the foreign keys for the Activity table? Addresses and phone numbers remain another challenge. First, I want to separate the data into their respective tables. Phone numbers are no longer tied to addresses or just a company. In my database, phone numbers will include main & fax numbers, multiple emails, websites, and IM addresses. Let me create an overview. Contacts have personal addresses and temporary office addresses; as well as company addresses. Companies have addresses for various locations and divisions. Projects have addresses for various locations and do not always reside at a company. Again, the database should help me to keep a mini-history and perform the necessary follow up for a freelance professional. It does not have billing and invoice objectives at this point, yet it should track resumes and their effectiveness. It should organize people and track opportunities for follow- up. For example, Jane Doe has given me insight to 20 jobs over the last six months. She will be the first person on my email blast when looking for new opportunities. Some contacts serve as networking points and information providers. Another example, I have submitted 3 resumes to DD over the last year. Let me include this info in the next email to them. My industry hires people on a per project basis; then releases them until the next project. Networking allows one to stay continually employed. It is a difficult art until you become well established. My goal relates to organizing these efforts in a more scientific manner and minimizing the downtime.

Are you calling the joint table what I call the relational linking table? In my file example, this table is trelContAddr. Where is the multi-line key? Do you mean the checkbox field which acts like an option group/radio buttons? I am just discussing terminology here, so I can ask the appropriate questions in a language you understand. The sample file remains a big help.

The sample helps me a great deal. Thanks! I will need a day to review the database and post more questions. About cathegorization… It is a table occurrence based on the person table. How would you classify and describe the purpose behind this relationship? Right now, I am just trying to understand the language used in this thread and switch my brain to Filemaker nomenclature.

Even with all my previous database experience, the points made in this thread are beyond my scope. The discussion is highly theoretical and academic, which may be on track, yet cloud my mind with nomenclature. Illustrations and samples would help me understand various scenarios. My data scenario is complex. My previous development efforts have proven it. The data relational structure remains a subset of the challenges in my objective. I understand data relational design fairly well. I do not understand creating Filemaker layouts and portals with complex relational structures. Nor do Filemaker reference books help me beyond the fundamentals. In Ms Access, the sub-form represents a portal. You base the sub-form on a query, which contains the intermediary table and the foreign key table. These queries allow one to create the many-to-many relationships outside the relationship graph (where they exist as well). In Access, these many2many relationships are a great challenge as well. But SQL and queries help you filter the data in many ways. Thus provide you with methods to find, update, and delete records. One application, Jobtabs, represents my desired goal in features and data organization. The activity tabs easily cross-reference each task with contacts, companies, or recruiters. So Jobtabs let you know what people in your network led you to a job opportunity. Unfortunately, the application has several shortcomings like frequent crashes and the inability to export data (which ties you to one application). Presently, Jobtabs list layout is not ideal for numerous entries. In addition, it does not allow you to filter or search through the data. These other databases solutions serve as inspiration, yet I need a Filemaker solution for cross platform support. A Filemaker solution will help me move with the times. Even though Filemaker is desirable, the learning curve is not. I have to appropriate development time from my job search efforts; so need a quick start guide in terms of my data structure and some guidance at various junctures along the way. At this point, I need to make some hard decisions about data organization. I may not understand TO (Table Occurrences) and TOG (Table Occurrences Group). The concept seems simple enough, but my mind has trouble coming from other database packages. I am guessing that TOGs are similar to queries behind forms in other database packages. If I am on track, then the scenario would intimidate me since my previous database had hundreds of queries. The relationship graph in Filemaker would be huge with this many TOG. Even though I have read about the TO and TOG, an illustration or sample would help me visualize different relationships and layouts. I am not looking for a ready-made solution, yet an intermediary development project with some data. If you want to view my Access solution, pm me and I'll email you the file. It may help you understand the big picture from my viewpoint. Link to Jobtabs http://www.jobtabs.com/

Before moving forward with the database, I want to gain valuable insight on this exact topic. As you mention, do I need the additional linking table? I do not have the same experience as a developer who creates 100 solutions a year where I may anticipate future challenges with layouts. In my experience, these scenarios about duplicated data were a significant challenge for me in other working environments. The solution and maintenance tools took me over a year to work out. I have no idea what you mean here. I am trying to visualize this concept, but do not have any reference points. Unfortunately from this point forward, you have lost me. Multi-lines keys are interesting, yet a new concepts for me. I need to open the debate on organizing my data and classifying it before learning individual tactical approaches. My relational structure is very similar to my previous solution in another database environment; and has been battle tested. It was time for a complete redesign thereby adding resume tracking and converting to Filemaker. My objective in creating this thread relates to re-evaluating my data structure in the context of Filemaker 7. Hopefully, someone may lead me along the way since it is a complex scenario. Contacts have individual addresses since my data deals with people who work freelance. They may even work at multiple companies and multiple projects at the same time. This point is very relevant and viable. The insight from this thread will help evaluate the data again and make the hard decision whether to use this approach or split the data across multiple tables. A haste judgment on pursuing this design needs to be avoids at all costs. Yes, in my scenario everybody may work at multiple locations (addresses) for the same project or different company. Please message me if you need further specific details on the industry and other scenarios. An industry explanation will lengthen this thread a good deal and confuse casual readers.

Thanks for all the great insight. Comments on the actual database structure would help me a great deal (I'll pm you the URL address to d/l the file). I am starting from the beginning rather than converting a solution. This database will serve as a complex contact manager and resume tracker. I hope to incorporate some of the features from the Filemaker's business tracker sample. Features like tab controls, multiple addresses per contact, multiple phone numbers, list view, detail view, etc. Instead of invoices, it should associate contacts, companies, and projects with tracking resumes, letters, and phone calls. The complexity begins with the many-to-many relationships existing in this data structure. Every contact may have many addresses, which is a one-to-many relationship. Every address may have many contacts. These same addresses may have many companies and many projects. Now, you have a many to many relationship. Thus this situation may present a need for intermediary linking table. These scenarios present a challenge when setting up the data structure and implementing new, delete, and find records functionality. Filemaker's native buttons do not always work for these portal records. In addition, Filemaker 7 does not have the native tab controls and require more forethought before creating the interface. I need comments on my present data structure and how to link the data. The current version is a bare canvas with a rough sketch. Development begins with the contact data and does not include the company and project links. Assistance on setting up layouts for the multiple addresses and phone numbers would help as well.

I need some advice on creating a complex contact management solution in FM7. The main objective involves tracking my resume submissions. It serves as a resume, contact, company, and project tracker. My questions revolve around defining the database's relational structure. I have not found and advance tutorial on database relational structures. Let me start with the simple part. How do I create a relational structure for multiple contact points (phone numbers)? Say you have a contact record with multiple phone numbers, but you do not want 50 fields for potential phone numbers like home, mobile, email, personal email, website, intranet web address, etc. You create a table for the contact data; a table for the phone numbers data. How do you link them together? Do you create an intermediary table? Do you place corresponding key in each table and forget about the intermediary table? Now, let me make the scenario more complex. Add multiple addresses like west coast office, east coast office, production department, and accounting department. How do you create this relational structure? This database should track submittal activity or any activity like a phone call. But this activity could relate to a contact, company, or project. How you account for this relational structure? These questions seem fairly elementary, yet I am beyond the fundamentals. I need a resource for illustrating complex relationships, table occurrences, and attaching them to layouts. Think about the business tracker solution on the Filemaker.com website. Does a tutorial exist for creating a similar database?

After turning off the Global option, the calculation field began performing as expected. Thanks for your comments they have shed some insight on the situation, yet additional idiosyncrasies appeared. Now, the layout shows the tabs graphics and appropriate labels. The tab graphics and labels change when you switch layouts. But the layout does not gracefully go into Find Mode. The tab graphics and labels disappear in Find Mode. In addition, the database solution has a built-in Find module. The interface example does not have the same Find Mode behavior; yet the my solution and the example have similar construction by design. I can not help deduct that this scenario relates to the

I am working on an interface solution and have a question about calculated fields. My database has calculated fields for displaying tab graphics and the related labels. These calculated fields do not seem to update automatically. In fact, their behavior is not predictable; thus they are broken. The situation has me scratching my head as I have elementary knowledge about these intricate calculation fields. Upon reviewing another interface example, the calculated fields have both check boxes under

Ok, the global error has been over come. But it was related to a FileMaker 7.0 bug; and the FileMaker Update 7v.3 resolved the situation. The text box disappearance related to a one pixel shift into the header. I am still developing a tab solution with considerable difficulty. The Find mode gotcha presents additional development or other hacks. The main issue relates to my elementary knowledge of scripting in FileMaker. I am still learning the workarounds and basic concepts. Let me know if you have any sample exercises and databases to build upon my reference book resources.

I have been working an interface solution for some time now. One solution has form the basis for my interface; 15 Minute tabs (http://www.filemakermagazine.com/) . In order to develop my own solution, I re-created Matt Petrowsky solution. My re-creation left me with some perplexing questions. First, one particular text box disappears when I place a background pattern on the body part. The text box just has a caption and is not attached to any field. When I remove the background on the body part, the text box appears in Browse. The Text box appears in layout with or without the background. I tried Arrange > To Front and still could not resolve this odd behavior. My second question relates to the 15 minutes solution itself. It has a major limitation when entering Find mode. The Interface graphic vanishes. Upon reading the comments at filemakermagazine.com, someone suggested converting Tabs_Display into a GLOBAL. Filemaker will not allow me to change this field to a global. Filemaker keeps returning the following error message. The calculation