Relationships

Hello all. I'm very new to Filemaker, and unfortunately I have been assigned a project to modify an existing FM6 file. Here's the breakdown. I'm going to have 3 files. One is Invoices, one is Customers, and one is Tax. I've created a relationship between Invoices and Customers, so that I can type in a customer number on the Invoices screen and have an auto-lookup to get the customer name, address, etc. No problem there. Now I need a way to take the City and Zip, and look up the tax rate in the Tax table. In itself, that might not be hard, but the kicker is I need to be able to account for misspellings in the City field, or some other strange happening, so that if …

Hi all, I have attached my database file with the hope someone can help me get over this problem. I am trying to develop a database which provides a report based on 3 tables which are linked together. I generate a report with sub summaries but It shows me a single field from the main table (Document) with records for each individual record in table 2 (section) which relate to the record in the main table, but doesn't then repeat for the next record in the main table OR show the records in the 2nd table as a list below the record from the main table. It doesn't show any records for the 3rd table. Is this a relationship problem with my tables. I know it mi…

Hello Everyone! I have a pretty good feeling about the answer being "NO" but I just wanted to double check with more experienced develpers. Does the Filemaker automatically enforce Referential Integrity between the related files? in FM words: 1------->many relationship FileA--->FileB autoID-->number How to(NOT scritped): If I create a record in FileA and want to "force user" to enter something in the portal (create a related record in FileB) before being able to continue? Many thanx for your thoughts!

hello Iam beginner but I managed to reach till generating reports I got stuck I need to generate Weekly report for invoices entered any help how I can do. I have searched on the net I got the result but I cant understand to a advance level if any one please help me Problem I have 3 tables and 1 Layout. first table stores all the values for invoice(each) like item and pricing, second table stores Customer information, Third, stores all item and prices for each item. After Creating the Relationship between the tables. I have created a Layout called "invoice". In which I managed all the fields works properly. Now I want to view and print repo…

