Relationships

In the olden days, back before FileMaker 7, you used to be able to put "client" databases on individual computers on a network. These accessed data from a database on a central computer. The advantage of doing this was that the individuals who just wanted to look up an address or something wouldn't monopolize the whole database and everything went smoother. I hadn't tried to program anything like that until just today when I discovered that creating a relationship to an outside file in FileMaker 7 doesn't work very well. Everything goes very slowly and data in portals doesn't seem to change when I change the data in the linking field. The relationship I am work…

Here is my trouble. We deal with Youth Pastors of Churches and churches. I want to be able to add a new person and a new church to our database when they call in and request information at the same time. The problem is that I cannot keep the personal info and the church info in the same table because youth pastors frequently change churches. Here is my question. How can I make a relationship between a church and a youth pastor so I can enter them at the same time, yet be able to separate them later and join them to a new church? I really need so direction. I am lost. Thanks so much!!! Drew Global Youth Ministry

Let me appologize in advance, please dont flame me. I am very new to Filemaker and although I love the power of the program Im finding it very troublesome to use. I just purchased filemaker about 2 weeks ago, to track my clients and their warranty claims. I have 2 tables set up within the same file, One is for clients and there information (Name, Address Warranty Number & Expiration Dates) This information is imported from my accounting program and is basically set. The other table is for the claims, this table holds the same information as the first but also additional information about the claim. What I want to happen is for the user to be able to look up a …

I am writing a small database that will track jobs - i have created tables that store clients, people, jobs and quotes - so far i can create a job with a serial number - assign it to a client with a drop down and this pulls in the people assigned to that client so i can chose who requests the job in the quotes i need to select the job number from a drop down and have that pull all the information into the quote from the job table that has been set previously this i cannot seem to do any advice would be grateful thanks

Ok, I've seen this twice today and Matt Petrowsky refers to it in one of his tutorials. What is it? When do you use it? Also, when/how/why to use ≠ in a join? Any help or documentation on this subject would be appreciated. Thanks, Al

I know this is a dumb question. I have 2 databases (Expenses & Deposits) I want to get my account balance by subtracting the total expense amounts from the total deposits in the Deposits file. If I create a relationship it seems I have to match a field, however, I don't want to relate only certainb records but all of them to get the total amount of deposits. How can I get that figure (total deposits) from the other database? I know, simple question, but I'd really appreciate the help.

Hi All, I've made a date calc: If ( AC.Report Date ≥ AC.gDate Range Start and AC.Report Date ≤ AC.gDate Range End ; 1 ; 0 ) Which does return a "1" when a date falls in the range, but when I use this as the right hand side (left is a fixed "1") of a relation nothing shows up in the portal. Any ideas? Thanks Ian

In the attached file.. the layout that the file opens to a vacancy layout.. per floor (of a building), it is meant to calculate the vacancy on that floor over time.. I have a case calculation that is meant to return the text "few" if there are 1 or 2 vacancies, and "many" if there are 3 or more vacancies.. then, that text relates to a colors table, which pulls over some container fields to color code the layout with a yellow highlight for "few" vacancies and a green highlight for "many" vacancies. The hightlights aren't working correctly.. they seem to come up correctly when I launch the file.. but if I use the buttons to the left and right of the l…

I'm getting the following error message while trying to delete a record: I looked at my relationships and everything seems fine. What could be triggering this?

hi there may be a simple problem for the experts here. I am working on a product database like this: products-table - product_id (P001...P999), productname_name) options-table - option_id (OPT001...OPT999), option_name, for_product (maybe P001,P005;P006, P999) the problem i can´t solve is that many options are avaible for many of the products. i want to see the "option_Name" in the Products layout through a portal. but i have no idea, how the relationship would go in this case. any ideas out there? many thanks hara_m prodOpt.fp7.zip

Hi all, This question was probalbly asked many time but I couldn't find it trought all the post here so if anyone can point me toward the solution it would be great! Filemaker pro 7, I have a student table and a course table and a registration table, when I register a student to a course it create a registration entry with info from the student and the course. So far so good. What I want to do is when I change the e-mail of the student in the student table, then it would automatically update in the registration table. (I started to creat a script for that but then I realize that there should be something with relation and tables that would do the trick) …

Please help if you know what relationship I need to build to do the following: My Contracts table has a month field and a dollar amount field. I need the data to display in columns with all the Jan amounts in column 1, all Feb in column 2 and so on. In other words, the 12 months of the year across the top and dollar amount for each contract in that month listed below, with a total at the bottom of each column. Jan | Feb 400 | 600 250 | 350 ___ | ____ 650 | 950 I may be having a daft moment here but I can't figure out how to do this. Thanks for any ideas.

Preface: this issue could affect relationship design, so I put it here, but I *think* the core issue is normalization. Tool_table is an inventory of gardening tools. Most of the the records have only one tool in them, so rake A has a record separate from rake B. List_table's purpose is to generate a list of tools that a single user will use for a given day (there are no other users, so there is no issue of double-booking a tool). The user will have some mechanism for choosing the appropriate tools, and be able to save the output for future work (improving the efficiency of the lists all the time). When building the day list of tools, I would also like to be able…

