Relationships

I have a hosted FM8 DB. I am only allowed one FM8 file on the host. I bought an email plugin and I trying to intergrate this with my solution. I was able to import the tables & fields from the email solution, but I can't see any way to import the relationships. Is it possible to import (or copy & paste) relationships? I can't create a reference to the file, as I am limited to only 1 .fm7 file. Thanks.

I'm having problems getting data from one db to another. I have an external db with one record that generates a unique code (tri-alpha) 17,576 possibilites. When the user clicks the confirm button in the main file, it runs an external script to increment a counter once. I want the resulting code to be reflected in the main file - it doesn't work. I have set up a relationship with a key field, that seems ok - the confirm button runs the external script, then setfield code::externalcode. returns no data. What am I doing wrong. If the explanation is a bit hazy I can elaborate.

I have a many to many rellationship and I have devided it so I have a rellationship table it looks like this: Image (with id) Image_Searchword(ImageID and SearchwordID) Searchword (with id) Now I can add the same searchword on an Image more than once, and I dont want that to happen, how do I make it unique on the combination of ImageID and SearchwordID in the table Image_Searchword Thanx for the help! // Jocke

I'm struggling with POV for a Sales report. Request: Ability to find Customer orders which have a LineItem ProductID of P00085. This is a floor display (just the display itself). Be able to specify a from/to on ShipDate (of Order). I need the report (parts) to look like: Leading Part1:[color:blue]SALES REP Leading Part2:[color:green]CUSTOMER NAME, ChainName, LastContact body - ShipDate - InvoiceNo - TextField ( * see below) body - ShipDate - InvoiceNo - TextField ... Leading Part2:[color:green]CUSTOMER NAME, ChainName, LastContact body - ShipDate - InvoiceNo - TextField ( * see below) body - ShipDate - InvoiceNo - TextField ... Leading Part1:[col…

I need some advice on creating a complex contact management solution in FM7. The main objective involves tracking my resume submissions. It serves as a resume, contact, company, and project tracker. My questions revolve around defining the database's relational structure. I have not found and advance tutorial on database relational structures. Let me start with the simple part. How do I create a relational structure for multiple contact points (phone numbers)? Say you have a contact record with multiple phone numbers, but you do not want 50 fields for potential phone numbers like home, mobile, email, personal email, website, intranet web address, etc. You create a t…

I've set up a relational database set (invoices + line items). However, when I delete a master invoice record, the related line items records will NOT delete. Yes, I do have 'delete related records' checked off under the relationship settings window. And 'allow deletion of related records' in the portal itself too. I've done several complex relational systems and this is a very basic function. In fact if I start a new example from scratch it works... but my existing solution appears to not work for some reason. Any help or hints at weird hangups, appreciated? thanks.

Hi, I have mysql Version 3.2 on my server. I want to upgrade it to MYSQL 4.0. Can you please help me how can i upgrade my mysql database users to this new version? I do not know already existing mysql server users passwords so i can not create them in new MYSQL 4.0 database. I have tried by importing mysql database to MYSQL 4.0 but its not accepting users. Regards, Ali Rizwan (Web Developer) Aztek Computers http://www.aztekcomputers.com ..............................

Does anyone know specifically when GTRRs fail and when you need a Select Window script step? I have a FM7 Multi-file solution that seems to fail to go to the proper layout/TO when doing a GTRR from a different file. It seems that it fails when the related file is first used because I regularly end up with unusual records in the table that's associated with the first layout. Guesses are welcome (please let me know if your response is a guess though!) Thanks much.

Sorry for the newbie question! I have 4 tables: 1) companies, 2) clients, 3) locations, 4) employees. Each company has several clients, who each have many locations. Each location has its employees. Someone from a company has to be able to: - Sign up their company, then push a button to go to "clients" page (layout) to sign up a client. - From each client page, they need to push a button to go to the Locations layout to put in several locations for this client. - The employees will be automatically assigned to the locations via a zipcode relationship. What's the basic theory on how to accomplish this? Sorry again, I'm a novice, just coming from FMP 3!

In my PROJECT db I have many records referencing many contacts, but many records keep referencing the same person (for instance in a transmittals "TO:" field). The people are referenced from a CONTACTS DB. I am trying to make a list in the individual PROJECTS db showing each contact's information used in the file, but when I try this it shows a list of the contacts in the order they are in the db records, including duplicate instances. I'm not a master at FM, so forgive my ignorance if the answer is obvious. Thanks!

