Relationships

I

I am trying to get my merged letters to work. I think maybe my relationship is wrong. I have a Participant table and a Payment table. They are related by ID, First and Last Name. I have a portal in the participant table to enter payments into the Payment table. I have written a script to go to the Payment Table and find all records with the current date. It does find the records but it lists them as 1 record with 2 or more entries per relationship. When I continue to try and merge the information I only get a letter for the first record for each participant. How do I set up the relationships so both records will print merged letters? The letter is a receipt of pa…

I am using FileMaker Pro 6 and was wondering how to have a pop-up menu where the value list changes depending on a selection made with in another pop-up menu or pop-list(preferably either), this is just within one file. I'm not sure if that makes sense, so I'll try to rephrase it. If the user picks option A on menu #1 I want the value list A to be used on menu #2, and if the user picks option B on menu #1 I want the value list B to be used on menu #2. Someone already suggested you can have it only show certain values from the same list depending on what option is picked, but I do not know how to do any of this. Help would greatly be appreciated. Thanks

I have a layout with a portal of child records. I want to create a button that will open a new window and start with a new record. I need to keep the parent record active such that the new child record will be related to the parent. When I set up the script to do that, the "new record" clears the parent also. I want to keep the parent record (obviously). What am I doing wrong? Script: New Window Go to Field New record / request

I've attached the relationship diagram I have for my invoice. Most of it works beautifully to get all the info my users will need. However, I want to relate the quotes relevant to that particular Job No, there may be many. I thought the best way to do this would be to have a portal but I can't seem to find the right relationship to do this. I've tried relating the tbl quote by job no and quote no to tbl invoice but haven't had any luck. I must be missing something. Any ideas? Inv160205.pdf

I have one table ('Bank') that holds two fields; name (text field) with 4 records of 'bank1' 'bank2' 'bank3' and 'bank4'; and a second field; drive (number field) that holds a unique drive number. In my second table I have the user choosing a bank via a valuelist from Bank::name. What I would like is when the user selects a bank, the associated drive number is also copied into the local table's duplicate drive field. I can't use a standard relationship, as the drive fields in 'Bank' will change over time, but I don't want my local table to reflect the changes, but to keep the value it was assigned. Essentially I want the valuelist to update two fields in my lo…

