Relationships

Please excuse any brain dead questions...I'm a newbie. Right now I have two tables, a Client table, each client being assigned an auto enter serial number. I also have a Job table, each job being assigned an auto enter serial number. The tables are related, and each new client entered is added to a value list. The relationship works fine with new records, the same serial number being identified with the correct client, in both tables. I then import my entire client list, from a tab deliminated text file, into my client table. The imported clients are added to the value list, which is displayed in a pop up list. However when I choose to enter the client name man…

I am a Newbie and have a question that I just can't understand. I am designing a datebase that has Customers and invoices. On the Menu, I would like to have invoice and customer portal queries that I can open from there. What would be the relationships between menu, customer , & invoices. Thanks

I am busy designing a database to manage projects at work. From the attached relationship graph, you can see that there are both "timesheet" and "to dos" tables related to the "jobs" table and each of these, in turn, includes information about the relevant staff member for each record. I have designed layouts for acessing information by clients, jobs and employees; in the employees layout, I want to have portal records for "to dos" and "timesheets". At the moment, it shows me all the "timesheet" information for each staff member, but I cannot get it to do the same for the "to dos". It seems to me that some kind of relationship is needed between the "staff" and "to dos" ta…

Hi to all, please excuse my lack of knowledge but i'm learning fast! I am creating a solution for my wife's osteopathic practice here in England. I have a good "patient" table which works fine and i have added a "bookings" table which appears through a portal in the main layout. The bookings table is very simple just with a BookingID, PatientID, Name(Looked up), date, time and treatment type. Now I need to add an "Invoice" table. What i need to know is do i really need to produce an "InvoiceLine" table to produce the invoice or can i just use a portal to list these bookings relevant to the patient? Her patients require different combinations of invoices fr…

Hi I have a number of tables for contact info for different types of people- producer, client, deliver to, etc. The main table is a job sheet. The contact fields on the job sheet have relationships to the other related tables and do lookups for the related info- phone, email, etc. How can I allow creation of new records in the contact tables from the job sheet? I clicked the option in each of the relationships setup to create a new record in the particular contacts table, but this does not seem to work. Am I missing something? Thanks in advance.

I searched a little for an answer first, but couldn't find this exact scenario. I figure it's pretty common, so I hope someone can help me. I have a product database that I'd like to add hierarchical categories to. Right now there's a few hardcoded category field that work through conditional value lists. The structure is going through a bit of flux though and I'd like to generalize it. The data modeling part is simple enough: Product has a CategoryID and Category has an ID and a ParentID. The hard part is user interfaces and reports. I figure that I can probably generate a value list that includes dashes in front of the sub-category names by addi…

I need help designing a relational structure to link a catalog, inventory and sales system together. Basically I want to be able to enter any item number from either the inventory's table or the catalog's item table into sales item portal on a order form and for it to pull the information (price, description, etc) into the order. Current Tables Inventory (holds all item information: Item number, price, desription, etc) Catalog table Catalog Item Table: Holds the items that are in the catalog. Item info is pulled from inventory when item number is entered. sales table sales line item table: holds the items for the order. Item info is pulled from inven…

I wasn't sure how to define the subject for this one . . . I have created a database with primary and foreign keys using text fields that the users would understand, so they can track and re-assign the records, via a alpha-numeric code that means something to them. I have also included a unique number field. The users regularly need to re-assign the records, which they can do by changing the foreign key for that record. The GTRR works fine for navigating with the user text field keys, but searching needs to be via the unique key. As an example, the User Key is AN1 and the unique key is 00100 for this record. Searching the unique field for 00001 only…

I have two tables (Table 1 & Table 2) in which if Field "A" in Table 1 is populated than a record is created in Table 2. How would I force the deletion of that record in Table 2 if I go back and unpopulate Field "A"? I would imagine that there is a calc that would allow for that, but cannot seem to find any reference to such a thing. Any help would be great.

I'm wondering if anyone can offer me some help. I'm trying to do what I think should be a simple thing, but I can't seem to figure it out. What I want to do is a simple check to see if there is a certain related item in the database. Our situation is that we want to check to verify that a catalog has been added to certain orders. I'm currently going to the other table and looping through the items to see if it is there, but I feel like I should be able to do it with a calculation from the main record. I want to be able to do something like the following but that only checks the first item in the relationship. PatternCount ( Order Items::Name ; "Catalog" )…

Ok, I am just not getting the picture here... I have two tables and all I want to do is if the field in Table 1 "SerialID" is populated, that a new record is created in Table 2 and users can view the data of those new records in Table 2 sorted by the "SerialID". What would be the easiest way to accomplish this?

I'm not sure if this is a relationship or layout question but since I'm having difficulty picking up self-join relationships (new to FileMaker from Access) I thought I'd start here. I have a MAIN table that has a one to many relationship with an Images table. I would like two relationships for these tables: one that forbids adding/deleting records in Image from MAIN and one that allows it (different permissions for different layouts). Problem: When I create a second occurrence of Images (edit to and from DISABLED) and use that relationship to join the portal, my scrolling portal now shows only the first image (record) of the related records. Both table occur…