There is something I don't quite understand about the x operator. Why do I need to define two fields to relate using an operator that specifies don't bother compairing the fields? (I already understand the 'parents' answer to this. Do it this way because this is the way you have to do it!') Does it ever matter what two fields you use in the relationship? For that matter why do you even need a relationship, couldn't you use a non related table. (Again the 'parent' answer applies here.) Any guru's explain this? It seems like a doubious advantage over including a 'constant' field in each table like in previous versions of FMP. TIA Jerry

I have 2 separate databases that have unique records which represent our CM Library. These databases have to remain separate. When someone comes to me to get a particular item, I have to search one database and if it's not in there, I have to do the search in the other database. Is there an easier way? Can I have a field that would query each database? If so, how? This database is about 5 years old, so I don't want to redesign anything. Thanks, dcecil

Hello everyone. I have a small business and currently use FM to run most of it. I also use Quicken to keep track of my checkbook and purchases. There is some double entry of information with this process and I would love to have a FM solution to replace Quicken. I have looked into third party solutions that are built in FM, but they all have more than I need. I really just need a solution to replace what Quicken currently does for me. I have also downloaded ClickCheck and while that keeps track of my checks, I don't see any reconcile capability. So, my question is this. How difficult will it be for me to build my own solution? I am not a trained FM develop…

Hi, I'm testing fmp8 to learn about it's new features (and I must say I'm impressed). Even though I think there's still a lot of work to make working with (and especially viewing) relationships easy or should I say possible (like a text version of the relationships) I was happy to learn you could add notes. Only...how? Can't find a button or menu option to add those notes. Now, as you might have noticed in previous topics I sometimes miss out on things -) Who can tell me where to find this new 'add notes' feature. Thank you for reading and eventually answering this mail. Michèle

I have been building a version of a workflow management tool and have experienced some odd behavior, I was able to recreate it for the technical support people but they have not come back with an answer. The behavior involves the portal relationship manager. When creating a script with a GoToRelatedRecord command, the relationhip manager asks for the relationship and then normally provides a list of layouts that match that relationship. After choosing the layout, the script step reads GoToRelatedRecord [show only related records; From table: "table_occurance_name"; Using layout: "layout_name" ( table_occurance_name )] The unexpected behavior is: 1.) a fi…

I'm trying to setup my database so that I can basically have a global status for all the records in that database. So I'd have a main layout page that sayed ie; "Final Version". This would be changeable by me on the main layout page. Problem now is that the data I put into that field now becomes linked to a specific record, so as scrolling through records the global status disappears. I'm thinking I need a relationship and something linking a table that holds all the records to the table that is escentially my 'global' status. Is this correct? Not sure how to set something like this up. Any help is greating appreciated.

I'm putting together a log-style database. It has a central table for actual log entries (called log), and a bunch of related tables for certain objects. These work like this: every log entry can be related to zero or one of the following: an advertiser an agency a particular issue of a publication So I've got a table for each of those bold-text words and everything's got a unique auto-entered serial number. So: log::advertiser is matched to advertisers::id log::agency is matched to agencies::id log::issue is matched to issues::id issues::publication is matched to publications::id. issues also has an issue no field, which in combi…

I having been using the book, "Special Edition FileMaker 7" by Chris Moyer and others. It is generaly a good book: very deep explanations of relational database concepts and it touches upon just about every detail in FileMaker 7, covers a lot of sophisticated subjects. It is, however, not very good in the editing. Any book this size needs an excellent index (comes in at almost 1100 pages), this book's index is so, so at best. It has a number of typos' and a problem with staying in sequence when explaining an example of the current subject. On that note I built one of the examples in the book, pages 168 - 173. The point of the lesson is to illuminate how to use a…

Apologies for a noddy question, but I just can't find an answer to this anywhere. I am struggling to get a working where I have a one to many relationship, and want to step through all the related records. Imagine my "main" database that has a field called matchfield which is always "1". And I have a related database that has two fields, lets say "data" and "matchfield". "data" has records A, B, C, D, E, F.... "matchfield" is always 1 I want a layout based on the main database, and I want a script that will sequentially give me all the values in the related database. So I define a relationship main database:matchfield = related database:…

Hi! Maybe this is off topic but here's the deal. I have problem with navigation in database. I want to create one table for persons and each person should be treated with his set of records like person's income and expenses. I have to be able to select the person I am working on and database should show me just person's records. I want to have everything on one layout. Database would be similar to Home Budgets template from FM but with many person's. I know how to create database schema and relationships, that is not a problem. Problem for me is how to select a person (or with Login) and then work just with person's records. GoToRelated record works only if…

I am working on a FM7 database. The master db is the one where all records are entered. The child db is almost identical to the first one with few minor exceptions. Child db is used for inventory and deliveries.When an item is deducted ( delivered ) from the child, the master must remain un altered. I am working on this for almost 2 weeks and cannot figure out what am I doing wrong, to start with, records from master do not dispay on child or vv. Heeeelp please !!!

I am getting an error message every time I attempt to delete one particular record. None of the other records seem to be affected. It reads: "This operation can not be performed because one or more required related tables is missing." This is an important issue for me since this is for a commercial runtime application, which has been a very demanding and complex development effort. The record was created in the middle of the build, which initially was in FM7 Dev, and now with FM8 Advanced. (The error occurs in both versions.) I created a Database Design Report in FM8. It's quite long. In scanning it, nothing stands out. But I don't know what to loo…

I'm trying to decide what would be the best approach to design a Customer Relations Manager. There's already a complete "Contacts Manager" in place (FM7), which is (or will be) used as a starting point for the CRM, debet control and some other databases (in a small fashion company). I'm a novice developer, and often struggling to wrap my head around relationships (of the database kind). Some things I can get to work, some not... The CRM is fairly simple: it is used to track conversations between the sales dept. and customers. A short topic line, and longer input field where the gist of the conversation is filled in. Was it by tel, fax, email ? Is it a new topic, op…

This looks so simple but I cannot make it work' I have three tables Members: with 2 fields MemberID, Name Clubs: with 2 fields ClubID, Name Membership: with 4 fields ID, MemberID, ClubID, Position (There are other details but they are irrelevant to this question. All IDs are auto-enter serial numbers) There is a many-to-many between clubs and members and I am using the membership table as a "sort of join". Position refers to a particular members position within the club heirarchy, Chairman, Treasurer,...Committee member and a member can hold more than one position so ClubID & Member ID do not uniquely determine a record in the membership table (this may…

I am trying to develope an office database which involves tabulating data from multiple layouts into text files. I have about 8 tables linked in a one to one fashion by a "patient number". I am trying to pull data through the link to populate multiple fields. I have exhausted filemaker pro advanced and "using filemaker 7" and basically have gotten nowhere. Getting frustrated. My links do not work and cannot pull data through. I have tried GTRR, tried using globals to store data and nothing works. The record numbers have to be unique and discontinuous (have to be entered manually) because of the chart system in the office. Also I have tried auto entering serial number…

I have used FileMaker for years now and have developed a complex relational database for storing data on my plant DNA collection which I use for research purposes. So far, things have worked, but I want to clean-up the database and make it more streamlined. This has caused me to re-address a couple of relationship issues! For example, I have a small table containing the plant name, each plant name has a unique numeric identifier - PlantID (automatically generated by Filemaker). This identifier is used to link to my DNA table, so that I can see layouts that include the plant name. The plant name is not stored in the DNA table only the PlantID is stored. OK…

I have a big problem. Payroll assigns our employees their own 'number' when they start with us. However some may leave then come back so payroll assigns then a new 'number'. This doesn't happen a lot but it does happen. In our database I use at least 10 related tables and they base their relationship off the employee number...so if someone changes to a new employee number I have to manually change the records in all those tables. Is there a way to fix this (etc)...if I have to manually do changes to make the relationship work better I'll do it! Thanks!

And I'm not just talking about FileMaker. I need to update a database that my WIFE uses for her business and if I can't get it done quickly OUR relationship may need some work!! I have posted a topic before and received some help, but I still can't get the silly joins to work correctly in my simple database. The primary issue I'm having is getting the related data from the parent tables to display in the child tables. It works for a couple of joins but the last one doesn't. I would be willing to pay someone an hourly fee to help me on this. It has been an issue for several months and my wife is running out of patience. I would like to be able to email the …

I have two tables, the master table has records with an Id that links to a sub table via the Id. The master record can have 0-4 records in the sub table, I want to list all records from the master table that do not have any records in the sub table.

Attached is a little example of an application I'm working on. Background: A patient visits the doctor and a battery of tests are run that day. The application produces a file called Patient Test Results. The Patient Test Results file tracks the following: Patient ID (unique per patient) Patient Lab Date (date format) Patient Test ID (unique per test) Patient Test Result (numeric) Goal: I need a way to give the user the ability to populate a date field based on a choice of existing Lab Dates (as found in the Patient Test Results file) for a given Patient ID. Extra Bonus: 1) Ideally, it would default to the most recent Lab Date. 2) Even mor…

I have 3 tables altogether: Prospect, Patient, and Message Messages (phone messages usually) can come from either prospects or patients. How should I structure a relationship so that when I create a new message in the message table, the user is presented a dropdown list of all the prospects and patients ? I was actually wondering that perhaps I don't in fact need a prospect & a patient table, and that I could just have a person table, that had a person_type field so that person could either be a prospect or a patient based on a checkbox. Any insight anyone can offer would be awesome. thanks. :

Hi, I have a large database that tracks the jobs of a transport company. Each job has a collection and a delivery date. I need to create a layout (on screen and to print) that shows the record that match a specified date, like to day. The point is that by looking at that layout you can see all jobs where they are either being collected or delivered on that date, or say tomorrow. So far I'm a bit stuck, can anyone help.

Hi all, I have a question. I was given a DB with eight tables. Now, I have to integrate two other DB's that have 10 tables each in them. Getting the relation is not that big of a problem; however, is it optimal to just copy and paste the tables from the other DB's into my current DB??? Or (sorry I am still a novice with DB's) is it possible to keep each of the three DB's individualized and put a primary key link from one DB file to the other DB files??? Cordially, --R

Hello all, This should be fairly simple(Famous last words) to accomplish. I am trying to have a few fields auto populate, but I am having a problem with the table occurances/relationships. I want to be able to put in a product # on an invoice and have the following fields auto populate from the product# Supplier# SupplierName GraphicsSupplier# SAP# ProductDescription When I enter in the product# the ProductDescription and Supplier# are auto populating(from a lookup), but I can't get the other fields to do the same. PLEASE HELP!!I attached jpg's of my relationships and invoice table with highlighted fields.

Not sure what's happening -- but since I migrated I can't get the self-join relationship to work to show the maximum value among records (in the self-join relationship). So, for example, I have a self-join to link records with the same assignment name (say, the relationship is called "assignments"). Now when I try Max(Assignments::grade), I don't get any results (even though the records are fine). What simple thing am I doing wrong!??

Although I am not new to FileMaker I am still in the weeds on some of the finer points of relationships. I have a fairly good book that has gotten me quite a ways in constructing my database but it is advanced and some knowledge is assumed and of coarse this is the hole where my question is lurking. When you create a Primary key field in one table and then need it to be a foreign key in another table (match field?) do you need to create a field in the other table to make the relation between the 2 tables? I know this seems really obvious but the book blew right over this issue. I have noticed that that is what many people have doen here when I have downloaded so…

Hello everyone, Thanks for any help in advance. I am not new to FM, but am still unsure about how to structure my database. This new one is more complex than any of the others I have created. I am trying to "reproduce" a database system we have now that dates to around 1986. It is a price estimating database and I'll try and explain this as clearly as possible. We recieve an inquiery from a customer for prices. These inquieries may have one item or several, but each has one estimate number. I currently have two tables; Estimates and Item Number. These are linked through an Estimate number that is auto entered. On Estimates I have a portal that allows cre…

Hi I am fairly new to relationships but would like some help. I have a contacts file which has up to three different addresses for some of my clients (Home, work and friend), all this data is in one table, I have another Table for invoices. I would like the contactID no. (unique serial) to be automatically inserted (along with the clients name and main address etc) when I click a button for 'New Invoice'. At the moment I have to insert the number in the related field (contact ID) to bring up the contacts details in the relevant fields. I also need to be able to change the address that the invoice goes to, to his work address sometimes - via a button would be …

I'm using a TO to populate a pop-up but it has some extraneous entries in it. The relationship looks like this Items<->Charges<->TXN_Type. In my Items file I have a portal that has two fields, charge type and charge amount. Thus I have a charges table related to Items. The charge type is a number that is related to a TXN-Type table, which has a number field as an identifier and a description field of the charge type, which is displyaed in the portal in the Items file via a value list. The strange thing is that the first two values in the pop-up list are not records in the TXN-Type table. When I first set this realtionship up I defined it incorrectly and ended …

Hi, My database has three tables. You can see the relationship here: The main table is called "Load" and it is related to the table "CustomerCommission" by the field "CommissionCustomerID" and is related to the table "LoadCommission" by the field "LoadID". I want to find all the records in the "CustomerCommission" table that match the current Load's "CustomerCommissionID" and copy some of their fields to new records in "LoadCommission" table (there is a portal to the "LoadCommission" table in the Load layout. Here's the script: Go to Layout [ "Load" (Load) ] Go to Related Record [ From table: "CustomerCommission"; Using layout: "…

Hi, I'm getting an error in a very complex Fm database I'm working on. I am in TableA and there is a portal to TableB. There is a relationship from TableA to TableB via Field1. The relationship allows new records to be created in TableB from TableA. When I'm in a TableA layout trying to enter records into a portal to TableB, I get this error: "This operation cannot be performed because one or more of the relationships between these tables are invalid." Anyone know why I might be getting this error? It's making me crazy! Thanks, Andrew

Hi, I've been doing web development for the last couple years and have strayed from Filemaker. I've now been recruited to work on a complex Filemaker 7 database and am having a little trouble getting my head back into the way that FM handles working between multiple tables. What I need to do is very simple to explain and would be easy in web programming. I'm in a certain table (TableA), and based on a value in tableA I need to search for all the records with the same value in TableB and then copy multiple records from TableB to TableC. Sort of like this $SearchID=20 (a value in tableA) select from TableB all records where fieldA=$SearchID $row=resul…

I need to implement a solution to track multiple people per event. Linked to the event on a many-to-many relationship based on their role in the event. Where this gets tricky is that the people are known only by their names, and each person can have multiple names (formerly known as, etc...). Also for each persons full name there can be more than just First, Middle, and Family names. For example "Susan Ann Smith Johnson". Whereas she has two family names, and it can be assumed that she was formerly known as "Susan Ann Smith", therefore a relation to "Susan Ann Smith" should be linked to the person, now known as "Susan Ann Smith Johnson" based on formerly …

I'm working on a database to keep track of ads for a publication. I want to show the ads that each advertiser has posted in a portal on the advertiser's page. There are tables for advertisers, ads and issues. I've attached a bare bones sample. But what I want is to have the ads that show up on the advertiser's page limited by a selection menu for the date of the issue they placed ads in. It's something I've done before with another database, and I just can't figure out why it won't work here. The advertiser Copy Layout is the one that should work with the selection menu. The plain advertiser layout should show all of the associated ads in the portal. Any h…

Greetings to All, I have 2 separate files, a Staff file and a Student file. In selected situations I need to relate a certain kind of staff person to a particular student. I placed the related field for the staff person's name on the student layout, together with a button to perform a find for this certain type of staff person in the staff file. That part works. I get a found set of 6 individuals. But what goes wrong is that I get the entire staff listed in the "staff person's" field on the student layout. Isn't there some way to just list the found set? I've tried a bunch of things but obviously nothing has worked so far. Thank you for your help. Veronica …

Hi all, Am trying to create records in a portal but have been encountering problems when trying to move off the layout that has the portal. I get the following error message: "pk.pt_id is defined to require a value, but it is not available on this layout. Use another layout to assign a value to this field" pk.pt_id is the primary key by which the two tables are related and the privileges are set to allow record creation etc. I am assuming that this is a relationship problem but could of course be wrong. Another user of the system (data-entry level user) then gets the option to revert or cancel, the only way she can get around the problem is to revert th…

I am trying to write a script with two DB files file 1 contains zipcodes and post office names [color:brown][color:orange]file 2 contains a list of members and their zipcode . I want to create a query using FM7 that totals the number of members in any given zipcode. This total would be a field called total members in file 1. Any suggestions on how I can do this?

I am a NEWBIE, so forgive me. I have two tables, tableone and tabletwo, they are joined by a relationship that can add new records to tabletwo from tableone. The relationship is joined at tableone::id and tabletwo::tableoneid. There are fields in tabletwo that contain text called for sake of discussion kitchenSummary. I am trying to make a text field in tableone that contains text from all the related kitchenSummary entries in tabletwo maintaining text formatting. Stumped, can anyone help?

Dear forum'ers', I really hope I missed out on something really important. It's really great what you can do with relationships in fmp7. But... Is the relationship window really the only way to view existing relationships? Even though you can do your best to 'organize' that window. It's really not 'lookup' friendly. You have to search really hard for existing relationships. Wish somebody would tell me there's also a text version of the relationships. Something like there used to be in previous versions. It's really weird there is an edit relationship button but no 'view relationships' button. Maybe somebody can give me ideas concerning namin…

Hi, I don't have any experience with FileMaker, but I'm trying to help someone out. Using the standard Time Billing template that comes with FileMaker, let's say I have a database with hundreds of line items (Day/Project/Customer...) across dozens of Employees. I would like a table that takes each unique Customer field (regardless of which Employee it was entered under), and then lists each unique Project for that customer. I need it to be more than just a report, because to the right of each of those entries we would like to add two other fields tied to that combination. For example, if we had Employee 1- Day: xx/xx/xx, Customer: A, Project: 11 …

I'm working on an invoicing system and I have a relationship between a lineitems table and a customer table. When I set up a new line items record I enter a number into the customer_id field I get the following warning: "This is the foreign key from the Customer table". I'm given three options of "Revert field" "No" or "Yes". If I press yes, the customer_id is entered and everything works fine. However, I don't want to get this warning every time. Can it be turned off? I don't get it when I put in foreign keys in other tables, so I don't understand why it is coming up in this table. Andrew

I need a little help. I have 3 databases. 1) Manager 2) Assets 3) Employees I want to use the Manager to edit, add and control the data in the other datbases. The other databases are to be used as data storage only. All information is to be viewed in the Manager. I created a "recordID" field in each database, but I am unsure how to make the relationship. Can some one pass along some tips?

