Relational Database Theory

Unfortunately because of required access to various files, someone renamed a couple related FileMaker 9.0 databases and now, of course, every time I start either file, I get the message that the related database cannot be found. Sure, I can navigate to the 2nd one and open it that way BUT............ isn't there a simple way to "erase" this problem and make FileMaker automatically recognize the two riles are related without having to navigate all the time. - THANKS!

I've been reading up on the Anchor/Buoy methodology developed by Kevin Frank. I like it so far but I have a question (some questions, but only one now). He states the following: "All TOs are not created equal. The first TO created for a given base table should always be used as the anchor. This guarantees that calculated fields and lookups always "start" from the correct TO". My question is I thought all TOs were created equal. What's the meaning of his statement? Why can't you use any TO as an anchor?

I "lost" a layout yesterday, while working on it, by simply changing a the layout view. Fortunately, I had a "back-up," but not on this computer. This opened the question of how FM 8/8.5 handles files. I thought I was being wise when I saved databases in different directories after working on them. This was probably the wrong thing to do. On opening my database, I started using "recent" to avoid the error message: XXX.fp7 could not be opened (Not Found). When I looked in the "file reference" section, I found 6 files I hadn't opened in over a year!! And, when I tried "define database," I got the error message above again. So, I tried to read everything i could about…

I have a DB that has the tables patient and patient samples. When creating a sample from a patient, at the moment, the user has to create each on separately. In most cases, the user knows based upon the study what samples they need to take. I want to help them out by creating a template creation so that when new patients are added, they just need to select the template (dropdown or pop-up window) and hit create samples. When creating the template, I've thought about two methods -create all record and just import into the samples table and replace the foreign key with the new patients primary key (extra records) -create one record with the rel…

First, let me apologize if my question is hard to follow. I'm completely new to FM (and databases in general) and find explaining my question a bit...difficult. I'm trying to set up a database for my company. We sell a product for use with septic systems that has a PO, an owner of the site the system is for, a designer who designs the system our product is a part of and an installer that installs the system our product is a part of. I originally set it up with 3 tables: Table 1 has the main project interface layout that has fields for the PO, the lot owner and their info, and our model and date installed, as well as a tab for the designer company and info a…

Ok, first let me apologize if this is too basic of a question. I had developed some pretty solid database structure using Helix Express 'back in the day'. Maybe the experience I got from that to Filemaker is the reason I'm stuck now. At least I had database design experience to work from. I have a very basic application to program. 8 guys playing golf for 4 rounds. After each round I calculate things like handicaps, different payouts for the current round etc. I have set up the database in FM as 18 holes (A1...A18 then B1...B18 for the next round etc) and their name. My thought process is that one record consists of the Golfer and A1..A18. All my…

(Sorry for my poor english, i also use french version of FM so some terms might be incorrectly translated.) I'm quite used to database stuffes but cartesian product isn't working the way I expect it to work. Maybe you can help me on this one. In a table TSESSION I store settings, filters, sorting orders, etc. for the current user. I want to use these settings to filter out any table based on TSESSION: value. No big deal! Let's say I have an TINVOICE table i want to filter out based on settings from TSESSION for THE CURRENT USER. I have 4 sessions, 20 invoices. I link TSESSION::ID to TINVOICE::ID with a cross join relation (cartesian product). I EX…

I am building a call log database. This database has 3 tables. The Call Log is where all the details (date, time, reason, who) are stored. The Contact table is where all the names of the people who called are stored. Keyword is a list of certain keywords that a script uses to find out how many times a keyword comes up in the call log. My question is, currently the script I wrote goes to the keyword table, sets a variable to the name of the keyword. Goes to the Contact table and sets another variable to the name of the contact. Then goes to the Call Log, enters find mode, then sets the contact name and keyword. Then performs find and reports the found count. T…

I am curious as to how everybody brainstorms or visualizes in early development- Does anyone use software to make ERDs? I've been learning alot from Petrowsky's site. Extremely useful stuff for a beginner like myself. He uses EXCEL to plot out tables and fields initially. That is not for me. I'm a pretty analytical guy but I have to use spatial diagrams that I can manipulate quickly and intuitively. I can't find any free programs that actually reflect cardinality and optionality as I understand them but I've been using CMAP Tools to conceptualize my entities and potential relationships. It can export to many useful formats (.svg, pdf, html etc). I've attached…

I am in the process of building a DB and running into a situation where I can use some help. Db has many tables , but pertaining to this situation, we need consider only 3. OrderMaster (OM) â�� Table where individual orders are stored. StatusMaster (SM)-table where individual statusitems are stored. StatusJoinTable (SJT) â��This is a Join Table between OM and SM. (One status item can belong to many orders and one order can have many status items). I currently have portal in OM (from the SJT table) table listing out the various status items and their attributes (like date rcvd, due date , status etc ). So ALL the status items show up in this one …

I have a table with 200k records. Lets say I have the following data: [color:red]PK | [color:blue]KeyWord | [color:orange]Data [color:red]01 | [color:blue]Search Engine | [color:orange]Google [color:red]04 | [color:blue]Search Engine | [color:orange]Yahoo [color:red]05 | [color:blue]Foo Bar Foo Foo | [color:orange]Bar| [color:red]06 | [color:blue]Search Engine | [color:orange]Alta Vista [color:red]09 | [color:blue]Search Engine | [color:orange]MSN From each record that matches on the KeyWord, I want the previous matching record to be seen, E.g: PK 09 would see, PK 06... PK 06 would see PK 04... PK 04 would see PK 01 PK 01 would see nothing …

Could someone remind me again why we use more than one table? The more I think about it, the more it seems purely cosmetic. I am considering implementing an upcoming DB with all records in a single filemaker table, and do all the normalizing through a tTable field that would be used in relationships. Anyone care to talk me down from that ledge? -Raz

