Relationships

I'm not sure if this is possible, but it would be great if it is. I have 3 databases: projects, companies, people i want to set up a search function within the project database that allows me to search for relevant companies. NOT companies that i've manually linked up with a project through primary/foreign key, but rather those companies that match certain criteria. I want to build something so that when i'm in a project record i can say "i'm looking for companies of this size from this country" and then those companies would show up in a portal. is this possible? thanks for your help.

I am trying to figure out the best way to duplicate complex records... these have several portals in which related records are created, and when I duplicate, I want all that duplicated also. I have some ideas, but right now they all look pretty complicated. Anybody been there done that? (What I'm really after here is a way to copy a record when user selects an "edit" button, then present either the original or the copy to the user for editing... end result, I hope, is a way to switch back to the original version if necessary or just see what it was... I have an audit log field that works OK but doesn't capture the child data)

Somehow I

I have a customer purchase system for taking orders. The file contains an order table, line items table and a table that counts the quantities purchased from the line items table for inventory and reporting. There are additional reasons as to why I have this setup but those aren't pertinenet to this question. My question is, I have an Order Status field in the order table. I don't want to count those items in orders that are quoted, cancelled, etc. My problem is this: If I create a calc field in the line items table to give say a 1 or 0 to a complete order based on an IF statement - I cannot use that field as part of my relationship for the counter as it isn't i…

With RalphL's help, I've been able to write a script that uses GTRR (using the related records only check box) to then print out items using a different layout. Only problem is, there are many times during the day that the script returns no records, even though the layout has a portal showing the correct related records. Re-running the script does not help, exiting that layout and returning to the same layout and re-runing the script does not help either. Restarting FileMaker on the client and re-running the script returns the correct records. All Macs are running Mac OS X 10.3.9 and we are running the database on FileMaker Server 7.0.3 and each client…

I' working in the typical School system: Student-Enrollment-Courses

OK, I am so lost and don't know what to do but smash the computer (which I haven't, yet). I'm making 3 simple databases - Purchase Order, Inventory, and Suppliers Account. I have created a relationship to Purchase Order and Inventory, and another one to Inventory and Suppliers Account. I'd like to make the inventories as a main root, and contains both (Purchase Order and Suppliers Account) info (i.e. when it was purchased from what client, and when it was paid to what suppliers). However, I noticed that some fields are related and some are not (doesn't show up on the inventory form), even I made sure those fields are from the other databases (Purchase Order +…

Okay, I'm thinking there is a simple solution for this: I have two files: FilmTitles (holds film titles) Engagement (holds information like "open date" & "theatre") Here's what I would like a "browse" layout to look like: 1ST COLUMN: (left of page) 6/24/05 Sunset 5 Theatre Star Wars Beaches Monica 4 Theatre Madagascar Toy Story Bridget Jones 2nd COLUMN: (right of page) 7/1/05 Sunset 5 Theatre Scream 2 Charlie's Angels Shaft Monica 4 Theatre Tomb Raider NOTE: The text above is as follows: Dates and Th…

I have a table named Items and two fields in the table Stock_Number and glb_Stock_Number. I have defined a relationship between the two in the database as well (rel_Stock_Num). I fill in glb_Stock_Number with return delimited values, along with some other global fields that will be used to populate associated fields on the Items table. Once all the global fields are filled in with appropriate values I run a script to loop through the glb_Stock_Number values and update the associated records. here's the script Go to Related Record [show only related records; From table: "rel_Stock_Num"] Loop Set Field[rel_Stock_Num::field1; Items::glb_field1] Set Field[r…

I am trying to get our database to do something that I don't quite understand or know how to explain very well. Someone else set up the database and I am just trying to expand it for us. Here is my problem: I have three files, "Customers" "Inventory" and "Invoices." When I open "Invoices" and enter a customer name, their shipping and billing info drops in from "Customers". When I enter a SKU for a product, all the product info drops in from "Inventory." What I would like it to do is that when I type (in "Invoices") that a customer order three of say SKU 5-101, that the inventory count of that item will be subrtacted by three within the "Inventory" file. I …

Hi, Thanks for any help in advance.. my brain has fried so hope you may be of assistance. My quandry is as follows. I need to generate letters to be sent to each borrower, at each specified address. For example, if I have borrower 1 2 & 3 (B1, B2 and b3), and addresses A B C and X (AA, AB & AC and X) then I need to select which borrower can be found and which (mutliple) address with the security address being mandatory (X) i.e B1 has addresses AA, AB & AC (and X) B2 has addresses AA & AC (and X) B3 has addresses AB & AC (and X) I attach a file which I hope clarifies it a bit more. I have purposely created separate field…

Typical Order entry database with Orders, Order Line Items, Company (Customer) and such. Up until this point each customer could be associated with 0 to 1 Reps and the Rep was paid a set % at the order level. Recently things have changed slightly: 1) A customer may be represented by more than 1 Rep 2) The percent a Rep receives can change, a change in percent should be reflected on future orders only, older orders should not be affected. 3) The percent a Rep receives should be editable at the order level to account for 1 off deals. I have attached a sample database that shows the basic structure I started for adding more than one rep to a company. My issue i…