I want to make a layout that will allow for a drill-down from a menu of choices. All levels and all fields can be browsed but (maybe) not selectable unless one of the preceding levels has been chosen. For example the top of the tree might be a field (or text box) labelled World and the next level five different countries listed by name and each in a separate field. Then a choice of State fields. When a State is chosen (Pop-up, radio button) the next level is selectable, and it might be capital cities entered by field. Say twenty of those, then the next level is Towns, maybe fifty of them (and so on). It would be nice if the selection of one choice (pop up or whateve…

I've got a company table, company location table and contact table. When I run a quote I want the user to be able to input the company code (tbl_Company) which brings up a conditional value list of Company's offices and then, once that office has been chosen, a list of contacts at that particular office. I have achieved the first bit but I just can't get the contacts to come up properly. I've attached the relationships graph and the value lists for company are based on fields from Company 3. The value list for Co.Loc are based on fields from Co Location 7 starting from Company 3. I've tried loads of combinations for the contacts - so many I'm now soooooo…

I have a multi-table database that seems to be almost ready for prime-time (thanks in large part to some helpful folks here). I've run into a small problem. Perhaps I just haven't mulled it over long enough. In one table I have some calculation fields that use information from other tables. The tables were related (are related) when I look at them in the calculation set up screen. However, if I want to make a portal between the same tables, suddenly they are no longer related. I'm sure I'm missing something really dumb and obvious....Thanks for your input.

I am trying to create conditional value lists based on State, City, and Restaurants. I got it working so that if I choose a state, then the next value list limits cities to those within the chosen state. But I can't get the the third value list (restaurants) to be limited to those within the choosen city. I have attached a copy of the file. Thanks for your help.

I have two files (A and which are related by a unique ID field (ID). When I change the value of the ID of A, the ID of B does not change automatically. Does anyone know how to solve this problem?

I have my business database that I put together over the last few years and it works well for what I need. Now I need to extract multiple fields and place them on a layout as a order form. Sounds easy but I can't work it out. I repair cameras and have to order parts, (before this was done manually) now I have 5 separate fields which I place the part numbers into. Most of the time only one field is required but there are times I need up to 5 or more different parts. I can't work out how to get these fields onto a layout without there being spaces between the fields. Can anyone help as this is frustrating me as I feel it should be easy and it probably is but I can't work …

I'm having a bit of a problem getting a relationship to work. Basically, I've defined an unstored calculation field that returns Get(currentdate). In a related table I have an actual date field that users enter dates into. I have specified a non equijoin, greater than relationship from the calculation date field to the actual date field that should only pull records that have a date in the future. Problem is, it just doesn't work. I know the calculation is working because it shows the number of days since 1 A.D. if I put the field on a layout. Any ideas? Is there a problem with comparing a calculated date to a stored date?

I set up a relationship for a field that repeats several times. The problem is the relationship only applies to the first entry in the field but does not work in the repeating fields. Example: Part Number ...........description repeat......................repeat repeat......................repeat repeat......................repeat If i enter a part number in the first field it will automatically fill in the description in the first description field. but if i enter the part # in the second field it will not enter the description in the second description field. Any help would be greatly appreciated. Ives----- banging head against desk

I am desperate: I am trying to make a tape database with shotfinder. I created two tables, one tapes and one shots. I created in Tables a Field called Tape_ID (autoserial) and in Shots a Field Tape_ID. I made a single relationship (Tape Table creates Tape_ID in Shot Table). No i want to create a Table with Tape Infos and want to create a Button with "Related Shots". When clicking on this Button, every related Shot of the choosen Tape should look up in an other Layout (List view). In the Shots Layout i want a button "New Shot" which will create a new Record related to the Tape choosen before, and i can type in the shot specific information. It worked to view with…

Hi there, I have got a rather tricky problem to solve - I hope you have some good ideas to improve the performance. My database consists of huge a lot of different tables. I need to show two of them in two different windows, each layout showing several portals linked to other tables. (Between the data of the two windows there is a 1:n relation - that is the reason, why I cannot show all of the data in one single window.) To navigate in the first window I created some navigation buttons that call a refresh-script to always show the correct data in the second window. This procedure takes a very long time, because to show this data I have to use a search. I found…

Greetings all. I'm having a problem understanding how to do a slightly more complex report out of several related tables. The project itself is a lot more complex than the test file I'm attaching, but understanding how to do the test example correctly will give me the necessary "know how" to do this right. In essence, my primary table is a "company" table. Associated to this is a "Lot" table that defines how many lots are in each company (related via a company id index) Tertiary to this is the "contacts" table which provides three seperate contacts per lot per company (related via a contact ID index) So... 1. In my attached example, there are five compani…

Hello, should letters, invoices, labels etc be in a separate database instead of the same database that contains all my records(plain example attached)? Or is it o.k. that for each record in my main database I create a layout with e.g. "welcome letter". How would this affect the efficiency of the db if I'd like to send out a serial letter to certain customers? Thankful as always for any help, Andy records_letter.zip

It seems this should be simple but I can't figure out how to do it. Dr. P has a DB of accounts, Dr. J has a DB of accounts. The third DB is personnel. I use the Personnel DB for informaiton and processing payroll. Staff can be funded on both Dr. P's and Dr. J's accounts. I want to plug in the account number on Personnel and have it search both account DB's for the related information. I have a relationship of account number to account number for Dr. J's accounts and it works fine. How do I make one field check 2 DB's for information?

I have a related field on my layout that is not updated when the "mother record" is updated. The specific field has allow entry not checked. If i make a copy of this field but with allow entry checked the updated works perfect. I have tried numerous solutions redraw screen goto related record empty chache Please help me out on this one Thanks Jitse

Hi Everyone, I'm sure I've done this once before, but I can't for the life of me work out how, so any ideas would be much appreciated. I have a table with 10 records in it and a self relationship that contains 4 of these records. How can I have a field that shows the sequence number of those related records (ie. 1-4) in alphabetical order (they were entered in random order)? I can do this easily in a portal, but that won't help me in this case. Also, I'd like to avoid using a script. I can set the first related record to 1 easily enough, but thats as far as I can get. Thanks again, Sean.

I have a Football Match dB storing games played in competitions all over the world. Each match takes place at a venue in a country. When the user enters data, they enter a competition (Comp table) specifying the country in which it was held (from the Country table). They then enter a match for the competition (Match table) from where they select a venue, via a valuelist (from the Venue table). What I want to do is only display the venues for the country the game was played in. At the moment I can either display all values from the Venue table or nothing. My relationships are screwed up, but I can't figure out how to do correct it. Does anyone have any ideas? …

Hello, can someone tell me how to define a standard field, in order to see all the checkboxed values from a value list from another file? They are related many-to-one! Transpower told me "to create a summary field in the other file, using PatternCount, and use that in the first file... " but this sounds a bit like chinese... Maureen

Thanks to the tons of help I've received from this site, I'm now the man at work. So, now they throw something HUGE and, frankly, I'm not sure FilePro can do it. I can't figure out how anyway. Think of it like this. The boss wants FilePro to do a seating chart for a theatre. I need it to be able so that a user can click on a seat, have the seat clicked on show up in a record, and make it obvious that the seat is no longer available(change color, whatever.) Killer stuff. Again, I don't know if FileMaker is even capable of something like this, but if anyone has a suggestion on how I might be able, I would be unbelievably gracious.

I'm new in FM7 and trying to understand TO's and the relationship graph in general. I'm starting to like it! My question: I have a table with 35 calculated fields - one for each day in a 5 week period. Each record in the table is a rental unit. Each of the 35 calculated fields is displayed on a report layout displaying for each day whether a rental is starting, continuing, the unit is available, or out of service The value of each calculated field depends, among other things, on what is found in a related table for the date/rental unit. The related table contains rental records. I'm defining 35 relationships - one for each calculated field - from the date…

Hi, and thanks in advance for your help. I can't seem to get my brain around this relationship . I have a file [Contacts]. Each Contact could have multiple Children for which I have set up a file [Children] and a relationship that works - no problem. Now, a contact could be a Student. Also, a Child could be a Student. (Each record in these files has a "yes/no" field for Student.) I need a third file for Students where a record will be created automatically from the Contact or Child file when the "Yes" box is checked for someone who is a Student. This file will also hold other information such as Emergency Contacts, Medical problems, etc. It will also be relate…

So...I have split up my records in one databse into 2 tables. I am using another database to reference these records by looking through a portal. Is there a way to see both tables on the same layout? You must specify the portal to one or the other table, so besides adding another portal, what to do? Any help is appreciated. Thanks -Brett

I am rewriting a database system that used a calculation field for each month and year to calculate the sales for a given company. So there is Jan04TotalSales...etc for each month. I'd like to try to get out of that by using relationships a bit better (if possible). So, if you have these tables: Sales - holds a record per sale, and contains a companyId Company - holds a record per company And then I want to be able to look at a record for the company, and view their cases summed by month (in regular browse mode). Is there a better way than a calculation field per month and date?

I'm trying to create a many to many relationship with keys that are calculated values. I have a table that contains a fair number of fields that includes things like times, dates, and ID numbers. I need to define fields that will keep a summary of things like the total of a specific record in a given month and year. So my relationship is set up like this. Relationship_1:: Table_Instance_1 Table_Instance_2 YearOfDate = YearOfDate MonthOfDate = MonthOfDate ID = ID The YearOfDate and MonthOfDate fields are calculations based on the date of another field. YearOfDate is defined as year(job_date) Mon…

Hello my name is Will, and I've been using File Maker for almost a year now. I recently upgraded from 5.5 to version 7. I love it. The new graphical interface for Relationships is great. However I'm stuck trying to decide the best way to structure a tracking DB I've been working on: Problem - 1.) I need to create a database that lets me collect Contact Information,2.) track Indivulal Items from our clients that are sent to the Contacts by displaying the date it was sent, 3.) it needs to be show multiple items and dates, 4.) It needs to show items #2 & #3 for each client. Filemaker 5 Set Up: In the old version I have the main contact information layout, a…

Hi, any help much appreciated I have two main tables, People details and Company Details. They are connected by a table called Peepony which says who is connected to which company's and vica versa. This table appears as a portal in the layouts to the two main tables. When entering from the company details page it auto enters the company name and ID from the displayed record in the layout. You then select a name from a pop-up list which displays all names within the people details table. The field for People ID in the Peepony table is set to auto-enter from people details::people ID and they relationship has been made and seems good but it is not importing the ID into…

In a first file I have two layouts: layout one is designed with a data entry portal to another file for recording item selection in each portal row (with a relationship that does not allow deletion of portal entries) -- this creates many records in the second file, one for each portal row .... When that set of selections is complete, the data within the portal is deleted and is ready to be used again. I am looking for a way create a record in a third file to group those records made by that portal each time -- kinda like a duplicate of what was selected in the portal before the data was deleted to start a new set of entries in the data entry portal. This file w…

Anyone help? I have come across some very weird relationship behaviour I need to resolve. I have a complex solution with a large amount of data, upon which statistics need to be performed. Situation Data held in a number of tables and files, accessed from one separate, common interface file. All FM 7.0v3. 2-4 users, files hosted using FM7 (standard). Same behaviour on Mac OSX and WindowsXP single or multi-user. Relationships Many, involving multiple criteria. Background I have a relationship from the interface file (HyIS) to another file (Runs). Some of the data in Runs is auto-entered by calculation from a third file (Races). The relationship between…

Hi, I am trying to add a set of new tables to an existing database and am looking for a couple of suggestions how to make the relationships work. I have the following tables: event groups (related by a DATE RANGE calculation to...) events finally, I have clients What I want to add is a sales table that lists inventory, defined by clients AND by event group so when looking at an event, only the inventory related to both will be available for calculation. I will need to total sales and calculate sales tax/commissions with this data so I am concerned that a portal will not be a sufficient way to view this data. I will also need to add inventory for numerous c…

Does anyone know how I should set up a relationship that will allow me to display only specific related data? I have a many to many relationship with a join table linking to master tables. Within the join table I have a field describing the type of relationship, and a sequence field which sets which records have priority over the others in the same relationship. On the main display, I want to show two records with the two different possible relationship types. For example, in my system, I have a lawsuits table and a parties table. Linking them is a table which contains which parties belong to which suits, along with whether they are a plaintiff or a defendant. O…

I'm developing an FM7 application. One table - Charges - currently has two relationships originating from it - one to another table in the same file and the other to a table in another file. The linking field is different for each. The 'name of table occurrence' for both is 'Charges'. I'm trying to add a 3rd relation originating from this same table. The linking field is again different and it will link to a 3rd table in a 3rd file. FM7 insists on making the 'name of table occurrence' for this 3rd relation 'Charges 2'. Why the '2'? What significance should I attach to this? TIA

The Relationship diagram in FM7 is great - when there are few relationships defined. I am currently re-designing an FM6 solution in FM7. Several of the files have 30+ up to 50 relationships defined. The diagram is of no value in this case - impossible to read. Is there an alternate way in FM7 to display the relationships defined? E.g., the list format of FM6 and earlier? TIA Tom

Is there a way to view and print my relationships? Do I have to go to every database or will the master print all my relationships??

In part of the solution I'm building I'm trying to track trires. I have a tire table that each record represents a tire. Related to the tires table, I have a tirecheck table (related by tire ID) which displays as a portal in tires and records every time the tire is checked. I created unsorted calc fields on tires (CurrentLocation, CurrentPosition, CurrentTreadDepthto,etc) that display the last occurance of the portal i.e. =last(tirecheck::field). This to reflect the "current status" of the tire. I'd like to relate (and self join) to one of these calc fields but FM tells me I can't relate to globals or calcs that use related fields. How can I go about this?

I am wondering if there is a way to display data in one file from a file that is 2 or more relationships away. For example, I have 4 files: PEOPLE(storing contact information about the various film makers, distributors, etc who are associated with each film) LODGING (storing information about where people are staying while they're here) FILMS (storing information about film--name, length, description, etc.) SHOWINGS (storing information about the times, places, and dates that each film will be shown) They are linked as follows: LODGINGS=by person ID to=PEOPLE (one to many, since each person may have more than one lodging) PEOPLE=by person ID to= LINK …

Hi there. Sorry to make my first post be an annoying request for help, but I am stymied here. I created a Filemaker 7 database. It's a simple list of items, each of which has some data (file containers, text fields, etc...) associated with them. They also have a category associated with them. So, the relationship I have is that the Items table relates its Category field to the Categories table's Category field. A many to one relationship. Looks like this: Items::Category >-- Categories::Category I have it set to allow the creation of related fields in the Categories table. The problem is, when I enter a new value in the Items::Category field, i…

I need to find records based on the content of one date compared against a second date field in the same table. if date1 is greater than date2 show the record Do I need a self-join to accomplish this? Thanks

I am trying to modify the database for a small non-profit. I am not a database guy and to date have only done simple modifications to our system. So here is what I am trying to do and how I am trying to do it. We have a members database. I want updateable list for each member record showing which activities they attended. I want to be able to search for members who attended activities based on date, number of events, sponsor of event etc. I want to be able to add new activities. I also want to do a number of calculations across this data including a count of activities each member attended, total number of members that attended more than x number of activities e…

I have a database I've created for customer relationship management; and now I'm trying to integrate another FMP DB for scheduling purposes. I file referenced them one to the other, and creted tables for each in the relatioships window, now what I'm trying to do is: 1. Open the Scheduling window > 2. Get the Customers info, for the new appointment, and place it in the customer field in the Scheduling program; manually enter the rest of the information > 3. After the new appointment has been submited, a portal row in the ,management program should display all the appointment related to that specific customer. I currently have this script run…

hi, I want to stop using repeats in order to get lots of data to 1 customer. How would i set up a relationship ( 1 to many ) between a tbl_customers and tbl_orders. So that a customer can have many orders?

I have a restaurant database with a value list (based on another database) for cuisines. On the web I use their codes (as opposed to their names) to avoid any overlap in the search between, say "Chinese/American" and "American." So the way the database works is that you check the cuisine(s) in a field. I was wondering if anyone had any ideas as to how I could get another field to have the ID numbers from these cuisines in it (like the way that html checkboxes have a value that is shown to the user and a different value of the box itself), perhaps separated by a colon. A looping script would be fine, I only need to get these values occasionally. Thanks for…

Hello everyone. I am relitivley new to developing in FMP and am stuck with a problem I suspect you all will have an easy answer for me. I have two tables (People=Parent and Publications=Child). The people is simply a list of individuals and their mailing address. The publications table is a related table that shows the various types of mailings each individual is supposed to receive. There are a maximum of 15 mailings that one individual could be receiving. I have created a single field in the parent table called people.publications. I would like that field to be replaced with all the related values separated by a hard return. In other words, I just want a…

What is the elegant way to create a relationship using key fields using a popup menu and without showing the key field in the popup? What I do now is make sure the popup field (drawn from the to-be-related record, say, a product name) is useable as a temporary key and place it in the originating record. This defines a temporary relationship using those two fields. The foreign key field in the originating record is set to auto-enter the other's prime key using the relationship defined by the popup entry and then the key-field relationship is used exclusively after that. This works but it's a kludge. It requires a field that is only used for that purpose and that …

I am sharing multiple related files on FM server advanced. This problem seems to coincide with my updating FileMaker 7 to version 3 but I was having similar problems on some user's computers before the update. Server Advanced is updated to version 2 but I believe the problem is not on the server end because the same thing happens when running the database from my local drive. Basically, when "Go to related record" is included in a script, alone or otherwise, it doesn't do anything (doesn't go to the external layout it's supposed to). When I make a button that does the same thing without actually writing a script, it works fine. Unfortunately, I need to run an if sta…

is there a way to create a table which is linked to other tables but just shows the information from them? I have not found a way yet in FileMaker pro and i am just wondering if it is possiable.

New at the Forum and getting deeper into FMP6. I have 2 sets of records in 2 databases. database A with 20 records, each record indicates a succession of tasks to do. database B with 5 records. each record provides the cost of a task based on multi-criteria I want to be able to have on database A, for each task, the cost of the task. This cost, would be picked up from the database B using a relationship. I'm stuck with trying to associate the 2 databases. They don;t have the same number of records, so a correspondance by an ID relation doesn't work. Any help would be greatly appreciated!!!

I've attached my latest (! version millionth) relationship diagram because I just don't seem to be able to get my head around the problems I seem to be having: 1. I want to run a status report based on all live jobs. I have a portal within the report to show all tasks allocated to employee within each job number. I can get my report to show the first task but it won't show any of the others. 2. I want to build Purchase orders based for job numbers. The Supplier will have a Company name related to the Company Code in the Company table and the location in the Company location table. So far, I can enter the Company Code and get the correct Company Name. But …

hi another neewbie question, Currently working on a project where the user has to match turkeys to customers orders. the basic structure is tbl_customers, tbl_orders and tbl_turkeys. Now customers can have 5 orders via a repeating field etc. But the user has to match the turkeys to the orders. 5 turkeys to 5 orders. I want a way to filter out the already matched turkeys in order to stop the user matching more than 1 turkey to several orders. hope that makes sense MnR

I was so impressed with the last lot of help I thought I'd try for a bit more! Amongst others, I've got 3 tables: Company, Company location and Contacts. I've attached a pdf file of the relationships. What I actually want to do is have a layout which will show each contact with the company and the appropriate address. I've managed to create a layout of each company with a portal showing each address but can't go the that last step. Relationship200105pm.pdf

I have created a Music Schedule Database with 2 tables: Music Schedule Music Schedule Lineitem I want to display in a portal the following fields Date ( person is singing) Song However, I want the user to have an option to display all dates scheduled or display only future dates scheduled. How do I go about making this a conditional portal? Thanks for your help! Sam

I want to build a smart help system in my application. After the user has entered a number of keywords he/she will be able to select from a popup list a number of help files that matches ALL the keywords (so an AND search). This is what i have done. 1] I have made a help file. Each record contains a help header (for example How can i find a specific order) and a text field containing the help description. In addition i have a calculated field that contains all the words from the text field. 2] This file contains a number of help records. 3] The user can enter maximum of 5 keywords. 4] I have 5 keyword_index calculated field that contain the firs…