I have some good FM books and training materials, but would like to buy one general RDB theory / design book (relationships, data normalization, ERDs, etc.). I've seen a few that look interesting, particularly: "Relational Database Design Clearly Explained, Second Edition" by Jan L. Harrington Any recommendations for an intermediate FM developer appreciated. Bob

Today, I was at a sales presentation for a software product that ties Autocad to a database. It became quickly apparent that they had the database very badly structured. I was trying to find the correct term for what they did, but in reviewing my books on relational theory, I couldn't find the term. Here is what they did: Consider a table that contains all the employee data for the London office. Now, suppose you add a Paris office. They create a second table with exactly the same fields as the first in order to hold the new data. New York office--same thing--new table. No way to create summary reports of all employees without some horrendous scripting. This is …

This is a trivial problem for someone out there with more brains than I've got. My DB keeps track of cleans for coolrooms. The operators use up to four different cleaning products. They use whichever batch no. is 'Current'. My customer needs to keep track of the dates during which a particular batch was used. The detail of the calculation is simple enough; it's the structure that has me tricked. I've ended up with four extra table, one for each product. The fields in the tables are simply Date Started, Batch #, If Current. This just seems kludgey. It's also a PITA because this is a runtime solution and upgrades mean importing existing data - 4 imports to go w…

I am wondering how other developers have implemented a generalization hierarchy structure in FileMaker. I am referring to supertype/subtype generalization which is often used in object-oriented systems and known as classes. There are normally three methods to map this in SQL: 1) Use one table for the supertype with separate tables for the subtypes (AKA Separate tables method - a compromise method, which can be complex to comprehend) 2) One single table with fields for both subtypes, where the fields are null for some subtypes without certain subtype attributes (AKA Single table null fields method - probably easiest but messy due to all the nulls) 3) A…

Maybe this has been discussed extensively elsewhere, but I am wondering about the strategies and pros and cons for structuring tables and files in FMP 8. I started out in FMP 5, where there was no option to include multiple tables in one file. Several years later, I am in FMP 8 and still learning what I have missed in the upgrades. I have read some things about tables/files. One source recommended separating out a table into a separate file if it was very large and required a different method of backing up. Another idea was to separate out, for example, calculated inventory fields into a table separate from product information. This was used to prevent problems when …

Hi all, It is often the design decisions that I find most difficult in a database. Here I have a general (hypothetical) question: - Would you suggest a company database to be global (monolithinc) with all data in a central location and provide a UI that gives selective access to the data? For example, suppose you are designing a database for a large parent corporation that acts as an ubmrella for several smaller companies (including franchises, subsidiaries ... etc)the child companies might not be offering related services (for example one might a bicycle company and another might be an exercise equipment company). Should the database be built so …

Hi all, After laying out the foundation for a database, I caught myself encoding some layout interface functions into scripts and the scripts were using global variables to store the status of a layout (toggle buttons ... and the like). ... however, on second thought ... isn't this a bad idea ? For example, if we have two users signing in as guests and one of them makes a change (presses a toggle), then the other will be seeing those layout changes happening to his screen all by magic. No ? So, here I am, back to the drawing board trying to design (or simplify) the UI and avoid use of global variables for the UI. Your thoughts on this ?

I have tried very hard to normalize my data and have entries in line item table. However, sometimes you want to manipulate the data to look different for reporting an a line item is not the best way. In the attached file does anyone know how to convert the top into the bottom table. I need to export into excel (a subsummary report is not helpful) to send in a mandated report.

Welcome to the Champagne Room. While you are waiting for a seat at the Singles Table, please remember the following: The Champagne Room (CR) is an afterhours thread where all are welcome to come unwind and flirt with something new at the Singles Table. Here, we do not care about such mundane issues as practicality and establishment standards, These issues are gladly welcomed on This Thread, but will be considered a bit gauche here. Eyes will be rolled. The CR is reserved exclusively to play with the powerful potential of a single table data file, and for the fun at trying something new. Highlights: -Single calc data spirals to access all remote relatio…

Just want to start off by saying thanks in advance. I've been coming to this forum for awhile now and always recieved great advice. I've been playing around with filemaker trying to design an application my company has been wanting to implement for a while but has never taken time to do. I wouldn't say I'm new to FM7, actually I would say I understand some aspects very well, but others I have no grasp of. What it is is a database to help order tracking. What makes it difficult, or at least for my abilities, is how to track data flow. I work for a job shop so each order is unique. We have seven departments that are involved; esimating/sales, engineering, burning…

As Kevin Frank uploaded his recent Powerpoint presentation, I thought I may post a link to it so that you can review the "Anchor and Buoy" theory... Kevin Frank Web site

I have three DB, one Personell records, one Courses and a Master called Attendance. Here at the Library, where I work, I use FM Pro7 for Windows. I am doing a lookup from the personell DB and it does OK. I am also trying to do a lookup from my Courses DB into repeating field. I got it to works on field 1, but when I tab into field two, I can see something is going on, but nothing shows up in my two fields?? I did exactly the same design on my Mac w/FM Pro 6 at home, and it worked like a charm. I brought that database in to the library, converted to FM Pro 7 (Windows) and it is not looking up the record. Except for the first one. Frustrating!1 Anybody has a clue?…

Hi. So I have this data base with two different tables. One of them is called item and the other, move date. There is a relationship between the two (item i.d.). I want to be able to track all the places I have moved a certain item. Say I am moving out of my house and I would like to track where all the boxes go, just in case I lose one. So I want to input data into the item table, giving the date it was moved and where it was moved to. When I input this date, I want the move date table to update for that specific item and keep a list of the move dates and the locations on those move dates. When I create a new entry, though, and I type the same item name, it autom…