I am a newbie so bare with me. I am designing an invoicing database for my landscape company. I was wondering if the relationship of multi products (Plants, equipment, labor, subcontracting, and hardscapes) would have a relationship like I have in the attachment? My next question is if wanted to group together a plant item and group it to a labor item from each tble to make one line item and one price how would I do this? Some landscape databases call this an assembly or kit. All the items that make up the assembly are added to the estimate and the quantities of each item are calculated based on the quantity entered for the assembly. I would want to be able to create…

Currently I'm working on a test Invoice system. Right now I just have two tables. One is Test_Main, and the other is Test_Invoice. Just for testing, I only have 3 fields in each; CustomerName, CustomerPhone, and CustomerComments. I'm relating the tables with CustomerPhone (because their phone number is their account). When I go to Test_Invoice and type in the phone number (with the dashes and such) I have the other fields doing a Look-up to get the name of the customer. My problem comes in when I want the phone number to auto format. For example, user types 1234567890 hits enter and it formats the field and shows 123-465-7890. Doing this I just coded the foll…

I work with patient samples and often mix two samples together when doing analysis. The data from the instrument used gives me an output file with one record for each run that contains two sample names (ID1 and ID2). After the first run, I then rerun variable samples by themselves and end up with one record with one sample name. In filemaker, I created a database that contains a table (data1, data2 etc)for each run conducted and a table (ID) with sample IDs to pull all the data together in one layout through portals. I began by importing the data twice into filemaker, once for sample ID 1 and then again for sample ID 2. Both IDs were imported into one sample ID…

Hello, I have 2 databases DB1 and DB2 records are related by Field1. That is the relationship that is established in the relationships table. However, field4 is also related. What I need is for when a new record is created in DB2 for field4 to be populated with the workorder# from DB1 field4 matchiing field1 on both databases. I.e. DB1 Field1 value is 559 with field4 wo#31959. Now user creates a new record in DB2. He enters 559 in field1 of DB2. Now I need field4 to auto populate wo#31959 w/o user input. I created a field in DB2 with autoenter "looked up value" from relationship field :wonumber (aka field4). So, a wo number is being put …