I have 2 separate databases and I need to create a "combined" db that would represent both dbs together. I created a database where all of the fields are pulled from the 2 separate databases, using relationships. When I add a new record in one of the separate databases I got it to add the record in the combined database, but I only got it to work once, but I never could get it to work for both databases. The combined db has approx 70 fields and each one is relational (:field). Is there something I am missing? Thanks,

I have a many to many relationship of contacts and companies. A company can have several contacts and contacts can be associated with multiple companies. Each contact has a contact type for each company it is associated with. It can be any number of types from the list, but each company can only have one primary contact. This data is stored in a field in the Join table. I want to have a field in the company table that identifies who the primary contact is. I've tried using the calculation: If ( PatternCount ( Company::Primary; "Primary" ); Contact::Full Name) but it only pulls the name sometimes. I think it only pulls the name if it's the first pe…

I'm trying to build a database where the user only has to access one table. Any modifications to other tables could be done through this "master" table via relationships, value lists (pop up menus) and the like. The reason for this is simple: I'm building a database for use by other members of my lab, and its purpose is to create requisitions (for use through our organization's purchasing scheme). I've mentioned this in another thread, at a stage where the database was being thought of in an entirely wrongheaded fashion, and was much further from conception than it is now. I also failed to elucidate what the database entailed. So, I figured I would give it another shot an…

I checked on my line items layout a couple of weeks ago and found that some of the information is missing, namely the details that are supposed to be looked up from the job sheet (name, company which job relates to) The Name and company info is there for some of the line items at the beginning so I figure I have done something which has upset the connection. I have been testing unsucessfully for a couple of weeks and I can't get this info to show up now. I have a one to many relationship from jobs to line items and also a one to many relationship from products to line items (products are showing up fine) Is there anything I can check or anything I am miss…

I have a database that has the following tables: contacts companies contactcompanyjoin (contacts can actually be associated with several companies) conferences registrations (a join between contacts and conferences) Contacts and Companies are joined through related ID fields in the contactcompanyjoin table. Contacts and Conferences are joined through related ID fields in the registrations table. Companies are related to a second occurence of the registrations table by both Company ID and Conference ID (so that I can see who is coming to each conference from each company in a portal on the Companies layout) Each Registration Record has a co…

Hello all my filemaker friends....I'm having some relationship/portal issues...let me explain: I have 3 databases: Projects, People, and Companies, each record in all 3 DBs have IDs to use as primary keys. I have a table that links all of these together called LINKS with the only fields being Project ID, Person ID and Company ID. The trouble is that the links i make are always between one project and one person, one person and one company or one company and one project. In the Project Database I have two portals: one that shows related people and the other that shows related companies. The problem is that when i make a link between a project and a person, the…

Hello, This is probably a newbie question that I should know the answer to! I have a calculated field that is serving as a match field for two tables. This match field is: Subject_ID & Initials & Visit_Date (Side note: In both tables, the Initials field is pulling from a different (third) table.) The match does not seem to be working - in the portal, I am getting data from the related table, but too much of it, as though the match field is not working properly. It seems to be matching only on the Subject_ID and not including Initials nor Visit_Date. Could the problem be that Initials is a related field to begin with, or perhaps it is a …

Post deleted by Joel C R

Is there any way to make data from a many-to-one relationship appear in a repeating field instead of a portal? I want to use the getrepetition function and it doesn't work on portal info (as far as I can tell at least)....

I have several tables that contain a field with the same "last name" and a field with a "serialized ID number" in each record. Also have linked them via relationship. How can I make all tables match up when I move between records. eg, if a record in table 1 is accessed via ID # or last name, can I force all other linked tables go to that ID # or last name with out manually manipulating each table? As it is, I have to manually adjust each table to that corresponding ID field or Last name field. Any suggestions?

I hope tha ti am in the place for this question, if not, Moderator please move it. I am trying to build a database for keeping track books sorted by subject. I use the LC (Library of Congress) CIP subject descriptions. The problem here is that there are an essentially unlimited number of subjects that are possible for each title. An example, for the book "Salvage Man: Edward Ellsberg and the US Navy" by John Alden the following subjects apply: Ellsberg, Edward, 1891- Marine engineers--United States--Biography. Salvage--United States--History--20th century. I have a file with the title and other relevant information already entered into it. The …

Wasn't sure which forum to post this in. I have a layout with 4 fields. 3 fields act sorta like grocery carts. The other field is a total field. The 3 fields have popup list that is created from records in another table (Product Table). In each record in the Product Table there is a field titled cost. This field shows a dollar amount. What I need to figure out is how to get the total field in the first layout to reflect the total cost of the items selected in the 3 fields. I hope this isn't too complicated. Any help you can give is much appreciated.

im getting soooo soo frustrated at this. i am desigin a program for the admin of a fanclub. I have one section that you can add each individual fan and their contact details as well as how much they owe etc then there is another table which has the price of membership field and extra costs for stuff. so what i want is for the admin to be able to enter what the price of subscription is in the management section and then be able to have it appear in 'total cost' box in the members details. I dont know what to relate to where and its just not working. I even tried adding the cost field onto the member layout...but that wont work either. someone please help me..

Hi, I am currently trying to design a jobs allocation database and am having trouble setting up a certain aspect, I have a jobs table with a drop down list of customers these customers need multiple clients for the multiple departments they have. I setup 3 tables the jobs table which has and relationship to the customer and then the customer table which has a relationship that allows creation of the contacts record i can create multiple contacts using a portal but when I select them in the drop down box I have created it overwrites the contact in the portal with the last contact I had selected until the same contact exists through all records and when I select a different…

Okay, I am stumped... This is an involved question, so hopfully I can ask the question is just one post. So, I have created a database solution for keeping track of people, their vehicles, addresses, etc... I have a table called "Participants." This table contains basic information on people. All other tables are prtty much related to the participant's record ID number. One of those related tables is a table called "vehicles_participant_relations." This is a table to link a participant with a vehicle within the "vehicles" tables. Here is a diagram of how the tables are related: So, in a layout when I click on my "participant" and then the sub-form o…

I have attached an illustration for a database I am making in Filemaker 7 I am relatively new to this program and understands the basics of FMPro ( I believe so ; ) In the attached illustration, I wanted to know which fields should I be linking in the relationships editor, whether my thought process as illustrated in the picture will work to implement the logic. Please Help !!

I have a main table that holds all he fields my database requires. I have several other tables in seperate files that hold the same data just in different chunks. I.E. client info, billing history, products. I would like the database to create the appropriate record in the related tables when i type the information into the main table fields. If you could just point me in the right direction I'd appreciate it, still kinda new to filemaker.

I need help with this database. What am I doing wrong too have these errors. The file is attached. Thank you. test.zip

Hi, I've been working on a small database for my company, a database that creates invoices and keeps track of them I have three files: one for Clients, one for Invoices and one for Services. In the Clients database i have data regarding clients. In the Services database is the data regarding the services offered and pricing. In the Invoices database it all comes together and u can create invoices for a client with the services you provided. Here's the problem. In Services, for example Service1 has not one but 3 prices (being a copy center, the price differes on how many copies a client gets). So Service1 has Price1 Price2 Price3 Service2 ha…

Really need some ideas to progress here. Have some very basic skills but just don't know what I should be trying to do. I have an existing customer database with names addresses and several layouts etc. I want a button on this to open up a new "quotes / orders database". I have created the quotes database separately and it works as a standalone. Once I press the button in the contacts database it should copy all the address details into a new record of the quotes database. I will also create a new "products database with product ID and description price etc". What I would like is with the new quote I have started is to type the first few letters of a prod…

I have been driving myself crazy trying to get my file to relate correctly. I'm trying to create a self-join with a field I also use to relate to a second file. I am trying to find duplicates. There should not be any duplicates at this time but my duplicates calc field is showing that I have some when I don't really. My match field is a concatenating calculation of 4 fields. I found recently that there was a size issue, but I thought I fixed it...maybe not. The 4 fields are: Drain,Fund,Account,Year. (this is a file storing beginning balances) My calc (text) is Drain & " - " & Fund & " - " & Account " - " & Year. An example would look like A0…

This is a more complex version of the problem of creating a relationship based on ranges of dates. I got a great solution from my last request for help on this problem, maybe I

Hello. This is probably quite simple but I have ODed on databases the last couple of days and I can no longer think straight! So I have clients, and the clients attend sessions, two tables, many to many I guess. When a new session is created, the session number increments. However, I would like each client's session to have an identifier to show which session number that individual is on. If I understand correctly, I need an intermediate table called clientsession. Is this called resolving? And if so, what are the various PKs and FKs? Many thanks Gerard

When I display a related field from the driving (parent) table it does not display upon record creation. I must enter a value into another field before the related field(s) will display, and even this behavior is inconsistent. Sometimes I have to go back to the previous layout and then return to the original layout for the related field to display. Any ideas?

This is kind of hard to explain, but I will do my best. I am working on a job tracking system. It allows me to track time entries and expense entries. I have a table for Job Information, one for Invoices, one for Time entries, and one fore expense entries. The Time Entries and Expense Entries are related to the Invoices table by the Job# and Inv#. This works very similarly to the Job Time Tracker that comes with FM7 Creative Starter Pack, except I have added expense tracking, thus adding another table. I am having trouble when it comes to generating a printable invoice. Because I have created time entries and expense entries in separate tables, I need two …

Current database config: Adding test result scores in database. Because of quesitonable data input errors, boss wants to make sure scores are correct - he wants to input scores again in another layout and then have FM7 compare the fields in the two layouts. I have 125 fields plus 125 more compare fields (i.e. testscore and testscoreC). Right now, it's working ok but I think there is a better way since I need to create compare fields for 12 more tests. Current solution: Layout #1 has the regular fields (testscore)- tester inputs scores in this layout. Layout #2 has compare fields (testscoreC)- boss inputs scores from same test in this layout. Button on la…

Hi, I enclose a copy of the relationships my simple database is using. I'm trying to work out how to put on my customers form "Customer X bought Y amount of Product Z" - if that makes sense? In Access, I would simply run a query: "SELECT * from customers, invoice_lineitems WHERE customers.id = 1 AND invoice_lineitems.CustomerID = 1 AND invoice_lineitems.prodid = 1" - or similar. Help! Many thanks, Dan.

I have a FMP7 database of voting data that now contains these tables and fields: tblVoteData: County, Location, Candidate, Votes tblPrecinctData: County, Location, Precinct, RegisteredVoters tblVoteData contains raw results for each election and now runs about 120,000 records. There is one record for each candidate in each location in each county. On the other hand, tblPrecinctData contains a set of fixed information listing the number of registered voters in each precinct within each voting location in each county. Voting locations will have one or more precincts. Here's what I want to do. I'd like to add a set of fields to tblPrecinctData for certain …

hey, i run fmpro7 for a list of photo files in a medium sized database. trying to view my photos individually is fine, so is the 3rd tab for the spreadsheet view. My problem is with the middle tab (the List View Tab), i click on the tab, and i get a single photo. that's all. instead of listing all the photos and dates etc.... where are all my photos at? how do i get them to reappear in this layout? any help on this would be thankful. patrick

I have a table with records of companies. some companies are related to others (like subsidiaries) and i want to be able to show this connection. i also want to show a connection between subsidiaries but don't want to be bogged down with manually inputting primary keys among 10 subsidiaries so that all the companies show up in each other's portals. any suggestions on how to proceed?

Although this problem is happening when I print a report from my "Items" table it has to do with relationships. I defined a report with a Grand Total and broke it down 4 ways as follows, destination, voyage number, container numbr and item. Each item has several records associated with it that are invoice line items. I am only totaling them up on each line instead of breaking the line items out individually. However, when I put a summary field in the Grand Total part (the same summary field used to sum the line items), it comes up with zero. The line items amounts reside in a separate table called "Charges", so i suspect that has something to do with that but I don't see …

I have a table

Hi, I am a novice, but have managed to create a very useful tool that I use daily. When I initially set up my database, I didn't understand the relationship concept, and created a separate table by copying infomation from the original table, rather than the traditional (correct) way... In other words, I have a table of 167k records, when I would find a record I wanted, I had created a button that would copy all the fields that I wanted, and paste them in another table. That table now has over 280 records in it. I did ask for advice on setting this up about a year ago, but didn't understand - and now I see the drawbacks. My question, I would like t…

I am registering individuals for a youth camp. I have one layout where each camper is identified by a field that tells what church they are with. I have another layout that will show the total campers / workers from each church and the appropriate costs. I need to figure out a way to make the "total camper" value on the church layout form to be automatically updated when a new camper record is entered onto the camper registration layout - but only when the "church name" field are a match on both layouts .... help!

On a single file with two tables, the first table becomes the default(current). How can I switch to the other table? If I create a =relationship between 2 tables, how can I add records into the related table via script? This done easily using 2 related files but I can't figure out how to do it on 2 related tables(on a single file). Any light would be highly appreciated. Thanks in advance, Garry

Using FM 7. I have two tables - Insureds and InsCompany. They are related by a text field "Carrier". Each insured record has one "Carrier" and the InsCompany table contains 275 "Carriers". When I try to add numeric fields from the InsCompany table to the Insureds table, I either get nothing or only a few instances of the correct information. The Carrier text in each field is exactly the same. Any ideas.

I am doing a job for a company that is locked into FMP v6. I have to make a relationship to records within a range of dates. I have done this by setting up a text field (tFindDate) and then running a script that Replaces the data in that field with 1 or 0 depending of whether the record is within the date range or not. The Replace formula looks like this: If ( Date > BeginDate and Date < EndDate, 1, 0 ) (Those signs are supposed to be "greater than or equal to" and "less than or equal to" signs, but those aren't part of basic ASCII so I can't use them) To call up all the related records within that range, I have a global link field that contains the…

I have a simple model: Bank Account Table and Interest Payments Table. I have linked them via the Bank Account ID Field. I have a layout based upon Interest Payments Table and a popup list based on Bank Account ID. No problem. However, when I try to insert the field Bank Account Name from the appropriate table although the table is shown as being related the field name just won't appear in the field box. Consequently the data isn't there either. What have I done wrong this time?

Is it possible to set up a relationship that would take a range of dates and show an issue number for that range? IE: range 5-2-2005 thru 5-9-2005 = issue 847 range 5-10-2005 thru 5-17-2005 = issue 848 if I have a table that includes the start and stop range for each issue I can set it up easy. problem is that I have article that are from each day in that range and not a good idea how to set up the relationship easily.

I am trying to create a database of all our computers that links a computer contents table to a software lists table(I would like to write a script that quieries the number of machines with a license and subtract the number from the total)I am not sure how to relate the two tables.

Once again my programmer's brain has no trouble with how to go about this using pseudocode or SQL statements, but get me into FMP7, which is still new to me, and I'm lost. I poked around the forums and found ways to simulate an OR equijoin, but I'm dealing with inequalities. I have two tables: Incident and Owner (there are more, but these two are sufficient for illustration). Incident records a problem with a computer, and has an OpenedDate field. Owner records an owner of a computer, and stores three fields relevant here: StartDate (when the owner received the computer), EndDate (when the owner turned it in), and Current (flags which ownership record is current for …

Hello, In Relational database systems like Oracle you have the ability to create a trigger. A trigger can be triggered to do something whenever the trigger is triggered. What is the equivalent of this in FM? My guess would be a Calculated field. If my guess is correct is there a down side to using a calculation in this way? I would guess this would also be a little limiting since a Calculated field only works on the same table - how would you triggger an update to be made to a different table? Along the same lines is there anyway to do a JOIN on two table to create a View of the two? So in this way you can include fields from more than one database on the same layo…

I'm not such a newbie but I have run into something that is totaling frustrating me. I've looked elsewhere in this forum and can't seem to find a similar situation. I am begining to develop a rudimentary student information system. I have 4 tables, (TO's) Teachers, Schools, Students, and Attendance. Attendance is the many2many table. Each table has a key ID field. But now to describe the problem. The TeacherTO has TeacherID & TeacherName. The SchoolTO has SchoolID & SchoolName. Each TO has a portal to hold the ID and name fields from the other TO. Currently I have one record in Schools and 3 records in Teachers. None of the records from either of the TO's popu…