I haven't been able to figure out how to make a BOM system with FM. Any ideas? There is a table with two fields: parent_part_numbers and child_part_numbers. Generate a table or list of records (Results) where parent_part_number = TOP_PARENT (entered by user) Repeat [Concatenate to Results records from original table where parent_part_number in original table = child_part_number in Results] until no more matches or a specified number of iterations. This should create a results table of all decendents of the TOP_PARENT part number. It is essencially a recursive or nested search problem, where the functions are field contents found in the database. …

I have a database for a merchandising company, converted from FM 6, so, it still has multiple files. The main one "mission" is related with many others like "employee", for the employee doing a certain job, "clients" for the client who ordered the job, "shops" for the point of sale where the assignment takes place, even "invoices" to automatically obtain the invoice monthly for each client. My problem now, is to find a way to save sets of shops and be able to duplicate one mission for each of those shops from my saved set. The user's request appeared from the need to avoid a lot of manual work, because there are tens, sometimes hundreds of similar tasks, in behalf of th…

Hi All, This has me stumped. Hope there is a simple solution to this that I haven't thought about yet. I have three tables: Items, Locations, and Transactions. Items looks like this: 001 Widget 002 Gadget Locations looks like this: 001 Warehouse 002 Branch Transactions looks like this: 001 Widget 10pcs Warehouse 002 Widget 20pcs Warehouse 003 Gadget 30pcs Branch 004 Gadget 40pcs Branch I would like to have a portal inside the Items file that contains a summary of the number of pieces in each branch, like this: 001 Widget 001 Warehouse 30pcs 002 Branch 0pcs 002 Gadget 001 Warehouse 0pcs 002 Branch 4…