Hi, i a m new to filemaker and i am having a hardtime with the logic behind it. I have created a database for tracking my freight business. I have rates depending on the following criteria : origin and destination of the trip, client, and subclient, rate, etc. ( this is the rate table) Depending on these fields (which are in a table) i do the programming of the trips on a second table which i called "TripProgram". Now, if my customer changes its prices i would need to enter a second entry in the first table to include the updated rate. However, i dont know how to have filemaker lookup in the RATE TABLE based upon ISSUE DATE AND EXPIRY DATE OF THE RATE how to select t…

I am contructing an electronic medical record. I have a patient's medication table related the patient demographics table. Of course many patients will be on the same drug. I will have a master drug table which will facilitate rapid entry of medications into a patient's medications table. Now I could use a join file, med_record_# to Drug_ID#. But in this case if the user wanted to enter a drug into the patients medications table (the join table) that was not in the master drug database, he would have to stop and add the drug to the master drug table first. As well, any corruption to the master drug table would affect many patient records. In this case, is it acceptable …

I have a DB with 5 records and one field in each record that contains the names, Al,Barb,Carl Doug and Ed. Now I want to have a new DB where there are 5 records and 6 fields. The first field contains the same 5 names as before, then field 2 contains the name Al in all of the records. Field 3 contains the name Barb, field 4 contains the name Carl, field 5 contains the name Doug and field six contains the name Ed in all 5 records. Next, I want a checkbox next to each field so that I can put a check in the box which will combine the contents of field 1 with contents of any of the other 5 fields into a new record. Example: record 1, put a check in the box next to field 4 a…

I was watching a video at Petrowsky's site and he said that every table should have as it's first field, a number field labled ID, auto enter serial number. My question is: is this necessary in child records? What is the purpose of doing this? Toffler

In my SQL (mis)adventures, I've been very careful to only query/relate tables by integer primary/foreign key relationships. In Filemaker, I find that I'm forced, as often as not, to use relationships based on often lengthy text fields (like category names and so forth) in order for value lists to function properly. Is Filemaker somehow creating PK/FK relationships in the background to speed up these operations, or am I heading for a world of hurt when my solutions grow beyond their current (small) size? If the latter, how can I avoid this kind of problem and still benefit from value lists and other niceties which make FMP such a wonderful rapid development envir…

I've been assigned to redo a library type system. The current system was done in 4 and 5. There are 3 main db's, one each for pictures, documents and audio/video. Each one has a few similar fields but most are different. There are 3 category db's, one each for building, corporations and subject matter. There are 3 line items db's. So the pictures database has 3 portals to show any buildings, corportations or subjects related to the picture. Same with the other two - documents and audio/video. I think I should combine the main db's into one table and the category db's into one table and line items into one table and have one portal in the main for…

Can anyone give me any pointers for analyzing an ERD for problems? I understand how to put together simple diagrams, but how do I spot problems in a complex business solution. How do you test the ERD on paper before you start building the database? I have read what there is in Using FM 7, as well as a couple of white papers, but I am still having trouble. Any help would be appreciated.

Hello all, again a q. that may help me understand the details of FP7 databases. I post it in this forum because I am puzzled in the context of building a SM-designed solution, though it might as well belong to the "Security" forum. If the q. is dumb, well, maybe I am. I look for a detailed documentation of what "referencing a file" means versus "opening a file". My own observations as of now are: 1. When I open a FP7 file that has a reference to another FP7 file, the referenced file appears in the "windows" menu in parentheses; the "on open" script of the referenced file is *not* run. 2. When I later access that file that requires an "open" file (create a re…

Hi All. I am getting started with Filemaker Pro 6 and would like to know if someone can help me with a problem I am experiencing. I have created a Job Tracking & Invoicing database for a existing client of mine. There are 6 DB all up together. DB 1 is the Main database DB 2 is a DB for "Lump Items" DB 3 is a DB for "Material items" DB 4 is a DB for "Others" DB 5 is a DB for "Less options" DB 6 is the Invoice DB How it currently works: I use the Main DB to create a new Job. With each job you can have 4 or more different categories to bill. The Categories are: Lumb Material Others Less That explains the other D…

HELP! I am familiar and use to using Access, and I am now teaching myself FileMaster Pro 5.0. for a new job. I am having a problem relating the different terminology and functions in FileMaker. Can anyone help me with the FileMaker functions & terms, and how they = Access Termonology & Functions, so I have an easier time learning to use FilePro? I am baffled, and getting frustrated with it! New User Deb

Currently I have a relational DB made up of CONTACTS and COMPANY. Both files have key fields such as Company ID and Contact ID in both files that are related to each other. Now that's simple enough...right? Well, When I needed to add a third relational file called PROPOSAL using Company ID and Contact ID as the key fields that will match with the other files, it didn't work. Can someone tell me what I did wrong.

This is interesting: I created a many-t0-many relationship using three tables, TableA, JoinTable and TableB. Tables A & B each have ID a serial #. JoinTable has TableA_ID, TableB_ID. I have relationships from TableA to JoinTable and from TableB to JoinTable. All my relationships allow creation of records via the relationship on both sides. (so a portal in TableA can show/create JoinTable records, as can a portal in tableB) Now, in a layout based on TableA, I create a portal and it gives me the option of showing records from TableB, even though there is no direct connection between the two. "Interesting" I thought whilst munching on my Hot Pocket. I cre…

Hello, I was wondering if someone can stirr me to the right direction. Basically i need to develop attendance database.I already have a database which is student record. Now, I want to track the attendance base on Classes . There is the tricky part, i am having issue with going about this. i know that i need to develop some kind of relational database. the classes type are, Anatomy, Technique, Business, Hydrotheraphy, Internship, Physiologyy. I want to record the hour miss and the classes the students attendance. if anyone have example file that i can look at, i would appreciate it. i running fm 6.0

Hi, I'm using FMPro 7 to build a simple accounting system for my business (I'm a photographer). I have 5 bank accounts in 3 currencies to cater for and my accountant has asked me to make it effective for 'double entry' accounting. I have a table for debit and another for credits and yet another for 'position at bank' (the 'starting point' for bank balance calcs). I will need to produce reports for my accountant, with a running balance of my bank accounts and it would seem best to me that they be date sorted with debits and credits intermingled as and when they happen and with the corresponding bank balances calculated and displayed. I envisage all the cur…