Hi. I'm having trouble with a price tracking solution. I'm writing an Inventory DB that track price changes of a product. I have two tables one is the Tbl_Product table that has the particulars(including PrdRecID_pk is the serial# auto created) and a Tbl_Product_Price table that contains: EffectiveDate(of the price change);PrdRecID_fk;UnitCost;PriceRecID(serial) I have a One-to-many realtionship set between PrdRecID_pk --> PrdRecID_fk I've been trying to set a calculated field to (Tbl_Product::CurrentCOG) to contain the most current price based on the EffectDate. Here is the calculation: If ( LookupNext (Tbl_Product_Prices::EffectiveDate ;Higher) ; Tb…

I'm working on a relationship between two tables in the same file. One contains records of expenses, the other shows statistics of these expenses. Some of the fields in the expense file is: Username - text Year - number Month - number Type - text Description - text Cost size - number Sum cost size - statistic sum of cost size In the stats table some fields are: Username - text Year - number Month - number Type1 - text (global) Type2 - text (global) Type3 - text (global) Type4 - text (global) Type5 - text (global) In the type field in expenses you write food, petrol, rent etc. The type fields in the stats table is based on…

FM7, 2 tables, Clients and Jobs. When I create a new job for an existing client, I want to increment the Job Number for that client's jobs only. I want Client # 001-Job # 001, 2, 3, etc., then Client # 002-Job# 001, 2, 3, etc. So I need to find Max value for ONLY the related records, in order to add 1 to it. Script triggered from Clients layout will create the related (by client#) record, and assign the next highest job # FOR THAT CLIENT. How would YOU do it? Thanks!

This should be simple, but I can't figure it out. I'm developing with FM6, while it's being hosted (via FMServer on Linux) to users. The files I'm developing are being hosted, however they're not being opened by the startup script & therefore, not being accessed by other users. I've recently found that a new file (Elem_Class.fp5), that I created about 3 weeks ago, is being opened twice. I checked the file paths of both these files. The fist is the hosted file, the second is the file as it exists in the directory. I checked the relationships & value lists of the other related files, to make sure that 'relative' location is always, checked... it is. …

3 files: EVENTS, ALLNAMES & STAFFNAMES. Personnel data entered on EVENTS is staff names and other non-staff names. Am entering names in portal (with multiple rows) to create a file of all names in ALLNAMES, but want to staff names only to list onto a seperate file (STAFFNAMES) as well. Can I do this from one portal in EVENTS or do I need two (one for non-staff and another for staff)? And how can I get the staff name entry to create a new record in both STAFFNAMES and ALLNAMES files with a single entry in EVENTS. Hope that's clear.... feel like it should be simple but just can't get it to work.

I just need help doing a simple procedure. I need to create a sort of counter that will count how many times a serial number occurs... In a SEPERATE database file, and then pull the number into the other database - this needs to auto update everytime the number increases or decreases. Edit:: I REALLY need help with this please PLEASE help me!

Hi, I have 4 tables - People, Facilitators, Participants and Courses. The main table is People. People could be either Facilitators or Participants or both in many Courses. People and Participants are related by Client ID(primary key for People) and so is People and Facilitators. Participants(of a course i.e.) and Courses table are related by Course ID(primary key for Courses) and so are Facilitators and Courses. From the People table I need to get information of all the Courses that a "Client" has either participated or facilitated. I thought of adding 2 fields to People, FacilitatorCourseID and ParticipantCourseID(the CourseID from Facilitators table and Pa…

I have a related file that is opened when needed and is minimized upon startup. How do I prevent users from accessing it without having to put a password for that file?

Hi. Relatively new to version 7 so sorry if this doesn't make sense. When creating or editing a relationship in the graph, the table menus above the field lists are dimmed. So I cannot create a (say) in-depth relationship. In every available documentation, these menus are not dimmed. Could anyone explain the reason why? Thanx.

I need to generate a report. I am posting in this forum because I'm not quite sure where it would be more appropriate. In my database I have a core "clients" table. My other related tables look like this; "CLIENTS" > "FLEETS" > "UNITS" > "TRIPS" Each "unit" makes several trips during a reporting period. The reporting period data is stored in a table called "CLIENT QUARTERS". The "TRIP" table stores many records that contain the unit number, quarter, state that the trip took place, and how many miles were traveled in that state. I am trying to build a report that will show for a particular "CLIENT" "QUARTER" and "FLEET" a list of "UNITS…

Still a total newb to this, but I grabbed a pretty helpful book and I'm making some inroads. My big question is: Is it possible to draw a relationship between a table and a specific record? In my case, all the data for a real estate site is held in a record. This record is used to generate a slightly more complicated sheet full of demographics and the like, the template for which is held in another table. Would it be possible to relate that table just to one specific record (Say, 123 Nowhere Ave vs. 233 Here St.). I'm still in the planning stages, so I figured I'd check this out before I kept going on.

Please help with a very basic question? I have two databases for common customers (one with contact info and the other with specifics of their orders). How do I link them? I apologize for the probably simple question. Thank you.

Hi. I'm working on a complex solution in which I strangely meet a delay in a look-up. I've been able to reproduce it in a small file, please see attachment. We have 3 tables: People, Sessions and Jobs. We have 2 one-to-many relationships. Now we need to capture the age of people at time of each particular job. One session may last for weeks - thus age may change from job to job in one session. Age is then part of a multi criteria relationship (not reproduced in the attachment). To capture that age, we use a look up from People, which is 2 tables away. Here we have delays... Can you reproduce it in your system? Do I miss something in my db design? Too_bad_…

This might be hard to describe, but I have three tables. If you look at the attachment you will see the three tables in question, PO, JOB and TASK. The table between, Task_Join is a self relation ship to TASK. So, here is what I am trying to do. Jobs can have multiple tasks. POs can have multiple Jobs. So far easy, right? Now the fun begins. A job may cost more than the value of the PO, so some amount of the job is applied to a different PO. So, jobs can have multiple POs. But because potions of the tasks assigned to the JOB are paid with different POs, I have to keep track of which ones. So, the task has to be assigned to the PO as well. But POs can't have…

Hello how are you? I'm using Filemaker v5.5. I'm having trouble creating a lookup. I have two databases that I'm using. One (Customer) has information such as business name and address. What I'm wanting to do is create a lookup in the second database (Main) that will copy information from the first database (Customer). I've created a relationship field linking the two. I've define field (business name) in the second database (Main) to be a lookup field. But when I attempt to use that field (business name) the (Main) database it doesn't do anything. Please advise.

I have an ID that is used as the key in many tables. I want to find all records from one table where there is no corresponding record in another table where both have the same key field. Sorry for such a simple question.

I am looking to perform a find on a field in which I want to search on a date range. The relationship I have is a one to many. In the associated database, there are many dates associated with one record. What I had hoped to do was have a find field in the original database which was on the related field, and then zip over to the other database. The reason for needing to perform the search in the first database is a bit complicated so I wont' say here. The problem is that when I perform a find on that related field, I only get results on the first related record to be shown to me. Is there any way that I can get the find to work on all the associated records, or do I…

Hi Everyone, There seems to be a bug in FileMaker, where <> (a not equals filter...and probably others as well) does not work for parent > child > grandchild relationships. To reproduce the problem do the following: 1. Create the following 3 Tables: Parent (1 field) ParentID AutoEnter Serial Number, Unique Child (3 fields) ParentID Number ChildID AutoEnter Serial Number, Unique GrandChildFilter Number GrandChild (2 Fields) GrandChildID AutoEnter Serial Number, Unique ChildID Number 2. Then, create relationships between the three tables as follows: Parent <-> Child Parent::ParentID = Child::…

Hi. I'm rather new in this Filemaker but am very excited in its possibilities. Background: I have a prospects database. I want to filter the records by StateCategory using filterStateCategoryCriteria (global field). So, I created a self-join relationship between these 2 fields. StateCategory is a calc field dependant on State. It generates 3 categories - Lead, WIP & Product. I am trying out 3 layouts - Table, Form and Portal Problem 1: The Portal works but doesn't display the "State" correctly. Why? Problem 2: The Table & Form layout is giving me repeated records (about 7 times) and it's not filtering correctly. Why? I've taken …

I just upgraded to FM 7 and it's like I'm in a foreign land... I've got 2 tables: Fossils- fossil id; fossil name; location id Locations- location id; city; county; state; country I have a relationship (=) between Fossils::location id and Locations::location id Fossils::location id is a lookup to Locations::location id On my fossils layout I have the fields from the Fossils table and a portal containing the city, county, state and country fields from the Locations table. I want to be able to type the city, state, etc. into the portal, and then have the lookup copy the correct id into the Fossils::location id field. I want the lookup to be …

I related two databases and need to perform a lookup. When I hit Relookup, I get the error message "There are no fields that look up values based on the field "Ability1". What have I done wrong? Thanks, Thomas

Hi there, I am new to using multiple tables, relationships, and scripts in FMP, and have gotten myself to a point where I am too confused to know what to do! I am working on a database to catalog my various research materials, and need to make it easy to enter data and expand to great proportions while keeping things organized (I already have over 900 records). So far it looks like my best bet is to design the database in a hierarchical manner, but I'm not sure how to do this without being redundant or losing data. I have fields such as Country, TypeOfMaterial, and UniqueID that will be required for each of the records. From there, I need to enter specific in…

Hello everybody, I have a many to many relationship between papers and journalists. How can i define a many to many relationship in filemaker to solve this problem? I have now the repeatition problem of the information because the papers and the journalists are in one file. Then if in one peper i have 4 pepole working,then i have the information of that paper 4 times in the database,I want to solve it in a way that i have two apart files(tables) and a table to connect them. The third table include the keys of two another table(paper and journalist). Can anyone help me with this problem please? Thankx, Sima

I'm new to relational databases and even newer to FM7, so forgive me if this comes accross as a total newbie question. Here's the scenario: Two tables : 1. 'customer_details' (client ID, client name, address, etc.) 2. 'customer_contacts' (client ID, contact type, time & date, details) It's a one to many relationship. One 'customer_details' record could have many 'customer_contacts' records associated with it. Both tables have a common field 'client_ID', which is auto-entered by FM in the 'customer_details' table when a new record is created. Once a new customer record is created, there is button on the 'customer_details' form to take the…

So here's the story. I have a database containing "Client Activity"; it's related to another database which provides the client information. What I need is a way to produce a report for each month of activity of each client. The problem that I'm running into is that the relationships for my report are pulling all the records for a client (giving me all months, not just the one I need), or all the records for a month (giving me all clients for a month). Is there a way to hide looked-up records from another file besides the source file? I have a feeling I'm missing something really basic here.

When putting a field on a layout, FileMaker allows you to choose a global in an unrelated table, but when you go to browse mode, "unrelated table" appears. Adding an invalid relationship (between any two fields that link the databases, fixes the problem. Seems to me that this just clutters up the relationship graph by linking TOG's that don't need to be linked by any valid relationship. Am I missing something?

What I am trying to do is to keep track of owners of a piece of hardware. I have a Customer table and a Machine table and then a Machine_history table. What I would like to do is copy some of the data (customer name, address, city, state, phone, job number) from the Customer and Machine tables to equal fields in the History Table (or combined into a few fields with a readable layout) I need to keep the relationship between the Job number in the History table and the Machine table. How can I do this? I have tried a few things but they don't seem to work. Help Thanks

In FMP6 I had two related databases (goals and goal_bank) and used a relationship and to create a portal in goals that access information stored in a repeating field in goal_bank. I've tried to recreate the relationship in FMP7, now using two tables instead of two files. The relationship partially works - only the first entry from the repeating field shows up. If I reimport the records and split the repeating field into separate records, the relationship works. Has anyone else experienced this? I really want to keep the data as originally designed - it's simplest for the data entry.

I have a database for our Scholarship and within it there are four databases that talk to each other; Student, Donor, Scholarships and Dinner. We have a different database for each Fiscal year. I copied the FY04 database into an FY05 database and everything was fine, for a while. Then out of the blue it started communicating with the Fy04 database, when it wasn't open. So I went in to define the relationships and I saw that it was relating to FY04. I can only assume this was happening because when you copy a database you copy all of it's values and relationships? So I went in and changed the relationships to FY05 and now it will not share the information at all. So each d…

What is the best way to create a relationship that allows for values from one file to be displayed in another file. I don't want them to be associated with an ID or anything. For example, I need to define a relationship where I can create a drop down list that is generated from field values from another file and in order to make it useful to me, I would like another field to be automatically filled in depending on what was chosen from the drop down and this is where I need a Relationship. I just define two of any fields and it seems to work but I am not sure if this is best practice. Hope this makes sense. Thank you.

In my Invoice system addresses are stored in a separate Address table. The three addresses associated with an invoice are referenced by address ID. The relationship allows the creation of addresses from invoices. When you start entering an address FM creates a new address record, gives it an ID, and assigns the ID to the AddressID field in invoice. Nice. The problem that's cropping up is that sometimes the AddressIDs are getting messed up. A lot of them are just being set to "1", other times it's set to some other random ID that's being used by another invoice. That causes a lot of trouble because changing that address will change the address on another invoice…

Help, i'm really stuck!! Hope someone can help! I have created a many-to-many relationship between two files (Clients and Interests). The files and relationships look like this: CLIENTS: ClientID (Auto number), Email (Text), FName (Text), LName (Text) etc. Relationship: ClientInterests (ClientID::ClientID) INTERESTS: InterestID (Auto number), Interest (Text) Relationship: InterestedClients (InterestID::InterestID) RELATED: ClientID (Number), InterestID(Number), Client_In (Calculation - ClientInterests::Email), Interest_In (Calculation - InterestedClients::Interest) Relationships: Clients (ClientID::ClientID), Interests (InterestID::InterestID) …

I am developing an equipment checkout system for a university media lab. I have entered all the inventory info and much of the student info (each in a seperate file.) My problems relate to the file for the checkout forms. I think that the best way to keep track of our equipment would be to make a table called checkout with two main parts: the header, which will contain a student's contact info, and the body, which will include a list of the items they checkout. We will use a barcode scanner to check every item out. I would like to have the real name of item appear in a seperate part of the layout but I'm unsure how to do this. For now I just have one field named "items ou…

I have 2 files 1 contact 1 organisation In the contacts file I have a calculation (unstored) or CONTACTID & "

Hello, I am fairly certain this has been answered in other questions but I was unable to apply it to my question, so please bear with me. I am using filemaker pro. Here is the situation. I gave 5 different surveys to a bunch people once a week for a bunch of weeks. Right now each survey has its own file with all the people and all the visits in each file. So in each file there is a person ID and a visit ID. Now I am trying to get all the data into one layout. My thinking is that I would like tell FMP to give me (person1, 2,

I have a table of people, for a fundraising db, and a table of events. Each event needs to be tied to a person, as it contains their payment info, etc. There need to be many events per person. I want to have a layout that displays all events per person. I am planning on using a portal for this. However, I can't create new events on that page, as they need to be looked up. I did this by creating a field on the Events table which is joined to the Person serial. On my layout for creating an Event, how can I get that Person serial? I have a choose Person field, created by a value list that looks that the People field and references a Full Name calculation…

Ok. I have two types of Customer records, field TYPE containing 'Invididual' and 'Company' which is a Value List called TYPE. I want to give people a global text on a layout to the same table. I want three things in it as a radio button: Individual, Company, All and I want a self portal to adjust accordingly. I can do the equal - put Individual in field and have it match (=) a new TO related on Type. Same with Company. But when I select All, I have nothing to relate it to. I want to use the 'X' here I think, but I'm unsure how to make this work the best. Do I need a calculation? And my other problem is that the 'All' isn't in my existing Type Value List so …

Apologies if my questions seem confusing - seems I only post questions when I'm confused, otherwise I wouldn't need to post. I have file with two fields: CompanyName and IndividualName. Only ONE should ever be filled in (and this fills in the CustomerName with auto-enter replace). I've tried validation and it works, but staff puts the persons name in Company because they think, because it's a Customer, that's where it goes. I have decided to try to help them by forcing them first to select whether it is a company or an individual and only presenting that field for them. Otherwise, they try to also put an employee in the Individual field, which is wrong (I have re…

Hi. I am new to all of this although I have been using filemaker for a long time. I have mainly used it for just inputing my friends contacts etc. I think filemaker is pretty awesome, however alot of manuals don't help much to creating something that I really need. I'll try to explain to the best of my knowledge, and if I can have this created, I wouldn't have a problem in sending it to who ever wants it. Currently I have many fields, with photos of my friends, and all the information as address, numbers, birth dates, height weight etc. I have created 7 tabs, a layout for each, which are. Personal, Contact details, Photo, Information, Messages (TXT), Email Mess…

Greetings, I am a long time user of Filemaker (4, 5, 6) and I have recently started to work in FM7. The changes in relational concepts, namely the TOs and TOGs, have me all turned around. A link to a good resource to get a solid understanding of how to properly organise TO's in TOGs would be appreciated. I am currently working on a system in which the data would be seperated from the interface. In order to get familiar with the concept I examined the New Century Data seperation model (download it here) and I now have a couple of questions: On the NewCenturyData data seperation model exemple, the TO's are linked in the data file in a way that resembles most…

I have an invoice type file with a customer code field that is related to a customer file. When you enter the customer code it should lookup the customer info and put it in the invoice. Entering the code doesn't seem to generate an automatic lookup in the form. In V5.5, everything worked perfectly... Any ideas on what may be happening or any workarounds? Thank you,

Ok, working with the plug in for FM 7 called Business Tracker and am manipulaing it to my companies needs. There's alot of changing to be done and TONS of relations already set. If I change a name of a field that is link to something else, is file maker smart enough to know that has happened and adjust the relation linking to said field?

This is probably simple, but I can't seem to figure it out. I'm creating a gradebook. I have four classes of about 30 students each semester and each class has a unique set of about 60 "assignments" (which could be quizzes, homework, exams, lab, etc.). My goal is to make my gradebook.fm7 table contain a record for each grade. Each record would show the student, the class, the assignment type, the assignment number and the score for that assignment. Here's what I have so far: I have a table of all students that can be filtered by a relationship to show a specific class. I have a second table of all assignments that can be filtered to show a specific class, a…

I hope I can get some help on this one before I book myself in for psychiatric treatment! I also hope I am not repeating any questions already asked by others but I couldn't find any threads that relate specifically to what I require. I have been using a database in FM6 which had two files (among others) called Company and the other called Individual. The Individual records were accessed via a portal in a layout on the Company database. The common identifier was a field called CompanyID. This worked OK but as a very basic relationship it had significant limitations. Almost all of the user interface operated from within the Company Database and as bad design wou…

I have a numeric field A set by lookup via relationship X. This field A also acts as the left hand side of another relationship, Y. This much works fine. But if the value of A is relooked up via X, relationship Y does not update until I exit the record and go back to it. But if I set A manually, Y updates as soon as I leave field A. How can I get Y to update when A is reset by the lookup without having to leave the field? The weird thing is, I have this same situation working in another file and it works fine. I can't figure out what the problem is. This seems to be my main area of confusion using FM 7. Can anyone explain what things will update right…

Is there a way to force Goto Related Records to show all related records even when some or all of them are not in the found set. I'm trying to show all the line items in a 1-Many relationship. I use Omit in a script and some of the line items are omitted, so goto related doesn't show them. The only solution I can think of is to switch to the line items layout at the beginning of the script and do a show all.

I have a simple one-to-many bibliography db: one author, many titles. I am unable to recreate in FileMaker what seemed transparent in MS Access. I created with no problem a layout for data entry listed by author with a portal for title(s). I am trying to create a report by author that will list all the authors with all titles. I followed the wizard for columnar layout with title sub-summary. What I get is one author on each page, with only the first title in each author's list. Likewise, if I do a find for any of the title fields, it returns the first title only (although the second or third title holds the searched for information). Obviously I am …

I have a question with relation to how lookups work in FMP. I've looked through many books and FMP online help to find my answer, but alas, neither go into great enough detail on the subject. Perhaps someone here can be of help. First, let me state the obvious, as I know it: If I want to fill a field with data in one DB, with the data currently entered in a corresponding field in another DB, I assign the field to be filled with a lookup, and relate it to the other DB with a defined relationship (relating through another field that the two share in common). But, where does the data that is filled in the lookup field come from? From the related DB? …

I have a field which is related to another file with field that contain serials separated by "

Hi All, Can I ask if anybody knows if it's possible to embed a portal within another portal e.g. db A holds 1 document with one to many relationship to db B for each entry inside db B there is a one to many with db C [ab A[db B[db C]]] [----[----[db C]]] [----[db B[db C]]] [----[----[db C]]] [----[----[db C]]] [----[db B[db C]]] Have you got any suggestions? Best Giles

I've made a very basic database with a very large amount of entries. From what I've done, I want to create two "spreadsheets" that display the the different values of two variables. For example, I simply want an ongoing list of all of the different entries for a field that I've titled "Authors." In attempting to do this, I tried creating a table ("authors") with a relationship to my initial database (something to the effect of "author"="author"). I have created this relationship and I want to automatically create records in my "Authors" table from my database, but I can't figure out how to do this. I've tried defining the field in my author table as the "author" values fr…

I am continuing on my quest of a single file solution and have a products table with which I need to do conditional value lists on several fields. The fields are as follows: Class, Manufacturer, Model, SerialNr, Category, Subcategory, ActiveStatus and ProductName In FM v5.5 I built relationships starting with a Keyfield and compared it to a global field for conditional relationships KeyClass=Class KeyClassManuf=KeyClass & Manuf KeyClassManufModel=KeyClassManuf & Model etc... same method with globals gClass=Class gClassManuf=gClass & Manuf gClassManufModel=gClassManuf & Model etc... then I built the relationships gCl…

This seems like it should be obvious, but it's not clear how I would go about defining the relationships to accomplish the following: I have a list of 200 or so numbered Functional Requirements. I have 50 Technical Specifications documents, each one addressing any number of the 200 Functional Requirements. I need to create a relationship whereby I can enter a Functional Requirement ID and see a listing of the Technical Specifications documents that address it. (There will be many Techical Specifications documents from the pool of 50 that address a particular Functional Requirement.) I also need to be able to enter a Technical Specification document number…

I have a FM Pro 5 database where if I type in the main client number field it is suppose to return info in several lookup fields for name and address. It works fine when I type some client numbers and then on some it doesn't work at all. I'm not sure why because the numbers do exist in the database that the relationship is with. Has anyone seen this before?

I have a sales database that I have written for my business (cellular phones) and have a question. When I type in the customers mobile phone number on a new record I would like a field to display the last date this mobile number was used. I have created a relationship Mobile number (to) mobile number and display the creation date but it always shows the oldest record date not the last one. How to I solve this problem. I hope I have described this clearly! thanks for any help

I created an invoice db with these files: invoice (with portal to line items) invoice

I have a file "Currency Rates" which contains three fields: Currency, Rate, Starting Date. It looks approximately so: EUR 1,5 01-Jan-2004 US 1 01-Jan-2004 EUR 1,4 01-Mar-2004 EUR 1,6 05-Jun-2004 ... There Is other file "Price-List". Fields: ItemKey, NativePrice, Rate Date, Currency, Rate, Currency Price (=NativePrice*Rate). Currency are related. I want, that in this file at a choice of currency at me the prices at the rate from a file " Currency rates " were recalculated. It not a problem. But, I as want, that I could receive a PriceList for any date. I.e., if I choose for currency EUR date 10-Mar-2004 the r…

Hey, Forum Members: I

Hi, I'm looking for a way of creating a value list of [record Numbers] that appear in a portal, for use in a calculation field. Can you help? Thanks Giles

Dear Forum Members: I

I have : 1 contact file 1 company file which is a many - to - many relationship. My problem is this: If fred is related to company 123 and company abc and at each of these companies he has a different email address. So what I need is a file that is related to both the company and the contact in order to display the different emails? Can anyone help me on how this relationship is established? I started by creating a new file Which will contain both an ID for the contact and the company but how to a relate this to the contact file so that clicking on different companies in a portal they will display the related contact details? …

I have a table Dogs, with one entry for each dog. I have one table for Vet Visits and one table for Shot Records. These 2 tables have a lookup for dog name in the Dog table. As far as I can see when I compare these 2 tables things look the same. The problem is, for the Vet Visits, on the dog name field, the list of dog names pops up as I wish, BUT on entering data in the Shot Records, the dog names will NOT pop up. I'm stumped. If someone wants I can send screen shots of the questioned fields, definitions and relationships. Thanks.

If I have a table with ID1 and ID2 fields that match the ID field in another table, can I have two relationships going where each ID refers to a different record? Something where I can get ID1->Name or ID2->Name. Initial experiments aren't yielding an obvious answer. Is such a desire too complex for the relationship system in Filemaker?

I have files A and B and a relationship in A to access global fields in B. The relationship is between gA and gB, two global text fields. I've done this many times. This time it is not working. From A I can't read the global fields in B. The files are not corrupt. I've tried to create another relationship between the same fields but it is not working too. All the other relationships (between standard or calculated fields) work fine. All the other global-to-global relationships in the other files of the solution work fine. Any idea? Thanks Paolo

One of the items many of us wished for in new versions of FM was multiple comparators. Now that the wish was granted I was hoping for more. In developing a relationship for a value list I filter my people table by the fields STATUS and CATEGORY. Where Status is Yes or no, and categories are Employee, Vendor, Contact, etc. I can use multiple comparators of Status and Category but I first need to build a global field and define them as gYes="yes" and gCategory="employee". Why did FileMaker not make it available in the relationship comparator dialog box to go ahead and define Status="yes" and category="employee"? It seems like a very natural place to do it, an…

Hi, I'm stumped in FM7 on one thing! I have a database which has a "product listing". You can create a product in the database giving it a description and catagory. Auto serialised product code. Then in a portal you can enter in a "purchase of new stock" related to the product code stating quantity purchased, buy price, retail price and where you bought it. In the "purchase new stock" database I made a relationship based on the product code and purchase number to ensure I'm looking at the correct stock of any item. The relationship's other end was the sales database - I then created a sum(sales db::quantity) calculationship to keep track on how many…

I know that using modifiable data as the relationship key between tabls is a no-no. Here is what I want... Table A with one or more ID fields that relate to table B. In story-problem-speak, let's say table A is an event and table B are participant names and each record in table A can have, say, three particpants. So far so good, but the trick is to build a layout with three layout-fields that each display a specific piece of data from the related table B, but not the ID number. I want the related piece of data instead. It would be nice to have the layout use a drop-down listbox to select entries from table B and when selected, it would update the appropriat…

I have two tables related to this question: "Main" & "Tracking" Main Table fields: MainID Tracking Table fields: Rel_MainID Glb_MainID TrackingID Relationship Used: Main::MainID --> Tracking::Rel_MainID Script Used: Set Field [Tracking::Glb_mainID, Main::MainID] Set Field [Tracking::Rel_MainID, Tracking::Glb_MainID] What I want to do is make a script that when is run will add a related page to tracking for the main page i am on. This works very well see script used. But I run into trouble when I already have a related page for the main page I am on...and I run this script and it adds another related page... What I…

I've got a direct relationship based on four fields. The relationship is valid after I fill in all four fields, but related fields on the layout do not fill in immediately, not even when I exit the record. However, they do fill in if I close the file and open it again. There are other circumstances where the fields show their proper data, although I haven't isolated exactly when this is. For instance, while I was investigating the fields and relationships, when I went back to the layout, the fields were filled in. Has anyone else experienced this? I'm using FileMaker Developer 7.0v2, but the behavior is duplicated in Pro. Thanks, Chuck

hi there.! I have 3 tables 1) Sounds table 2) sounds keys 3) lineitem Every new record in "sounds table" is actually a new sound with children a few key-words like "Organ", Violin" "Horn" These Key-words are records in sounds keys When I make a new sound (in sounds table) I feel a portal with some keywords. this portal is from line items table My line item table has 2 fields Sound_ID and Word_Key_ID I want to fill 5-10 temporary fields with keyWords and show the found sounds in a portal Of course I don't want a sound appears more than ones. For examble If I have feel 3 fields with "Flute", "Melodic"& "Country" I want (in a p…

I posted this question in the FM7 forum, and am reposting it here where it probably belongs. Two tables: 1) People - Person ID (auto-serial), Name 2) Places - Place ID (auto-serial), Person ID, Place Name A portal on a People layout contains a list of all places the person has visited, where (People::Person ID = Places::Person ID). Person #1 has visited Chicago, Seattle, Chicago, New York, Seattle. Person #2 has visited Seattle, Fresno, Seattle, Seattle, Fresno Is it possible to make a relationship for a portal on the People layout that condenses similarly visited places? I.E., Person #1 sees "Chicago, Seattle, New York", while Person #2 sees "S…