Something strange happened. My data (2000 entries) were showing up in a portals but there were zero records when looking at another layout based on the same table. I copied it and hit recover and the data reappeared but another table (relatively small amount of data) is missing. What happened? Did this corrupt? Can I avoid this next time?

I'm working on a smallish database for taking inventories of various types of items. This is mostly just for my own use, both for convenience and for more practice with FileMaker Pro. Anyway, I have a number of tables created, with each table storing a specific category of item- one is for books, another for videos, etc. I went with separate tables since there are only a few common fields between the different tables. I have a single layout for each table for data entry and searching. Eventually, I'll likely add different layouts for doing different tasks. Anyway, what I'd like to create is a layout that lists the total cost of items on a single layout. For example, …

I am sure there is a simple way to do this but I am brand new (1.5 days) to filemaker. I have some experience with SQL Server and Access but no Filemaker. I am having a problem creating a composite key. What I want to do is have a table that does not allow data to be entered when the information in two seperate fields are the same. An example would be 3 tables-Class, Student, ClassMember. ClassMember would be the entity that matched up a student with a class but I want to prevent having records in ClassMember that have identical Student and Class information in it. I don't want to have multiple records with the same info in them. A student can be in many classes …

I have some tables to keep track of family informations: a parent table, a child table, and a mapping table that relates parents to children. Each have only one occurance in the relations graph. The relations graph has equal relations between the parent table's key field and the map's parent.key field, and the child table's key field and the map's child.key field. Both these relations have the "Allow creation of records" and "Delete related records" set on the map side of the relationship ... but when I add a new parent or child it does not add a family map record. Obviously I've made some terrible blunder or else I'm just not undertanding how those relations …

