Relationships

I'm trying to make a many to many relationship database as follows: An address can have several persons An address can have several companies when changing an address, the information should be available to each person and company that is on this address when creating an address, the information should be checked so that there are no 2 addresses the same So I figured I need a table for the persons, a table for the companies, a table for the addresses and a table that specifies the links between them. I hope so far I'm not off the track with my thinking ... Then I tried to create a single layout that allows for me to create an address for a person work…

Situation: Tables I have a table of contacts, call it CONT. I am setting up a system for sending direct mail to some of my contacts. There are different direct mail programs, each targeted at a different group of contacts. So I have defined a table called DMGroup one record for each group of contacts. One group will contain multiple contacts; one contact can belong to multiple groups. So I have set up a join table DMGroupMembers. Its records contain only 3 fields: a primary key for this table and two foreign keys, one to the CONT table (fkey_cont) and one to the DMGroup table (fkey_dmgroup). Thus, each record in this table represents the membership of a CONT reco…

Hi, I am in a huge mess. I need to be able to create the ability to enter information in one portal that is transfered into another portal and vice-versa. Let's say that I am taking notes on different developments in the New York City area. one of the things I need to do is note which banks financed each of the developments, and what type of financing each bank did for each development. so basically, I want 2 layouts. 1 (developments layout) with a portal in which I can enter the different banks that financed the project and what kind of financing they did. This then needs to create that occurence in the Banks layout (portal because each bank finances many diff…

Hi All, I have created a database to track magazine sales. There are six issues a year and a separate integrated marketing area for miscellaneous sales. I have created a separate contact table, print ad table, and IM table. Nothing has an absolute price, everyone is different depending on their member discounts and number of issues purchased at one time so I don't have a Price Item database. I have created the data entry in portals so i can carry history for multiple years. The problem is the invoicing. I need to be able to create an invoice per issue. I am stumped on how to do this pulling data from a portal or can it be done? I am self taught and flying by the seat of…

My main problem is, I can't get my head around relational database concepts probably. I've enclosed an example of what i'm trying to do basically. I have a product catalogue in excel on sheet 1. I have several other sheets which are called amazon, pricegrabber, etc. About 95% of the content which it references over to the amazon, pricegrabber, etc sheet is the same as sheet 1. However, there will be extra fields which don't appear in sheet 1 which i will type manually. What I have as a problem in excel is if i delete line 5 on sheet 1 then it doesn't delete the whole line in the amazon sheet. This is why i'm trying to use filemaker. I could just delete the record and it w…

I have a portal that allows creation of records in a new table. However, whenever I want to add new information in the portal of the other table, because I made the I.D. for that record unique, it won't let me edit that record because it think I am trying to create a new record... How do I get Filemaker to realize that I want to edit the record with the i.d. I am using instead of trying to create a new record with that i.d.?

Hi and thanks for reading. I suspect this is a very easy question to answer and the only way to do this is to divide up one table into two, but I thought I'd ask first. For simplicity, I have four fields in 1 table. The first field holds Serial Numbers. The second field holds Descriptions. The third field is 'Requested Serial Number' and the fourth 'Requested Description". Through a Record Entry Form, I wish to type in the Requested Serial Number and as soon as I commit the value, the 'Requested Description' field will lookup the Description from the 2nd field which relates to the Serial No. in the first field -> and this field tied to the 'Requested…

I am a relative noob with FileMaker so please excuse me if this seems to be a stupid (or redundant) question. As a learning tool, I offered to build a database for a friend of mine that is a Private Investigator. I have tables of "notes", "persons"(targets), "containers", "clients", "investigators" and "cases." When a new case is taken, the client information is entered into the database in the "clients" table. If there is an insurance company or an attorney associated with the case, that information is also entered into the "clients" table. The person(s) being investigated is entered into the "persons" table. Then, the case is entered into the "cases" table…

I have two tables - one for bills due, the other for pay cycles. Very simply on the bill side there is a due date. On the cycle side there is a start and end date, and the portal shows all bills with a due date anywhere from the start to end dates. I had it set to a different date in cycles and it worked fine, but I changed it to this for several reasons, and after the change is when I'm having a problem. The relation is set up as follows: Bills: Due Date ≥ Start Date (And) Bills:Due Date ≤ End Date. When I create a new record in the portal, and I type in the date, at first it was wiping out the bill date and changing it to whatever the DAY …

Designing a multiple file database. How do I prevent from entering a password each time I open a file. Right now I have a dummy file that has an "Open" script that opens all the files that the user needs to start. Although I may change this when I get my custom menu set up. Either way I don't want the user to have to enter a password more than once. Is there a way to accomplish this?

I have 3 databases that I have linked based on a couple fields. They are linked by the actual values in the fields and not by foreign keys because of limitations in how much code I can change. I would like to be able to change one of the linked values in any one of the databases and have it change that value in the two other databases. Any ideas in how to do this would be greatly appreciated! Thanks Smeshy