I am having trouble setting up relationships which will be used in SetField script steps in a Lotto number picking solution. Only the relevant fields to the problem I'm having are listed below: Two Tables: History (complete historical lotto results - currently 367 records) Fields: Ball1 (the number drawn for Ball1) Ball2 (the number drawn for Ball2) Ball3 (the number drawn for Ball3) Ball4 (the number drawn for Ball4) Ball5 (the number drawn for Ball5) Ball6 (the number drawn for Ball6) BonusBall Frequency (49 records, 1 for each ball number) Fields number (the ball number) win_hits bonus_hits A script imports a data file…

I'm not finding the exact answer to my question in the posts I've read so please forgive me if I'm repeating a question. I need to create a record in another table from a current table behind the scenes (the user doesn't see and get confused. ) I want the computer to create it because there needs to be a relationship inherently made. (not relying on the user) In 6, I put a script in table 2 named "New Table 2 Record" consisting of the command "New Record/Request". In table 1 I put a script "Create New Table 2 Record". In "Create New Table 2 Record", I would set "g_Table 1 ID" with "Table 1 ID", call "Table 2: New Table 2 Record" Then set "Table 2…

Hi, I have started on project I imagined to be not to difficult, but have become stumped. Was wondering if anyone would be willing to share ideas on the basic relational structure that I would need to complete this project. It is a member database with a basic table having fields for the member info: primaryID, name, address, phone, etc. Then I need a forms database to hold dozens of forms that can be associated with a member who requires a particular form. The forms associated with the member will need to be archival, that is a permanent record. Some of the data for the forms will be pulled from the member table, other data will be unique to…