Hi There I have a minor issue i'm trying to get my head around. I am presenting statistcis via a portal to show the number of calls logged against a particular customer contract. What I am finding though, is that when the portal has been accessed, I have to click off of it (i.e. background or another field) before the values are displayed. When they have been displayed once, Filemaker seems to cache the stats ready (instantly) the next time this record is accessed. If you quit & relaunch the database, they need to be refreshed again. The stats arrive via a relationship to another table which is using a summary field to calculate the statistic in th…

Suppose I have tables apples(ID) and oranges(ID, applesID) such that apples::ID = oranges::applesID. The main layout shows records from apples with related information from oranges. What I need to be able to do is automatically create an oranges record whenever a new apples record is created. Is this possible? I need to be able to do this without scripts, and as transparently as possible for the end user.

I am trying to conceptualize how to set up a relationship and/or portal. Any thoughts on this one... Tables: Orders, Clients, Payments Relationships: Orders::ClientID=Clients:ClientID Orders::OrderNumber=Payments::OrderNumber I thought this was a great setup, as it allowed for partial payments. But here's the issue: The user previously liked to browse Client Records and enter payments though this layout, as it was quicker since there was a portal to show ORDERS. The payments were contained in a payment field in ORDERS. The 3rd PAYMENTS table did not exist as the payment was recorded in the ORDERS table. How can I show ORDERS in a portal on a…

Can anyone point me in the right direction to learn about the related records script step? I have an invoice table an invoice detail table. I would like to be able to view all products ever purchased by a customer but only the last date it was purchased. Thanks much. John

Anybody out there have time to give a clue to a newbie? I am working in fmp 8 for the first time, developing a file of student scores for my own use. The file has four tables: Students, Assignments, Assignment types ( homework, quizzes, essay exams, for example), and Scores. Each Student is identified by a five-letter StdntCode, which serves as the match field between the Students table and the Scores table. A unique Assignment Number provides the match field between the Assignments table and the Scores table. So each record in the Scores table is identified with a particular student and a particular assignment. In addition, a two-letter Assignment Type Code p…

Hi having a little trouble with a counting function in a database with several related tables. The database consists of 4 tables related in a linear fashion from patients to visits to samples to sequences. Each relationship from left to right is one-to-many. Sequences is related to samples via sample ID which is defined in both tables. Sequences also has a calculated field "PatientID", which is a manually entered value (stored) in samples, and a calculated field (patientID = samples::patientID) in sequences. I am hoping to be able to go to a record in patients, and count how many sequences from a particular region (for example, the "gag" region) from that patient…

I have a main database with points for each record. Then I have another table or separate database with 7 different user types. you can choose the user type and amount of points in the main database but I can't seem to get a summary of points/per user type in the user database. I tried using the sum(field) but it wont update across the tables/databases. I think this is a relationship problem but i I don't understand relationships well enough to know what is the problem. Can anyone helpe me? In the main database it is linking to the user type database and the user type database is also linking to the main but for some reason the totals wont update across databases.

