Relationships

Please excuse this probably rather easy problem for you experienced users. I don't use FMPro a lot and have never figured out a solution to this problem. I have two related files. One file is called Inventory and the other file is called Sales. The related fields are Inventory Number, Date Sold, and Sold For (dollar amount). In the Inventory file Date Sold and Sold For always relate to only one item. In the Sales file Date Sold and Sold For could describe one inventory item or many inventory items being sold as a group. The Sales file uses the field Inventory Number to lookup the Date Sold and Sold For data and place the data in the Inventory …

i am trying to link two tables where one ID field information "is contained" in the text field of another table. for instance, a company has reviewed information in another record and I want to have a history list of who has seen this info. the history field is a long string of continous information that contains the company name and ID. I want to have a portal that list the names of these companies based on a relationship that "finds" all the instances of the matching ID in the long string. any ideas?

This subject has come up several times is of interst to me in database I am going to redesign for FMP7. I have a Family table, an Individual table, a Committee table and an Office table. Family has a one to many relationship with Individual. Individual has a many to many relationship with Committee. Committee has a one to many relationship with Office. I have added a join table Membership, which is related to Individual, Committee and Office, i.e., a star join. I have left the Family table out of the attached file. The Membership table has fields for the start and end dates of a term of office so that a history can be maintained. The Committee table has a filt…

How do you establish a One-to-Many relationship database? I was making a simple list example and noticed that each time I added an item to my list it created a copy of that item, if it were one to many then I should be able to add a single item to many lists. At least thats what I think it will do.

Hi There I have a query regarding the relationship structure for my magazine publishing business. The Setup I have 4 tables - Customers, Contracts, Sales and Reps. A Rep is allocated to Customers. Contracts typically include about 6 Sales (ie: Contract line items). A Rep is allocated in Contracts as the salesperson. At present I have about 2,000 Contracts with about 12,000 referenced sales from 5 Reps. The Question The Rep table is linked to the Customer table which in turn is linked to the Contracts table and in turn to the Sales table. I view lists of Contracts in a portal on a Titles table form where I like to see the Reps name next to the contract…

Hello, I have two tables: 'Customer' and 'Work Order' This is a one to many relationship. In 'Customer' I have several fields including Customer Name. In 'Work Order' I have several fields including Customer Name as well. The two tables are joined together by the Customer Name field (set at = ). The focus of the 'Work Order' table is a Work Order Number and not the Customer Name. On the 'Work Order Layout' I have a pop-up menu tied to a value list. This value list is related by the field Customer Name in the Customer table. If I change the spelling of a customer's name on the Customer layout then I want it to change (or refresh) the selected Company …

Hi. I'm looking for a simple solution to a problem described below: I have a company database with a portal to employees database (related via the company ID). I have a primary contact checkbox for each employee and would like to be able to only check one employee for each company. My problem is that I can't figure out how to make a rule for the checkbox so that only one employee could be the "primary contact" for each company. (checkbox has values 0 and 1 --> false, true) Now filemaker allows each employee to be checked so there could be 5 primary contacts. I want to avoid this. Thanks !

I am having an unusual problem. I have a script which copies various fields to a related table through a series of set field script steps. I have enabled the creation of new records through this relationship. The script works great when the filemaker files are on my local computer. I have now hosted the filemaker files through filemaker server 7. When I use a client filemaker to access the remote files through "open remote", I am able to access my database files remotely. when i run the set field script, it is able to perform set field script steps within the file I am browsing but filemaker hangs permanently when the script hits set field script steps which involve…

i'm trying to create a student database for a nonprofit. i started out using the student records template that comes with fm7. in addition to the layouts already present, i created two more - one one called 'student attendance' and the other 'all students attendance' my first question is: when i click on create new record on the student records layout, it creates one and automatically inserts a serial number. how do i make fm also automatically create a record in the second layout (attendance) that also contains the same serial number? why not just put the attendance information on the first layout you ask? ideally on the attendance layout, i would like to hav…

I do a "Go to related records" on FILE1 (which is a copy of FILE), with relation ContoDB = gConto. Function "GetFoundCound" returns, let's say, 3, which is correct. Now I do a second "Go to related records" on FILE2 (which is also a copy of FILE), with relation ContoCR = gConto. GetFoundCount returns 3, which is false : there are no occurences with this relation in the DB ! What's wrong

I currently have a global field (first_name_search) which relates to a field (first_name) in another table (individuals). If you type "Bob" into first_name_search and there happens to be a first_name = "Bob" in the individuals table, then the portal displays "Bob". This is basically used to obviate the need for Find Mode and restrict users to searching for data in certain fields. Now, I'd like the user to be able to "search" for a first_name OR a last_name. I could have two global fields where you type in data into first_name_search to search for a first_name or type into the last_name_search to search for a last_name - and there would be two relationships;…

I want to access records using "Go to related records" with the following relation 1. Operation = gOperation 2. Status = gStatus (numeric, 1 digit) There's no problem when looking for a precise status (eg 0,1,2 ...) but i'm not able to pass a range for all the status (0 to 9), something like "set gStatus 0...9. Please help !