hello everyone, anyone knows the solution for this problem: table product witrh product_id, product_name and product_prize table bill, with Billing_ID, Billing_date, product_ID in the layout of bill (it's planned for making bills) i wanna choose a product, for that i use a list. The problem is only the product_id can be chosen. But i only want the product name to be choosen. The costumer don't have to know the product_id but only the produduct_name. how can i solve this? thx in advance bill.zip

I was just bitten by my own poor logic. I wanted to see the first related Invoice for a Contact (based upon Invoices Unpaid) which would be natural creation order, so I just placed the Invoices Unpaid::Invoice directly on the layout (since I only wanted to see one). Unfortunately, it was a sorted relationship so the incorrect invoice displayed. Yes. I should have opened my graph and checked it but, as with field definitions and indicating data-type (and whether calc) within name so fields can be easily identified on the fly, I think it would be helpful to pull that concept into the graph as well. I am considering including the relationship sort within the table …

hello, i have a question about how i can fill in more fields automaticly by only fillin in one field and that I can still edit the other (automaticly filled in) fields Example: 4 fields post adres, bill adres, visit adres, and storage adres. Many times these adresses are the same so i want the user to fill in the post adres and let filemaker fill in the other adresses. I can make this work 2 ways..with a inner relationship, or i make separate tables for the different adressen. My problem is when i want to edit on of the other adresses, the one filled in at first is edited as well and i don't want that. pzl help thx in advance

I am designing a soccer club db. I am stuck on a little problem with relationships. I have two tables: Coaches and Teams, the coaches can perform several roles for different teams, and one team can have several coaches/ managers, etc. So it is a M to M relationship. I have a joint table called Roles, so new roles are created in a portal in the teams table and also displayed in the coaches table showing all the roles for each coach. So far so good. The problem arrives when I create another joint table: Preferences that creates all the preference roles that each coach wants to have for the upcoming season. To complete the relationship, I have to create another table oc…

i have the beginnings of a scheduling database (i just scrapped the other one and started over). i am only focusing on one shift right now, and when i have worked out all the kinks in that one, i will do the same thing for the rest. right now, i have a table of employees, a table of shifts, and table for the week dates, and a table of assigned shifts. When you click next to an employee's name on the am shift for monday, either a window letting you select the shift will pop up (if they are available), or some dialog will pop up letting you know that the employee is not available for that shift. HERE'S THE PROBLEM: I want to be able to make a forward slash (/)…

I put a sort of tester up on the employee layout of this file, to show what files are related from "assignedmonam" to employees. the relationship id based on name, date, shift, and dept. the dept is the thing giving me trouble. the relationship says that assignmonam::dept = employees::gDate. gdate it a calculation that just steals the gdate value from week::gDate, which is manually set. i put the two fields right next to the portal as demonstration. any thoughts?!?!?! schedule.fp7.zip

Ok within the seperated model I have, 3 files GUI- The pretty stuff(layouts etc)1 Record Client Data - 1000 records Client Calcs - 1Record Is there a way that when in a layout showing records from clients you can show a calc that applies to that client based on a relationship without doing a setfield script? Ie calc record = current record in data

I have two tables, Teams and Players. The teams table has a list of names that repeat for each division (age group). I had two approaches for the key field: The first one would be to have an auto-enter serial number on the teams table (team_ID); on the players table the key would be selected displaying a long value list that shows a concatenated field of division and team name, but enters the team ID (serial number). The only thing that I don't like on this approach is the long VL. The second approach would be to use the two fields as keys (division and team name), and enter them in two value lists at the players table. With this approach although simpler, I am…

I have a table (of products) which has a number of entries, any one of which could be related to multiple other entries in the same table. I'm wondering how (if?) I can cross-relate multiple entries to each other. Specifically, for record A, I want to display a portal showing record B (along with others). Likewise, record B would display record A (along with others) in its portal. My real question is, can I do this via a single entry in a related table? Can I establish a two-way relationship (since A is related to B, B is therefore related to A) without creating two records in a related table (one for the A > B relationship, one for the B > A relationship)…

Hi I am having trouble with couple of table relationships I have attached FHP file and screen shot.... I am have trouble with linking "project sheet" table to "job instruction" table I can link through script (i.e navigation linking) from "projects sheet" and place in job instruction through pull down menus... What I waiting to do is set up some script up for a nav buttons so I can link from "project sheet" table to "job instruction" in addition: (screen a little different to FMP file attached I also tired adding a job instruction ID in all the tables I wont to related as you can see in the screen dump but this also not working? …

It's funny. I spend so much time tweaking these databases that I've created that when it comes back to creating a new database for someone, I completely forget the basics. My client wanted to add a calendar db to the parent. No prob. But the 3 lousy fields that need to carry over to a given day on the calendar seem to refuse to do so. I created a new ID field on the parent and a mathing ID field for the calendar. Did all the steps for defining the relationship, but the fields on the calendar stay blank. I figured I would post this for good luck since I always seem to solve the problem within minutes of asking the question. But if someone would please post …

I've been using filemaker to make simple forms (ie. purchase orders, work orders)for about 5 years but now I need to add a sort of lookup feature and can't get it to work. One section of my work order form has a place for quanity, part#, description, price, and extension and right now we enter everything ourselves. What I want is when someone enters a part # it fills in the correct description and price. I created a new file and put the part # info in, and in the master file when you type in the part # if it's not in the other file it does leave it blank, but if it finds the part # in the other file it puts the first description and price in rather than the one associa…

I have a Stockroom solution (FM8) that I am trying to figure out how to filter a portal list. User Account Table School Table Stock Requisition Table Of course, there are other tables, but they don't play into this at all. From a layout using the Account Table, I have a portal showing the Stock Requisitions. I have setup two global fields in Accounts. One for School ID and one for Account #. My relationship is Accounts::SchoolID = Stock Requisition::SchoolID AND Accounts::Account# = Stock Requisition::Account# BUT, this requires that I have the contents of BOTH School and Account # completed for the list to show. Would I would li…

Hi all, Hope someone might be able to make a suggestion. I have 2 databases. One is for contacts the other for jobs. Each contact has a unique ID and each job has a serial number. I would like to be able to paste the contact ID into a box on a newly created job on the Jobs database so that the companies name, address, telephone and fax info from the Contacts database moves across automatically to the respective company name, address, telephone and fax empty fields on the jobs database. I can't figure out the relationship between the 2 database and the identical fields in both? Although each job has a unique serial number, a contact can have different jobs o…

I'm having trouble with one to many and many to many relationships... I've tried creating a 3rd table keying off elements of two other tables. But I'm not getting any results (populated fields) In a nutshell I've got a database that idenitifies Composites which are made up of Roles. Composites can have many Roles under them. The same Role can appear in multiple Composites. In addition there can be a number of transaction codes attached to a role. Under "test" layout I was attempting to populate results. the issue I'm having is that multiple Transaction codes can belong to a Role. thanks to Ender I have relationship to define Composites and Roles but it stops at…

I have two layouts one which I want to use to assign projects to operators. I have set the operator layout so it goes to a new record when it is called. When I select the operator I want it to open a seperate layout that will show all the projects assigned to that operator. Obviously I don't want to duplicate assignments , it would be even more useful if I could figure out how to check a selected assignment against what was already assigned. Essentailly what I need is some form of control for when a field changes but I have not been able to find that so far. I am of course a total newbie I am trying to switch from Access to FM. Used to wrting in VBA but scripts are someth…

I am able to link my master table to the Background table and the CESD table by carrying over all 3 fields: patient_id, assessid, and visit date. However, if a record exist in the master table and it does not exists in Background or CESD then it should prompt the user "Would you like to create a new record?" If user clicks yes, then the Background or CESD table will populate with all 3 fields from the Master Table. Can you please help. ;) prototype.zip

Hi Everyone, I have a pretty simple database - a table for Project Info, one for Expenses, a Media Contacts Master List, and then a Media Contacts Per Project table. Schema below: Full Size Image: http://www.ontargetmediagroup.com/img/DB_Schema.jpg I would like to be able to create a new record in the Media Contacts Per Project table, select the project from a drop down list, then select a website name from a drop down list and have the relevant info automatically fill in on the layout. Because the SiteID is the key that links the tables, I can easily get this relationship to work by using a SiteID drop down list. I pick the ID, and the websit…

hello i have a question that's hopefully for some of you very simple: i have 2 tables company with comp_id and comp_name contact with contact_id, contact name and comp_id when i make a new contact i need to either chose an excisting company where he works from a list, (maybe the new contact is the 2nd or 3rd contact of a company already known) or otherwise make a new company. Ofcourse the comp_id in the table contact is related to comp_id in the table company. does anyone have an example or know how to do this? thx a lot

If the relation between table A and table B is an equjoin on the primary key, then all related records in B show in a portal on a layout from A, on both the client and IWP. As expected. If I add a status field to the relation, intending to show only "Open" related records in the portal, it works on the client but shows nothing via IWP. In table A, I'm using a global calculation set to "Open." Am I doing something wrong or is IWP doing something wrong?

I am having difficulty creating relationships. have defined the relationship to the master file on the unique record number. However I am unable to see all of my records. I do a find but my records do not appear. I think I need to create a portal, but I am lost. Can anyone point me in the right direction? Thanks, Phil

hi all, i have a few items, each of those has a on_hand_qty and a reorder_qty (qty=quantity) field. now without using some sort of global or other calculation, i want to have a portal which lists all items where the on_hand_qty is below the reorder_qty . to archieve that i made a self join relationship which looks like reorder_qty > on_hand_qty ... at first it seemed working but then i realized it doesnt...but actually produces a strange effect. i guess it is a good example of how relationships work though, and can be viewed in the easy example file i attached. if anyone can give me a hand here that would be awesome. if possible without resorting to globals and c…

Hi On the CarViewer Layout in the attached file I have three pull down menus (Thanks to Comment for showing me this). I seem to have the Year and Manufacturer Menus working correctly. But I am having trouble in getting the Model Menu to act correctly. Any help appreciated. CarGuideTest.fp7.zip

I have a litle database about cars. Each car as a color. What I want to have is a portal that list how mush cars I have for each color. Is there a way to do that without scripts ? Thanks ! David St-Onge Maerix inc. www.maerix.com

I'm trying to come up with a way to create a generic field that isn't necessarily tied to any of my other table's records. The purpose is so that a user could enter text into that field and the text would then be inserted (via script) globally for all the records in the found set. For example: I've got 20 different records, each need one specific date. Rather than going to each record and adding the date, I'd like to be able to enter the date into this "Global" field and when I hit a button, a script would run that would insert that "Global" field's text into the 20 records. Writing the script isn't the problem. I can't seem to come up with a way to display th…

I am building a database for a local EAP (employee assistance program) group that has 23 tables (client, counselor, employer, initial assessment, clinical notes, etc.). I would like to have a main nav page that the counselor can type in the client ID and have a portal display any form that uses that particular client ID in a list with the date and the form title. Although I have been using FileMaker since 1988, this is far and away the largest database I have built. I am probably way in over my head, but that's how you learn (or develop a drinking problem). How do I set this up (if it is possible)? Should I create multiple table occurances and make sure that all have a re…

Hi Please help. Since I've been using Filemaker (not that long now) I've created a number of datbases, but this is the first real problem I've encountered. I'm creating a Personnel database which contains a list of items they have in their hardcopy records. I currently have three tables: EMPLOYEES, EMPLOYEE FILE CONTENTS and FILE CONTENTS. Employees is obvious. EMPLOYEE FILE CONTENTS is an intermediary table between EMPLOYEES and FILE CONTENTS. FILE CONTENTS contains a list of items (i.e. Application Form, Personal Details Form, Emergency Contacts etc). What I need to do is have a portal in my EMPLOYEES layout which lists every item in FILE CONTENTS an…

How do I best define table relationships to track family relationships? Problem: I am stuck because I don't know how to relate to individual's IDs to each other. Background: I am compiling a dataset of high officers of the British crown between 1603 and 1763. It turns out that many of these individuals are related to each other. Hence I determined that it will be useful to track personal relationships. I have assigned each individual an ID. Now I would like to link them to each. So far, I have created a variable that expresses the nature of the family relation (1 father, 2 mother, three sister, four brother). I have also created a variable to express n…

I am having a major issue with records showing up in portals where they shouldn't be. I have a relationships between two tables where this is occuring. Although there are 4 tables involved. There is a BOM Baan Connection table and a corresponding BOM Table, then there is an Item Master table, and an Item Master Baan Connection table. The exact same data exists in the Baan Connection and non baan connection tables. So the Item Master table and Item Master Baan connection table have the same data, I did this to allow for a revision control system (although now that I look back on it I hsould have used one table and just used a single field for control), anywa…

Hi Everybody, Setting up a database for my marketing company. We need to keep track of projects, websites we've marketed to, last contact status, etc. I am trying to decide what is the best way to go about this. I have a MASTER database with info about all the websites we work with. Full contact info, etc. I only want to change THIS COPY if a website record needs updating. I want to be able to have an individual database for each project, that contains the relevant subset of websites from the MASTER list, as well as specific entries about each website (notes that relate to the website's involvement in the individual PROJECT). So - is this possible…