Anyone have suggestions on how to define the relationships between multiple tables (which represent individual account ledgers using table view) and how to auto create info (transfers) between them without having to use portals? I am currently thinking through the star join approach. Additionally I realize this has been done before and there are probably templates and posts concerning it but I'm having trouble using the right key words for a search. If anyone has suggestions about good key search words (or existing templates) that would equally be helpful. Thank you.

i want to populate fields based on a customer name. So for example, Customer A ships to a certain place, gets a certain discount and so on. i have done that but how do I go about making sure that the information cannot be cahnegd by the end user. Right now I have a seperate db set up with the info I pull in and it works fine, but I can change the info too easily.

Sure wish there was a calculation function that would give ;1 when that record is active (currently displayed). Even better if this can be done unstored. This way it would be possible to create a relation from another file to (only) that active record. I would just need to have 1 record in the other file to display the same info of the file with all the records in it. Perhaps somebody has figured this one out. I tried it with a Selfjoin relationship but this makes the calculation unstored and a relationship only works with Indexed calculation. Or is there a neet trick with the look-up. Can't understand that there is no Get (ActiveRecord) function. …

I have a fairly simple(famous last words) concept that seems to result in very slow results from filemaker. the database consists of one main table ( people) and several ( six) related tables (transactions). five of the related transactions table have "conditional " joins. For example the join condition might be PersonID=TpersonID and gTvalue > transaction_record_value. This allows me to have a calculation that SUMs the related records that meet a range of results ( for example the sum of records greater then 60 or 120 days) this all works fine except that searches on the sum calculation from the people table take forever due to the lack of index…