Hi, I'm a real newbie to databases, but am a computer veteran of some 22 years. I'm using Filemaker Pro to create a really simple customer tracking database, where I just log their details into a single page. Now I want to add some extra functionality: I need to make a kind of sub database that can be attached to the main customer record where I can make lists of what each customer brings in to me to be repaired. I initially didn't think I'd get that much repeat work with what I do, but it's growing and my database is inadequate. Ideally I click a button and it takes me into a sub menu or database with all that customers repaired items. Any suggestions…

Hi, I have a database that I created to serve as a rolodex. A while ago I added another database related to the rolodex to hold map images. Everything was working fine until at some unknown point when opening the rolodex FM tells me it can't find the maps database... It's right where I left it the last time... So I point FM to the file and everything is fine until the next time when I go through the same thing, everytime I open it. Can anyone tell me how make it remember where the related file is? I'm not moving it or anything... Thanks in advance Kyle

Hi, I have typical master- 1 - M - <details relationship set up. I also created another relationship against it to include the above but also based on a condition of a field. For example the field might have "Drum", "Guitar" or "Piano" I want to count the number of Drum records and Guitar records and Piano records so i came up with this idea to use a relationship and I guess kind of a filter. I add a record to my first relationship - a new child. But when i get the count from one of my other relationships like DrumRelation or PianoRelation the count is off by one. Is there anything i need to do to force a refresh? thanks J__

I already have a sales DB made up of CONTACTS, COMPANY and bridge file (REALTIONS). Would like to add an extra file called DEAL PROPOSAL. Obviously, the DEAL PROPOSAL is a file where I can produce a proposal that would be related to CONTACT(S) and COMPANY(S). I'm stumped...I can't figure this one out! My guess: CONTACTS file to the DEAL PROPOSAL file would be many-to-many COMPANY file to the DEAL PROPOSAL file would be one-to-many or RELATIONS file to the DEAL PROPOSAL file would be one-to-many : Whew!

Hi, I posted something similar in another forum, but it wasn't enough for me to figure it out. I have a portal of Projects I have a drop down list above, which displays a list of authors of that project. When i pick a name from the drop down list (which is a value list (not in a table)) i filter the portal to only show those matching names. I use a global for the drop down with self join and this works fine. This part i can't figure out though: the projects table has a 1-M relation with a tasks table. and it turns out that a task has an author too. Someone else could have created the Task - who is not the Project Author... The task tables author field i…

I did a quick search but couldn't find an answer to this question. Is there a way to summarize data from a related table? For example, I'm creating an inventory validation tool with the following tables: Systematic Totals Physical Totals I've got a list view set up with the Systematic Totals table that relates to the Physical Totals table by part number. As well as viewing the variance between systematic and physical quantities for individual items (which is the easy part), I want a trailing total of the units for all parts for both systematic and physical counts. The summary of the counts from the current table (Systematic Totals) works fine, but how can …

I might be oversimplifying this explanation, but here goes - I've got a many-to-many relationship set up so that I can assign the same office to several doctors. The middle table is called 'locations' and that mainly contains info about the doctor pertaining to the office (like a billing TIN, whether he sees new patients or not, etc.....). The viewable info in the 'Office' table contains things like the physical address, telephone # and such...... Everything is working smooth, as far as I can tell. One client is reporting that the locations are somehow duplicating themselves. So now 'all of a sudden' a doctor has 12 locations, when he should only have 4 or …

I have a couple of tables that are related to each other...the layouts share 3 or 4 fields...a strange thing is happening - when I create a record in LayoutA from scratch, all the information is automatically filled in the fields in LayoutB as designed & expected. However, when I create a new record in Table A by duplicating the previous record and then modify the existing data in the duplicated record (some/most of which is the same as the previous record, which is why I duplicate the record in the first place, to save myself a bunch of redundant data entry), the duplicated information is not automatically filled in fields in Table B. Why would this be happening…

I am really new to databases, so please use small words and short sentences. I would like to have lookup fields in a repeating field. Is this possible, and if not what is a good alternative? I have steered away from portals because only some of the fields are related. It is a simple purchase order form with a repeating field of products. These are products I order from at least 30 different companies. I am an end user in a chemistry lab. I want to be able to enter the product id and have the name come up automatically, but not the price, or quantity. I self joined the table but product id and used the look up option for the product name. Thanks f…

I'm currently making a database for a warehousing and distribution company, the database keeps track of deliveries and items out of the warehouse, goods in, client's details and invoices (based on all of the above). When implemented the program will be installed on more than one computer across a network. My worry is that details on one computer may become different to those on another. I havn't worked on this aspect of the Dbase yet, but my theory is to set each record in each table as <the computer name>_<recordID> to ensure that every record on every computer is different. Then give the user an 'Import Records' Button which will import and update where…

Hi there, I wonder if there is any solution to achieve the following goal that is, in fact, not really too complex: Using the script command "Go to related record" (I hope this is an exact translation from the German "Gehe zu Bezugsdatensatz"!), one can display the related table either in the same window or in a new one; but not in an already open window. As I often need to actualize the data in an already open window, it is a rather unsatisfying solution to always close and reopen this window. Does anyone know an ingenious possibility? Best regards, Bernhard

Greetings. I'm just coming back to FM after a long hiatus (since version 4, I think!), and I'm trying to get my head around the way it does relationships. I've got a problem that would be doable with SQL, I think, but I can't figure out how to approach it with FM 7. It seems like it ought to be a standard kind of problem, so maybe one of the Wise Ones here can point me in the right direction. I've got what I'd describe as a many-to-many-to-many relationship. I've got a table for "people" in my organization; each row contains a person's name, email address, phone number, etc. I've got a second table for "roles": chair of the hiking committee, secretary of the pad…