Hi, While FM7 brought some new relationships optimization tools, and a better Relational Model, it seems to me that we'd need to break the rules by scripts even more than with prior versions. What do you think ?

Hi, My current solution have Quotes, Customer Orders, Deliveries and Invoices, for the "Sales" part of it, each being a File with a Many to Many relationship to the other through a Line Items join. With 7, each module became a TOG relating to a Line Items TO, that has 4 separate Foreign Keys (QuoteID, CustomerOrderID, DeliveryID, InvoiceID). What could be the problem in having one ModuleID and a Module Type field instead of the 4 previous Module keys ? I can't see any difference at the moment, and I'm even thinking I was a bit overcomplicating things with my v6 version. There must be a reason why I did that on the first place, but it now makes no sens…

I've read the manuals and articles I could find on the web. I understand relational design I think. I understand the different types of relationships and I understand the need for primary keys and foreign keys. But.... here's what I don't get.. When I'm listing information in a portal from a related database for a user to pick from, the "key" field is usually a meaningless number to the user.. If I'm assiging students to classes in the class assignments table via a portal, I don't want my user to be bothered with knowing the class ID. My question is, is there a way I can avoid showing the ID at all ? and make it so that the user just sees the info…

FMP 6, Mac OS X I'm building a simple Students/Classes-type database. A student enrolls in classes. A class is an instance of a course, plus a date ("Trigonometry I will be taught in the autumn semester and the spring semester" - Trig I is the "course"; autumn Trig I and spring Trig I are two "classes"). I have four databases: Students, Enrollments, Classes, and Courses. I use relationships (and some unstored calculations) to connect the information. If someone discovers a typo in a Course description or course fee, he can correct it and, because of the dynamic nature of relationships, this change is automatically reflected in the Class record and, further, …

Hello all, Been crushed by practical issues lately, and haven't been around the Forum much. (Holding down a job, starting a business, the usual.) But I'm seeing a little daylight again. Now I've got a theoretical problem I'm having trouble getting my head around. I need to create a classroom attendance database. Nothing too fancy. I have a file (People.fp5) where the student names are held. I need to track their daily attendance throughout the school year. What would be the best way to structure this so that each teacher can see a list of the students in his or her class, and mark off who is present on a given school day. It seems this is a many to m…

Why FM? I had a go with Excel, Perl, MySQL (too complicated, hired programmer) I tried Access but am not sure where I can go easily I remember FM from 1990 and it seemed friendly Manufacturing company using M2M (horrible SQL database) tracks manhours, inventory, etc I need to have something easy and something difficult. For one job with 500 widgets Easy - import tables from excel create relationships, make reports That data needs to create a MS Project Style Schedule preferably by sucking manhours out of M2M SQL DB or by simply importing, exported excel data Now add PDA to walk the shop floor and track work in progress, update master schedul…