Greetings all, I have been using FileMaker for years, and created basic databases over that time. So when I decided I was tired of Genealogy software that didn't meet all of my needs, I decided to write something myself in Filemaker. But I have quickly found myself over my head, and I am hoping I can find some helpful solutions out there. My biggest problem seems to be with relationships. I have been fine using simple relationships, but never worked with anything this complex. I'd appreciate any suggestions or examples that might untangle these knots. 1) On an individual's record, I want to be able to display the following information linked to other indivi…

It’s been quite a while since I designed a FM database, so I’m wondering if anyone can point me in the right direction here. I’d like to use Filemaker to hold several large tables. From these tables, I’d like a single popup menu to populate layouts with subsets of each of these tables. For instance: Colors Table: Red Description ID Green Description ID Blue Description ID Yellow Description ID Sounds Table: Sharp Description ID Flat Description ID Muffled Description ID Loud Description ID Layout 1: [Popup Value A] Color Portal: Red (autopopulated from popup) Green (autopopulated from popup) Sounds Portal: …

I would like to figure out how to synchronize various fields. The data needs to display as a text field but it also needs to be stored as a number so that it can be statistically manipulated. There might be a more elegant solution. I am imagining a pair of fields. The first is a text field, the second stores the correponding number. Thanks a lot for your help.