Alright I have a many-to-many relationship between two tables, tblOrganization and tblTypeOfOrg...I have followed the help file on creating a look-up table between them which i called tblTypeToOrg. tblOrganization has an OrgId as PK and tblTypeOfOrg has a TypeId as PK. TypeToOrg has both OrgId and TypeId and i have set up all the relations between the tables accordingly. In the layout for my Organization i have a check box list pulling its list from the tblTypeOfOrg table. I am storing this in the tblOrganization::tblTypeToOrg typeId field. The problem is when multiple check boxes are selected instead of creating a new record for each marked box, filemaker is simple conca…

I have 2 tables and want to make a summary layout. One table is a list of character symbols and their expanded names. FRED -> Fred Barnes BLUE -> BlueBird LAKE -> LakePark The Second Table is a list of reported sightings. Fred saw a BlueBird at LakePark 3 times FRED BLUE LAKE 3 I want to make a summary layout. I don't care about FRED, just the bluebird's total number of sightings. So the result is found by looking through the 2nd table, adding up all of the times any BLUE was sighted, and SUM() into a field in a new Layout. Q. When I add a new field to a layout, the field has to represent a field in a Table, yes? So I de…

Newbie question again, sorry. Is there a way to search in a field and have it search all related fields (in the same file) as well?