So I'm building a database for tracking computers. They can be assigned to a person, a location (e.g. a computing lab or classroom), or a group (e.g. the technology department). I have tables for each of these kinds of things, plus a table that stores the associations by keying on unique IDs (I use textual serial numbers like person0, location1, and group2). The only problem I'm running into is when I design UI screens, because then it's often necessary to have three basically identical layouts, one for each thing. It just doesn't seem very elegant: Create new repair incident (search by Person, Location, or Group); Assign computer (to Person, Location or Group); etc.…

Hi, I'm trying to map a bunch of fields to another set of fields with different names. The way I thought I'd do this is to make two tables and create relationships between the synonomous fields. I'd like it so that when I add a record in one table, it automatically creates that record in my new table. For example, I have two tables, "apple orange cucumber banana" and "orange yello green red" So I make the relationships apple = red orange = orange banana = yellow cucmber = green And I checked "Allow creation of records in this table via this relationship" and "delete records in this table via relationship" for both boxes. What else do I have to do…

Hi. I'm new to the board and I've done a fair amount of searching to try to find some help, but I figure it can't hurt to just ask. I'm a newbie to FileMaker, and a novice at best when it comes to relational database design. Here's what I need to do and my understanding (or lack thereof) of how FileMaker can do it. My company has a simple database that we use to keep track of orders placed. It is functional and good in most ways, but customer specific data (contact info, etc) is stored along with the order specific data (quantity, item, etc). So if a customer places another order, their contact info must be entered again. We would like to change this so that we have …