I have a question about field lookups. I have a equipment layout where there is a company that is associated with each piece of equipment. I have the list of companies appearing in a drop down list from a related client table. I cannot figure out how to add a new piece of equipment and have the associated ClientID appear in the equipment table. Is there a way to do this, I have researched matching field and lookup fields but cannot seem to figure it out. Thanks in advance.

Is there a way to copy/remove a table (and all its fields) from one file to another existing file? Of course it is possible to duplicate one file and delete all the tables one does not want, but this does not work if you have two files and you want to move one table from one to the other.

I am not sure how to word this so be patient with me. I have a file called Customers it has all the information about each company we have as a client. I have a portal on this page which is linked to all the sales we have with this customer in another file called Sales history. The portal only shows the last sale and not all that this customer has purchased with us. Everyone of our customers have a number which is the relationship I used to link the two files. Should I do it more like a self join with a constant or is that not the problem at all. Any help would be great! Thanks Tony D.

...to have two table sets and when a record is made in the first table it is also created in a second...but when a record is made in the second it isn't created in the first?

How do I get a portal in the same file to show only what I find in a search and stop showing all records. Thanks Tony D.

I am trying to streamline the order processing for my ecommerce business by setting up a FM database--the emails are out of hand. It's been nearly a decade since I have played with FileMaker (versions 2 and 3), so please bear with me. I'd like to take in our orders from various websites via csv files, then be able to process the orders by entering the information in to our dropship providers' websites (manually--unfortuantely). It would be great if I had a layout for each provider to facilitate easy data enrty into their sites. Other issues involve tracking the status of the orders and items within the orders (processed, shipped, backordered, etc) for easy tracking a…