I tried asking elsewhere, but this area seems more appropriate. I have a simple DB setup where I have a separate DB for each project my office is working on (ProjectDB), all link to my ContactsDB. I would like to Merge all my projects into one DB. Issue: I am thinking of setting up one master ProjectsDB whereas I can switch between different projects on the fly, so the DB only reflects info related to that particular project and only allows info to be entered for that project. This is just for my correspondence files (faxes, transmittals, memo's, shop drawings, logs, etc.) and I would like it to be idiot proof. My current ProjectDBs have a lot of auto-enter data…

ERD Confusion - Can anyone provide helpful advice in untangling my brain? I'm attempting to put together an ERD, but I'm starting to confuse data structure with how I wish to control the input of data. Here are the basics, in plainish English: An organization hosts different programs that involve school children (in teams) and volunteers (adults who take on different volunteer roles). The programs are comprised of activity types (competitions, committees, mentoring, other events), and volunteer roles are defined in terms of these activities. So, you can have Program 1, Activity 1, Role 1; Program 2, Activity Type 1, Role 1; etc. Teams ca…

Learning as I go along while designing the information management systems for my company probably hasn't always been based on the best design possible, but figuring out the way I could make it work. But I am trying to understand good design. I'm preparing to launch a Time Sheets database that will produce a daily time sheet for each employee with a related Time Line Item database which contains the time increments spent for each client job number as well as other overhead line items. I want some of this data to end up in our main Client Jobs database file for any time increments which relate to a client job number. Concerns: I have Graphic Artists and Desi…

I'll try to explain what i want. I've got a customer (ID,name, adress, etc.) The customer wants to buy a boat, and i want to send him an offer wich i would like to store in the database also (for future reference)(type of boat, customerID, price, options, etc.). One customer could also have more than 1 offers (some past validity, some still valid) (i suppose at this moment i need 2 tables, so one for the customer, one for the offers) The story continues, cause i want to make the offer also using FM7, so i search for the customer and push a button add offer... FM then takes me to a screen at wich i must first select the type of boat the offer is abou…

Hi Forum, Gurus and all users, I need some comments on this please. Some of you may already have noticed that I have that bad habit to use Relationships for any "sauce". This will be *double* true for this post... One major issue I wanted to solve in joining the Forums was to learn how to correctly adress the Relational Diagram issue I had in my own FM business solution. I started an update, which quickly became a complete rebuild, by maximizing the use of Relationships. As I started as a developer some month ago, I decided to use my own files as a Visit Card and somehow a "Laboratory" for some experiences. So here's the point I'd like to di…

I am very new to FileMaker. I am creating a database for a middle school, and am struggling with how and where to store students' parental data. Students data is simply FirstName, LastName, MI. Parental data includes FirstName, LastName, StreetAddress, City, State, Zip, DayPhone, EvePhone, CellPhone, email, etc. Of course a student may have more than one parent. Parent may or may not share the same last name, address, phone, cell phone, etc. Also one or more parents can have one or more children (students). One child in a household may have a different combination of parental contacts than her sibling. The data will be used to 1) look up a student's parental c…

I'm puzzling over how to best organize a DB structure--perhaps just thinking out loud and looking for some feedback. This is for a nonprofit group in which we need to track info on both family units as well as the individuals who make up each family (parents and children). What's the best way to organize the relationships to do this? Or what are the right questions to ask to decide that? Should I be entering individuals and then combining them into families, or entering families with related members? There really isn't any distinctive "family" info that's true only of the family (other than the list of people who comprise it)--even the addresses might be different i…

Hi, I am trying to develop personnel-scheduling database for my Anesthesia practice. This can be looked at as a baseball team, where each player is assigned to a different position each day. There are more players on the team and positions, so some players will either be off or on vacation each day. I'd like to be able to go back and tabulate how many times in the year each player has played each position, as well as how many times each player has pitched on the weekend. I'm having trouble conceptualizing the setup and relations for this. I have a table for Players and a table for Positions. Could somebody please give me some guidance on how to proceed from here? …

I have see this done many ways and have been reviewing many resources What are the pros and cons on the implementation of this scenario Tables: Company Contacts Locations Phone Each Table contains data unique but each the company and contact could have their own addresses and phone numbers How would the Relationship work best? Contacts to Phone ContactID = ContactID Company to Phone CompanyID = CompanyID In the Phone table have the following fields ContactID CompanyID Phone Number What of relationship between Contacts of the same company? Would a join table be the correct approach? CompanyContacts Co…

Hi all, I'm not sure if this is the right place for this kind of question, but I'll try to describe it as fully as I can. I'm receiving an error when using the Relationship Graph in a demo copy of FMP 7 for MacOS X. The error dialog reads "There cannot be more than one relational path between any two tables in the graph. Another occurrence of one of the tables must be added to the graph." With a text field labeled 'Name of Occurrence:' and populated by the name of one of my Tables with the number 2 appended to it, and the 'cancel' and 'OK' buttons. I received this error when attempting to add a relationship from one table to another. Now, it's ver…

I'm currently developing an inventory and POS system, and I have a few files that store the financial transactions: purchase (inventory reduction), orders (inventory increase), and one for miscellaneous financial transactions (supplies, copies, donations, etc.). I've created a reports file to show the end-of-month and YTD financial summary, which is related to the above files. But, I would like to show a monthly purchase summary, and I'm not sure if this should go in the reports file or the purchase file. Since these two reports are different from each other, my sense is that if they both go in the reports file each report will have a bunch of extra fields that don't pert…

I want to archive a Filemaker Pro relational database that consists of 8 files. I need to rename the files so that they are different from the "live" files. Is there a way that I can rename the files but don't have to reestablish the relationships, and fix the scripts one by one? Your help will be greatly appreciated!!!

Im having trouble finding the most efficient way put this database together which is designed to output a weight lifting workout from a database the desired output is a page with the users name, and lines that include(lift name, set,reps,weight(by percentage); sets, reps, weight) example joe schmoe 02/24/2004 snatch (2x3[200lbs]) || (3x2[210lbs]) || (1x1[220lbs]) benchpress (3x5[300lbs]) || (2x2[320lbs]) " " " " ignore formating its just: lift (set x rep[calculated weight]) ||...... and each set has an id and multiplier which is how many sets per (set) heres a …

A friend who develops in other programs tells me that for a large scale, multiuser project, it would be best to use one file, so as to maximize speed of use. Never having used any other program, I assume the same concepts apply as in Filemaker. In Filemaker, I concieve of separate files that each contain records of a different catagory. I wouldn't necessarily consider putting customers and inventory in the same file. But maybe I could. Would this make the solution faster? I could use self-relationships for all my portal, etc. It would be one huge collection of fields (certainly one advantage of multiple files is it's easier to keep track of everything and to concept…

One of the things that bothers me about FileMaker is the lack of global variables. I know that a Variables plugin is available and would welcome any comments on it. In the meantime, however, I'm trying to figure out how to share information between files without having to resort to all sorts of copy-and-paste maneuvers. One of the ideas I had was to create a single record file called Variables that contains a number of global fields. This file would also contain a variable match field that would be used to relate it to all the other files in the solution. Let's say that the match key is the letter "X". Each of the database files would contain a variable match fi…

Hello everyone. I'm working on a project that involves an invoicing-like system. Let me explain. The company I'm working for is an acting studio. They teach acting classes, hold workshops, etc. I'm about to start building the class enrollement/management side of things and am a little uncertain of how best to handle this aspect of the solution. I have a database containing client records. These are the students. I have another database containing teacher records. I plan to create a Classes database with all of the relevant class information and then a join file to link Students and Classes. Teachers will be linked directly to their Class records. So far…

Hi all, As I fiinally sold my business and I

I've heard many FM developers recommend splitting up a file that has over a few hundred fields. I'd love help (I really just have 2 questions) on how to do this per the following example: BACKGROUND The file is called "People.fp5" and has 450 fields, mostly attributes about the people (just bare with me, I know it sounds strange). 1) WILL FINDING BE SLOWER - If I put some of these attributes in another file and create a 1:1 relationship so that the person's name is still stored in people.fp5 but their race, gender, and birthday are stored in demographics.fp5 and linked by the person's uniqueID, WILL FINDING BE SLOWER?? Say I want to find all people records…