I have a datebase for persons. In a portal I can enter relations, i.e to which other persons the actual person has a acquaintanceship. Example Debbie knows Martin Frank <-> Ken Frank <-> Martin Rebecca <-> Eddie Eddie <->Martin I am looking for a FM solution to the following problem: For a given person, lets say Debbie, I want to know who is the intermediate to come to another given person, Frank. In the example above: The solution is Martin Debbie<->Martin<->Frank (The next more complex problem would be over 2 intermediates: example Debbie<->Martin<->Eddie<->Rebecca, but let…

I'm having a problem with a new database I have written. It's a CRM, and I have a portal that displays users that have experienced the problem listed in that particular record. The users are stored in a different related database. However, on occasion when I create a new record, some of the previous portal records are copied into the new record. Anyone have any ideas? Thanks, Chris Purchasing_Knowledge_Base.zip

Don't know if this is the right forum to place this thread in, but it looks like it... I have a problem that I will solve eventually, but maybe some of you could have good opinions on the best way to do this (and thus preventing me from invent the wheel twice)! : I think it has been talked about before, but couldn't find a perfect solution. This will be a meeting planner solution for several groups with several people and several dates. Most of it's already done, and it is workable, but I want to present it in a cross-reference style, and that's where I am now. BACKGROUND A bunch of people wants to plan meetings. Since all of them are on tight schedules i…

Hi, This is probably a simple mistake but I'm used to MS Access so FileMaker seems a bit strange at the moment. Please be gentle! I've made a form which is my Contacts table. I have a portal which lists fields from my Contact History table and joined them on the ID field. I want to show records in the portal that relate to the records on the main form records. On click of a button, I can update the first record in the portal with time of call etc, but on further clicks, the same record is updated when I need a new record to be added. Can anyone help?

Hello, I am trying to understand relationships in more detail, as I have run up against a problem. I have uploaded a simple example. I have Clients, who are related to jobs (of two types hardware and software). I then have employees. To assign an employee to a job, I use a join table with a referance. Say "First Job of the Day", I then take the jobID and the employeeID and put the two togethin ini the Join table. This works fine for Hardware but when I try to put the relationship in for Software (see example) Filemaker says "there cannot be more than one relational path...." and tried to put in another table. How do I get around this. You can se…

Sorry, I have done this in the past, but It has been a while, so I have forgotten. I have to do a M to M. relationship between to tables, I remember that I had to create an intermediate table, but then what? I am in FMP7, and the help and manual only explain single key or multi-key relationships. Thanks a lot.

My plan is to have the user view a list layout showing various reports and their contents and other significant data. Some reports have similar types of data from different dates and comparing that data is important. One of the buttons in the header is "Same Client & Report" which would (using setfield) copy the client ID and Report items fields into corresponding globals and the command show related records. The result would be a list of all records having same item and client id as the initial report viewed. This worked great in FM5 but not FM7. What am I doing wrong. Find would not work because reports can have multiple topics A,B or B,C, or A,D. If my…

Can anybody tell me how to make a field automatically resize to the amount of text i type in it. For example, when i type in a field "you have two apples" and i show it in the browse option it is one line but when i type "you have two apples and you have two eggs" i want to see two lines instead of one very long line and i want the fields underneath it to slide down.

Hi, I'm in the process of creating a simple "sales leads" database and am wondering whether it is better to us value lists or a related table for things like "lead status" or "industry". Is a related table overkill? Is a value list in filemaker really just a related table cloaked in an easier GUI? Thanks in advance, Dan

Actually I'm not sure if it's a relationship or a lookup. I am a total FMPro newbie. I have a database from a manufacturer that has product UPC codes and prices for those products. I have databases coming in from customers with the same product UPC codes and how many cases they ordered of each product but with no price attached. What I would like to do is have my customer database match it’s UPC code to the manufacturer’s database UPC code, then take the price associated with that upc code and put it into the customer’s database and then total the price of the product by its quantity. The last part is a simple calculation that I can handle, but I’m not sure how to go abou…

Hi all, hope someone can help me with this.. I've made a very simple database in which 1 field is a repeating field with 21 repetitions. I would now like to change this repeating field into 21 separate fields on the same record - can this be done? any advice greatfully recieved!

... righto just a small problem / query... i have a table set up with an id and a global store field.. when you execute a script, the id is added to the global... now ive made a self-join relationship between the global field and the id... basically, how do you check if the id is already in the global returning a 0 if its not in the list or 1 if it is..?... lol work around but hey... cheers, genx

Hi At times i need to access staff information so i should create 1 staff database isn't it? Is it create itself in a single file? Then whenever i use script to open this particular file and access the staff information?? else all the files need to have 1 staff database in it??? it seems ridiculous... Thanks a lot on advance Kelvin

Searching another data Base (Filemaker Pro 6 with all data bases on Server version) Problem: Data Base A, Text Field 1 Data Base B, Text Field 2 These data bases share no common fields and are therefore not related I want to write a script which will set a flag in Data Base A field 3 if the text string in Data Base A field 1 is found in any record of Data Base B Field 2

I'm working on a database and I am having a difficult time figuring out how to get the data entry field to automatically return to the next row in the portal. (please see attachment). The data is coming in correctly from what I can tell, but when one line in the portal is filled out, there is no continuation to the next line. Is there a way to correct this problem? Any help would be appreciated. Thanks, Nemesis

Hi, I am trying to represent a dependency relation between events. I have a table called 'Events', and a join table between Events and itself. The join table is called 'Prereqs' and has two columns, 'earlier' and 'later'. The values in these columns are primary keys for Events (this is what I mean by saying it is a join table between Events and itself). So if one row of Prereqs is the pair then the event whose primary key is 'a' is earlier than the event whose primary key is 'b'. I have a table 'Extended_Prereqs' and I want to populate it with the transitive closure of the Prereqs table. So, if is in Prereqs and is in Prereqs, then I have an algorithm c…

I posted this problem earlier but in an incomprehensible way. Hope this is clearer... A record in Table 1 may have several or no related records in Table 2. The same record in Table 1 may have several or no related records in Table 3. The same record in Table 1 may have several or no related records in Table 4 (and so on for tables 5 to 8). What I want is to have a few fields from each of Tables 2 to 8 showing through a single portal on Table 1. If, for the sake of argument there were 2 related records in each of Tables 2 to 8, my portal on a Table 1 layout would show 14 rows. Obviously I need some kind of linking table. Is this possible, and if so ho…

I am currently working on a database and would like to link search criteria fields to one another. For example, I have a drop down list that shows the year a certain event took place. Ideally, once a user selects say, "2001", I would like all the events that occurred during 2001 to be displayed. Unfortunately, at the moment, a user will have to scroll over to the next field and use the drop down list which contains all the events that took place from 2000 to 2006. Is there anyone out there who knows how to create the relationships I am trying to establish? Basically create a system whereby a user can select 2001 and automatically have ONLY the title of events that took pl…

I have several scenarios where I want the user to be able to select a nice friendly string from a drop down list, but I don't want to store the string they select in the record.... rather, I want to store a tiny little unique ID (that happens to establish a relationship to another table). Since the drop down lists in these cases are long and I want to use auto-complete, this has proved difficult. What I do now is have a temporary match field w/the drop down list. User makes the selection, and this establishes a relationship w/one TO.... call it Fetch. Then an ID field (still in original table) does some kind of look up in Fetch to get the corresponding ID. …

Our customers are stores (wholesale) and individuals (retail). The retail customers pay their own invoices (usually via credit card). Some stores pay their own invoices; others are chain stores. With Chains, the invoices are sent to the store but payment comes in a group (sent as one check with a breakdown) from the headquarters. They take cross-credits between stores, etc. and these accounts intermingle quite a bit. I've established a PayerID field in Customers which holds the CustomerID of the corporate headquarters (payer) from a different record in customers. A calculation, if the PayerID is empty, uses the CustomerID as the 'official' payer. Management wants …

I would appreciate any help anyone can come up with. I have a client with a multi-file solution that used to work. Recently (now using FMP 8) he cannot delete records. I have tried to figure it out but am having no luck. It is not something wrong with his copy of FMP8 as I tried it on my FMP8 Advanced as well and I'm using a clone of his with just a couple of new records. Regular dialog box comes up asking if you really want to delete - say okay and dialog goes away but the record remains. Can't find anything in any privilege set that would deny access to deleting. Have gone in with full access. If you duplicate a record but do not click out, the delete button wi…

Is this possible, and if so how? I have a layout showing records from Table A which contains a portal to Table B. When a user types within this portal a related record in Table B is created. What I want is for a record also to be created in Table C at the same time. Can this be done without the user having to do anything or do I have to provide a button/script to create a relationship to table C? (I'm struggling with this approach as well - with my limited knowledge I would have thought the Set Field function would be the way, but I can't seem to make this work properly) Any advice appreciated.

I'm trying to work of the "tasks" template to create a database that will allow me to have multiple projects and each project having it's own multiple tasks. The problem I run into is importing my tasks from an excel spreadsheet. I need to be able to either create a project or assign tasks two a project while importing from my spreaadsheet. I've got spreadsheets that have many lines of "tasks" and then infor for each task and I need to be able to put those tasks under a project. I can't seem to figure it out. If I import them to the "tasks" table in the template it doesn't create a project and I can't figure out a way to import while i'm looking at the "project" page. As …

I have two (2) tables. One table is for company information, and the other is for contact (individual person) information. In my contact layout, I have the company field configured as a drop-down value list, referencing the company field in the company table. I have a one-to-one relationship between the company fields of both the company table and the contact table. When I go to create a new contact in the contact table, I choose the company in the value list that the contact is associated with. So, let's say I'm creating a new contact and want to associate that contact with company X. When I go back to the company table, and search for company X, it now…

Where are the legs ?

Hello everyone, I currently have 3 separate FM files that are all relational. each file has 1-3 tables. I'd like to integrate them all into one file so that I don't have to have 3 windows open all the time. It seems that this should be possible. How do you import tables into a file in order to consolidate them all? WIll i be able to maintain all the relationships?

Is it possible to create a relationship based on "Match any/all of the following rule"? I love how "similars" work. You know that cool feature with OS X Smart Folders? Or iTunes Smart Playlists, where you can set multiple criteria and choose "all" or "any"? I want to do that. I have a grant file where each grant has restrictions—some you need to meet all, some any. I want to create a conditional value list to that table, based on said similars. I'm wondering if it's possible.

I need to take the database we have and have either another layout or database added to give reports. How it's set up, status of the products (In Graphics, In production, Shipped) is a drop down list as is the product quantity list per customer. Now I need to be able to pull certain information from it. Like how many small bags are in Graphics, or how many large purses were shipped. I can't seem to be able to pick all the the various bags in Graphics without having the total from In Production and shipped along with it. I don't even know where to begin to do this.

I've been struggling with this query for a couple of days, and looked on recent posts for help, without avail. Here's hoping someone can help! I have a database which generates quotes for complex direct mail print. I used to use a single table for each project/quote, but this was getting unwieldy and inflexible. The reason for this is that a typical direct mail piece might have 1 or two different types of envelopes; it might then have 2,3, 4 or 5 different print items; it might have 1 or 2 inserts ( a plastic pen, and or some other kind of premium); 1 or 3 or sometimes even three of the print items might be personalised... and so on. Hence my old 'Quotes' table allow…

Hi, I'm new to this forum and I've just recently learned to use FileMaker Pro to solve database problems. I have made one simple database that involved a simple script and I have some working knowledge about defining fields, value lists, and relationships for portals, but not much past the very basics. I have some programming experience. Basically, the database I'd like to design would be used to keep track of some biological samples that are stored in freezer boxes. These boxes are in the form of a grid, and thus have rows and columns which can be used to identify the position of a single sample. I'd like each sample to have its own database entry. My quest…

I'm not sure why I'm having such issues with this, as it seems like an easy problem, but here it goes: In the relationships below I am having issues with creating a new quote. As you can see I have a relationship of contact id between prospects and quotes. When the user is looking at the main prospects layout (which contains only data from the prospects table) I want them to be able to run a script to create a new quote for that customer. Right now when they create the quote a new record is created in the quotes table, but the contactid in the quote table is not being populated. I'm sure I'm missing something easy... any help would be appreciated. If need be I c…

Can anybody tell me how to make a field automatically change when you select another option through a drop-down list

Can anybody tell me if it is possible to: when i change country through the drop-down list and when i press the button print, i will have the right layout. For example; i have a costumer who lives in france, so i select the costumer and i have all the data of his address and when i press PRINT i want to have the layout for france.

i have two tables, and two key feilds in each date1 = date2 and constant1 = constant2 when i just have the date feilds linked the portal based off of the realtion works, but if i add the link between the constants it no longer works. the craziest thing is that i have already made this link 12 times with the same date feils and another set of constants, and i don't understand why this set won't link, i have checked to make sure that the info on each side is the same and i have directed the correct feild to its counterpart...is there anything i am missing or should check

Hi All Can any one help? I have a table, call it vehicle-data, with a one-to-many relationship to service-history. Vehicle data is about the vehicle, and a new record in service-history is created every time work is done on that vehicle to store the service details. To create a new service-history record you can use cut and paste via script: 1. Copy vehicle reference number in vehicle-data (the field used to join in the relationship) 2. Open a new record in service-history 3. Paste the data into the new service-history record relationship field You have now created a new related record for the vehicle ready to enter all the service dat…

Is there any way to change a layout automatically to another language. For example: if you have a bill to England and a bill to France, is it possible to change the language of all the fields automatically to that language without having to make a different layout or does anybody know how to make a drop-down list where i put the languages in and when i make the bill in English, i have to select the language in the drop-down list and it will automatically choose the right layout. Thx

Good morning! I have a rather simple database, kind of a call center that that tracks incoming calls and consists of a CALL table and and a NOTE table. The primary key in the CALL table is CALL_NO and there is a foreign key in the NOTE table also named CALL_NO. Basically I create a call record for all calls that come in to my organization. As we try to resolve the calls we may create zero or more notes. These notes are created in the NOTE table and are attached (related) to the calls. I have a portal on the CALL table that lists the notes and I have buttons that allow the user to drill into the notes because the note could be anywhere from a few words to a dozen…

okay... i've got three tables: clients, employee, jobs. jobs is the primary table. each job can only have one client but can have multiple employees. my problem, is that i seem to only be able to register only one employee name to each job. the relationship is based on the field "employee name" which, in the employee table is a calculation combining first name and last name. what do i need to do to be able to assign multiple employees to each job?

Hi there, When I design my database and create some relationships between tables, should I do this before or after importing data? If I already have data in my table, will the relationship be made properly when I insert data in another table? Tnx in advance for the help, greetz

Hello Everybody: I have a big problem, i used the sample of Contact Management and now i want to make a new layout where i can make a bill for someone from my contactlist but i want to automatically have his name and address at the top of the bill. The problem is that i only have the ID at the top and not the name. When i press NEW RECORD, everything is gone. Please HELP

... including the fact that I'm not totally certain that what I'm looking to accomplish really *is* a MtM relationship. : (Quick background... I used FMP quite a bit several years ago for various personal projects, but never had a need for a relational database. I recently bought FMP8 -- my last version was 4.1 -- and I'm working on my first relational DB. I've got "FileMaker Pro 8: The Missing Manual" as my documentation crutch, but I'm hoping some human interaction will help me get my head wrapped around this.) Here's what I'm trying to accomplish... I currently maintain a web page that lists info about books related to a particular game. (It's at htt…

I've just set up a really big database (well, for a newbie, anyway). Before I go too far with it, I'm wondering if any of you experienced users could look at my relationships window and tell me if I've made any errors that will cause problems down the road? My grasp on relationships is a little shaky, but I *think* I've got it set up right. I've attached a gif for anyone who has a second to look it over. Thanks in advance!

Not sure if this has been mentioned here before. I spoke to someone at Filemaker and they didnt seem to be aware of this problem. As of 7.03 the Go to Related Record Script step requires you to select "Open new window" if you are relating to another file. However, if you are relating to another table within the same file it works fine. I remember having to go back and change all of my scripts when the 7.03 update came out because it killed a bunch of my scripts and it seems that it went un-noticed in releasing V8.0. I have a demo that demonstrates what I mean if anyone is interested. Anyone else notice this problem. Thanks & Happy Holidays. Ron

I have been writing a file to run my office and need help with a routine to count down the number of authorized session for a given patient. I have had no trouble assigning a value of 1 to each session and then subtracting that from the number of sessions allowed. The problem arises when the allowed sessions are used up and a new authorization is granted for the same patient. I have tried linking the calculations to a serial number I have given the authorization grant but no matter how I do the calculations the prior sessions end up subtracting from the new set, whereas the process has to start over. I do not want to erase the old data as it can be important to retrieve a…

I have looked through the FM Help documentation and cannot find the answer to this. I have a file with 2 tables in it. The main table has a portal in it and I want it to display records from the other table. Now when I set the match field relationship to "=" none of the information show up in the main table, neither in the portal nor outside of it. However, when I set the relationship to "X" everything shows up fine. Is there a particular reason for this? Thanks.

Hi, I am not sure I am in the right section of this forum, if this post should be in a different category let me know and I'll change it. Here's my problem: I currently have one table called STUDENTS. In this table there's a field called CLASS (from the student). Now, what I am trying to do is get a list of all students in each class. I think I need a second table called class and make a field CLASS ID. But when I try to make this I keep getting a many-to-many relationship, result: on the layout my data is invalid... How can I filter these students from each class (let's say in a dropdown list), so I can choose which one to edit? Thanks in adva…

I'm trying to work on a database for the company I work for. My boss wants a sales report totaling each product we've sold. Now, we have a database with each customer and the items they bought. What I can't figure out, taking the totals of all the small bags sold, taking the totals of all large bags sold, ect and put it on a new layout. All help would be appreciated.

I am new to FM and I am trying the evaluation product. My client is looking for a project management system containing the usual things: estimates, invoices, receipts, correspondence, etc. I would like to combine these documents in a single table called "activities". Then I could display a history of activities e.g. rased an estimate, raised another, got go-ahead letter, raised sales invoice. My problem is how to incorporate an activites table. In MS Access, for example, I would just create a "union join" of the various document tables using the fields "date", "desciption" and "amount". I do not believe that I can do unions in FM 8. So I need to populate a separa…

Hi, I'm just getting into fmp. I have a database started for my company...it's basically a contact database...names, #'s, emails, URLs, etc. etc. All this info is in one main table. I want to change the layout, so, for each company or contact, there's a new table that exists, where I can enter all information following an actual contact....e.g. date of contact, who I spoke to, what was talked about. I guess I'm getting confused, because there are going to be multiple contacts for some of these original records. I can't figure out a way to logically connect this to the original table, because of the instance of multiple contacts. Do I need to create a whole…

I have a problem with a simple DB that I am putting together. I have attached a schema that illustrates the relationships existing in the DB at the moment. What I am trying to fix is the ability in table ESTIMATES to select a SUPPLIER from a drop down menu and, by clicking a button, create a PURCHASE record which is linked to the previously selected SUPPLIER. Please, please help. TIA

I want to lookup information from previous records for new records in the same table based on conditions where multiple fields all match. Can anyone help me the basics for this relationship? I'm getting nowhere, but frustrated. Unfortunately, I can't find the help I need in Search feature. Thanks

Hi, This is my first post on this message forum so I hope I'm posting this in the correct place. Here is my setup: I have a relational database with two files. One stores events, the other stores event activites. It's a 1 to many relationship respectively based on related eventIDs. The relationship is working great. What I'd like to happen is when an event is deleted from one file, the associated activities will be deleted from the other file so I don't have orphaned activites. This was easy for me in Access because I would just check off "cascade delete" in the relationship between the two tables and voila, it's done. However, I'm not exactly sure how t…

I have a feeling this solution is simple, but it's got me stumped. I have 2 tables: - Graphics (all items available for sale) - Inventory (those items which we currently have in stock) These two tables are joined by a field called "ItemID," which is defined in the Graphics table. On my Inventory layout, I have inserted the field "ItemDescription" from the Graphics table (so that when I enter an ItemID on the Inventory layout, it will automatically fill in the description). Okay, so this works fine. My problem is this: on the "Inventory" layout, both the "ItemID" and the "ItemDescription" fields appear to be empty after I've entered data into …

Sorry guys you are going to think this guy is dangerious with a computer let alon filemake. I have a list of clients which includes there e-mail address. I want to be able to create a separate table which will list all the emails i have sent to the clients. I have created 2 tables One called clients and one call messages. I have created a relationship based on the email address. I want to be able to select a client click on a button next to the email address which will create a new message record and populate the e-mail address field with the clients e-mail address. Many thanks in advance

ok , I've only been using filemaker for about 2 weeks so sorry if this question is stupid. i have 2 files, one where the user enters data about classroom bookings and one where there is an overview of the all the rooms in a day.For each day i need to be able to show all the rooms and have what activity is booked for that room. Any help would really be good. Or any suggestions of doing this whole thing diferently.

I have a multi join relationship Three tables are Asset (y/n) Fund (y/n) and then this multi join is related to a Company What I want to be able to do is this and I'm getting so confused.... See attached..I want to be able to have a main company page that shows the four types with the definite yes and no. Then I want to be able to drill in to each one of these and see all the definite global yes and no related to that. For example Global Def yes to Asset1, Fund1 Global Def No to Asset2. Fund2 Therefore the following when I go into Asset1 I should see a yes to Fund1 and a no to Fund2. On top of this I would like to be able to add…

I am creating a database that stores student exam data. Each exam question has three corresponding database fields: objective measured (there are many more questions than objectives), student response and correct response. I need to create a design that takes the fields from a single student exam record and creates a temporary table (or temporary repeating fields) where each record in the temporary table corresponds to a single question. The reason for this is that the exam questions are randomly distributed such that there are not predetermined clumpings of questions associated with a specific objective. I need to produce a list that orders the questions from a s…

In Case Studio 2.22 I made an ERD as can be seen on Link. When I made the same diagram in Filemaker 7, Filemaker doesn't accept the making of relationship 5. Does it have to do with the 'circle' I'm creating in ERD or is there something else wrong? Case studio allows me to make this ERD... Kensje

Ok, perhaps there is a place I can go to define error messages, but I can't find it. I am getting an error message (See attached file which contains error message and a copy of the layout) when I try to enter an invoice date on my invoice data entry screen. We get retainers or "Advanced Expenses" on all of our assignments. So I need to have a table and an invoice for the initial payments and then a final invoice. All Invoices are related to the "Job_Orders" table. So, why would I get this error? Also, this site has really, really helped me and I'd like to make sure I support it properly. How do I do that? Thanks. Al JO_ID_Error_message.pdf

I am new to the concept of having several Tables in one Database. Previously, when I had to create a record in a (child) related database, I transferred the data using globals, then called a script in the related database, which added the record. Now, this should be easier with multiple Tables in one database I thought. Just call "New record", specifying the Database and insert the data. However, "New record" will create a record in the parent database. You cannot specify the target table - or I simply did not find this function. Well, I can jump to a layout where only the child database is listed, or as an alternative, can jump in a layout, where a portal i…

Giving that this is my third database I'm creating for a business, it is REALLY embarassing that I CANNOT figure out why these two databases are not sharing data. The relationship is built in both databases, the related fields are in place. Everything is indexed that needs to be and yet, I'm looking at layouts with blank fields. I know I've missed something simple and I'm going to hide all sharp objects when I read the response. Thanks in advance.

Hi, I'd really appreciate an extra set of eyes on this one. I'm trying to create 4 product pulldown menus with a respective field next to each that does a lookup and pulls in the corresponding product code. The products need to be in a seperate file from the main database. I'm very close, but when I select from the first menu, the code appears on all 4 of the code lookup fields instead of just the first. Hmmm. Any help would be much appreciated, the small file is attached. I think the problem has to do with the relationships, but I'm just stumped. HappyLookup.zip

Hi everyone, I’ve done something I know NEVER to do. I term it Band-Aid design. It’s similar to urban sprawl, indicating unplanned-for additions on an existing process. I created a major dog, ie, unstored calcs referring to unstored calcs which refer to unstored calcs because info was needed in the exact moment it was requested (know what I mean?) and I couldn’t THINK in that exact moment! I’ve attached my existing process (only two example TOs) and a GIF showing the existing process results. I hate to fess up to this kind of mess but I want it right. I’ve tried several things and I just can’t determine the best approach here. My method works but it's very very …

Hi: I find I am over my head with a database that I've promised our baseball league. If this sort of question is not allowed, or is something I should be paying for, please let me know. I've been keeping the ball league stats for 15 years now and have some simple relationships, but we have only ever kept the stats for individual years. I know this will be child's play for most of you, but I can't seem to wrap my brain around how to structure this database and then how to show the results that we want. We have 27 teams, each with at least 20 players each year, for 15 years, so many different players. Each player has statistics typical of a ball team or …

Ok, I don't get it. I have built a database with 19 tables and multiple relationships. All of them have been working fine. My portals find and sort the data correctly. When I enter an ID number from the parent to the child (Ithink that's how you refer to it )the fields are populated with the correct data. So, now I go and add two more tables. The database is for a recruiting firm. So, I add "Reference Check Names" and "Reference Checks." Ref Chk names has the following: REF_ID CO_ID Contact_ID rName rCompany rTitle rPhone The Contacts database has over 15,000 entries. We specialize in Scientists in the Pharma industry so pretty good chanc…