Hi hoping someone can help me. I'm stuck. Here are the match fields: Match Fields: Customer::Invoice ID match to Invoice::Invoice ID Invoice::Order Num match to Order::Order Num Order::Order Num match to Elec::Order Num Order::Order Num match to Parts::Order Num The problem is in the Orders to Elec. and Orders to Parts tables. Once Order Num is created, it uses the same Order Num for both Elec. and Parts and if I have two or more entries for either tables (Elec and Parts) it uses the same Order Num. I can create different Order Nums for one Customer but only one (the first) is being used when I create records in Elec and Parts. It should…

FM 7, OSX Tiger Hi Helpers! I have a database that includes "Companies". Many of these are independent but some are related to each other as either a "Main Office" or a "Branch" location. I'd like to have a portal in each Company record that shows the Branches and Main Office with the Main Office highlighted or sorted at top. If the Company is independent, the portal can disappear (extra credit). Call this group a "Region." Can someone please put me on the right track? Ideally it is a solution that allows for easy data entry of the "Regions" Thanks in Advance.

I have having real trouble getting my head around a certain aspect of creating a particular relationship. I have tried to describe the problem below, but i fear not very well so i have attached the file itself which should help make sense of what i have written below The first table (MAIN) is a series of records which contains three fields - SECTION, PAGE and NAME. So for example each record runs as follows: 1, 1, a