Ack! I give up on this one, can anyone help me sort out this relationship? So I have a database for my company that consists of the following tables: Account Reps Companies Individuals Status Account Reps are employees in our office. Companies are the businesses we sell to. There is ONE Account Rep for every Company. Individuals are people in those companies Status is the status of each Individual (lead, potential, active, inactive) In a layout for Account Reps, each Rep has their own record (determined by field "Rep Name"). I would like to have three different portal views of Individuals: One for [color:red]Leads, one for [col…

Back again... I am in the process of adding some charting capability to the database I am working on. However I need to get a breakdown of one of my fields. I have a field called Status which has a drop down menu in it containing; Active, On Hold, Routing and Printing entries. I need to get the total number of Active Jobs as well as On Hold jobs from the status field. I've tried using the Aggregate Functions to get a total for each, but I am missing something. Your help would be greatly appreciated.

My database was originally set up to permit the user to enter data into portals on the first page. All was well. : Now, after adding some additional portals for the same purpose, I cannot enter data into the new portals. I cannot click on the fields in the portal, nor can I tab to them. The fields simply will not accept entries. I set them up just like the originals that function fine, but the new ones are not functioning the same. Can anyone help?

Hi All, Wondering if there are any "accepted methods" for implementing temporary tables. Not looking for any hand holding... but if there are some common practices or pittfalls I woulad appreciate the advice. From what I've read it seems the easiest way is that once you've worked out your table structure.. to put in the same set of TO's again (called temp_) and use those... am I on the right track?

Simple problem/solution I imagine... I feel that I am way overthinking this and can not wrap my brain around it. Lets say I have Recordset (Table) A. Unique ID and a unique name field. And I have Recordset (Table) B. Also Unique ID and a unique name field. Each recordset is a 'One to Many' to the other... Example, a record in Table A would have many Table B records that should be associated it. Vice-Versa a record in Table B would have many Table A records linking to it... How can I set this up so that when viewing a record from Table A I can see the related B records in a portal - and if I jump over to a Table B record I can see …

Hello, I have what I thought would be a simple relationship task, that has me scratching my head. One of the problems is I dont do this full time and so I get something to work, then get sidetracked and loose track of how it is working. Was hoping someone could point me towards some sort of tutorial on relationships, or something that would help me. The database I am setting up is for two sets of stores. One is a clothing store, the other is a jewelry store. They all produce a magazine, and I am trying to use this to track production. What i have set up is contact table, a stores name table, a local editorial table and a national editorial table. I am tryi…

I wish to move from a hierarchical category structure to an attributes system whereby I can look at/create a product and choose any attribute that I think it matches. The attributes are not part of the Product table, but they need to be presented to the user as being available for selection and I need to compile records into a Product Attribute table. My initial thoughts are that I will require x4 tables, Products (to include Parent Key = Product ID as well as a Global field to store the Attribute Group ID so that I can filter the Attributes) Product Attributes (to include Child Keys = Product ID, Attribute ID) Attributes (to include Parent …

Hi, I've searched for two days to try and hunt down the answers to my questions, but I've struck out. I need to do something that I'm sure is simple, I just can't get my head around what FM7 is looking for. I'm sure that once I get past the initial understanding, I'll be able to put the rest together. Can someone point me in the right direction? I've made a number of databases over the years. Mostly simple ones. Here's the newest challenge: I need to create a simple database. I'm using two tables: 1) Inventory 2) Purchase Orders To make things clearer, I'm limiting the fields required to just the ones that are relevant to this. Inventory: Item#, Initial_Qt…

Guys been away from filemaker for over 2 years now and forgotten all the basics..... Im trying to create a quotes database, where staff create a new quote record, then select a client from a dropdown menu. I then want the related address and contact details to auto complete and save the staff having to manually type in details. We have a clients table created and a quotes table, i've created a relationship between the customer names field, but can't get the address fields to complete. Hope someone can help.......!

Here's the background - I have a file to track bills due for a regular pay cycle every two weeks. One table is the bill pay date, the other is the list of bills. What I'm trying to do is this: I will have two dates on the paydate side, which would be the pay date and a start date which would be one day past the last paydate's date, so a start and end date for that pay clcle. Then on that page would be a portal of all of the bills that have due dates within that date range. So when I enter a new bill on the bill table side, the portal in the paycycle side would list all of the bills that have due dates after the start date, but prior to the end date. I would…

Hi, I'm wondering if someone could point me in the right direction here. I've got a database that manages clients. It's pretty basic from the users side. I'm needing to separate the information available to users who will log in to the database, based on the territory that they manage. What is the best structure to use here? I understand that I can use account privileges to make certain things available, and is it possible when setting that up to lock the user out viewing information about clients from other areas / territories? I'm thinking it's a relationship that will filter out the clients in each territory that is a part of the solution (i.e. dupl…

I have recently converted a database from Fp6 to fp8 where it worked very nicely- I have an Order table and an Order Lines table with a relationship connecting them by order number - I generate a unique sequential line number for each order line by auto entering a calculated value based on the Orders::Next Line field - Count(Order Lines::Order No) + 1 - an Order Lines Portal in the Order Layout allowed creation of order lines with the line numbers incrementing (i.e 1, 2 etc) in 8 now this only works if I click away from the portal after I created each Order Line otherwise the line number remains the same - it looks like the Orders::Next Line field is cached. I don't want …

Hi - I'm still trying to figure out a way to create a conditional relationship between two databases. Basically, I have a db with contact info for a variety of people (call it ContactDB), and I want another db that has other information for a subset of the contacts (call it StudentDB). I want to find a way for the StudentDB to pull those records, and ONLY those records, for people who have a "Yes" under the "Student" Field in the ContactDB, and then have additional information associated with each student record like "Graduation Date." How do I do that? Sorry I'm a newbie and thanks. -Kate

Hi, I’ve been looking for ages and don’t seem to find any info on this relation. I need to be able to give individual customers discounts on different Product categories. When the Product is selected, the Line item should get that Discount from that specific customer. How do I get those categories to display on the Customers Contact so I can apply a discount on each of the categories? How do I relate this Info? Am I dumb or is this really that hard? Looking forward to your reply! Thanks Ron

Starting from scratch I seem mostly to be scratching my head. I'm trying to put together a database of my pathology patients-- Patients have multiple tests (CBC, Marrow, FC, CG, MolecDx) and may have these (some or all) done on multiple occasions. Attaching file for review of tables and relationships. Haven't added data fields in and relationships from all tables because the existing relationships are not correct yet. Current structure adds a PatientID w/ every new test; I really need to be able to add tests to the same patient w/o adding an ID-- I think. I know I need to be able to search for a PATIENT and find TESTS from different DATES while maintaining t…

Once again, being a newbie (mostly self taught) database person, I'm having some relationship problems.... What my bosses are looking for is the following: Client can be 1 of 3 professions (Medical, Other Health, Other) Medical client can be 1 of many specializations Other Health can be 1 of many specializations Other Health Specialization can be 1 of many subspecialties Other can be either picked from a list or entered if it doesn't exist. So far, I have Client Table Client Key - unique key for each client And I don't really know where to go from here, if I want to make the choices mutually exclusive (ie, a client who is a Me…

I am attempting to get a lookup field to input multiple values for a single record. I have a main table that contains information about species (each record represents a single species). Each species has a distribution. The distribution is selected from a Country field (which is a series of checkboxes). I have another table (Country table) that lists all the possible country names and their abbreviations. The main table & country table are linked by a unique country ID#. I want to have a lookup field in the main table that will have the country abbrevations (data from Country table) in it based on the countries selected in the main table. Everything is wo…

I have a database with referral records to different medical clinics in our area. I've hastily simplified and modified the file if you feel like looking at it. This is an already made database that I'm trying to restructure to work better. There is a table for the referrals called "ReferralData" Also, there is a table I've just created for types of services provided called "Terms", examples: "general physical", "cholesterol blood test". This is related to the "ReferralData" table in a Many-to-many relationship. I also have a table for groups of these service terms called "TermGroups". This is related to the "Terms" table in a many-to-many relationship so th…

So I am getting to the point where a single letter in front of my primary keys is simply not enough to distinguish the origin of a serial number... Can I build relationships off of a stored calc field that concatenates a prefix to the serial number? Forseeable problems, friends?

I have a quite complex DB for tracking all sorts of details of animation production (cartoons) for a studio. This was created for a single production. The DB has about 10 tables, I would guess 120 relationships, about 60 scripts and over 100,000 records. It works beautifully for that one production. Recently we began work on an additional two productions. Of course the new shows want the same set up. Clearly I can save a clone and set them up with what we have today - however we're constantly tweaking and adjusting things. So if I went the route of a clone, I'd have to make every tweak three times (and if/when more shows come along, who knows how many more times). …

Hi, trying to build a simple client tracking DB here... The relationship I have is fairly simple: A company can have many employees An employee can be assigned to zero or more roles within the company. By "role" here I mean something like "primary contact", "billing contact", "technical manager" etc. I have the following tables: Cust_Company (Company level record) Cust_Company_Entity (Company employee level record) Cust_Entity_Role (List of roles that an employee can be assigned) Cust_Entity_Role_Join (The join table for linking a company employee to any given role) Now, I have an overall company view, which has some portals to various c…

Here is what my bosses want: The ability to store multiple mailing addresses for individual clients. Now, I know about setting up a key field relating my client table to my address table, using a one to many relationship so that 1 client can have many addresses. No problems there. However, my bosses don't want to have to use a portal and scroll through a number of addresses. They want to be able to display the one "preferred" address for the client in the same page as the rest of the client demographic information, but be able to change that preferred address to another address by, say, clicking a "preferred" checkbox on the record. They want to retain ALL the …

Multi Criteria are always styled as "AND" in relations. How could I have multiple criteria styled as "OR"? This is my issue. 2 tables, A & B. Table A has field 1A, table B has field 1B, 2B, 3B. The relation between the tables should be: 1A = 1B OR 1A = 2B The contents of 3B shows up in a portal for all records that match 1A=1B OR 1A=2B. Can this be done in FM and if so how? Thanks!

Hello there, I'm new to relational databases, so any help is greatly appreciated. I think I've gotten my clumsy head round it, but there is one problem that's baffling me. I have two tables: One is a list of materials, the other a list of vendors. So a many-to-one relationship, right. It's a database of materials, and where to get them. I would like to enter a new material, and then assign a contact person (the vendor). I think part of the problem is that I start with the 'child' and then assign its 'parent'. Anyway, I would love to have a nicely formatted drop-down list of names to choose from, ideally followed by the company name. Or even a pop-up wi…

I discovered recently that I could copy & paste tables from one database to another. If I copied the contents of a layout from the first database I can also paste these contents into a similar layout on the second database. So far, so good. What I want to do now is paste the relationship graph from the first database into the second. There does not, however, seem to be any mechanism on the relationship graph for making this happen. [color:red]Can anybody recommend a way to paste relationship graphs from one database to another? Thanks, Jarvis

Recently I had a problem with a Global field in a relationship. The rel. wouldn't return the values I wanted, but with enough messing around in the definitions the same relationship, without any changes, would suddenly work. In an effort to solve this issue, I changed the global field into an auto-enter, without modification, and the relationship is working perfectly. QUESTION: Is it advisable to not use globals in relationships? Thanks folks. Michael

I have a table that contains part numbers for contacts, connectors from hundreds of manufacturers. Via a line items table (called cross-ref), we use a relationship to match tooling and accessories we manufacture. Unfortunately, there is no consistent nomenclature scheme for these, eg, Company A has A445-2cX13.0-2, and another NSN-5120-00-23-5441 and so forth. Initially, the primary keys in the various tables used record id's, of the type that were auto-enter. A few design challenges got me to wondering if things might be easier if instead of record id as key, I made use of the part numbers in both tables as keys. I realize the main argument against is the…

I have two repeating fields, Term, and Win. Win is a pop up field with only one value, "X". I have a non-repeating field, Win Term. I am simply trying, upon entering "X" in the Win Field to have the contents of the corresponding Term field placed in the Win Term Field. Have used the Extend Function but am missing something-can only get the first Term value. Would really appreciate some help. Thanks. Term Win 24 36 48 60 Term Win ?

FMP8A, WinXP I have the beginnings of a school file which I’ve been wrestling with and which includes the following tables: Teachers, Classes, Enrollments, Students, and Attendance. Teachers ----< Classes -----< Enrollments>-------Students 1) Not sure where to place the Attendance table. Should I relate Attendance to Enrollments (many-to-one)? 2) Should I have a separate table for grades or should grades be an attribute of Enrollments? 3) Finally, not sure if or how I can archive the records at the end of each school year or if there is a better method than archiving. I spent yesterday and today searching and reading over t…