Hi Everyone, I am no programmer and would appreciate a bit of help from you gurus. I am designing a small wine product database in FM7 and have created 3 tables. Table1: Wine Table Field1: Wine description Field2: Wine year Field3: Wine Specification Field4: Wine Producer Fiedl5: Wine Region Filed6: Wine Province Filed7: Wine Logo (Container) Table2: Producer Table Field1: Producer Filed2: Producer Logo (container) Filed4: Region Table3: Locality Table Field1: Region Filed2: Province My problem is how to create the actual relationships. I want my "wine table" to use info from the "Locality Table" and the "Producer T…

Hello, I recently adapted a client's FileMaker 6 flat file database with 1700+ (and growing) fields in it to a relational design. In the old design, each record was a contact, and each contact had any number of projects associated with them. They spent most of their time on a layout where the basic contact info was at the top, and they would have their most relevant projects going down the side with project status going across. They'd change the projects they were looking at in layout mode, and simply reassign the fields as needed. When they needed new projects (which was often), they'd shut down the server and add them. I thought having 1700 fields and adding n…

I have a Products table with one field - StockOnHand (standard number). It is updated from Inventory after each day's shipments. I use a static field so displays are quicker - an aggregate was too slow. I have a TO of Invoices which identifies which invoices should ship that day (Invoices Ready To Ship). Problem: If we have insufficient stock to ship that day's Invoices (on any one product), we need to decide which orders to ship and which to flag as back order. I need to sum each product scheduled to ship that day then compare that sum against Products::StockOnHand and present Sales Manager with a list (portal or columnar list) of those invoices which contain…