As I try to document my work I'm not able to find the mechanism to print or even copy the fm relationships defined (other than a pen). Am I missing something or is there a simple script that could print them all out by db ? Jonathan

I have a workstations database and a peripherals database. The relationship is based on a computer serial # which is a text field. I have 2 workstations that have the following serial #'s: YM339WNQNVS & YM339WFLNVS. As you can see, these are not the same serial #'s. I have a portal in the workstations database that allows users to assign peripherals to a workstation. When we assign a peripheral to the first serial #, it also shows up in the portal for the second serial #. To troubleshoot this, I started deleted characters off the end of the second serial number. I got all the way down to YM33 before the peripheral no longer showed up for the second workstation…

I have attached the file I am having trouble with. I want the "Quote" to display the part number and store it in "Quote" file. I have tried about everything. I am missing a step, or just cant see the forrest for the trees. Use the password "n7236y" to open the file. cssinv.zip

Would like to be able to create a record in a table based on a related record. I'll try to explain things as best I can. This is an inventory database. I have a table for CPUs and one for displays. Both tables represent 1 workstation. In the CPU table the field for workstation is an auto-enter serial number. I want to enter info for a CPU that can be either a desktop or a notebook. Then if the workstation has the need for a display record I want to click on a button to take me to the display table/layout related record and automatically create a new record for that workstation if there is not one already present. The CPU and Display tables have a Workstat…