Ok I am tring to put a list view or portal to create a list of all employees co you can click the name and goto that record. How do I make a portal in the same File that will actually show all names instead of just the one I am on. I think I can get goto record to work if someone can help me create a portal list of names that reside in same File. Thanks Tony D.

Hi, friends it is impossible to make a relastionship based on a calc field unstored and calc: get(FoundCount) ?

I'm not the greatest with relationships so if anyone can help I'd greatly appreciate it! WHAT: We have a personnel database with several tables. We would like it when a department an employee is assigned to is selected from a drop down list, the 'supervisor' field is populated with the supervisor. How can this be done? I've already created a 'supervisor' table. The only problem I can see is that all the employees assignments are related to another table called 'assignment history'. Thanks in advance!

Hello, I can't understand why this is so difficult, but hopefully someone can help. I have two database files (Inventory and Quality Control) with a relationship defined by a unique ID. I import data into Inventory and have ID created automatically (serial number). All I need is to have records created with these same ID values in Quality Control, so that it can display several related field values from Inventory based upon the ID relationship. I have created the relationship between the two and checked "allow creation in QC" as well as "allow deletion". Creation does not work unless I have a related field in Inventory to Quality Control which I enter data i…

Hi, What I'm trying to do is perform a pattern match across tables as a way of aggregation. Basically, when a user enters a date range, it matches against a specific set of hourly records, performs a PatternCount() on a specific field, and returns the date of the record if the pattern is matched. However, I can't figure out how to aggregate the result of the if() statement in such a way that all the dates are returned to the first table. Let me know if any of this is unclear; thanks so much for any help you can give. --whynot

Dear FM Experts, I am working on a company wide asset database. I want to create a relationship that gives computer models that have a model number but no name for it, hence "Unrecognized Models". I have created a relationship joining Computer Types::Name = Unrecognized Model::Null Value. The Unrecognized Model::Null Value has an auto enter value of "". So essentially this is asking if Computer Types::Name = "". In the Unrecognized Model table I have a field called Counter that counts the number of unrecognized models. Counter is calculated by Max (0, Count (Asset::ID)). Asset::ID is a unique number throughout the whole database. In my Computer Types ta…

good morning everyone, I'm attaching my relationship graph in the hope that it will help me explain my issue. I have 3 database files: projects, companies and contacts. Between each I have a simple linking table (project-company, company-contact, and contact-project). Totally separate from these links, though, I want to create a relationship between projects and companies that will allow me to create a portal, listing all companies that "match up" with projects, according to the "sector" field in each database. I've tried to create that relationship in my graph but the portal i made in the projects database to display related records in the companies database is …

Hi, I'm new to the FM world, coming from a background in SQLServer and DB2. I'm refactoring a legacy v6 app, and I'd like to know if the following is possible. The logic is: If FieldValueinMainFile = 1, Create New Record in Related File 1 Else Create New Record in Related File 2 The original legacy app has 400+ fields in a single table (?!), most of which are calculation fields which do different processing of the data entered in the layout, based on what the user selects from a dropdown list. I'd like to split these calculations out into separate tables/files to get rid of the big switch statements, both to maintain my sanity and that of the maint…

Please help - I am going nuts!! Mac OSX - Tiger Moving from 5.5 to 7.0 I have two tables in my Special Ed File. One table is student/teacher schedule. Second table is the entry table where grades, behavior etc is stored (along with the student/teacher information). Each week, a teacher needs to create a new set of empty records in the entry file to update student progress. In FM 5.5 I had a button that would find that teachers schedule in the related file, and then in the entry file, I would import that found set of teacher/student records, Therefore creating an empty set of records for data input. With FM7 I am feeling totally stupid on how to do this…

I have my main customer database and database 2 that lists all the classes available to my clients each year (new database 2 each year). In my database 1 I have a defined relationship that goes to my database 2 and pulls the items back into database 1 as radio buttons (for data entry). I now need to see a list in database 2 showing all the names, emails of the people who signed up for each class listed in database 2. I can only get one person to show up under each class (regardless if there is 1 or 50 people signed up for that class). How do I get it to show me all names in alpha order?

Hello, I'm a very new user of FMP7 and I'm working on the most classical problem, invoices, I managed (with some difficulties) to build two databases (Invoices and Contact), now I wish to program the DB in such a way that in the invoices there's a popup menu where you can choose the customers data (not sure if I want to work field by field or if in the customers DB I want to make a field with all required data for invoicing) and I'm having serious problems with relationships/lookup, any suggestions on how should I proceed? So far I relationed CustomerID in Invoices with CustomerID in Customers but I can't figure out how to obtain the desired effect; I know it's …

A short one. One FM table with existing records called [color:blue]Incoming with fields: field1, field2, field3 ... All fields standard text fields. First Incoming record may or may not be the field names. If record1 is field names (and I understand User must choose to say which), display them 'as field labels' and omit/delete from the record set. If not, allow User option to 'name' them (if they wish). Another FM table called [color:blue]Receiving with fields: CompanyName, FirstName, LastName, Address1 ... etc. User views the record-set in side-by-side list (on form hopefully) and MATCHES Incoming fields to Receiving. Various relationships exist from Receiving …