I had this one working and then I did something and shot the poor thing in the leg. In summary: [color:red]Vendors Layout works OK. [color:blue]PROJECT Layout doesn't. So, I copy Vendors layout and change the layout setup to show records from PROJECTS. PROJECTS "STILL" doesn't work. (of course I add fields from PROJECTS) The fundamental problem is that none of the FOREIGN KEYS in IMPROVEMENTS will update when records are added to the PROJECT or PROPERTY fields. (Yes, I have [color:brown]But the VENDORS::FK_VendorID in Improvements WILL update. Why? I don't know. ALL FK fields have "Allow creation of records in this table via this relationship" tu…

Is it possible to accomplish the following - I tried but were not able Table1, Fields ID, Name Table2, Fields ID, Name, Last Table3, Fields ID, Name, Last, Task Table1::ID = Table2::ID Table2::Name = Table3::Name In Table1 I display a portal from Table2 When I enter ID into Table1 and data into the portal (Name, Last) a record is created in Table2 and all works well. I want to know since Table2 now contains the Name which is a relation to Table3 can a record be automatically created in Table3? Hope I explained it well enough., Thank you.

I hope I can explain this well enough to get a few good suggestions. I have have Contacts stored in a table (7000+)records and they have medical records another table (4000+) records, not all contacts have medical records but some have multiple There is a subset of 500 contacts that our lab is interested in looking at in more detail. But for reasons I'm not 100% clear on the lab can not see personal info so they have give me a list of "Sample Numbers" and I've imported those into a separate table (which maybe I don't need but its there if i need it) called Report. I need to grab list of all medical records associated with each contact that is in my list …

I've got a database with job descriptions for our agency. The database needs to hold all the job description data and generate evaluation and training reports. Simple enough, I figured. I created text fields for the appropriate areas, filled them and created reports that display this data. I've come across some limitations however from this approach and realize I may need to create more complex relationships. The main issue lies with the duties for each job description which vary in number and the responsibilities of each. So say a person 1) Manages staff, 2) Completes monitoring reports, and 3) Watches YouTube when nobody is around. Underneath these dut…