Hi Guys, Im working on a new project, and for once I've decided to sit down and plan out my database on paper before creating it in FMA8. Ive attached a very basic sample of my relationships. In the diagram you can see the relationship: subjobs------ How do I reverse it so that I can have subjobs>------status Thanks as always

Any ideas: I've finally ditched PC for mac. I'm still using (for the moment) Virtual PC for Mac and MS Access but I want to move fully to FM8. My problem. I've exported my Access data via ODBC into FM8. Some Tables are fine as they're standalone. But I'm having real problems with linked tables. I can easily get linked tables to show information from all appropriate (imported) tables, but the whole thing falls over if I try to add new data. By this I mean that I can see all of the old imported data but new records will not populate those imported fields. And vice versa - I can add new records by defining new fields and populate those new fields but I cannot see t…

I've been trying to work this out and I just can't figure it out, sorry if it is really lame-brained. I want to figure out total payments received from customers, comparing customers against each other. For example I want to quickly determine our top five customers in 2005. I have a customer table, each record has a unique customer ID. I have a payment table, each record has a unique payment ID. When a payment record is created, it looks up the customer ID (from the invoice) and copies it to the payment record. So this should be easy but my mind can't find the answer. I think I want to search a date range in the payment table and then subtotal by custom…

Am building a CMS for a client who is an investment adviser and am having a problem with a particular relationship setup. The tables are (tables in """"): - "Investment provider" which has a one-to-many relationship with "investment products" via provider_no. - "Client investments" which has a one-to-many relationship with "client investment products" via investor_no. So a "client investment" can have one "investment provider" but many "investment products". What I am trying to achieve is when a new "client investment" record is created, the user selects the "investment provider" and when they go to create the "client investment product" they are presen…

I'm trying to build a system to keep track of inventory for a restaurant. It is to replace an older DOS style one that only shows results after a count (it calculates everything by a batch process). Anyway one headache for users is entering many incoming product invoices. Ingredientinvoices to be more specific. Our current system has a layout with a list to chose items assigned to that vendor. I would like to add reoccuring sample invoices and purchase orders that can be made up as a work sheet but may or may not be used. I'm wondering if I should keep these types of things in the invoice/line item tables with a marker field or put them in completely differ…

Can anyone help me. I am looking for an online contact manager. I have thought of trying to create one using Filemaker. I thought possibly someone on this forum could suggest a solution that can be maintained online and accessible by any browser windows or mac. Thanks

Hi everyone, I am in a spot of trouble. I'm trying to set up a DB which contains SUPPLIERS, CLIENTS, ESTIMATES, INVOICES and PURCHASES. I have managed to create relationships that allows me to create SUPPLIERS from CLIENTS. I can create ESTIMATES from CLIENTS and then use those details to make INVOICES. However I have a problem with PURCHASES. What I would like to do is to be able to access any existing ESTIMATE (which is already related to a CLIENT) and from a drop-down menu containing the list of all SUPPLIERS, choose one which will then be linked to a newly created PURCHASE. I have attached below a siplified FM file for you to have a look at. Please help. Thanks…

I have developed the attached system (u: Admin, p: "blank") so far with a little help from those on FM Forums. : Things were going really well until I tried to add several new features. These essentially revolve around limiting what a user can choose from two pop-up menus after having selected a value from a first. In the attached, on the Lease layout >> Property tab it shows three pop-up menus ... island, property and company (manager). The idea is for the user to choose an Island first and then have the values in the Property and Company pop-up menus change. I know I do not have the relationships defined properly on the ERD (graph) as I am not eve…

First of all, let me just say, I have learned so much through this formum...Thank you to everyone. Now my question. I have a table which I call ProductDetails and relationshiped to that table I have Sizes and Colors. Ok, here is the deal. Every Product comes in many different sizes and different colors. I want to be able to show that in portals from the ProductDetails page. My sizes and colors table will already have static information needed the ProductDetails layout needs only to pick out the choosen colors/sizes. How do I now add the more than one sizes and colors available through a portal? Pictures attached to help explain. I'm hoping…

Hi, I did a search and came up empty. I have a file I am updating the reference links (well attempting to) so it displays a pdf. I can get this to work locally. But the server it is on is on my desktop. I have this FMP db hosted with FMP7 server currently. I have messed with the paths and I cannot get anyone else to see the pdf's? I copied the exact path, so I thought as they would log into it? No luck. Here is my path. The pdf's are located in samples. It is on the server that is running filemaker server... Ughhh! filewin://192.168.0.202/Library/WebServer.server-applesaved/Documents/samples/ Thanks for any help.

I think this is a relationship problem but I'm not sure. I've run into twice today & don't remember seeing it before.... Here's the situation * I have a table occ. I'll call Table A. * Table A has one record & is joined to a TO named "Call" in such a way that the one record is related to all the records in Call. * Call joins to a TO called "Officer Involvement" which joins to a TO called "Officer" * In a layout based on Table A, I place a portal displaying records from Call. This works as expected, showing multiple Call records when appropriate and in the sort order I specify (either in portal or in relationship). I also want to display in ea…

I am having a bit of trouble understanding TO's Say I have an Orders Table and a OrdersPart Table. I want to be able to make a new OrdersPart for that orders table so I create a OT NewOrdersPart. Now I know, at some point (?) the variables in NewOrdersPart are stored in the OrdersPart table right? What if you have other tables attached to the OrdersPart Table...such as Size,or Color. Do I now have to make OT's for them and attach them to the NewOrdersPart OT? or can they get that information from the origanal Size, or Color table through the OrdersPart Table?

Hello, I was wondering if someone could help me out with something that might be very elementary. I have a database of teachers each with unique IDs which I regularly delete and repopulate from data on our server. There are a couple of flags that I place on my end on FMP which I want to store on a related file with the IDs as the matching field. I pretty sure I've set up the relationship properly because I can click on the checkboxes on the main file and the related checkbox in the related file checks on. But, when I try to find records with the related checkbox checked "on" on the main file, I get a message telling me that "one or more of the rel…

I have two tables - PROPERTY and MANAGER. Each contain a MANAGER_ID field and the two are related using these fields. I also have a PROPERTY layout. It lists all the fields in the PROPERTY table ... one of which is MANAGER_ID. I want the user to see a dropdown box with MANAGER_NAME (the contents of which should come from the MANAGER_NAME field in the MANAGER table). Once selected this dropdown box would populated the MANAGER_ID field in the PROPERTY table with the MANAGER_ID corresponsing to the MANAGER_NAME choosen by the user. I can't seems to make it work. I hope that wasn't too confusing. If so I can provide clarification. Thanks. BeachDave

Hi, I'm working on a solution which includes my invoices and I appear to have 'boxed my self into a corner'. If anyone can offer some advice it would be much appreciated. Here's what I have A wide, full-screen layout with 2 separate 'Tab Panels'. Each Tab Panel has 3 Tabs. In the right-hand Panel, I have Portals which show a list of the invoices, according to some self relationships. 1 is a filter based on names and dates, the 2nd is based on Paid and Outstanding and the 3rd Currency. Clicking on one of the Portal Rows in any of these Tab Panel Portals, will show the relevant record in the left Panel. The Tabs in the left Panel show the record in…

I had hoped to create a module (file1) that I could link up to one of many files (file2)that are structuraly the same (same tables and fields) I added a reference for a test file and did a bunch of design, using the other file's tables in my relationship diagram. But when I tried to edit the file reference to point to a different file, all the fields on a layout that was based upon one of the external file's tables(with exceptions) showed "". The exception was a global container field from an external table that continues to show the correct data. Is what i am attempting impossible or am I doing it wrong