I know I shouldn't have to ask this, but I have a question about portals and related records. I have a layout showing records from a table we'll call 'A'. And on that layout is a portal showing records from a table we'll call 'B'. Now, on the portal row along with information from B, I want to include information from a table we'll call 'C', that is not necessarily related to A, but is definitely related to B. I thought that was possible, but it's not working out. And if it doesn't work, then I am in big trouble, because I've finally worked out all the complexities in this database and everything else works. Everything!!! I will be so sad if I can't get this last sma…

I need to be able to click into a portal row and with a script collect the data in one of the fields to populate a global field on the layout. Any help is very appreciated!

...someone clued me in to what I was doing wrong with my find scripting, and I was chugging right along. Then, in the database in the attached picture, I tried to create a relationship between the "Invoices" table and the "Labels" table (using the "invoice number" field). I got an error message to the effect that there can't be more than one relationship path between two tables. Since there isn't a direct relationship path between these tables, I assume that FM is referring to the path that goes through several other tables to join these two. So, the question is...if I want to go to the Invoices layout, and find all the related records from the Labels table, wha…

Hi, all - Is it possible to get to record "B" via a repeating field in record "A"? I'm currently doing this with a global variable as an intermediate step, but it feels like kind of a hack. Is there a smoother way to do this? Thanks...

Hello, I'm quite new to FM, but I have to make an inventory for my faculty. There is a complete inventory for the school that I can use. What I want is to make a database per classroom. In there I want two fields. One with the hardware ID number and one with the descritpion. I want to be able to enter the Hardware ID and have FM find the description in the school inventory. Where do I start?.......

I have a question about relating calc fields. I noticed that if I try to establish a relationship using an unstored calc, it won't work. (if I try GTRR it tells me the relationship is invalid) but if I establish the relationship using a lookup or auto enter calc and then change the field to an unstored, it works. Why is this? and how unstable is it once it is unstored? I appreciate any input Mike

Hi, Is there a way to reference the next / previous record in a found set from a given record? For example, I have a database of books. On the form, I have forward and back arrows to take you to the next / previous book record. I'd like to have writing below the arrows that says something like "Backward to 'Grapes of Wrath'" and "Forward to 'Of Mice and Men'" Any NextRecord(FieldName) type function? Thanks! Andrew

hello everyone, I am having trouble with making a simple overview to see who works at what company and making new companies or Employers if i wanna show the people working in company x, filemaker always wanna change my records. Instead of just showing, filemaker changes it or making a new record. But it can't be done since i want the companies to be uniek in name. Does anyone have a simple example for this. Just 2 tables company employers relationship is in one company can work more employers. i wanna look in my companies layout what people work there and if i make a new employer i wanna chose from existing companies where he works,…

hello, i'm new at filemaker, and i really need some help here. i'm creating a project with two layouts, a and b. in layout a, if i click a button, the script will show layout b in a new window. the problem is: i want to click a field in layout b, and then the value in that field will be transferred to a field in layout a (layout b is like a lookup form). how can i do this? thank you

Hi, I've run into what should be a common occurence, and I'm wondering how others have handled this situation... If you have set up 5 tables... Departments Accounts Invoices Line Items Products And you want to create Line Items in a portal on an Invoice layout. Looking up prices into the Line Items from Products is fine, but what if you also want to look up a markup value from the Department table via Invoices and Accounts? If the Invoice record has not been committed prior to adding line items in the portal, the lookup doesn't work. Would you force people to commit the record prior to creating line items by making users script rel…

I'm stuck with accessing fields from 'other' related records. I have 1 'grandparent' record (Main) with 4 related 'parent' records (Item) that also have 'child' records (Options). I wish to choose which Options are displayed. On the (Main) layout the 1st options are displayed, and on the (Item Print) layout, the 4th Options appear in the Trailing Grand Summary. Thanks in advance . . . related.fp7.zip

Hello, Lets say i have two Files (tables if you wish). One is Contacts, one is Purchases. Now each Contact has a field describing its Status as either Active or Inactive, and another field describing its type as either being a vendor or a customer. now lets say in the purchasing table i want to add a new record and choose a vendor via a value list. I only want active vendors to appear though. this is how i did it: make a global field in the purchasing table called status , and fill it with the value "Active", make another global called type, and fill it with the Value "Vendor". Now i made a relationship from the purchasing table to the contacts table relating…

I'm building a corporate donation tracking database for a small student group on my campus. They have various committees who often all end up talking with the same corporation, so I'm hoping to help them better track every time they talk to the business. The main panel includes a space that lists all previous contact made with the corporation. Below that is listed every previous donation from the corporation. Clicking on a donation is supposed to give detail on the donation made and the contact history having to do with _only_ that donation, instead I get all the contact information for that corporation in the portal. Here is a link to the file I'm using http:/…