For some reason this has me stumped. I have three files A, B & C. File A are people who say things. File B is what they say. File C are issues based on what they say. So, I have File A related to File B by a unique number (1 person to many quotes). Each record in File B (quote) can have more than one entry in File C so I have those linked by a serial# field (1 quote to many issues). Is there any way to show one quoter, what they said and the issues for that quote on ONE layout?.....thanks....

Hi, I am wondering if anyone has some experience with this one: Does the number of relations defined has any influence of the overall performance of a database? Or differently put: Should one try to make as little relations as possible, or can one use relations whenever it makes things easier in the context of a script? I am looking forward to your point of view!

I have a field called route-to that enables any user to route a message to another user. I'd like to create a portal so that shows all messages routed to the account name logged in. I thought I could do this with a global field calcuation Get(AccountName) that would be liked to the route-to field in the relationship defining the portal. Works great when only one person is logged in, but when another user logs in the global field switches to their AccountName and everyone's portal switches to see that user's messages. Ideas? Much thanks!

I've taken on a legacy FM database for a wine importer; rationalizing the stucture, redesigning the input layouts, and adding features. The importer sets up an purchase order (multiple cases of a variety of wines) to a government warehouse, then orders and ships from the government warehouse either directly to the customer or to one of two private warehouses. Goods are subsequently shipped from the private warehouses to a customer. The current model doesn't take into account the private warehouses. There is a table tracking arrivals at the government warehouse and another table tracking shipments from the government warehouse directly to customers. I need to add…

Hello, I'm a newbie to this post and to filemaker pro and i recently ran into some problems. I have a database called prices and my master record called dailyjobs2004 and i lost the relationship between both. I have a field called plan#, labor, material, and cost which whenever i typed in a plan# in the master record it would automatically pull labor, material and cost from the prices database. I think the relationship got lost when the power when out and our server went down. I have tried to fix it but have gotten nowhere. Can somebody please help me. Thanks, Desperate newbie