Is there any way of defining the type of relationship I want rather than the one Filemaker pro decides on? I expect there is a rhyme or reason as to why it defaults to the one it does but I can't see it! In one relationship graph it drew a one to many relationship and in another it insists on having a many to many relationship. I keep redrawing it in the vain hope it will change its mind!

I'm trying to develop a database of all my dvds, and am quite new to filemaker. I have a table for movies, and a table for actors, I have the layouts set etc..., but now what I need is a way to have a record in the actor table show related records from the movie table, as well as the movie table records having a portal with GTRR buttons to the actor table. From reading the forums I understand I need a join table, but how I go about this I have no real idea, other then records in each table must have unique ids , and the join table would be used for browsing etc... Any help, or suggestions would be greatly appreciated, I downloaded Ralphi's seperation model but don't r…

Hi All, Is there a way to trap the error that is produced when a related file is not found as you open the "parent" file?? In an open script, i am trying to test to see if a related file is present & if not found to open the "Open Remote" window. My problem is that FM seems to trigger an error BEFORE my open script runs so I never get to direct the user to where i want him to go. Thanks Dan

(If this isn't in the right forum, pleae feel free to move it or tell me to repost). I'm in the midst of converting/re-writing a point-of-sale system (university computer store) for FM7. My boss threw me a challenge and I'm stumped on if it's possible and if so, how to implement it. Currently, the system looks something like this: INVOICES (invoice number) -----> LINE ITEMS (item ID) ------> INVENTORY (inventory ID). The inventory file isn't really named properly as it doesn't track in-stock inventory; rather, it tracks what items were assigned to which purchase order. A invoice can have many line items and those line items can have many "inven…

My setup: 3 Tables - Images - Organisms - ImagesOrganismLinker Each of Images and Organisms has a serial number. The linker table contains only two serial numbers, from records in each of the Images and Organisms tables. On the Images table, I have a drop down that contains a value list of Organism names. When a record is created in the Images table, and an Organism is selected, I need to have a record created in the linker table, containing the two serial numbers. What I have so far doesn't exactly do this. Can someone help me make this work? deleteme.fp7.zip

After fixing my previous problem, another has arisen. Basically, I need a button in my parent file to take the user to a layout in a child file. The most I can get FM to do is to open the child file and make the parent file blink. There must be one small step I'm skipping. Help?

I have searched the forum and have not been able to find what I am looking for. We have "Clients" who have "Time" billed to them for work done. Our billing manager wants to use the db to enter in a date from say 1/09/2005 - 1/13/2005 for all time entries that have been added(created) during the time frame of those dates. I have seen some diagrams about this with a 3rd table off of the Client side, but they don't seem to work in our scenario...or maybe I just don't understand it. Also, never used scripts before so a simple relationship with calcs or whatnot would be great. a simple description of how it works would be great too. Thanks---Mike

I've been designing a database PT for several weeks now. Some of our customers have two (or three) locations in the same town right near each other. Each location has it's own mailing address. So the city, state, company name are the same... just that one might be "Plant 1" and the other "Plant 2". How should I set things up to be able to combine the two at various points in the database? For example, we might want (on one screen to view or print) a list of all equipment that the customer owns, both by seperate plants or together. It this going to be a lot of extra work? Greg

I am trying to get a database going for a small family-based Christian school. The problem I am running into is this: Everytime I create a student record, I want a record of the family automatically created by last name if one doesn't exist. I've tried making a relationship between the "Last Name" of the Student table and the "Family Name" of the Family table and for the ability of that relationship to create records in the Family able. What else do I need to do? Thanks Chris

Hi...I've been working on a project for condominium/property management for a while now, and I'm in the process of revising/updating my project, and I'm running into the same issues from when I started the project and decided to take the easy way out. But now I'd like to get it right. I'm completely self-taught, and still consider myself a relative newbie at this whole designing thing....but enough preamble, here is why I'm writing: (by the way, I'm using Dev 7 in OS X) I originally stared the project with FM6, and since moving to FM7 I've been consolidating what was many DBs into one with multiple table (so much nicer!). The tables I'm working with are: Units, …

I just started on my first project. It is to be a system for tracking fabrics used in making sample shirts. So far I have a Fabric table and a related table (FabricsOrdered). This arrangement keeps track of how much fabric is ordered for each location (most fabrics go to at least two separate destinations). I've started another table called Samples. It will be related to the Fabric table. What I need is to be able to have the main fabric (for the body of the shirt) listed like this: Fabric Mill Fabric Name Mill# These fields are all also in the Fabric table and I could use a calculation (=) to fill in the Mill and Name fields. Is this the best way? …

I have many tables in my db but the three I am concerned with are Invoice, Orders and Products. I use orders to specify each line item on the invoice and products obviously contains the products for each line item in the orders table. Invoices is related to Orders via an invoice number field and Products is related to Orders via an item # field. I have a portal on the invoices layout showing records from Orders. I have a value list for Item number that shows the contents of the Item # field in products. I like that but I would also like the user to be able to type in a new number in this field to create a new products record. I have checked the Allow record …

I'm starting what will be a fairly complex FMP7 project (first time from scratch in 7) and am wondering what you clever folks think about layout choices in the relationships graph. How to use colours. Using an empty table for labelling. And especially, whether it is wisest to set up small (i.e. just for one layout) clusters of relationships or whether to try to get everything connected to everything in one big cluster. The former seems neater and probably easier to read but may be trading off some possibly powerful connections. Any thoughts based on experience?

Hi All, What would be the best structure to use when you want to create a portal that shows records from the same table which are related. I know you can do a self join, but I believe this is more complicated and I have become quite confused. I am trying to set up a recipe database which as part of its function identifies ingredient pairs which are good matches. I intiially set up a filemaker seven database which several tables, two of which were "ingredient" and "flavorPals". I created a relationship in filemaker 7 beween the primary key field in the "ingredient" table ("zk_ingredientId_p") and the foreign key field ("zk_ingredientIds_f…

Is there a way to create a new record in a related file/table? I am currently working with a database that consists of two file/tables. There is a one to many relationship between the two. The main file/table contains data on the clients office, while the second file/table contains information on the clients (there can be more than one client at a given office.) The user logs into the "client office" file/table and is able to view and manipulate the information in the "clients" file/table via a portal. The problem I am having is: When a new client offices are created how would I go about creating a new record in the "clients" file/table from the "client off…

Hi All, I've used FileMaker as a full time job until just before version 7 came out. I kind of missed the whole transitional heyday. I've been dabbling in FM7 a bit and am now getting back into it. I'm having real problems wrapping my head around the new relationship model. It is hard to get out of the stone age of FM. Whats the deal with table instances? How come they are made after the second relationship. How do they dance with relationships? How do you name them? Do they go in both directions? Is there an clean-up function in the relationship graph? How do manage a system with more then 4 tables and some tricky self-relations with out getting confused? …

Not really sure if this is a Layout, Database Relations, or Search problem. We have a single database of parts with their nomenclature, stock number (NSN), and various other part info. When we order parts we assign a job number (WON) and make a list of parts needed for that job. The relavent part of the layout looks like this. We have another database to keep records of each order with the job number as the key field and fields for each parts nomenclature and stock number. We would like to be able to type in the part number (in browse mode) and have the nomenclature for that part automatically fill in. Is there a way to pull parts of multiple rec…

Hi, I'm quite a newbie at this and am using an old version of File Maker to develop a database. If it's found usefull, we will upgrade the software. So my first question is whether the relationships I create in File Maker 4.1 will stay the same when we transport the database to FM7? My second question is whether the ugly web of relationships I've created will work? I think it will but its so ugly I just cant tell. The database is for a Fire Protection company to track which equipment needs to be inspected and tested. If the equipment doesn't pass the inspection the database should track which equipment needs to be repaired. All "Sites" have many pei…

Tinkering with a very basic, single-table database, I got into the "define database: relationship" tab and did some damage. The one table in the tab? I deleted it, figuring I could just create a new one. "Are you sure you want to remove the selected tables from the graph? This will break all items that use these tables, such as layouts, scripts, and calculations, but will not delete the underlying tables or their data." I confirmed my changes with a few confident clicks of "OK", only to find that the 398 records in my database had become zero. A check over at the "define database: tables" tab confirmed that the solitary table still existed, with its 398 r…

Hi! I'm new to Filemaker 7 pro, and I've got to learn it for work. I'm currently trying to do a forum to get used to fm. I now ahve some problem. I have a layout (display_forum) that displays the forum, then if you click on the name of the forum, it will go to a layout that show the thread in that forum (display_thread) I used go to related, but on the thread layout, it displays only 2 thread out of a total of 4, but it display 1 thread that is in the forum, and another one that aint in the forum. I dont know if you understand what I mean, but I need to know how to have access to the record that are like SELECT * FROM thread WHERE forum_id=(the forum id I just clicked to…

Hi all, Quick question that hopefully has a simple answer. I'm in the planning stages of an asset/customer/invoice relational dbase. How can I build an asset database that has individual items and groups of items - eg single items such as CD player, amp, TV, etc. and a 'kit' like an av trolley that is only ever signed out as a group of items? Like having the trolley as a main item and the TV etc as sub items. Any help appreciated.

I am trying to enter data into a repeating field and return data from a related record in a external table occurance. 1 - The related external table is a list of job records. Each job is is own record. 2 - The current record is in the "time sheet" database. I have the job number and the info. about the jobs all setup to receive data in repeating fields. 3- I am using this calculated lookup : If ( IsEmpty ( Job Number ) ; "" ; If ( IsValid ( OPMAPJS job number::Job Number ) ; OPMAPJS job number::Project Name ; "" ) ) My problem is that the data I am getting is being pulled for the first record entered in the repeating. I entered the jo…

I'd like to walk through a set of related records in a scripted loop. I know I can do this using a portal, but that--though time-honoured--seems clumsy since it requires putting a portal into any layout that uses the script. Queue at one point (#123703 ) suggested using a value list that refers to the related field, which is very clever but a bit subversive and, therefore, may confuse me if I come across it in a later life. My question is: is there an elegant and 'official' way to get at arbitrary members of a related set of records beyond the first one? Something equivalent to getRepetition(fieldName,n) or (and wouldn't this be sweet) fieldName[n].

Hello, Proceeding with some tests on the Relational Architecture (specially multi-joins and auto-creation of related records discussed elsewhere these days), I discovered something I haven't seen reported elsewhere. With the multi-joins capabilities with 7, you can fill a few globals in the Main file and have them populate fields in a related record on creation when the relationship is set to allow creation of related records. This is a big change as it reduces the needs of defining fields as lookups or auto-entered calcs, when you want them to be dynamically filled with values from this Table *where* they were generated (multiple Tables as opposed to a fixed …

Hi, I can't delete a record and I have this message one or more relationship between table are invalid. Some one can tell me how can I see fix that issue, how can I see where is the trouble. Thanks

Hi All, How do you prevent the user from seeing the "parent" record if it has no child?? (say an invoice header that has no line-items) thanks Dan

Hi, I have 2 tables Project -> Tasks, joining the two tables by the project_fk in Tasks. Works fine. the 'interesting' part (for me at least) is that when a user creates a Task record, they want a unique number (call nice number) based on a concatenation of the project Id and the first letter they pick from a drop down list (Statustypes). The task records related to a project can be 3 different status types. Open, Pending and Closed (in a value list popup menu) [called StatusListTypes. For bookkeeping, it's required that a number be generated encoding the first letter for each task - just for reference - like part numbers. so if I pick "Open" from the dro…

Hello, I'm trying to build a database for a motorcycle tour company, after I've spent about 6 hours on a specific topic I better ask the pro's here: Here's the task: 1. I have one field that contains different tour names, e.g. Wild West Tour, Route66 Tour etc. defined by a value list. 2. I have another field called START that shall contain the start date of e.g. Wild West Tour 3. a separate fp5 file contains all my start dates. Scoop: If I enter Wild West Tour I want field START to show me ONLY the dates of the Wild West Tour, if I enter Route66 it should only show me the Route66 dates etc etc. Do I do this by assigning a script to the field START …

Sorry if this is the wrong location for this post. Feel free to move it. I've been asked to create a report that will track students' height and weight over time. I have a 'students' database that holds the height and weight information. I could easily create a list report that just lists the students' names plus their height and weight, but I've been asked to have the report display historical data on this. Specifically month by month. Attached is a quick sketch of an idea of the layout of the report. At first I thought I'd need a wt/ht db where one month was one record, but that seems wrong. Any ideas? Thanks in advance.

I posted this earlier but deleted it when I read it back and found I couldnt really undestand what I had asked! I have essentially a very simple problem with two tables One table is product list and consists of two columns; numeric ID and name. Second table defines which products are allowed to mix with others to make a pair. This table consists of four columns ID and name and ID2 name 2. I want to make both name fields auto enter based on the values I enter in ID and ID2 looking up the name values for the corresponding ID from the product list table but cannot seem to do it. For example product list ID Name 01 Water 0…

Hi There I am merrily continuing with my database project, and although all my relationships and what I require them to do are working, I thought I should attach a PDF of my relationship graph to see if anyone can see some alarming errors or possibly a better structure. I am only 10% complete so there may be parts missing at this stage. Thanks, Peter graph.pdf

I work for a 24 hrs TV station, and they really need a db system that can handle the mess. They are looking for an consultant on fixed basis who makes C++ code and apps on his own. I, on the other hand have worked some in FM6 Dev. and would like to try to make a db. My primarly tasks are video post production, though. One of my fellow worker, who work with a schedule of programs to broadcast, sit on a Excel sheet and have a post for every half hour during a day, all the days of the week. One week is one sheet, and he is doing a lot of paste/copy work. When I saw this I was thinking about this main Excel doc could be the main db where you could see all the hours duri…

I have a relationship from one file to another based on two fields. I use the external layout to view the records. From the first file I can "Go to Related Records" and on my mac I either show the records or if there are none I get 0 records. On windows I get the same result if there are related records but if none exist then I still show all the records. I was using this as a way of deleting all the related records at times but now it only works on the mac on the PC I get all the records deleted. Is there a way to go to related records in windows and find out if any are really related or did it just not find any. I am new to v7 and the way I set the relationship works fi…

I manage an entire design studio with FM pro and have the following databases (tables): Work Orders - invoices - timesheets - project expenses - project numbers. All these tables inter-relate so that project numbers are value lists in all files etc. I then have one file called "Job track". The purpose of this is to gather information from all these files together in one "report" type of file. The job track does not have any of it's own fields except the Job number to create the relationship, every other field is taken from the other tables and displayed. This is a very useful tool to oversee the status of the project, time spent, billing status etc. My only …

I am attempting to find a more elegant way of displaying data of related records by using a calculation field. The purpose of this is to produce a snapshot of a record and all its related data for archival purposes where the format would be a text dump essentially. Use of calculation field(s) to do this will solve 2 problems: 1) Because the calculation is based on related data, it will not waste space because it will be unstored. 2) It will allow me to build the text dumps without having to rely on additional tables and scripts to transfer and build the data. Is it possible to use a calculation field to display data from specific related field(s) of a speci…

Hi there, I was wondering how to insert a sub table, or the FM equivalent of one. The terminology is slightly different from that of MS Access and I'm having trouble finding the tools I need. Thanks in advance!

Hi, I created an Invoice/LineItems database consisting of two tables in a one-to-many relationship for a client about a month ago. Really basic stuff. Recently we discovered it had gotten corrupted: there were orphaned LineItem records, some Invoice records had lost their LineItems, and there were even Invoice records with duplicate Invoice_IDs (used to relate from Invoice->LineItems). Here are some facts about the database that I think are relevant: It is being served on a LAN by FMServer 5 In the Invoices table: -- Invoice_ID is Indexed, Auto-enter Serial, Strict, Required Value, Unique -- The relationship "LineItems by Invoice_ID" is define…

Which is faster, sorting in the relation or in the portal? Anyone done any definitive testing?