Hi, I have a self joined relationship in a table for active employees. I created a global field called gActive and made it a calculation with "Active". I created the relationship "Self Active" and related it with my status field. The problem is when I try a GTRR command it is showing all records not just the active ones. How can this be? Did I miss something simple? I tried a button that simply says GTRR. Get related record from Self Active and clicked the Show only related records box but I still get all records. Someone help me before I pull the rest of my hair out! thanks

Is there a simple way to setup an OR relationship, i want to select records from a table based on one of 4 different variable (3 will be ORs and 1 will be an AND

Sorry for being so clueless everyone, but I promise i've come a long way in a short time. I've just gotten into relationships and portals and am trying to set something up that i think is an OR relationship. Some guidance would be fantastic! I'm attaching two shots of relationship graphs. Here's the deal: I have two tables: Projects and contacts (maindatabase). my first portal was very simple. if the company field in the contact database matched the firm field in the project database, then the portal listed the related records. great. But in the projects table, there is another field called "financial firm". i would like to make a portal so that if the…

I was wondering if someone could come up with fresh ideas to resource to client allocation over a span of dates. Multilinekeys created via repeating calc'fields or recursive definitions in custom funktions are great ways to avoid the join table in the first place. But if you need to pluck out a specific date in such a span and say cancel the appointment/booking, is the join table manditory - but they suck creation wise since they demand plugins to react accordingly to changes in the bookings ...and if the span is demanding 2000 records is it going to last a while until the cursor is given back to the user from the script the plugin pulls. Or is it really?? I've…

Hello everyone I am still feeling my way around 7 so be gentle with me. In FM5 I set up a constant by having a calc field set to 1 in each side of my relationship. I am obviously missing something in 7 cos when I have the same calc fields my relationship doesn't work ie I cannot display related data using the constant relationship. If however I change the match fields to simple fields (not calcs) with a 1 in each of them, hey presto, my relationship works fine. Can anyone guide me as to what I am missing or doing wrong TIA Phil

at my company i just inherited a 20,000 record contact database that i'm trying to make relational (in a many-to-many way). of course, no one ever gave primary keys to each record in this db. is there a way to assign unique primary keys retroactively? i've created a field with auto-enter serial, etc., but i need to avoid putting number in 1 by 1 by 1...

I wrote a bit about this before under the heading of Sums but the thread disappeared whenI uploaded a pdf of the relationship page. I obviously gave the server something it could not chew so it spat it out. I had a pay application form that was filled in using information from other files. With the conversion to 7, I seemed to lost the ability to use summaries from other files in my calculations. I have been reading the books and posts on here trying to find my way through this but to no avail. So, I politely knock at your door again. In the daytime, I work for a construction company and we are paid through a system of percentage of work done using activity num…

help! my databases have taken on a life of their own. I have a simple relationship set up between two tables: projects and contacts. One project can have many contacts involved and one contact can be involved in many projects. I have a portal in each table that displays the related records from each table. the key field is project name. in the contact table, when i indicate in the related field that the contact is involved in a specific project, then he magically shows up in the portal in the projects table. great so far. BUT, when i go to a second contact and indicate in the related field that he is involved in the same project as the first contact, all t…

Hi, I am having an issue where i created a relationship between two table ie. a parent table (1) and a link table (many). I mistakingly remove the link but now I can recreate it. I am getting the following error "There cannot be more than one relationship path between any two tables in the graph. Another occurence of one of the tables must be added to the graph....." Has anyone come across this problem or is this the way FMP is supposed to work? Is there any implecations with creating a TO? Are TO's the equivilent of views or alias and therefore is the data the same as what is in the table? a screen shot of the graph is attached i want to create a…

Hopefully I am in the right forum. At present I don't know if it is a layout issue, field definition problem but I suspect that it has something to do with the defined relationship. I have a database with two tables. A Main table and a categories table. In the categories table, I only have two fields,"Cat #" and "Description." I use these as a pop-up fields in the Data Entry layout. I also run a summary report that takes all the values for the month and lists them. The only problem is that only the "Cat#" field is displayed on the report and not the "Description." I need for the Description to be automatically show up for the number that is displayed in "Cat#."…

I'm finding that "Replace Field Contents", when chosen based on a field on a layout that is actually a related record, does not seem able to create related records. I'm trying to populate a Join table and it would seem that the easiest way to do this would be to (A) find all records without children, (: Replace Field Contents with the key field in the related table (which should create all the child records and populate them with the right key field). When I do this, I get the "Replacing field contents" dialog box, but nothing happens. I can do a work-around with a looping script using "Set Field Contents" and this works fine, but seems less elegant to me. …

Hello everyone I have spent a few days familiarising myself with 7 and I have been disciplined enough in that I have not just dived in there but have done a lot of reading about and peeking at 7. It looked really good and the more I read the more confident I became that I was doing the right thing - until I tried to do something productive with it Today I thought I better try one or two things out and blow me something that would have taken me 5 mins in 5 has taken all day and I still havn't achieved anything. I was trying to get used to the idea of context so I set up 2 tables Customer and Contact Customer has fields CustomerName and CustomerN…

Invoices are flagged with DocType = Sale if commissions should be considered (based upon items sold). They get a special commission if there have been no other invoices with DocType = Sale for that Customer within one year. The current invoice would qualify for special commission also if it's the very first order (and it's also DocType Sale) -- no prior invoices. I created a date calc which takes the Ship Date and subtracts one year. It's called 1YearAgo. I've created another copy of invoices called Commissions sorted on ShipDate descending and joined on: CustomerCode = CustomerCode DocType = DocType (Doc type only contains words SALE or is blank) 1Ye…

Hi, I'm pretty sure this is ok, but just want to verify with the experts. I have a field called Task Number, it looks something like this 2005-0021-0024 A project is related to a Task record and I have made the TaskID a composite of the Project ID, the project ID part is 2005-0021 and the taskID part is 0024 is it ok to create a calculated field, say called ProjectID_Calc, which is maybe Left(TaskID; 6) so the projectID_Calc for the above number will be 2005-0021 is there any harm or overhead/performance problems with doing that? it seems "mostly harmless", but i want to be sure. thanks for your help in advance. sincerely, j__

Not sure if this is the correct forum for this question, but... How can I ensure that a record has at least one related record? Example: I have a "Check" table, with a corresponding "CheckDetails" table (with a CheckID key). Each check can have multiple expense categories. However, some users occasionally forget to assign a category (or categories) for a check. How can I make sure that the category is created - at least one related record created? Many Thanks, Erik

I'm currently building a system that tracks healthcare information. The MDs enter data on one form for current data then it needs to populate a second form that maintains a historical record, oldest to newest so the MDs can view a record of values. The problem is that when we enter data on the "current" form, it overwrites the information on the historical record and doesn't maintain all the values entered. Is their an easy, pre made script that I'm overlooking for this? So basically, current information needs to display in one spot, and a historical record in another. IF anybody has ideas, I'd love to hear them!

Hi ! First of all im not so sure if Im doing it the correct way but I need some help if possible. im probably totally out of track.. I have 3 Tables, Contacts, Projects & Packages Now, Ive created my custom contact database , projects and packages, In each contact form , at the bottom i want a list of packages ive sent to them. On the packages database ive made a relationship to the projects table, so when im adding a new package to send i can choose from a pulldown menu the list of projects ive entered. Ive also created a list view to see a list of packages , Im just testing a scenario where like say each project gets sent to like 200 co…

Help: I cant figure out what I'm doing wrong. It's a simple solution but my simple mind wont let me get it to show all customers in the shed layout portal. I think the line Item TO is unnecessary. Please tell me what I did worng. Thank you again StorageSheds.zip

Okay, make that A LOT of issues! But here are the only ones I can talk about on this forum ... Lets say I create a file that has, say, two tables and they're linked together in a standard valid way, with the main table's related field being a serial index. If I place both of those fields in a record, shouldn't they display the SAME value? (They don't, the other field is blank.) If I add other fields to each table, create a record, then duplicate that record, shouldn't ALL of the fields duplicate? (Again, they don't.) I've read all about relationships in Filemaker, thought I understood it, and now have run into these amazing little twists. Help! N…