I am a schoolteacher creating a database for school use. I am still in the learning stage of using FM to create applications. Here is my situation. I have created a database called MASTER.( It includes different levels of access. What I am trying to achieve in one part of the database is for a form letter to be filled in with details which come from another part of the database. To explain my situation, do the following 1. Open MASTER.fp7 using MASTER.ZIP

I am trying to use a filter comboBox to filter another comboBox. I have 3 comboBoxes from 3 tables. The first box is a custom list that is linked to filter a second box. The second box then further filters the third box. I have been able to get the first filter to work, but am having difficulties with the second filter. Any help is appreciated. Pablo

I have a Filemaker file titled DataFiles. In it there is a number field titled Ap0. I have another field that is a summary(average) of all the Ap0 in all the records. I have another field titled round that rounds that summary field up. In another Filemaker file I have a number field titled averages. I am trying to pull the number from the round field in DataFiles. I have a relationship linking averages(DataFiles) to round. But nothing comes up in the field. I tried round as a text, number, calculation. Nothing seems to come up. Any ideas what I'm doing wrong?

I have a relational database which in the first table lists information about teachers at a school and in a second table lists the subjects taught at that school. A teacher can teach many subjects and a subject can be taught my many teachers. I need to create a report that lists the teachers for each subject and a query that allows a user to select a teacher and then displayes all the subjects taught by that teacher. I thought creating a database would be very easy but I was mistaken, this probably seems very simple to all you but I need help (I am using FMPro 3.0 on Mac OS 9) Thanks alot, Sam

I'm new to relationships. Is it possible to create a realational database like this & if so how? I'd have 3 databases 1. Items 2. Donors 3. Baskets I would want to pull out information like Basket B = item 2 & donor 3. Basket C = items 4,5 & 6 & donors 2, 3 &4. Is that possible to do? Would I need 3 databases? Thank you for your time.

I know this is a FAQ but I don't find the answer. Two tables with a simple relationship. Project ------- ProjectKey ProjectName Task ---- TaskKey ProjectKey Description The user is entering a new task. They know the ProjectName but not the ProjectKey. I want the user to be able to type into a field, the ProjectName and ProjectKey would be assigned. I currently do this with a pull down, but because of the number of Projects, the list has become unwieldy. I could do something with another window/area to do a find, but it seems over kill when the user knows the full project name. Any techniques for doing this?

I have to create a database for a silent auction. It will have to record the items, who donated them, what they are worth, minimum bid, what they sold for who bought them. Some of the items will be put together in a "basket" some will stand alone. I'm hoping someone here can help me with this. I've been looking at a lot of examples but I can't seem to pull it together. I've only created a relational database one time. Any help is greatly appreciated. Thank you.

Sorry for this lame newbie post, but I've been using Filemaker rather basically since v5.5 and now I'm trying something and have no idea. I'm trying to set up a relational database that is all the records from on database with a specific field, "hard drive 2," for example. Is this possible? Thanks

I have a three tables: Equipment, Manufacturer and Model, with a relationship between them. What I would like to do is when someone adds an entry into Equipment, they are presented with two dropdown lists (manufacturer and model). When the user selects the manufacturer I would like the model list to update, so as only to show the related models to that manufacturer. Can that be done? If my table architecture is screwed up it can be changed. Thanks

I am currently working on a program for a warehousing and distribution company. I have created three databases, one contains a list of records representing goods (items) which have been delivered to the warehouse and the quantities and dates of these deliveries. another a list of records representing goods (items) which have been distributed from the warehouse and the quantities and dates of these. The third file contains a list of all items (items cannot be recorded as delivered or distributed without first existing in this file) and the quantities of these items (which is calculated by subtracting the number of items distributed from the number of items delivered). The …

Hi, I am wanting to script a button that allows me to navigate from a customer contact database to a fax cover sheet database. I have tried to use the script for navigating to related records, however, unless the 2nd database actually has an existing record that is related, it won't work. For example, I may have a customer contact record in my database who I want to send a fax to. I have never sent them a fax before, so there are no related records for this customer in the fax database. I have been trying to script using the navigate to related records scripting, however this will only work if there are existing related records in the 2nd database. Is ther…

I am brand new to filemaker, I was asked to create a database for a survey that is mandated that we use. I would prefer to use something else being that I had never SEEN filemaker until this past tuesday. I bought a couple of books, but none of them contain the answer to my question. This survey that is being taken will be given to approximately 200 people. I have established fields for the site data and surveyors, my question is for every one of the 50 questions, one must rate the question on a scale of 1-4 and again rate it's importance on a scale of 1-3. I am unsure as to how to divy that up into tables, so I can eventually report percentages of surveyors who said 1, 2…

Hi all I am working on creating a project management database. Here is my simplified Table setup: PROJECT CLIENT CLIENT_ID|PROJECT_ID -PROJECT_ID -CLIENT_ID -CLIENT_ID -PROJECT NAME -CLIENT NAME -PROJECT_ID When someone enters a new Project I want them to be able to select from a list of client names , and then have the correct Project_ID and Client_ID inserted into the Project_ID|Client_ID table. How would I go about doing this, or is this even the right kind of table setup ? -Seth Pomerantz

I have a layout which shows a home arrived on a lot, and then a number of items in a portal that the user clicks on having completed. The following is a simplified example of a layout... Layout 1 Home: New Mercury Home Date Accepted: 12 Jan 2004 Done? Description --------------------------------- () Yes () No () N/A Damage noted () Yes () No () N/A Doors Locked () Yes () No () N/A Hitch Attached etc... The portal items table and the home table are associated with the field KEY. I also have another layout which just shows the homes from the home table, as in... Layout 2 Home: Date Accepted: New Mercury Home 1…

I'm relatively new to Flemaker and am trying to do something that I'm sure is quite easy. I'd like to set up a simple form where a user can select from a pre-defined list of values (e.g. hot, warm, cold) using either a popup menu or radio buttons and have a related numerical value (1, 2, 3) automatically entered into a(n invisible) field. How is this best accomplished? TIA. Richard

Hi, I have several layouts based on one table related to about 5 others. The main table contains 150k records, I do a search for what I need and 'move' it to another layout. This layout contains about 200 records, but I only want to work with around 15 or so that are 'active'. What is the best way to approach this? Thanks, Michael

I am using FM6 Developer and am writing a relational database for the befriending charity I coordinate. My start-up file has only one record but is referred to by other files using constOne::ConstOne relationships. ... it is 18MB in size !It mainly contains Text and numeric global fields. NONE are graphics containers. I also have a single record "about" file which is little more than a central menu and an about screen, again with the ConstOne relationships. This is 23MB. My clients file is 63MB in size despite having deleted all the ID photographs and having only 38 records. This file refers to the startup file for organisational info via a ConstOne relatio…

Hello everyone - I'm a bit wet behind the ears in the old database department, so bear with me. I have created an invoice database with a main customer information database related to an invoices database. Each customer has their own unique ID number generated from the auto-serial function. I can MANUALLY enter this number into the corresponding field in the invoices database, and it will lookup the customer's information appropriately. This is great, but I want to make a button that I can press that will cause the open "invoices" window to update, displaying the customer info in the currently open record in the "customer info" database. - I basically want to…

I have a FMP v7 with several files. The relationship to the parent file is on a Name in a text field. I want to add a field "Client#" in each file and base the relationship on that such that the Name can be changed in the future without affecting the relationships. How would I populate the new Client# field without manually editing all the records? I would want then set up the client# to add the next client# automatically for each new record. TIA Jim

Hi everybody - Here's what I'm trying to do: I have an Invoice with a Line Item portal full of Transactions. Each Transaction contains Product UPC, Quantity, Price, etc., as well as a 'Shipped' checkbox. The Transaction also contains details like Shipping Method and Tracking Number that are not shown in this portal. There's a second portal called the Shipping Manifest, and as Transactions in the Line Item portal are checked off as being Shipped, they show up in the Shipping Manifest, which lists the additional Transaction data such as Shipping Method and Tracking Number. I'm accomplishing this with a second Transaction Table Occurence that's linked to t…

Is it possible to export a layout that shows data from a relational file to a text file? I have 2 related files. 1 file has a 'summary' layout that shows some summary data and 1 field from the other database. I need to export that whole summary view to a text file. Is there a way to do this?

I have Shipping table. It has one day for every ship date (ShipDate, date). It has LastCall (time) so shipper (or manager) can set the deadline to receive orders for any upcoming day (or that current day). Sales wants to know when this deadline is. But Shipper (or manager) can change the time. Currently, it auto-enters (without Replace) "10:00 AM". There is ShipmentsClosed (number) toggle so shipper can shut off taking orders also, even if before the deadline. ShipmentsClosed also triggers events plugin to run scripts. If ship date/time has passed, it should list the next upcoming 'default' ship date/time. So I need to relate to Shipments on two different days (I thi…

Hi, as you can see, I am new to this group. We are a small company and develop software for biologists. Except coding software I am responsible for almost all other areas in our company and that includes our Contact database. Over the last two weeks I improved it a lot but now I stuck a little bit. To provide a better overview between certain contacts (relationships between people) I want to link a record with other records within one table. I played around with relations (which I already use for other issues) but I stuck. Linking records in one direction is fine but how can I automatically create the link back to the original record so it works in both direct…

The question involves 3 tables: Products, Resources and a Join table. Products and Resources each have an ID field of their own, both of which appear in the join table to link them. I have a portal on the Products table that shows which resources are required to product that product (The portal is linked to the Join table, so that when productID matches, the Resource will appear in the portal)... I've come up with a script to enter resources into the portal. Since it would be pointless to use a resource twice (there's a quantity field), I need to somehow COUNT how many times this resource exists via this product already. If that count is greater than 0, I know i…

Is it possible to have two seperate relationships to a table and could I then generate a report using one of these relationships? The reason I ask is I am having a problem with a report I have created which uses info from two tables. There is already an existing relationship between these tables but when I add another field to the relationship it works but other related information doesn't? Thanks in advance.

I want a function which will count how often a particular field value occurs in a range of records, where the number 2 will be held in one single digit field (e.g. how many records are there where the "Rating" field is set to 2). I want to display this in a layout based on another table via a portal. Thanks.

I have a database that consists of three fields: name, status and date. This is simply used to store days on which a person is actually booked to do something. The status field accepting values of 1 or zero depending if they are booked or not. I want to display this table through a calendar inerface. Dates along the top, people down the side. The 15 columns for dates are flexible and can be either days, weeks or even months.These column/fields therefore do not have a fixed value. I am not sure how I would start to achieve this in terms of number of tables used and how to calculate the calendar fields/columns. I have seen examples of calendars whcih are fine…

I am unclear about the meaning of the "Use External Table's Layouts" checkbox in the Go To Related Record (GTRR) feature. I understand that by checking this, FM will only use layouts that are based on the TO to display the related records. However, I have found that my application runs much faster when I use my old standby method of locating related records, which is to set global variables from the calling window, and then performing a scripted find using those globals in the destination window (This is a holdover from when the application was in FM6). Because of this, I have kept most of my layouts based on the "vanilla" TO in the relationships graphs (that, a…

I feel really stupid right now....I have been trying to grasp the whole concept of relationships for a while now and apparently just DO NOT understand. Here is my problem (I think..) I have a bunch of tables, but I will use just 3 for the example: Table_1: First_Name Last_Name emailaddress City State Zip ... UID Table_2: Cert_Type Cert_ID UID Table_3: Cert_Name Cert_level Cert_ID Right now I set up relationships UID->UID and Cert_ID to Cert_ID What I *want* is that when I go to enter a record I have the fields FirstName LastName, Cert_Type, Cert_Name …

I have a table that in amongst its fields has a name field, and then three category fields and three category description fields. Basically a simple webform gets people to fill out their details on-line and select (up to) three times one of ten categories. So they might have one, two or three of the category fields with data in. They can even select the same category three times. They also have to put a title in related to each category they choose. e.g. Name: Jones Category1: "3" CategeroyTitle: "Artistic practices in the late 1400s" Category2: "5" CategeroyTitle: "Mathematical experiments on rats" Category3: "" CategeroyTitle: "" in this cas…

I have attached the file if my rambling jibberish doesn't make sense. I have 3 tables (Training Log, Skier Records, Skier Log) that are joined in that order so when a coach is selected in a new Training Log record, it pulls all skier names from Skier Records related to that coach into a portal. Training Log (Coach) --> Skier Records (Coach)//Skier Records (Skier ID, Coach) --> Skier Log (Skier ID, Coach) What I have been trying to figure out (being new to Filemaker) is how to get the Training ID from the Skier Records portal in the Training Log all the way over to the Training ID in the Skier Log to tie the records together. What should I be look…

I think this is an application for portals but I am still struggling through the basics of FM so here goes: I have a photos table where I want to be able to assign one or several categories to each photo. The categories are in related table "categories". I am able to create a relationship that works fine for a single category, but everything I have tried for multiple categories has not worked. I tried to make 3 category fields in the photos table and relate them all to the categories table but FM won't let me do that (this is sloppy anyway). I know portals are supposed to allow this kind of flexibility but I'm not sure how to go about it. Any suggestions you…

I would like to find a template which covers monthly sales records for a small sales team. I have to administer total sales, commissions etc by sales person and branch. If possible I would like to run data I already have for the last 3 years. All the templates I have seen on the internet seem to be fully blown accounting packages for contacts, invoicing inventory etc which I do not need. Any suggestions?

Hello, new to FM and stuck early in the game... I have created a table that has a listing of 167k records detailing property ownership in my city (owner name, address...). I also have another table the same size detailing each home's information, such as size, bedrooms, etc... The common field between them is the parcel number. I would simply like to be in one file, and hit a button to take me directly to the related record in the other table and I just can't figure it out. I have tried a script, and relationships and portals, keep hitting a wall. Thanks, Michael

I am new to working with FileMaker Pro. To help out a friend who works at a Park District, I am attempting to create a

Hi everybody, I'm new to the forum and to Filemake Pro, so thanks for helping out. I have a table(1) of contacts for my company. This table includes fields for name, contact info, category, and a portal for dates on which each person was sent a package. Another table(2) contains names, categories, dates of packages, and the details of the package contents. So, when I click on a package date in table 1, a window opens up with the record for that package in table 2. The tables are related through the name fields. Is there a way I can relate the same two tables through the category field, as well? As I have it now, when I enter a new package in table 2, th…

Hi, folks. I'm currently working for a residential school. We have a large database system that tracks, students, staff, parents, etc. Now the nursing department wants to get in on the fun, as the majority of our kids take medication. In thinking about this, I'm wondering about the relationship: One student can have many medications (Janie takes Pill A, Pill B and Pill C). Also, one medication can be given to several students (Pill B is given to Janie, Johnny and Suzie). Sounds like the dreaded many to many, which I've been taught to avoid. How would you divvy this up? We already have a students.fp5 DB, and my initial inclination was to make a Medicatio…

Let's say I have the following... Table: PROCEDURES Field 1: PROCEDURE_NAME (Text) Field 2: PROCEDURE_DESC (Text) Field 3: PROCEDURE_USER (Text) When a record is created, the account name is automatically put in PROCEDURE_USER. So when I log in as "Admin" and create a record and I make... Field 1: "Procedure 1" Field 2: "Procedure 1 Description" Field 3: "Admin" (automatic) Now I log in as "Kevin" and create a new record... Field 1: "Procedure 2" Field 2: "Procedure 2 Description" Field 3: "Kevin" (automatic) Now I log in as "Joe" and create a new record... Field 1: "Procedure 3" Field 2: "Procedure 3 Description"…

First off, I am new to this board and want to say "hello"! I have attached a .jpg pic of my current FM7 file. You will see Ten TEXT fields (highlighted in blue) which all contain the scientific names of genes in the Fruit Fly Genome. What I am trying to do is relate each one of these Ten fields back to the SAME FIELD OF ANOTHER FM7 FILE to lookup whether each gene name appears in the other file... and then to report the record number it appears in in the file. You can see me trying to test this out using the two sample fields "Peptide1_Rec#_In_Unannotated_List" and "Peptide2_Rec#_In_Unannotated_List". However, when I link anything more than one of the "Protein1, …

I have been charged with setting up a database to track keys to employees and outside contractors. So far I have succeeded in setting up a nice relational database which allows me to connect a key to a client. Having a Client file, a Key specs file and a Main file. It has been getting me by so far, but it can definitely be improved. I want to add in a history log. I want to be able to access a key and display all of the people it has been checked out to, with issue dates and return dates. Also I want to be able to access a client and see all keys that are checked out to them, and that have been checked out to them in the past. Can anyone help me out with …

I have several tables. One table that contains athlete contact and coach information. Another contains training date/location information. For each training date/location I have the athletes for that particular coach in a portal. What I am trying to do is create a 3rd record in a athlete training log based on that specific traing date that contains attendence and performance information. How do I pass the appropriate data (athlete name [athlete table], training date [traing log table]) to tie everything together to the third table [athlete training log]. Set Field[]? can't seem to get it to work. Thanks for any pointers. DPSEF.zip

Is it posible to see multiple list views (from 2 different tables) in one layout? For the invoices I am creating I need to show 2 different lists of data (items & services) on the same invoice. The two lists do not contain the same type of data, so I can't combine them into the same list. I hope this makes sense Thanks,

I wasn't sure if this belongs in calculations or here, but being new to the relational database concept, I figured it's more of a relationship thing... The question involves 3 tables: Products, Resources and a Join table. Products and Resources each have an ID field of their own, both of which appear in the join table to link them. I have a portal on the Products table that shows which resources are required to product that product (The portal is linked to the Join table, so that when productID matches, the Resource will appear in the portal)... I've come up with a script to enter resources into the portal. Since it would be pointless to use a resource tw…

Hi 3 tables: Customer Purchases Inventory In the purchase table, there are multiple purchases per customer (by customer ID) I want to create a summary in which i would be able to see the total purcahses of each customer, but when i create the layout in the customer table , the body will only display the the last purchase the customer has made i have made a portal, which seems to work but i prefer it to be just the body istead, as i cant do calculations in the portal and do not like how the portal will always be 1 size and will not scale accordingly to rhe number of records somethign liek this: any help guys?

File A and File B are related. I have a script in File A that finds a particular set of records from a drop down menu. How can I get File B to also show only that found set without having the user perform a 2nd find in File B?

Hello to all. Long time. I may be losing my marbles, but something seems oddly messed up. I have a global in an "interface" table, which holds the field "glYear", which is a global number. I have two other tables, which are a general "Team" table and a "TeamYr" table. Teams can have many teamyrs. I have a calculation in Team to pull the global Year into Team, called c_glCurrYear. From this, I have constructed a multi-key relationship to a TOC of TeamYr: c_glCurrYear = Year PKey_Team = Key_Team (PKey is unique idea for each team; Key_Team is the corresponding identifier in TeamYr) So, this relationship works, which it should. Both…

I'm creating an Online menu form that writes to various tables. I have a table with all the menu items available for each day. In this table the item descriptions also have two other fields 1) the day in which it will be displayed and 2) the type of food it is considered to be (i.e. a meal, snack or drink). I am trying to create the order entry form and having no luck. For example on Monday the user can choose from one meal, one snack or one drink (not more than one of each). So, I can create a fields called [meal], [snack], and [drink]. I'm trying to make a checkbox field for each of the fields and the valuelist will pull from each catagory on each day. The item…

I have a User database and a Software database. The User database has a specific ID for each user and a field for Software assets assigned to that user. The Software database has a specific ID for the software and a field for the user it is a ssigned to. In the Software database I want to be able to assign Adobe Photoshop to Jim Smith and have that record automatically populate the Software Assets field in the Jim Smiths record with the Software ID#. Can someone help get me started? I could probably figure it out if someone can point me in the right direction... Thanks!

Filemaker help implies that relationships can be made between files as well as between tables, but if it tells you how to do this I can't find it! Can anyone elucidate?

I got a database with clients, and it contains a text-field for email adresses. Some entries in the database has several email adresses entered in this field, seperated with a newline. Now, I got another database with orders. This database also has a textfield for email adresses, but they always contain just one adress and no more. Which is the best way to relate these two databases? I am willing to modify the clients database if it isn't too time-consuming (6600 fields...)

Hi guys, Just a quick question. In FM7 you have different ways of creating a relationship as far as how it filers - either, exact or doesn't equal or anything but. How do you create a relationship where only partial information is needed like in a find request? is this possible? what I want to achieve - Lookup a surname in the client database using a portal called Johnson Banks - in the left hand field only enter John - all records starting with John in this field in this relationship appear Thanks

I have two databases. 1.client address book which has a customer ID which is a auto serial number field. 2.Is a contract database.I have defined the relationship between the two databases using the customer ID. Every time I enter a new record I want another field to generate a "Contract Number" According to how many contracts are there for that customer. Can anyone please help?

I want to set up a relationship which displays records from table A in a portal in table B. The records which should appear are those between start date B and FInish date B. There's a simple example in Filemaker Help for just this setup, but it's not working for me for some reason. I set up a dummy match field in each table to test that the portal is correctly set up. This works fine, returning all the records in table A, but as soon as I add comparative operators to any other fields in the relationship (with or without the original dummy match fields), all the records disappear. I've checked that I'm using the right fields, and double-checked the date ranges to conf…

Hallo, I am new here and would like to greet all ya first - nice place ! After posting and answering to another thread here I wanted to leave a question here and would like to ask for help on this: I am on a db table containing an extra global field which will contain text from a choice via pop up / value list which is modified thru' the "show all values of field "x" in the table" step. So if I (the user) would choose a list item in the layout, the global field will be filled with the content of this field "x" from a certain record of this db and can be used as a keyfield to this record. or not? Shouldn't I be abled to navigate this way via this pop-…

I'm trying to find a solution to a problem, but I'm not sure how to explain the problem, so I'm going to give the entire list of events that lead up to the problem I'm trying to find a solution to. Create a new file "test.fp7". Create a Table called "A" with a Field named "B_ID". Create a Table called "B" with a Field named "ID", and a Field named "NAME". Create a Relationship from A.B_ID to B.ID. Create a Layout with A.B_ID & B.NAME (as a dropdown box showing all possible entries from Table . Don't really have to go any futher then here as it doesn't usually work this far! But if it did the next thing would be to make sure that A.B_ID gets updated…

Hi All, I have 3 tables in FM 7 file 1. People ( pid serial field and name ) 2. Titles ( tid serial field, pid relation to people and title text title name ) 3. Documents ( docid serial, Programmer, Accountant, all rest is skipped ) I want to limit the pop-up list when I create a new record in Documents only to the title already filled in Titles for each Person. So in Documents.Programmer I want to see only programmers in the pop up list and in Documents.Accountant I want to see only accountants in the pop up list. (See the attached file ) I have one additional table that was supposed to limit the records ( it has two fields Programmer and Account…

I am new to FM7. I understand databases and have done most of my work in SQL Server, MySQL, or MS Access. I am trying to move one of our databases to FM7. It is very possible the layout I am attempting to use is foolish for an FM7 database, and I am more than happy to change that. Let me describe what I am trying to do. I have 3 tables right now. 1) List of all employees in the company. 2) A table that consists of EmployeeID, EffectiveDate, Salary. 3) A table of all valid paydates. I require 2 views of this data. 1) For any employee, look up their salary history. This is easily accomplished with portals, and works great. 2) The ability to look up any …