I know the drill ... Keep all Customer data within the Customer table, unless it is 1:n (one-to-many). But do you ever split a table just to make your life easier and the table more manageable? Our current design in 7 is hitting the 300-field mark (before any calculations or globals) and, wading through it is already tiring. There are no two 'like' fields within it but I have already gathered a few logical groups, ie, Credit information containing bank info, credit rating, FedID, OwnerOfRecord, etc - 45 fields); or Business properties, ie, sq. ft of store, # of employees, EndCap requirements - another 30). I see absolutely no problem or additional work to creat…

As a new user of FM7 I am having trouble relating a file. I have a file called "quote" we open to process quotes on parts. I want to be able to search the Master Inventory file from the quote sheet and entert a part no and retrieve the data and store it in quote file. I have tried lookups and finds to no avail. What am I missing here?

I have a pseudo object-oriented inventory setup in FM7. There's a base Products table, and then tables for specific types of products that "extend" the Products table. The tables are related through an ID. This causes all the sub-tables to be related to each other through the Products table. They really shouldn't be related, but I'm not sure if it's a problem at all. No product will even exist in more than one of the sub-tables, so even though there's a relationship defined, there won't be any data that actually establishes it. The only problem this has caused so far is pretty minor. I have some extra tables holding attributes of certain products, things like co…