Please excuse the newbie dimwittedness but I have an idea for a database but am not sure where to start. I have already constructed a flat database for my business which keeps records of student records, contracts and licence expiries etc and I have to say I am chuffed to bits with FM as it is saving me loads of time and trouble. However, I also look after a pal's website [www.masnoell.com] together with the associated internet bookings which currently arrive via email, and I am convinced that FM is the way to go rather than the method currently used which is using 3rd Party online forms, html pages to show availability and excel spreadsheets to keep a track of …

I have a database and there are 3 tables that are contained within. These are kept separate due to the need of being able to lock different parts of the information at different times. The problem I am having is that if I have a layout that contains information from 3 different tables there seems to be a delay in moving between the fields (up to 2 seconds). These are all linked by a single common identifier The tables do contain a large amount of data (over 70,000 records per table, but I am looking to increase this up to 300,000) and they are served by a G5 across the network at 100Mbps This will start to become very cumbersome when entered data. Is t…

OK, I thought I had this whole relationship thing down but then I ran into this and can't figure it out. I have two files - "Models" and "Agencies". I want to be able to assign agencies to models in the Model database using a value list of agencies from the Agencies file. I have serial ID's setup in the Agencies database so each record has a unique key. I'm fine up to this point, but this is where it gets tricky. I want the data about the agencies to update automaticaly in the Models database when a change is made in order to not orphan records. Currently if I make a change to the agency name, the value list updates but the existing content of the field does not. …