I've got two seperate database files - a User database with each user and various info as individual records and a Software database with all of our software licenses. Each user has a unique login name - Joe Smith would be jsmith - and in the User database record is a field for the software they are currently assigned called "SW Assigned". In my Software database, the records all have a unique ID number and have a field "Assigned to:" which is filled with a login name of the user that is assigned the software(ie., "jsmith".) I'm trying to create a relationship that automatically fills the "SW Assigned" field in the User database when I enter the users login na…

I have a self joined table. In my layout I have a portal of which I want to view the entire joined tree. So basically I want to see a particular record's children, grandchildren, etc in the portal. I know I need to use a multikey calculation to get all the related keys but, I'm not able to get it too work. I think a big part of my problem is there is an undefined number of levels. The self join could go 1 level or 9 levels deep (as an example). Can some one help me get going in the right direction?

I am new to the forum, and to FMPro 7... and to using relationships in databases... I've used older versions of Filemaker though, so am somewhat familiar with it. Situation: 3 Tables: Products table (info on each product) Resources table (contains resources required to produce the product) ProductResources table (joins the two tables above with fields: Product, Resource, Qty) If a product requires anything to produce it, I use a portal on my Product layout to enter in the Qty and Resource for each resource needed. Problem: I change data in "Product" field in Product table (title of the product)... Resources in the …