Hey Everyone, I am creating a database that has 4 tables: customers orders orderitems catalog_items A customer will log into this site and ONLY the catalog items that have been applied to that customer's account can be listed. In other words, let's say there are 400 catalog_items but that customer only can sell 90 of them, that is all that will appear. I am debating what is the best way to do this. I could create a table called allowed_items that is the same as catalog_items but with an extra field that says who this is approved for, but that table will be huge, and there will be duplicates (if 5 customers sell the same product there wi…

I'm creating an inventory program for my small business. Most of the parts have multiple suppliers. Right now I have two tables set up "Supplied Parts" which contains supplier info and the PartID from another table "Inventory". I want to create a purchase order layout that lets the user only add line items(parts) from the chosen supplier. What is the best way to implement this?

Hi, I have downloaded a trial version and am trying to built a simple database. I have an orchard with different blocks. I need to record management information. I have two tables one "blocks" and another "events". I need to be able to select which blocks an "event" belongs to and display this as a list on the event page. I have read the user guide and can make this work with one related record but can't find how to select and display multiple related records. Thanks for your patience...

Can anyone help this beginner? For several years, I’ve used Filemaker Pro 5 and now I use 8.5, I am just starting to set up my own tables, layouts, and such. I am developing a simple solution to store school records, plus track admin functions. I have a table of student info and a Student Detail layout that, along with common info like name, phone number, ID number, etc., has a portal of related records detailing the classes the student has taken with us. The portal is sorted to show at the top of the list the most recent class completed. I previously used a Filemaker Pro 5 solution built for me by a friend. In that one, the newest class registrations were enter…

I suspect there is some relationship aspect I am missing. But, it doesn't seem to make much difference what I do, none of the Primary or Foreign Keys will update. FYI: A Project is a single activity like "remodel kitchen" or "update bathroom" If I can get the keys to work, I want to make the Project table the basis for a POPUP on the Main Layout.... Got any ideas? Thanks Ron HomeBasis.zip

I don't understand this. I have a relationship between two tables, table A and table B, in the same database. The relationship works fine. I have that same relationship between those two tables in another database, viewed through some other relationships, and it won't work. What's strange, however, is that if I mess around with the database definitions, and view the relationships enough, , but don't change anything, eventually the relationship will work. Is this an indexing problem, or am I working with corrupted databases? Thanks for the help, comrades. Michael

I noticed that, when in Table view, Filemaker designates each row a separate record. Is it possible to set this up so that each row is a "child" record (each belonging to a specific parent record?) Example: A (parent) >>>1 (child) >>>2 >>>3 B >>>1 >>>2 >>>3 >>>4 The tricky part is that the user also needs the ability to choose a varying # of child records per parent record. For example, for Parent record "A", the user has 3 child records, for "B", 4 child records, "C" 6 records...and so on. I would also like to give the user the ability to "collapse" or "expand" which child records are vi…

I need to sell one product to several customers on one order. What's the best method? My db has four tables: Customers, Orders, OrderLineItems, and Products. The relationship is built using unique ID key fields for each table. The OrderLineItems table is a join table, and is used as a portal in the Customers layout. I have 5,000+ customers that will/may receive one product each month. The product will be unique, and only sold (to any customer) once. I need the "order" so that I can generate a report of the customers (found set, not necessarily all customers in the db) that received "Product1" in January, and "Product2" in February, and so on. I also ne…

I apologize if this has been asked before, but a search didn't reveal what I am looking for. I have two tables related by calculation fields, and it doesn't seem to be working as I think it should. Here is my setup (from a database used to test this issue) Table - View Input - Global, text IsThereInput - Calc, if (View::Input<>"";1;2) Table - Data IsThereInput - Calc, if (View::Input<>"";1;0) Value - Auto Enter Serial -------------- The tables are related by View::IsThereInput = Data::IsThereInput I have a portal on the View side that should show the value field of all related records. I input some text into the Input field…

Is there a faster way of setting up the script below, It runs against about 2,500 records every 4 hours, and it takes a long time to go through. squatters.zip

I imagine this is an age old problem that every FM developer has hit their heads against at some point since the beginning ... but I thought maybe some of the more "brainy" amongst us might have discovered a workaround to the limitations filemaker has provided us with its "out-of-the-box" set of tools. Let me preface this discussion by saying I am aware that there are a range of Plug-ins out there that would solve this problem with little effort, but I am a purist and abstain from any temptation of an easy path that leads me to develop a solution that requires a third-party add-on, forcing me to ensure every client has the required software installed before for my DB…

Hi, I started creating scripts and linking tables together but no luck. I must be doing something wrong. Can someone out there help me, pretty please. I am trying to link the following tables by study id and visit: BASCParent, BASCTeacher, BASCSelf, and Summary_Scores_II If user navigates from one table to another and there is no common study id and visit, then a pop up message will tell the user there is no related record on study id and visit. The pop up message will ask the user if they would like to create a related record with study id and visit. If the user clicks yes, then study id and visit will be copied into the table the user chose. If the …

Ok. I'm a super neophyte newbie so please don't be too harsh with me. Only a few days with my head in the database world. At the moment I'm trying to work out basic relationships and I'm alright until I get to one table. On the one hand I've got all my Purchase Order data, and on the other I've got Invoice data. In the middle however, I've got a Bill of Materials, which is in essence a formula table. Each record describes a Parent Product Code Number, a Batch Quantity (almost always 1), Component Product Code Number, Quantity of Component Per Parent, and a Decimal Control Code. I am assuming this would be a Many-To-Many relationship conundrum. I need to relate cos…

I have a client who has three databases that he wants to tie together. He wants to be able to put in a phone number in the 'order' database and have it pull from either the 'client' or 'vendor' database, depending on where it finds the data. Is this possible? I have tried all sorts of things, but have been unable to make it work. Please advise. Thanks!

I have a table called "Purchase Requests". It is joined to a table called "Funding" by a field labeled "recordID" which is auto generated serial. The table "Purchase Requests" also is joined to a table called "Vendor History" using a field labeled "Purchase Order No". Both "Vendor History" and the "Funding" table contain a field labeled "Bud Ref". What I need is a portal to show all vendor payments using a match between the "Bud Ref" and "Purchase Order No" fields. Is there a way to do this? Thanks.

hi there, I have a found set that will range from 30-300 records.. I need to delete all related records in a table when a user logs in... what's the quickest way to do this? I tried looping through the records and doing an "If not isempty(related record), then clear" but it's kinda slow I thought about going to related records in a tiny window and deleting all the records without dialog... I'll see if that's faster.. I don't need to delete the records... clearing the related field would serve my purposes equally well.. any suggestions ?? thanks :B

Just curious if FMP is simplifying (graphically) the concept of SQL Queries using TO's and Relationship graphs? For example - If I have 2 tables in a database - Customers and Invoices, Would creating a TO based on Customers be the equivalent of saying in SQL SELECT * FROM Customers And making a connection between Customers and Invoices in Relationship graph would be the equivalent of the rest of the SQL statements like SELECT * FROM CustomersTable, InvoicesTable WHERE CustomersTable.CustID = InvoicesTable.CustID AND CustomersTable.DateOfCreation ≥ "1/2/2006"

Hi everyone, We have a service business where we work with warranty companies to service their customers. I am designing an invoice where I have a "Bill To" address and also a "Customer" address field with names etc. ofcourse. The thing is the Bill To company is also a customer in a sense. So what I did was just combined both entities into one and call them both "Customer" (I have a single table Customer. How do I pull (and create) into the Invoice layout, records from the same Customer table but into two different areas of the Invoice (Customer and Bill To)? I have thought of the concept of table occurrence but don't know how to implement it. Is creating another occurr…

Sorry to ask for an apparently easy thing but ... 800 pda use about 20 different DC adapters. I want to add to the pda table a list from the adapters table from where to select the appropriate adapter. But I dont know how to create the relationship - there isn't really a realtionship..I would like just display the total value list from where to select... so I'm forced to create a relationship ...but I can't get through..I either get an empty list or a correct list but then when I select a value in the adapter list, all records in the pda table take that value.... I know Is stupid ..it was easy of FM 7 but here ... please help

I'm facing an entire redesign, and need a push in the right direction. I have a report that pulls in child records from two related tables by simply setting two different fields to value lists. I think this type of report is called a union report. Well, I've run into the layout size restriction. The value lists have grown to the point that they will get cut off. I need to redesign so that I have a traditional report based on the child table. How would you design it? Picture three tables, two are children of the third. I can run a script to create the fourth "report" table. Any ideas or am I being too academic and you'd prefer specifics to offer advice?

Here I am, undertaking my very first filemaker project- my nonprofit just "volunteered" me as designated inhouse dev. - and I am utterly stumped as to the best way to structure my data. Allow me to ignore the enormous scope of the project momentarily and just focus on the people problem. I found a more abstracted form of my problem in the RD theory forum, but it had no responses. http://fmforums.com/forum/showpost.php?post/241269/ My company has many clients for whom we provide financial management. To satisfy our reporting duties to the government we must track a lot of other people such as landlords, co-tenants, mental health caseworkers, etc. I…

Hey, my first question as a FMP user. Previous attempts using Access worked, so I wonder why I have problems now. I want to have 1 layout with on the top some general person's data, below that should be several tabs each containing answers to a specific questionnaire. 1 person could have filled in a specific questionnaire more than 1 time, and I need some controls on that tab-form to switch between these records... So: a table for each person and a table for each questionnaire, right? 1 to many, right? but already this gives me a hard time! -) > Thanks in advance.

I am sure this issue has been hit a million times and I have been looking for an hour for the same post.... now by saying this I know some will post links and say I didn't look... I really did... but here goes: I have a solution that is for registering for classes and it uses a relationship that show "future" reservations to a field that simple contains a calc: student_id_futureClass If ( get(currentDate) > class_date;""; student_Id ) And the key is from the student table to this field located in registratons table. In the student table, it is making a calculation on how many reservations this student bought, 8, how many the have signed up for, Sum(reserv…

Hey everyone, this is my first post, but I’m in real need of help. I am creating a club directory that has two tables; a table for all individual members and another related table for family groups. Each record in the family groups table will list all members of a single household in a “family members” field with 15 repeating fields (if they have more than 12 kids and a set of grandparents then I guess I’m in trouble). The relational link is one to many and keyed on a field in each table named “head of household”. When a members individual record is created the user will enter the name of the head of household, and if a family record with that head of household a…

Hello all - Essentially, I am trying to make conditional portals in FM6, and I'm not sure I can. If I can't and I can do it in FM7, please let me know, since I can get that version. On to the issue... I have a database of everyone that has ever had contact with the organization. This includes faculty, graduates, coaches, donors, major funders, and so on. For sake of clarity, let's pretend there are only 2 - faculty and students. What I want to do is create multiple databases - one that has everyone in it for things like mass-mailings and one that has only specific categories of people in it (like Students) to hold more specific info regarding that category.…

He everyone! I’m fairly new to filemaker pro and we use filemaker pro 9 in a client-server environment. I’m working on a old FMPro database that the truck dealership has used for years. The main db is Units. Units holds the truck info UnitID (our key ex. 06FOR1234), Year, Make, Model, VIN, etc. This db also runs the dynamic web site for the inventory (so I’m afraid to change it drastically). I was trying to make a sales.fp7 that would access the main file and use its information. Sales.fp7 just has a UnitID field in it. I then made a script that deletes all records then re imports the records form Units.fp7 so they can cycle through the inventory, run searches, …

I have two tables... one is a schedule which pulls employee information from an employee table. One of the fields is an fund source code... normally, we want that info to pull directly from the employee field.. which works perfectly... Every now and again, however, we want to override that fund source and put something different in the schedule table. When we do, however, it changes it back on the staff table... How can we prevent that?

I have 3 tables (all of them are one-to-many) that are combined to make other 2 tables: OWNER ownerID.pk FARM farmID.pk ownerID.fk BLOCK blockID.pk farmID.fk block_number OBS: blockID.pk is a serial number for all the the blocks from all the farms in the table BLOCK. block_number.pk is a name we give to the block and is used in the farm, we often use to number it is sequence. One farm can have from 2 to 200 blocks. ex: [color:red]blockID.pk farmID.fk block_number 1 1 1 2 1 2 3 1 …

Have 2 tables in a database Artists and Paintings with a OM relationship between Artists and Paintings. In a layout based on Paintings, I have a field for artist Name derived from the Artist's table. If I use the OM relationship to fill Artist Name from Artist table using a value list generated from the Artist table, I see that I dont save much in terms of file size. Because In the table view I still see that the Artist name field in the Paintings table is filled with Same name in multiple records. Whether I enter artist name into paintings table manually or using a value list via the relationship, my database filesize remains the same ! Wouln't this be …

Anybody knows if Filemaker is capable of displaying in a portal, information stored in 3 table’s base on a common key? Thanks.

Hi everyone. I'm building a feature into a database that will allow committees made up of employees to cast votes on job candidates based on criteria that are specific to their committee. I have the following tables: Employees Committees Candidates Committee_Employees (join of Employees and Committees) Review_Assignments (join of Candidates and Committees) Committee_Voting_Criteria (fk_committee_id) Employee_Vote (fk_employee_id, fk_candidate_id_, fk_criterion_id, vote_ a 1-5 number) This seems pretty straightforward, but I'm stuck.. I want to build a layout that will allow an Employee to login and cast votes based on the criteria specific to the…

I am accustomed to working w/ FileMaker 5.5 using a Mac and have been forced to switch to FileMaker 9 for PC. The learning curve is so steep - it doesn't seem to even be the same program. Different terms (tables? portals?), different everything. I have so many questions I don't know where to begin. I attempted to register for training with 2 different companies and they both refused me, saying the differences were to great. What??? That's why I need training! Anyway, can anyone recommend some good self-training books or sites that will help me learn how to set up my school's database using FileMaker 9? One question - when I create a record (unique Student ID) in one…

I'm new to filemaker most of my experience is with other dbs. I recently got a job working with a filemaker 9 db. It was just upgraded from version 3 or 5. The db has one table with 95 layouts. The table has 494 fields some of these are repeating fields. I would like to break this table into 6 smaller related tables. How should I start? Are there 3rd party tools that would make this job easier? This db is a real mess. I'm currently mapping the thing out in a spread sheet. I've put all the fields on the left side and the layout names on top. This way I can see visually which fields are actually being used and by by which layouts. I found a good bit of…

I'm in the very beginning stages of designing a database to run my business. There probably will be between 15 and 20 tables in the final design, all related in some way. At least two of the tables will be relatively large, with over 10,000 records each. What are the advantages/disadvantages to keeping all the tables in one file or making a separate file for each table? I have only recently upgraded to version 8.5, so previously I have always used separate files.

As some of you might already know I built a large database in FileMaker 5.5 Yes is was quite painful and as suspected we upgraded to FileMaker 9. I am using FileMaker 9 Advance to perform the migrations and upgrade of the 5.5 system. I have a question regarding repeating fields. I have a section of repeating fields and I would like to import that data into newly created portal fields in FileMaker 9. What is the best way of going about moving repeating field data into portal fields? Thanks for your help. Kasei

Heres the problem, i am pretty sure i am doing everything correct but i cant get this to work. There are two layouts. There are two tables. Layout 1 - uses table 1 Layout 2 - uses table 2 I want to display a value on layout 2 from table 1. A relationship is required because layout 2 is associated with table2 not table 1. Primary key from table 1 is set to auto serial. I have a 1 to many relationship from table 1 primary key going to the foreign key of table 2. This should allow me to display values from table 1 on layout 2 which is associated with layout 2. correct? On layout 2 i have a field that is set to display values from primary …

I have a database running on fm server 8.5. I want to create a local fm 8.5 db that calculates values based on field values on the server side. I've created a local file and created a reference to the server side table. Then I created a relationship between the server side field (itemnumber) and the local field (itemnumber). The purpose is reporting only and I don't want to modify the server side. The report output will be the creation date of the server side record and left(itemnumber,10)...itemnumber being the field on the server side database. I appreciate your suggestions. Craig

I have a database with a Case table that has fields for 10 documents (Form01 thru Form10) or document serial numbers. I have 23 different documents (FormA thru FormW), each is set up as a table. Each table has a serial number set so that the first character represents the form’s identity A thru W (so FormA’s serial numbers are A0001, A0002, A0003, etc.) The Relationship between the Case table and all of the 23 Forms is thru the Cases’ serial number field. Here’s where it gets confusing (for me, anyway)… -- I need to be able to create new forms from the Case layout. That can be attached a forms submenu, if necessary. -- Each form’s date and title nee…

Hi, I've gone a bit crazy building some pretty interface stuff... for example, red, green and yellow lights that show up on a record based on unstored calcs... unfortunately, when the script changes the value of the calc by setting a field, the images change somewhat sluggishly. I know I'm not providing specifics, but could someone talk to me a bit about what, in general, can speed up such responsiveness... for example: - the size of the bitmap image I'm using... e.g. 5k vs. 50k ? -whether it's in a global in the same table versus pulled from a related image table ? -whether some of the calcs the unstored calcs are based on are indexed at the "n…

My customer database is crossing looked up data. The lookup is based on a site number text field that sometimes has a "0" on the end of the number. We have customers with multiple work sites. Our site number works like this. "CustomerNumber.HowManySitesCalculation" So if we have 9 sites for this particular customer and we ad a site that new site is "CustomerNumber".10 I have changed from dashes to decimal points because the lookup didn't "see" the dashes. The lookup is crossing information from site number 1143.1 to site number 1143.10 I believe it drops the last 0 in the lookup because it not numerically necessary and pulls information from the first record i…

Does FMP allow for a degree of participation? For instance, if I have a db with Agents table and Clients table (w/appropriate keys), how can I limit the agents to no more than four clients. In ERD this is often written as (1,4).* *(Witht the presumption that an agent must have at least one client. Could also be written as (0,4) if the agent need not have any clients, etc. Thanks. dan

Hi I have a strange problem. As you can see in the attached fm file, this is a simple technique. In fm 8.5 you can see all the records in the left portal. When you click one of them, they are sent to the right portal and disapeares from the left one. Now, if you open the file in fm9, you can only see records on the left portal, if one or more is on the right portal. It uses 2 relationships: interface = data right interface = data left interface ≠ data right Anyone have a clue ?? ... it is a very urgent problem !!

I have a parent table that has a number of supporting tables, in which I am trying to roll up the date modified of last table update to the parent. So the parent will reflect latest update of its children. Make sense? Can it be done?

Hello, I am a beginner at FM, and have a question that has probably been answered on numerous occasions. I am trying to figure out how to contruct a database that will allow me to have a drop down menu, and a second (or third) drop down menu with values that change depending on what is selected in the first drop down menu. I am sorry, I am probably not even speaking in the proper FM language, but any help or referrals to resources pertaining to this issue would be greatly appreciated. Thanks.

Hi everyone, I'm putting together my first real database and I've hit a snag. The idea is this: as a film production company, we want to be able to log every screenplay that gets submitted to us. Using the FM9 Task Management template, I'm trying to use the Contacts table as the hub from which all other info flows. I've created a few extra tabs, one of which is called Submissions. Since it's certain that writers will submit several scripts over time, the Submissions tab should be able to accommodate an unknown number of multiple submissions. I tried creating a portal relating back to a Submissions table, and that part works fine--but we simply need too much infor…

Hello How do you setup a many to many relationship with a unique constraint across the two id's? eg Link table contains fields id1 and id2 I need fields id1 and id2 to form a unique composite key. I can not seem to find a way to do this... can anyone help? Thanks Jason

Not quite there, not all clear when it comes to relationships (IRL ones as well as those in FM ). Simple Structure: Employee_Table Week_Table Week_Line_Items_Table Employee_Table contains Employee Name, ID and rate. Week_Table contains Employee Name, ID, Week_Notes and some other fields. Week_Line_Items_Table contains Employee Name, ID, Time_IN and Time_Out They are related: Employee_Table::ID = Week_Table::ID Week_Table::Fkey = Week_Line_Items_Table::Fkey (that is where I am wrong most likely) What do I make the Fkey - what should be inside?

I have used filemarker 3 years ago, but could not remember how I created a portal fields. Fo example, i have a table only with two fields "name", and "hobboy". How to define a self-join relationship so that i can enter multiple recrods into the "hobby" field?? I am depressed that the Filemaker Pro 9 does NOT give a good example on this issue.. Thanks!! Jie

Hi there, I have a solution in which a handful of users perform actions on the same record frequently. I'd like to create a way to report on the modifications that have been made to a record since the current user's last review of that record. I started to create a prototype of how I'd like to compare record modification and record review timestamps in order to show ONLY those modifications that have occurred since the logged-in user's last review of that particular record. Please see the attached file.. I've started but am not sure how to get to the last step... which is, displaying a portal on the right-most tab on the Records layout. Any advice w…

I have a very simple database that uses a last name to match two tables of information together. For some reason I can't get the match to work. I know the data is good because I can copy a name from one table and search for it in another -- I will find a hit on the search. I have tried changing the search on in the text field to unicode rather than English but that doesn't work. No idea what I am doing wrong - makes no sense to me that if Filemaker can find a name from one table in another table, then using those fields to define a relationship should work, if for nothing else but at least those records where the searched names are matching. I'm sure I am doing something …

I have a script in fileB.fp7 which creates a new record in t_sessions and populates 7 fields using a lookup (from a table in fileA.fp7). Also on the layout is a portal, which shows related records from t_songs (another table in fileB.fp7). When the script runs, I also want to use a lookup to populate the portal with data from a portal in fileA.fp7. How can this be done? Hope that makes sense; Novice developer here. Many thanks--

This really has me stumped. Almost to the point of dumping FM. I have a parent table and two related tables. Each table is set to allow the deletion of the related records when the parent record is deleted. It is not allowed to delete the parent record if the related records are deleted. But FM has it's own mind. One related table works as it should. The other will delete the parent record when a related record is delete. It does't matter if the related record is deleted by script or hand, nor from which layout. I have deleted the relationship and started over but nothing changes. FM seems to have (this isn't the first time) hidden something in the co…

Hi everyone. I'm a beginner FM 9 advanced user and so far i'm loving this software! I'm currently using the simply client/contact management template with some minor modications i've found in this forum. I have an excel spread sheet of 30,000+ leads i want to import into FM to manage seperately from active clients/contacts as leads and also to be able to distribute or assign them to sales staff for prospecting. Once they qualify the lead they can commit it to active client/contact. I look forward to reading your input. Thanks in advanced, Damond

I have a script which creates a new record in t_sessions and, using a lookup, populates about 7 fields. At the same time, I also want to populate related records (t_songs) in a portal on the layout. The source data is also in a portal, in another (related) file. How can I bring data in, from one portal into another, using a lookup, whenever a new record is created in t_sessions? Hope that makes sense; Novice programmer here. Many thanks--

Hello, I am trying to re do our company job database and am stuck on something I think should be simple. I have a job order table that relates to our customer table. With a new order being input, I would like the person who enters the order to be able to start typing the customer name, and it would fill in by looking to the customer table. Well it is not working, I have a parent and child key to set the relationship. Tried setting the field as a dropdown list, which works but then non of the other fields are populating with the related data. Probably something simple i am missing. Am a bit rusty, havent used FM since v5.

I'm struggling with a way to look at "pre-requisites" as a relationship between a Student table and a class rooster. Essentially, I'm trying find who those students that have not taken a pre-req class but who are currently enrolled in the course The student table has the PK set as STUD_ID and the Class-roster table has the STUD_ID as well as the CLASS_ID but I'm stymied on how to set up a relationship that shows only those students who have not taken the prereq. Lost... any pointers accepted!