OK, seems to be done to death, but I can't get my head round it in relation to my project. I've attached a relevant section of the database to look at. I want to be able to select from a list of contacts on my job sheet that are only relevant to the company that I have previously selected. In the Customers file, I want to add additional contact details as they become required, ie. have more that one contact to choose from. I have worked out how to autofill the address and other related fields but am struggling to achieve this next stage. Any help would be much appreciated Thanks!! 1139516721-data_bases.zip

Hi I have staff member, students, and external members, any can be on a project. students have staff members as supervisors and the reverse. I have tried to keep the 3 tables (staff students external) seperate but this makes it difficult to use in projects then as you need 3 different field when you only want one that can look up 3 different tables. I then tried to create a single tabel called people and linked this to all three other tables but seemed to have problems then when looking a what staff supervised what students. Can anybody see and way around this? Besides projects I have grants, publications, travel etc. hoping for help Glenda

Hello all, my goal is to have a Product / Part Database, which only needs one unique Number, the Part Number, while still allowing for different Vendors which might offer better price conditions, leadtimes etc. Before we had it this way: A Part with two Vendors would have had two Records in the item database, of which both had the same Part Number, but a unique Item ID, which was an autogenerated unmodifiable serial. The Partnumber was self joined, so i had a Portal on my Layout showing alternative Vendors that way. There are a lot of Quirks with this, one of it is here: We use a BOM to build a Product out of multipe Parts. Now say i need 10 pcs of a SCREW (part n…

I'm trying to introduce a system of highlighting in to a portal... basically when a user chooses one of the records, they hit a hidden button that sets a field in the current table to a particular id from the related table... thereby basically "selecting a record"... the highlighting field, in the related table is just If(Count(related::field) = 1 ; Highlight ; "") ... now it all works fine and so it should... unfortunatley this is a multi-user solution and the the field in the current table has to be global... when the field is global though the calculation seems to always evaluate to true, therefore always highlighting... ive played with this before and never fou…

I'm sure there is a simple solution to this problem, but I have not been able to figure it out. I am using FileMaker Pro 8 and I have a database with two tables to keep track of actors and the skits they are performing: ActorsTable ActorID# PhoneNumber Email SkitsTable SkitID# Venue Date Showtime What I want to do is have a drop down menu on the SkitsTable that will allow me to easily add and change the various Actors for each skit. I also want the ActorsTable to show all the skits the Actor is performing in. Thanks in advance for any help.

I am having a problem with a database (copy attached with user and login "admin"). There are several portals on the layout TheSpace_JobSheet which have relationships to other tables. The problem is that when you select from the popup list for Photographer, say, and then decide to change the value, the changes do not stick. This is true of the other portaled fields as well, such as Producer, Retoucher, etc. Anyone have an insight on the strange behavior? Thanks in advance. TheSpaceInc.fp7.zip

I have switched database to a separated model. I just cant get one relationship to work??? File 1 Field (1record) vConstant auto entered as number 1 File2 Calc on a fieled resulting in 1 or 0 Added file reference Set up relation between the above two fields Layout set up under File1 Portal set up to show related fields in file2 But it doesn't show anything, have checked calc in File2 and it shows the ones and zero's for each record correctly. Ideas?

Hi Each item in my database has 2 play names with 1-10 points each. However, I can't get the second name to have its own point system. I am not sure how to set the relationships so each play name works separately. here's three separate database files and another file that does sort of the same thing in one file. I don't really get relationships very well. Anyways, if someone could point me in the right direction for the second play name, that would be great. You can log in as guest. TEST_dbases.zip

I am working on a database for invoicing landscape plants. What I would like to do is to print a picture ,description, and care instructions of every plant that is on the invoice. The question is, how would I set up the relationship between invoice, plant, picture, description, and care instructions? Also, there are maybe four sizes of Colorado Spruce that have four different item numbers but I only need one picture, one description (height, width, shape, fall color, etc.) and one set of care instructions to go with those four different item numbers. There may be only 20 different sets of care instructions for the 3,000 different plants. The plant table is large, with …

I am compiling a historical database of political leaders. Some of the individuals are related to other individuals in the database, most are not. If one person in the database is another's child, for example, how can I most easily check if the parent is also in the database? There will be a number of fields that are well suited to compare individuals: names, birthdates, death dates, aristocratic titles. The challenge that I am most concerned about is that I would like the database to alert me to probable relationships. I would also like to link fields if individuals are related to each other.

Im using FMP 7. I have developed an invoice database and a client contact database. I have Client ID's for both. I want to be able to type in a client ID in the invoice and have it auto enter in the address of that client from the contact database and Im not exactly sure how to go about this. Im not sure if its a relational problem or a lookup problem. Any help would be appreciated! thanks in advance

The application I've designed is a contract system for a travel company. There are 6 tables in the database intrer-related by various IDs. The problem is that all contracts get updated whenever I update anything in any of the tables. For example, if the tax rate on a property was 5% last year, and then changes to 6% this year, when I make that update all new contract work great, but the old contracts reflect this change and don 't match what the customers were sent. So... is there anyway in which to stop old records from calculating. The way I have it now, the calcs can't be stored because they are based on related fields. I know I can use lookup fields bu…

I have used the following terrific and easy way to find dupes in a field- (like email addresses) for years in Version 4.0: 1. Define a self-join relationship with 'PhoneNumber' as the key field for both the main file and the related file. A self-join relationship is one in which the main file and related file are the same file; thus, you would simply choose the same file when you are prompted to specify the related file. Name your relationship SelfJoin. 2. Define two fields: Counter (Text, Auto-enter serial number) CheckDuplicates (Calculation, Text result)= If(Counter = SelfJoin::Counter, 'Unique', 'Duplicate') 3. Find All records, click into …

Hi all I am using a filemaker pro 6. I have 3 files: SALES – each record represents a single sale to a customer. Primary key = invoice id. Each record can contain MULTIPLE batches of serial codes. (each batch itself contains MULTIPLE serial codes) SERIAL CODES – each record contains a unique serial code (primary key). Each record also contains a batch number, allowing multiple serial codes to be assigned to the same batch number. LINKING DB – This file only contains 2 fields – invoice id and batch id. This allows me to associate as many batches as I want to each invoice id. (to summarise: each sale can contain multiple batches. Each batch …

How do I create a reference key for a many to many relationship? I have two tables I'm trying to reconcile with shared fields. I'm trying to import 10,000+ records, so want to avoid portals so I don't have to do manual matching. Any ideas? Picture_6.pdf

Hi everyone, We work very hard to keep duplicate customers from getting into our system. But even our best efforts occasionally fail and we end up with two customer records for the same customer. Before FM it happened a lot. I'm attempting to clean them up and would like to establish a process to MERGE two customer records. Sounds simple. But by the time we identify these duplicates, it isn't just the customer record any longer - this CustomerID has now created records in Addresses, Numbers, Invoices, Commissions, Billing, Notes, Activities, and History. These related tables usually contain many related records in each table. I envisioned a script to go to each…

My terminology isn't all there but... when setting up relationships, I can have a one to many relationship. When looking at the details, it is always "x = b" AND "x = c" AND "x = d" AND "x = e"... Is there any way to change the "And" to an "OR"? Does anyone know what I'm talking about?

I have an accounting database set up ... I need totals per designer per quarter. Somehow I'm messing this up, I could use some help. Here's the relevant info: Relations: RoyaltiesbyFont (foreignkey = Quarterlies::key) Quarterlies RoyaltiesTotal (quarterlieskey = Quarterlies::key, designerskey = DesignersRT::key) DesignersRT FontsRT (designerskey = DesignersRT::key) PurchasesRT (fontkey = FontsRT::key) In Royalties total is the field: calcTotalPrice (unstored, from RoyaltiesTotal) "$" & If ( Sum ( PurchasesRT::calcPrice ) = 0 ; "0" ; Sum ( PurchasesRT::calcPrice ) & If ( Middle ( GetAsText ( Sum ( PurchasesRT::calcPrice ) ) ; ( …

I'm relativley new. problem I have is I'm in my main file and the user clicks "Find Diagnosis Code" this opens my Diagnosis Code Database, they then search the part of the body and injury and can find the appropriate code in the 2nd database (Example: Shoulder injury, Code = SH1) My description (Should Injury) is in one field and the Code (SH1) in another. At the moment we are just remebering the code, going back to the Main database and typing it in. Is there a way I could just click the code in my Diagnosis Code database and have it appear in the correct field in my main database. Only problem is we have a few main files and they all use the one Diagnosis Code…

righto small...ish question... what are some restrictions on the way to's can be related?... i.e. no unstored calc to unstored calc? ... the reason i ask is because when i try to execute a script it shows me a custom dialogue telling me that my relationships are invalid... in those relationships i have two related one text field related to another, two numbers related to one number... and one unstored calc related to another unstored calc... i assume that its the last one thats causing the problem so ill just store the results as there is no real reason for them to be unstored... but are there any other restrictions similar to this on relationships? Any advice would …

I have a schedule displayed onscreen, and I allow the user to "move" contacts between timeslots in that schedule. Before moving it, I use a script to see if the contact already exists in the destination timeslot. To do this verify (I'll explain this as simply as I can), I have the calculation field [ContactID & "~" & Timeslot] both in my Master Schedule file and the ScheduleLines file. I simply relate from the Master to the Lines, and if related records come up, I know that the contact is already in the destination time (at which point, a message comes up and the move is cancelled). Unfortunately, when I choose a contact and the Calculation Field updates…

Maybe it's the fact that I started with Access, but I seem to be missing the point. Does anyone have a suggestion on how to get a query that functions as part of script? Thanks,

I'm sure this is easy but I'm going crazy.. I've got a table (Properties) and each record can connect to a "tenant" table- Each property can have 1 or 2 tenants. Tenant 1 can be selected from a value list which looks at a Tenant1 table occurrence, likewise for a Tenant2 table occurrence. I know this is a many to many relationship, but for the life of me I can't get a join table to work at all. What I would like to be able to do is show from a portal within the Tenant table which properties the tenant is associated with, whether it is a tenant1 occurence or tenant2 occurence. I've attached a pic of the relationship. The shown join table does nothing, even with …

I am trying to concatenate two fields from separate tables and have them show up in a third field. but it's not working. I have an example #1 of how I want it to look and #2 is an example of how it is not working. What is wrong? Can anyone tell me why #2 is not working as well as #1 I am stumped and it's driving me crazy! Are my relationships fudged or is it something else? Thanks!!! urlconcatenation.zip

I'm new to fileMaker, but have been reading a lot on one to many and many to many relationships... but still stuck. I've tried creating a 3rd table keying off elements of two other tables. But I'm not getting any results (populated fields) In a nutshell I've got a database that idenitifies Composites which are made up of Roles. Composites can have many Roles under them. The same Role can appear in multiple Composites. Then I have the Role to Transaction Code relationship in which Transaction codes can similarily vary in Roles they are housed in. Under "test" layout I was attempting to populate results. Any direction would be appreciated. v5.fp7.zip

I have a database with only two tables and one layout. One table is invoice the other is invoice lines. From the layout invoice (Table:Invoice) I would like to enter data into three fields into a related table (Invoice Lines) The invoice lines would then be displayed in a portal. I have been trying to work this out for days if anyone could help with some advise I'd really appreciate it.

Surprised I didn't notice this before, but right now whenever a record is made in the contacts table, another one is made in the employees table. I want it so that each employee is in the employee table 1 time. Any tips for this relationship would be appreciated. Thanks....

What I am trying to do is build an estimate sheet that will autofill the unit cost field if the description has a match. I apologise in advance for being so green. The DB that I uploaded is extremely simplified because I really screwed up my good one. I am not sure what method to use I haven't been able to get it to work with a calculation. I am usually prety good at figureing things out but I don't really know what direction to follow. I would be very interested in an example that is similar to mine but I can't seem to find one. I think that I need to use a lookup calc and a value list but I don't know how to set up the calc. FM Forums does not endorse o…

The way I understand it, you can perform a relationship where you can relate to any of the values instead of all of the values by using a repeating field. (Please post any other way to accomplish this) I have 2 tables one categories and one items. I'd like to be able to show a portal with items from more than one category based on someone choosing several categories. I believe a checkbox field would do this and would place the selections in a repeating field? But I really have too many categories to put in a checkbox list. I'd like to base the selection (and repeating field) on a yes/no button for each category in a portal. I'm wondering if there is …

I am sure this is quite easy but for the life of me can't get it to work. A simple database with two tables. First table just contains an ID. Second table contains related records with an ID for matching and a second field, CONTRACTS - which can contains the following entries: UK, EUROPE, AMERICAS, VENUES. Now i can easily view the contracts in a portal for each ID, but what i want to do is have either: 1) A single field on the first table which lists the the contracts for that ID or 2) a field that can give me a TRUE/FALSE response, such as does ID 1 have any contracts for Europe Any help greatly appreciated, and basic file attached test1.fp7.zi…

Hi gang, I was wondering if there were a way to use wildcards in relationships. Specifically, I have a form with a field (SearchName) that is linked in a self-join relationship to another field (Name) in the same table. On that form I have placed a portal that shows the related records. All works OK. When you type in a name into the SearchName field, any exact matches are displayed in the portal. What I would like to do is be able to search for non-exact matches. For example, type "sm*" or something similar into the SearchName field, and have the portal display "Smith, Small, Smoog" etc. Any ideas how this could be done? Cheers!

I'm sure this is pretty simple thing for most people here, but I'm a newbie. Using FMP 7, I have a database of people from all over the country. I need to sort first by state, then within the state, alphabetically by last name . That much I can figure out. What I need some advice on is that the database has the state entered by the 2 letter abbreviation and what I need to do is to create a report that has the full state name as a heading above all the people in that state. I believe that is called a leading subsummary. I think I probably need another table in the database that has the full state names, but I don't know how to build a relationship (or is it a lookup?) be…

I've been working with simple databases for a little while. I've done some work with Access. I like Filemaker for it's simplicity and have started using it, however I've run into some problems. I'm in the middle of building a student database for a small private school. I've seen a few things on the forum about using an Enrollment table between Students and Classes, but what I'm hoping to do is have a Class Setup Layout that automatically adds the students to the class once the Grade Level is selected (based on the Grade Level of the student). Is there a way to do this? Thanks for any help.

Hello everyone, sorry for my noobisch question but I just started to develop with filemaker pro 8, 2 days ago and im getting a headache:( I have a DB model with 2 tables: Company Contacts This is a 1:N relationship, since the are more contacts within one company. Table Custumer contains Custumer_id (primairy key) Name Company name (sec key) Table customer Company Name (pri key) I have linked the 2 tables in the define DB options. How do i record a new entry of company name, in the company AND the customer table, by only fillin out one field? thx everyone for reading and even more if you're repying JustDippin' …

I want to work on my database, to add some new features, whils users continue to share it. I will make a copy delete all the records and work on the changes. Once I have done this I will import the data from the live database. However when I have tried doing this I find that the relationships are not formed, unless I go into each ID field and tab out of it. Is there a way to force filemaker to pick these up? Or a better way to work on a database, without effecting users?

I am trying to have a portal on my screen that will allow me to type into it and have the date of the entry auto entered. I have enclosed a copy of the file that I am working with. Any help will be greatly appreciated. Thanks. DataBase.zip

I am trying to get my head around this relationship business: I have 3 tables in this database: Orders PhotoSizeInformation PhotoCost "OldPhotoCostList" field is the way it was handled in the database using a calculation. So to allow the user to edit that data (in case of price changes) I decided to separate the calculation onto a third table "PhotoCost" with a lookup from the "PhotoCost" field in the "Orders" table. Now I read that the best practise in creating relationships was to have a unique number field on the one side of a one to many relationship. I have created those fields but am at a lost on how to use them effectively in this case. …