Hi! I'm using Filemaker for our music royalties and use the following tables: Sales Royalties Digital Download Royalties Foreign & Additional Royalties They are all related to a table called "Artist Information" which has our artist's names and addresses. My question is this - I have the Artist's name linked to each table, but is there any way to set it up so that if a name isn't entered into a royalty table exactly as it appears in the "Artist Information" table, it will let me know there is an error? I'm sure there's a technical term for what I'm asking, but I don't know what it is and probably wouldn't understand it anyway. So if there's anyone w…

Hello, I'm new to this forum and new to fmp7 (or any filemaker for that matter) and have been called in to a project using fmp7 that is fairly involved. I have a reasonably extensive background w/ database programming using sql and java. two questions to the fmp community: 1) one of the main difficulties I've come across can be expressed by the question "how does one manipulate data in a many-to-many relationship, even in the presence of a join table?" For instance, consider a "quotes" table that links to a "company" table (the company for whom the quote is being developed), along with a "contact" table linked to the "company" table (the peop…

What we are trying to do here for our school district is create a tech alert database where we can have X amount of check boxes for each of the buildings in our district. What we also have is a separate database with all of the staff emails separated by building. We are currently trying to make it where you can click a check box and it will automatically identify with the emails for the school. So basically if we want to send out an alert to each of our 11 Schools, we check each of the 11 boxes, click the 'send alert' button and our message will go to each email address at each school. Im not exactly sure if this has to do with relationships or more of a scrip…

I currently have (2) files: File (1) = Timesheet File (2) = Estimate I have established a relationship between File 1 & File 2 via the client name field that correlates records between the two files. It works great. My problem: The Estimate file has 6 years worth of estimates, so depending on the client, it takes a long time to display all the records. In the Estimates File, we have a layout called Active Jobs that displays only the active jobs for active clients. I want to modify the Timesheet file so that it only pulls records from the Active Jobs layout in the Estimates file and NOT all of the matching records in the file based on client name. …

I have two database which I would like to work hand in hand. I got one (Database A) that will do the calculation of a summary and I would like the other database (Database : to get updated as well once the total summary has been updated. When I try to make a relationship between the two fields I get a dialog stating that

I have a list of names which can be duplicated for example each department takes so many spark plugs and I would like it so there would be a running total where only one department shows up on the list with the summary of how many spark plug of which below is an example. Department A | Spark Plug A Department A | Spark Plug B Department A | Spark Plug A Department A | Spark Plug B Department A | Spark Plug B Department C | Spark Plug A Department C | Spark Plug A Department C | Spark Plug A On the list I would like it to show Department | Spark Plug A | Spark Plug B Department A | 2 |3 Departm…

Hello, I'm hoping for a kick in the pants here. I'm stuck in an old way of doing something I know is wrong -- using repeating fields. The solution I am working on tracks caseworkers' activities on a daily/weekly/monthly, etc. basis. They complete "work plans" for the week where they track their activities: whom they visited, how long they were there, what the objective was, and the outcome. They may visit anywhere from 1 to 5 or 6 clients per day. In the past, I used repeating fields that would track "Monday Visits", "Monday Start", "Monday End", etc.. with 6 repetitions. I know this is a bad way to go, especially for reporting. I found that out the hard …

I have a calendar solution (file A) that shows events from file B by having a different realtionship for each day of the month. When I select a date on the calendar, it goes to the related record in file B. But when I click on a date that does not have an event it still goes to the last date selected in file B. Instead I want a custom dialog box to pop up saying "This date does not exist. Would you like to add it? I know how to script the dialog box and the choices. I can't figure out how to check for a valid date in the calendar (file A) with a valid date in file B.

I have a database that includes multiple records for the same event, each record providing data for a distinct category. I need to convert this so that the new database holds a single record for each event with enough fields to cover each category. (Hope this makes sense!) To illustrate the problem, I've provided two simple datasets below, the first equivalent to what I have and the second being what I'd like to have. If it helps, the traps are light traps that capture flying insects, each trap collects multiple species (spp) and, often, multiple individuals within a species. I've tried to create a database with a list of unique traps (eg trap01, trap02, etc) and the…

I hope someone can help- I am trying to set up a relationship that goes from a global field to a calculated field. This has worked for me in the past... for some reason it will not work. If I turn the calculated field into a text field, it works. When I return the field to calculated text, it breaks. the calculation is unstored. Any thoughts?

Greeting to all. I need to create a patient visit DB using FM6 host by FMS5.5, which contain patient