Hi all, I;m new in a position and need to look at enhancing a flat file style filemaker database into a relational database. I have a decent background in relational database theory and in packages such as access and a bit of oracle. Can anyone direct me to a good simple straight forward web reference on implementing relational databases in Filemaker? all help very appreciated. also how do I view posts that I have created. I cant seem to find it anywhere...

Hi, I saw Cael Chappell's reply about Waves in Motion as a provider of a way to diagram your database schema. Are there any other solutions out there? Thanks, Carlisle Landel

I'm not sure conceptually how to do what I'd like to do in databases...essentially, there is a body of data that is linked to latitude and longitude that when the two numbers are given it returns average household income, demographic breakdown, etc. In programming you could use a sparse matrix or two-dimensional array or something...but in databases (particularly filemaker) how could you design something that given those two coordinates would return the record with the appropriate statistical data?

Hi all, I am new to FMpro v6 having mostly worked with PHP/MySQL. I have a fairly basic problem which I could code in PHP but am stuck with filemaker!! The system is for handling customer orders of books and then converting these to supplier orders. The files I have are as follows:- Customers:- cust_id, name, address, etc Books:- book_id, name, title, author, supplier_id (which relates back to cust_id in books, as some customers also happen to be publishers) Customer orders:- order_id, cust_id, dates, etc bookorderjoin:- orderjoin_id, order_id, book_id, quantity, etc (this is for line items of customer orders) supplier orders:- suppli…

I'm trying to implement a Sign-In/Sign-Out database and I have a few questions about how to approach the project. I work for a company that provides services, job training, etc. to the homeless. My goal is to create a database that stores client information and one that stores a client visit history. Clients need to sign in every morning and sign out when they leave. Right now this is done on paper. I'd like to make a database so that we have the ability to easily track how often a client uses services, how often they visit us, etc. My intial thought was to create two related files in a one-to-many relationship. Clients would store the client information and Vi…

I've created an underlying DB for 260+ vendors to report buying prices of 538 different items. I'm using three related files: VENDORS, ITEMS and BUYLIST. The process starts with each vendor receiving a fax with a list of the items for which they have submitted a buying price. It lists their current buy price along with a blank column for them to insert a revised buy price. When returned, the vendor's current buy prices are updated. Then the exported results of all vendor's buying prices are published in a tabloid journal. The VENDOR file is easy, but some vendors will only post prices for 10 items and others for as many as 125 items. The ITEMS fil…

I have been working with FileMaker redesigning a database for my work, and I have run into a problem time and time again. When working with other databases (such as MySQL), I often want to run a query something like this: [color:"blue"]"SELECT * FROM jobs WHERE job_number='1' AND client_id='7'". I have tried to get this [color:"red"]AND part into my FM databases, but I'm having trouble. Is there a way to relate two files using more than one related field? For example, I had a file called "materials" which has a radio button with the options "Duty Free" and "Non Duty Free". It would be nice to be able to pull up a report that lists all items which are "Duty Free",…

I am using a database with several (6) files, and various relationships between them. On opening the main file, they all proceed to open, either through script steps or because of those relationships. A new problem has surfaced: when I open that first file, it proceeds to open all the others, and then one of the last files tries to open one that is already open. I get an error message that it "is currently in use and could not be opened". The error message says it is "not multi-user" (but it is multi-user) or that the host could not be found on the network. I _am_ the host ... the file that "won't open" is already open, and I have no idea what the cause…

Edgar F. Codd died last Friday. He was 79. Codd developed the theories on which relational databases are built. He tried for years to convince the folks at IBM that he had something worthwhile, but by the time Big Blue understood, someone else had bought into his ideas -- one Larry Ellison. In 1981, Codd received the Turing award, which is the highest award for computer science.

Hi, I've got bad feelings about how to standardize a Relational database in a business environment, where many "parameters" can evolve along with the business itself. Many classics One To One relationships are driving me mad : - an agent may be "employed" by 2 different companies, but the standards are that one contact belong to one Company, - a Product may be unique or part of a set or service ..... and many other "odd" situations The more I go on the developpement of the db I'm currently on, the more I feel like there is quite no reason to handle a One To One relationship. I realize even a good Database Map does'nt solve all problems, but.... W…