In the good old days of FM6, I would have used a script in the second file to facilitate this. However I'm now trying to figure it out in FM7. Attached is a simple db with a few tables. Essentially there is a list of trades and services that can be selected. This is in a seperate file as apposed to an included table. Upon selecting the category of trade and service, I wish to click on the trade, and have that copied (not just referenced) to the portal above in a new record. Any suggestions? OPENME.zip

Building a db for my girlfriend who runs a cleaning company. Set up a simple client contact db (clientContacts) with a portal that lets a client have more than one cleaning address (CareProperties , address etc) . Each client has a unique number . Each cleaning address had a unique number (House Id) displayed in portal. Heres where i get stuck . I wnated to add a button to the portal so you could goto a new db (housedetails) based on the house Id, where you could add/change/view further details about whats to be cleaned , when etc. I can add a button fine but not sure how to relate the house Id in the portal to a new record in housedetails Thanks in advanc…

I want to change the relationship in a file. it means creating new fiels in both the parent and child records. Does anyone know of an automatic way to get the related files to take on the the new related ID? thanks

I have two related tables where each record in table A has exactly two related records in table B. Is there a way to script table A such that whenever a new record is created, two related records are simultaniously created in table B? I would rather not have to rely on buttons to start scripts, etc. if I can. FM makes it easy to automatically delete related records but it's the creation I'm struggling with. There is much more to this solution than I am explaining but this is the sticking point in my progress. Thanks!!!