howdy everybody! Trying to get a hang of this relationship thing and it's coming along sloooowly... I have 2 databases, one is a more complex one is a project database and the other is a simple contact database. When I enter the contact information for someone in the project database i want it to automatically create a new record in the contacts database (without making duplicates of course). I've created a relationship between the two tables (projects and contacts) with an "equals" relationship between the coresponding fields (first name, last name, email, etc.). I've also checked the box allowing for creation of new records in the contacts db. but when i e…

Folks Some time ago somebody posted a sample file that demonstrated how to create child related records using only auto-entered calculations, and no scripts. FMP 7. After spending 2 hours searching the fourms for it I've given up. Does anybody remember it? Thanks. Vaughan

Hello, I would like to know what to do to avoid duplication of records. I know I could do a find and if it is there, than I don't enter it again. However, it seems to me that there might be something more efficient that can be done. Thanks for providing this wonderful resource!

I have a many to many relationship between two main files (volunteers and clients) and in both of these files I use a portal containing data from a join file to move between the related records in the two main files. Because the links need to be created using drop down lists of the names of the people in the other file, I am using the volunteer name and Client name as the match fields. Naturally, when either of the names change (say I add a middle name at some later point, or someone gets married and changes their name) the link is immediately lost with the person/people in the other file. Is their any way that I can use recognisable data when choosing some…