What I wanted to do was to create a self join relationship where the field "End_Date" was related to "End_Date_g_filter". I wanted the global filter to be blank so that my portal would only show records with an empty end date. If the global filter has a value and the end date matches, those records show up in my portal; however; if the global filter is empty and there are records with NO end date (aka it's blank) the portal shows NO records. Should I create a calcuation on the table that says if the field is empty = 1 otherwise = 0 and then set the global to 1? Is there a better way? Greg

Hi from Canada! We have a Ford car and truck franchise combined with a body shop operation as well as a rent-a-car operation. We often sell to the same person more than once and we service the person's car more than once. How do I create a reference, in FM pro 6 ( my server is on linux and to my knowledge, FM 7x does not offer linux yet), that will allow me to change the adress of the customer (when he moves to another adress) and affect all the previous transaction as well as to quickly find his address when servicing his car in our departments. Right now, we write up the adresses everytime and do not update the old transactions which creates problems as …

I am lost again. I need to be abe to click a button in one database that will add the relevant info to another database. I have both of the relevant tables int he relationship part of both databases, I just have no idea how to set up a relationship. One is an events planning database and the other is a customer info database. So far I have tried saying last name in one is equal to last name in the other, but i have no idea what that means to the database, since it's not working out. The script in the events planning one says simply to call the add script in the other database. The add script in the other database says: New Record/Request Set Field [Contact Ma…

Does anyone know if there is a way to format a lookup field. I have tried without any luck. What I am doing is making a field of social security numbers that is linked to Social Security Numbers in another file. It finds all the data correctly but I can't seem to have both. Format the Social Security Number and have the field lookup the information in another file. Am I doing something wrong? And yes, the files are related and linked.

I have a file called Administration Database that contains 3 tables (Contacts, Project Initiation and Transmittals). Each table has 1 layout which has the same name as the table. I now need to create 6 more layouts using the fields and data from the Transmittals table. Do I have to create a Table Occurance for each new layout that I want to create? ALSO - Is it no longer possible to display only the layouts that belong to a particular table?

I created a "Faktuur" system. (Faktuur=List of work done by customer, and the cost). This is related to a DB with Name and Adress data (Contact DB) of all our customers. The faktuur gets the name etc from the contact DB, which works good. Now i created a payment reminder. It's a sheet in the contact DB wich is letter form in which several items are editeble, it auto adds current date in the letter and name. I created a relationship that allows me to have an pulldown menu in which you can select the fakturen which the client has with us, and whether or not he payed them. I used a Debet Nr, which is auto-assigned in the contact DB and used in the factuur to identify th…

I just experienced something very strange, hopfully any of you can explain this. I have a separated solution with 3 files (actually 5, but only 3 involved in this example). I have a script in my interface file that creates one record in the data file, and one in the br file. The relationship between GUI and data is a global number to ID (auto-enter serial) (creation of data record allowed). The relationship between the GUI and br is the same global number field to a plain number ID field. OK. The script goes like this: - The GUI global number field is set to empty. - A field in data TO is set to a value (a name field). - Record is committed. - Some other…

I receive election results in a flat file, and am able to print the basic reports that I need. But as others have suggested to me, I want to go ahead try and normalize the database. I need help for that. Here are the flat file's fields in the single table I have now: ElectionYear, County, Location, Contest, Candidate, Votes. Right now I have about 124,000 records for the general elections in 2002 and 2004. So there is a record for each candidate at each voting location in each county in each election. Thus each entity appears many times. In a normalized structure, I can see these tables: County (for 75 counties), Location (3,124 locations within the counties, an…

I have a database of staff, students, external people, grants projects etc. I have a table called enrolled which lists the degrees a student is enrolled in, I then have this linked to supervises, which lets you choose the supervisors for that particalur student and degree. In my layout I can look up all the information regarding a staff meber,eg their grants, projects etc but amd having great touble getting the students they supervise. I have attached the scheme. I created people2 which will allow you to see the students for t a given staff memeber but in the layouts how do I link to this seamelessly? Is htis a scripting problem or have I got the scheme wrong? …

Ok I'll try and explain this as best as I can... I'm creating a card game within fmp7 and I have one table for the main game (game) and one for the deck (card_pics) and I've got it all set up to create card ID's for each card in "card_pics". I also have a random card generator in "game" that deals out 3 cards. Now I wanted the ID produced by the generator to be related to the Card_ID from "card_pics" so that you could actually see the cards. I got it to work for one card but when I try to relate the other two card ID's (in game) to "Card_ID" (in card_pics) it just shows me the same card 3 times... i can't figure out why this is doing what it is.. if an…

Been struggling with a good way to do two simple things that seem diamettrically opposed? Each record in my database has a calculation field which determines REC_STATUS. This REC_STATUS calculation determines if the Get (CurrentDate) > Target Date (i.e.... has the date passed?) It seems that the only way to make this update automatically, is to not index it, and mark it as Do Not Store Calc Results - Calc When Needed. Problem with that though is that I have a self-join relationship for a portal based on a filter by the REC_STATUS. i.e. global Filter Status = REC_STATUS Thus REC_STATUS needs to be indexed for the filtered view. And then the calc…

Hello Everyone, I hope this is an easy question but I just can't figure it out. Here's the setup Table 1-Piece Builder -Field1- Job Number - such as 1001 -Field2- Piece Number - Unique Number Assigned by Engineer-"1001-1" or "1001-2", etc. Table 2-Burning Detail -Field1- Job Number - Relational to Job # in Piece Builder with List -Field2- Piece Number - Lists only relational pc. # from specific job #. So if Job # 1002 is selected, only Pieces for job 1002 are available to select. -Field3- Burning Dimensions -Field4- Bevel - Menu - "Yes" or "No" Table 3-Rolling Detail -Field1- Piece Number - relational only to Burning Detail Numbers …

Hello all, I am very new to filemaker and am enjoying struggling through my problems with the aid of forums and, I might add, a lot of luck, but I fear that I have come to a wall and am banging my head against it and am therefore looking for help. I have a number of related databases in my design with the main one including three field - Arrival date, departure date and total price. I have another db which includes the price relative to a specific date range. For example 1/5/05 to 01/06/05 = 230

Please Help: Filemaker pro 7 Table - Department master (contains the department number 'deptno') Table - Employee child (contains employee number, employeename, deptno) I have the tables related as such - Deptno in Department master is the unique value....deptno in Employee is the foeign key...in the Relationships section of the database its set up as a one-to-many (One department, many employees) My problem: I created a Department Layout and a Employee layout. How can i ensure that the foreign key (deptno) will only allow values that exist from the master (deptno)? In other words if master deptno only has deptno of 10,20,30,40 then child deptno…

I am a newbie to Filemaker - found a copy lying around in the office and decided to try it. I am trying to do something that simply won't work, and I suspect that it is a relationship problem. If someone can point me in the right direction I would be eternally grateful! Anyways, here is the problem: The database has 3 tables: Products, Clients, and Invoice. Each product in the Products table has an owner (the client), and is linked to the client table by a matching field. On the Invoice, I was able to select the client to invoice to in my Invoice layout, and have pop-up lists that list only the relevant items the client would buy. This part works flaw…

Hi all, I have 10 tables in my db... My problem is in the 3 of these tables...to talk more explicitly the names are projects, orderforms and subbudgets I have a relationship between projects and orderforms on the field projectacronym I have a relationship between subbudgets and projects on the field subbudgetID Thus, orderforms are indirectly related to the subbudget fields... And this indirect relationship is the one I really need and it is the source of the problem... For instance, when I use this while defining a value list with the include only related record option, it works as expected... However, when I use it in the sum function, …

Here's the scenario: Filemaker pro 7 Table - Instructor master (contains the contact info for the instructor) Table - Courses (contains course infromation/details) I have the tables related as such - SSN in Instructor master is the unique value....SSN in Courses is the foeign key.....in the Relationships section of the database I have them set up as a one-to-many (One instructor, many courses) My problem: I created a Courses layout that is based on the Courses table. I have included elements from the Instructor table at the top because I want the end-user to type in a last name or SSN for the instructor which will pull in more instructor informa tion…

I have a very very bad habit. I put my head down and tear into my work (at home). When I look up, sometimes days have gone by. And I don't remember anything except what I've been working on. When I'm *present*, I back up every hour minimum. Why am I telling you this? Because I just had a power failure on my home stand-alone system - in my design that is used every day. And I've done major changes to it (like always). And I realize (looking at the clock) that the last time I backed up was 12 hours ago. I have asked this before on power loss of hosted file. I know I need to trash the design if it happens - the risk of corruption is too great. But I don't…

Dear All, I have been desperately trying to find a solution for the following problem: I have an over 100.000 record db in which I need to perform fast finds. Since one find may result in over 10.000 hits I need to be able to easily specify further after performing the find. I would like to do this w/ a self-join and portal, basing the self-join on 3 global pop-up lists. I've read about using a calculated key to these fields, but this is not an option because in some of the records, the length of these fields may supersede the maximum index length of 20 characters, and not all 3 fields have to contain a value. Is there any other way to do this?: I want to t…

In FM7 I have a join of four fields; building, floor, room, subroom. Why break out subroom? Well, I've gone back and forth on that, I wanted it because it makes an FM find unique. But joins seem to fail when there is no subroom. Apparently you can't join to an empty field. As: BUILDINGFIELD1::bld <> BUILDINGFIELD2::bld FLOORFIELD1::fl <> FLOORFIELD2::fl ROOMFIELD1::rm <> ROOMFIELD2::rm SUBROOM1::srm <> SUBROOM2::srm matches and joins table 1 to table 2. But: BUILDINGFIELD1::bld <> BUILDINGFIELD2::bld FLOORFIELD1::fl <> FLOORFIELD2::fl ROOMFIELD1::rm <> ROOMFIELD2::rm SUBROOM1:[empty] <> SUBRO…

HI there. I am looking for a way to change a date automatically. I have 2 databases. If one date changes in the first database, I want the date in the other database to change as well. I want filemaker to know which record to change by the account #. If I need to be more clear, just ask. Thanks

I have a problem. I have sequence numbers that don't span the entire table. I have a join table that joins parties to lawsuit file. Each party has a sequence number in the join table. For example say Suit number 1-0001-0030 has 4 party menbers sequence number - party type - partyid 1 - plaintiff - 2454 1 - defendant - 1080 2 - defendant - 3456 As you can see, I have to keep track of the sequence of each plaintiff and defendant within the suit. They are independent for each suit number and party type. I cannot figure out how to set up a relationship that will allow me to access the dominant party (as in plaintiff with seq number 1). …

Hi everyone, I have a databas that has Invoices and PO's. They are in the same table. When a user clicks on the "Invoices" button, the script goes to the all the records that are marked as Invoices, and the same with PO's. I have a constant field marked as "Invoices" and one marked as "PO". I have a relationship between the constant field and a field called "Invoice_PO". (A "New record" script creates the new record and propagates this field with "Invoice" or "PO" depending on the script parameter). I have two fields called "Total_PO" and "Total_Invoice". What I am trying to do is have this field on their respective layouts and show the total of each. The current cal…