hello all, i have been chose to take existing database, add to it and create the web availability. existing database has the main file for the pictures and 3 many to many files for the picture descriptions. these are the organizations in the picture, the peoples in the picture and the buildings in the picture. so the one picture can have the many organizations which can be in many pictures, same with the people and buildings. so there is the picture file, the org, peoples and buildings files and their 3 join files. this to me makes sense as these 3 files are not related (for instance the picture can be of the meeting with different organizations and people from al…

I am designing a FMPro 5.5 set up for a small charity who's needs reflect membership, information storage such as affiliation within the charity, factual dietary information, sales and product data and documents stored within container fields, advertiser information for their in-house publications and so on. We have an accounts databse package, so all invoicing is created seperatley. I have come to the conclusion that I need 18 .fp5 files created. The relationships set up between them are mostly straight forward with the bulk of data being stored within an 'address' .fp5 file. This file also stores its affiliation within the charity as flagged fields (advertise…

That is 3 or 4 months that I spend a lot of time on this forum, as I'm planning to totally rebuild my database to suits my own needs. I'm so "excited" to run into it, but I just cannot decide my self to close my Explorer window, open FM and click the New Database in the Menu. It's just that there are so many answers to questions I would never have asked, but that could do a big difference, and tips suggested that would need to reconsider the general scheme of this new database. But here I am now. I just do not have too much time left as I planned to implement this database in a few months. So let's go and see. Nevertheless, I need 4 basic answers before defini…

Hi there, I think this is a fairly simple database design query but have struggled with it over the past couple of versions of our database. We are a charity organisation that deals with both individual and group clients (eg. Mary Smith, Humpybong Special School). In the current client details database, i use a first name and a surname for individuals. With group clients the group name goes into the surname field. The details are almost the same for both types of clients except the individuals have both home addresses and business addresses while the group clients only have business addresses. I'm working on a new version of the system and have experimented wit…

Hi everyone, I have a developed a database system for the company that I work for which contains about ten files eg.Contacts,Invoices,current projects,Timekeeping etc. The contacts file which is the largest contains about 3000 records and the whole system totals about 20Mb. I am currently studying Object Oriented system development and have been experimenting with a rewrite of my database, I have normalized my files and broken my system down into Classes and objects,my Contacts file is now complete and has been turned into four or five seperate smaller files. My question is has anyone else used this method to develop Filemaker? and what are their exp…

In my inherited database I have seperate db files for all different types of people: coordinator, investigator, site investigator, etc. This is yuck-o. I am trying to get all of the basic person info (name, title, degree, etc.) for each of these groups into one file: People. Also, I am trying to build a custom login system. I am creating a file called Users to facilitate this second task. Should the Users file have a PeopleID foriegn key or should the People file have a DBUser foriegn key? I currently have the PersonID foreign key in Users but I am having a hard time creating a value list containing names of the users. Also, along these same lines what about all the…

We are in the planning stages of a new live inventory system for my company... My question is two fold: When dealing with "line items" that will effect inventory levels, is it a good idea to have the line items share a common file? (so that purchase orders and sales orders both store line item data in the same file) Or is better to use separate line item files? Second question: Does anyone have a pointers (or links) to some ideas on how to handle year end inventory/stock counts? The inventory levels need to be reset to the qtys that were counted at year end; but, we still need to refer back to the previous year's data. I would like to avoid cloning the file se…

Hi, I need a little advice (who doesn't). Maybe someone can help. I've built a number of db, some fairly complex, some simple and the one thing that always wastes/costs more time than anything else is design, or structure. *: I currently have a db which, amongst others, contains a Sales file and also a related file for Deductions which are incurred on those Sales. (These are royalty sales on photos). Up till now this has been fine, since the 2 were not directly related other than in summaries. IOW Deductions did not apply to specific items. However, this has just changed. Deductions are now directly associated with specific line-items (in my case, phot…

I often come across bizarre situation in my retail/wholesale business ! - An employee from my professional customers can buy to my shop as an individual (for its personal consumption). - An employee from my professional customers (or a single professional customer) can be a "service provider" when he drives a customer to my shop (he is paid a commission on sales for that). - A customer can be a supplier (I sell some products to another wholesaler, from whom I buy some other products). This last situation is very annoying as it leads to problem with invoicing, as some wholesalers substract from my invoices the amount of their invoices. That means that I should h…

I have read the article on this forum "Auto-entered Unique PrimaryIDs". I think this is simply a brilliant approach to a primary key and I appreciate the explanations of why it's important to separate the actual primary key from the data itself. Everything I've read to date about primary keys suggests selecting a field that is a good candidate (such as purchase order number--which 'should' be unique) or an auto-incremented key (1, 2, 3, etc.). I also like the idea that this approach will be unique across databases. It could allow for entry into the db from two remote sites with the records later merged and virtually no chance for any keys to overlap. Here's my q…

I have a situation that appears to be a many to many relationship, but there would be no fields in one of the files except for the primary key. In other words, the only reason for having the second file is to generate a unique index (incremental numbering for CD-ROMs for example). Then I would have a join file that has a relation to the primary key for both files as well as the field(s) to enter. My question is whether is it common to have a file in a many to many relationship that contains nothing but the primary key or whether I need to ask a different question about my design? I can give more details about my tables if necessary. Thanks in advance. Ken

First of all, thanks to everybody in that forum. I only use it from some weeks, but it is so rich in ideas that I decided to completely personnaly redesign my DB, taking few days out of work every week for that purpose. Even if this could take a big time, as a small business entrepreneur in ceramic retail, I want to use FM Pro at the max level, even implementing an accounting interface. Here is what I've done for the moment, and why I need some advice before going any further. My general DB is mainly created with Clients DB, Suppliers DB, Product DB, Purchase Order DB, Proposal DB, Customer Order DB, Delivery DB, Invoice DB, Transportation DB, Net Price D…

I'm sure this is sooooo simple, but I just can't find the answer. How do I back up files that are related to each other, without needing to re-do the relationships if I need to use the back-up copy? I have a system of 3 related databases, which are multi-user. In fact there are only 2 users: a host and a guest sharing over a LAN. If I needed to use the back-up copy, I'd have to change 24 file relationships and 5 scripts. Not huge, but the fact that I needed the back-up at all would mean that things were in crisis mode and I'd have no time to fiddle. Can't it be done automatically? Help please!

hey there.... im a beginner and have a question... im trying to link two databases ...let me explain what i have so far.. there is a contact file and an invoice file...what im trying to do is go from my contact files directly to a NEW invoice file...while copying the information over.. now, i tried making a relational database and it seemed to work but if i need to edit the information...it changes it in contacts as well...i need to have that information copied over...not linkied the way i have it now... so i thought that making it a look up would work but now im just...really lost so any help would be great... if what i just said sounds like jibberish caus…

I'm developing a time tracking / job costing / punch clock solution in FileMaker pro 6.0. We are running FileMaker Server on a Windows NT box, hosting to Macs & PCs. I want to design the system as airtight as I can, so as to prevent "clever" users from changing their computer time to increase their paycheck or whatnot. I need one constant time to achieve this, and the NT server seems to be the logical "timekeeper". Is there an external function that I can use to tap into the FileMaker Server? Are there any better ways of doing this?

I believe this is the appropriate section for my question. If not I apologize. Here is the situation: we have three fmp databases. One is a program database that contains information on radio programs. It contains a program & station ID field along with other programming info. A program can be aired on several different stations and a station can air several programs. The second database is a zips database that contains stationID info and the zipcode of the station. So the two fields to work with are stationID and Zip(which is the first 3 numbers of the zip code). A station can air across several different zipcodes and a zipcode can have several differe…

I have a client that has a system in FM with separate data and structure. I'm doing some changes in some of the files but there are some points that are getting me confused. I would like to know the advantages and disadvantages of developing that way and would also like to ask some quastions. The fields on the interface file are related fields and these related fields are lookup fields from another file. When I enter the interface file to update or insert any new information , these new informations don't save. I guess I would have to change the structure because as the informations in the fields that are being viewed at the interface file come from…

I'm a self-taught FM developer. As such, there are some aspects for which I have a fairly sophisticated knowledgef by now, but other very basic aspects which elude me. I have a company running on an old DOS inventory system. I am gradually replacing its various modules with FM versions, working with daily ascii imports. I am now designing a purchase order module (Mac, FM Dev 5.5). I'm pretty confident about scripting, calculations, the need for concatenated ID fields & etc. But the overall design aspect escapes me. This is ther best I've come up with: I have three dbs (FM versions of the DOS system's dumps) Item file PO Headers file PO Line It…

Here's a question for all of you familiar with RDBMS design! I have studied relational database theory in my Masters education in Computer Science and I wonder how applicable it could be in the FileMaker development process. I have also developed many FileMaker solutions, albeit not really by using the "traditional" relational database design steps. With "traditional" i mean the process of first creating a conceptual schema, then a relational schema, then normalizing the tables, and finally implementing the solution into a RDBMS. During my years developing FileMaker solutions I have found that the implementation techniques you use in FM differs significantly from most S…

Hey y'all. Another unsolicited tip, that probably noone is interested in, but made my day today when I figured it out. The problem: There is no direct way to obtain the range of a set of data by relationship. This is particularly irritating in the very common situation of accessing data in an intermediary table meant to resolve a many-to-many relationship. For instance, I have a database that stores scheduling for classified ads. It formerly had a table 'Publications' and a table 'Pub Dates' and you selected what pubs you wanted the ad in and what dates. Now, this made an implicit many to many relationship between those two tables, such that every date with a certain…

I don't need any help (for once), but I just found the coolest niche, in terms of Relational Design, that I thought I'd let y'all know about. My old DB, the one that I inherited, had some major problems, and one of these is: repeating fields. Bad ones. To put it shortly, I had a repeating field of publication dates, and a repeating field of publications, to determine what date and pub instance to export classified ads into. At any rate, without getting into the nitty gritty, sufficeth to say, I wanted to export these out, into an Ad Instance DB, that would have one record for each combination of pub and pub date. In relational terms (I think), I needed to multip…

Thank you. I am trying to write a database to organise computer rentals. The problem i am having is storing the booking information so that I can view the availability of systems on a particular date. I currently use a spreadsheet like so (1=available, 2=booked, 3=On hire 4=Workshop) System Name march 1 march 2 march 3 etc... Compu1 1 1 1 Compu2 2 1 1 Compu3 3 3 1 Compu4 4 4 4 So how do you store this information in a filemaker database, I have tried using repeating fields, but you can do very little with them in scripts, for exa…

Can anyone point me to a tool that can be used to extract the database schema from a FileMaker Pro 5.0 database. Thanks for your kind help.

Hi, I'm pretty new to Filemaker. Hope my explanation is clear enough. I have a main database, which has the information of the company. There are 4 different smaller databases containing the restaurants, lodging, camping and golf facilities operated by these companies. The companies have unique 'Identifiers'. These identifiers are also included in the 4 smaller databases as 'Identifier of Operating Company'. The main database does a look-up for the different facilities operated by a particular company. I have defined a relationship between the main and the 'restaurant' database matching the 'Identifer' field(main database) with the 'Identifier of Operating Compa…

I'm at the stage where I'm considering using an installer program such as Installer Vise for installing and updating my solution. What is the best way to install new versions and transfer the data from the old version? Is the data stored in the data fork of the file? Can the data remain while updating the resource with VISE? Just looking for an easier way...

i posted a question about my rdb in another section and edited about 6 times.... now i've made my little db into what is, i guess called a "flat file". (?) except with FMP (5.5 Mac OS X) i can have a beautifully functional layout with searchability, value lists, pop-ups... loys of great functionality for flat file. basically i started out to impress my boss by cataloguing all our computers and their users and some other information about both. and of course have it on the web, searchable and printable results... and sortable! i started with what would be 2 tables: one for people (users) and one for computers -actually i started with 3, the computers were destops an…

Hi everyone, I have what seems like a simple task but is not working. Here is what I have so far. I have a db file I am sharing over the web using web companion and I am trying to setup a relationship for some of the fields. What I have is a popup list of tapes from a "tape" db list. What I want to have happen is when a user chooses a tape from the list I want the cost field to lookup the cost value from the "tape" database. In the tape database I have 3 fields: 1) title 2) cost 3) item number I have the field lookup for the tapes set to lookup the tape names from the "tape" db. But when I try to create a relationship for the tape name to tape price to the …