(I have searched other posting to find the answer to this, but I haven't found one that really matches what I am attempting. If the answer lies elsewhere, please aim me in the right direction.) I am attempting to create a "system" of databases for the purpose of collecting student scores. I envision one database that holds of the students (we're talking about 3000 or so), including FName, LName, ID#. Then, I'd like to have separate databases to hold science, social studies, etc. scores. (In all, there would be six tables; one master, and five related.) My question is how do I set them up so that I don't have to import all 3000 students into each of the five …

I am converting my FM 5 files into one self contained file. They work fine as external files but when I combine into a single file solution they need some tweaking. I have a people table which contains many fields of which one is STATUS for being currently employed and another field of CATEGORY. I want to develop a value list in which the contents of the peopel table is filtered by STATUS=YES and CATEGORY=EMPLOYEE. in previous FM versions I had made a Key concatentation of Category + Status and compared this to a defined global field in the file = "EmployeeYes". Once they match the value list is populated. Employee is a field value defined to be the current…

I have a global calc field, gA, in Table I. Its result is based on another another global field, gB. Table I has 0 records -- it just holds the global values. I have a layout based on a second table, Table II. (Table II is in another file, but that shouldn't matter.) On this layout I want to use the portal visibility technique to show objects based on gA, so that if gA=1 the portal is visible, otherwise it is hidden. Table II has a field C, which is an auto-entered text field with a constant 1. I've created a relationship between gA and C, and placed a portal based on this relationship on the layout. So, if gA=1 and C=1 (by definition) the portal shoul…

Dear Listmembers, I need your help. I am new in the FM and I am lost with this problem... I am making now a database for Art History Slide Archive where all the slides are sorted by their present location. I wish to do the following (and cannot find the solution): 1) First List - Country (choose one) 2) Second List - City - in this particular country (this list should change itself accordingly to the country chosen in the First List) 3) Third List - Collection - with all existing collections from the City chosen from the Second List (this list should change itself accordingly to the City chosen in the Second List). Meanwhile I've got hundreds of names for…

Right now I am just messing around with the trial version of FM7 to see if it is suitable for a project I will be working on. I have made a little Student, Teacher, Course database to play with. I have five tables right now. The Student table which contains a Student ID, first name, last name, etc. The Teacher table which contains a Teacher ID, first name, last name, etc. The Course table which contains a Course ID, course title, course description, etc. There are also two join tables. The first is StudentToCourse which joins the Student table and Course table by ID, the second is the TeacherToCourse which joins the Teach table and Course table by ID. I have…

Thanks for helping out! First post, so here goes... Using a list of contacts with a unique identifier, I also have other databases that have some of the contacts present in them, having the same unique identifier. The relationships are set through the serial number (unique Identifier). From the contact database, which has all the contacts, when I ask filemaker to grab and display some information from the other database, it works fine, but only when the other database has a contact with the unique identifier. If there is no contact with the unique identifier from the first database, filemaker displays the last record that I was viewing. So when there is a recor…

Hello and thank you for your help! I am at a block with this, and could really use the help! So.. I am creating a Reports database in filemaker 6. I have created the first report in the "Reports" database which reports on invoices created in a file called "Invoices". These invoices are created in the file "Invoices" but use a portal to record the information ordered in each invoice that is stored in the file "Reports". So, The problem is this. I have made a relationship for this first Daily report , which relates "Reports" and "Invoices", this relationship equals the orderdate (in "Invoices") to a field called datecalc (in "Reports") (…

I have a relationship of files that are related and clicking in between relationships that exist is fine. However if the relationship doesnt exist how can I flag this up and ask the user if they want to create a new related record or add this number to an existing one? Thanks Simon