Hi-- I have been running through the tutes and videos, I am getting a handle on relationships, and now I am getting ready to make the plunge. I am building a Job/Time tracking database, for multiple employees, multiple jobs, and multiple products. A job is a yearly iteration of a product, we publish yearly in most cases, so we have product X, job 2005. My first puzzler (for me, not the FM whizzes), not all jobs are divided into the same time periods. Most are published yearly, some quarterly, some monthly. Can a product be feb, Q2, 2005, while most are just 2005? Anyways the meat of my question has to do with relationships. I would like to track hours according to…

Hi, I have a jobticket database. On the jobticket database is a field for the supplier's name. I also have a 2nd database that holds all the suppliers details. I have a 3rd database for ordering. When I go to the ordering page, The name of the supplier is there (from the 1st DB). My problems is that I can't get the suppliers address (from the 2nd DB) to appear. I had this working in a previous version of FMP, but now I can't remember how to do it. Can someone give me some help please. gameboy1

Hi I would like to returns the contents of a RELATED field specified by number in a calculation. Like the last() function, which returns the last value in the related set, if it's not a repeating field, I would like to get a specified value by a number. Also like the GetRepetition(repeatingField;number), but with a related field instead of a repeating field. I've been searching for ages Thanx a lot in advance for any help or hints. I'm using OS X and FMP7 Dev

I would like to perform the following task: From my main entry form while entering data into JunkTO, I want to push a button and add a new record to a history table. The current record's JunkID should be used to auto-populate the JunkID in the history table. Here's the detail... << Italic = field, Bold = Table, Underline = TO >> JunkTO is over the table JunkTable and has a number field JunkID and a text field JunkDescription JunkHistoryTO is over the table JunkHistoryTable and has a number field JunkID and a date field JunkHistoryChanged The two TO's JunkID fields have an "=" relationship between them I have tried setting up a portal fiel…

I work in an academic dept in a university and I'm trying to recreate the database which lists the course options that students want to study next year. Our course structure has become much more complicated than before so manual checking of individual choices would be very difficult, and the students often make mistakes so their choices must be double-checked. There are various rules about option choices which I'd like the database to flag up if unfulfilled. For instance a few of the courses are scheduled to run at the same time so a student should only choose one of these courses. I'd like to set up some sort of calculation field that would flag up if a particular s…

Hello, Does anyone have any suggestions for resources regarding connecting two databases (patrons and books) so that when an item is checked out and the patron's barcode is scanned, the patron's information is automatically connected to the item and its barcode? We currently have two separate databases that are not connected, so when we check-out an item, we have the barcode of the patron scanned into the item's record, but the patron's record never shows a history of past or present items checked out. Therefore, we can't search a patron and find out which items s/he has or has had. Also, the item record only shows it's current status, is it possible to …

i thought i was well on my way to getting this database set up, but i guess i was wrong. i've read every tutorial i can find on setting up relationships between tables, but i guess none of it is sticking. when i input listings into my members layout, it should be creating new records in the listings table. it worked (sort of) for a while, but now the listingID field just keeps going back to one, even though the dialogue box says it should be a unique serial, up to 17 at this point. additionally, when i try to look at the listing records, it gives me some error message about ID fields not being defined in that layout. i have no idea why i'm getting this er…

I have two lab databases - a database of all strains currently in our lab ("lab db"), and a database of all strains that exist ("all db"). I've set up a relationship such that entering a strain name into "lab db" automatically enters information from "all db" into three different fields (call them A, B, and C). However, I need to import a file from our old db, and find that when I do, all information already entered in the A, B and C fields of the old db is lost upon import into the "lab db." [ie. if there is a match to a strain in "all db", then the info in fields A, B, and C is replaced. If there is no match, then fields A, B, and C are blank]. If I disable the rel…

Hello, Could you please give me a method to convert databases of FM6 to FM7, then integrate them into one database(the databases of FM6 become Tables of the database)? Thanks a lot!

I also posted this on the FMP7 General board, but have not gotten any responses. I'm somewhat new to FMP7. How do I make a TO that is a filter of my main table? I have a table for report cards (containing multiple terms). It contains fields such as student ID, course, grade, term, etc. I need to filter the table based on the term field and only want to show a particular term. How do I make an occurrance of just term 1 or just term 2 (ex. term = 1)? I want a layout to only show 1 term. Sorry if this is obvious, but this is my first database in FMP7. Thanks in advance!

I need advice on the best implementation on this scenario: I have a table of teachers, a table of classes, and a table of credentials. The class and credential tables do not contain the teacher name as I do not want it tied to the name making related records. What I want to be able to do is assign classes and credentials in a portal in the teacher info without creating records in the other tables. Basically a value list to choose the credential name and the information from the credential list gets imported, same with classes. Does this make sense? Thank you, it has been sometime since in FMP and any help is appreciated.

Say that a database has two tables "table_A" and "table_B". tabel_A has a summary field "table_A_records_counting" which counts the total number of records in Table_A. However, when I create a view based on table_B, and trying to show that count, it always shows up as 1 no matter how many records in table_A. it will count correctly if the view is based on table_A though... Please get a sample file from http://www.duke.edu/~jiehuang/jiehuang-test.fp7 it shows the problem. Please help!! Jie