Relationships

Apparently I don't quite grasp how a relationship between two tables works. I have been trying to get a calculation to work but the results are always wrong. If two tables are related by some identifier in a 1:M relationship, and a layout (based on the table with unique identifiers) calls for information from the related table (with multiple instances of the same identifier), why does the following calculation only seem to act on the first record it finds in the related table? Case (table2::field5 = 1; table2::field4; table2::field5 = 0; "N/A") So if table1 has 1 record, table2 has 5 records and record 3 of table2 has field 5 populated with a 1 it would…

i attached an excel file as an example of what i am trying to achieve with FM. I need a form with two linked fields when i assign value regardless in which one the linked field has to change. Any suggestions will be appreciated Book1.zip

Hello All, I have many jobs which have many details to them. I would like to enter an invoice from a vendor for a job and choose which details I want to apply to the invoice. The details reside in a line items file while the job code is in the master file. I can't seem to get the relationships to correctly pull up the details of the job. I get the details of all the jobs rather than the one job I choose. I would like to choose the details of the job from a value list, but again, I can't get the value list to just show the details of a particular job. I've been struggling with many to many, three way, etc. and conditional value lists but no go. Thanks! : [color:red]

I've made a booking system for a guest house. As you can see at the pictures, there are 6 rooms (left) and 25 days (Top, 5 past days + 1chosen day + 19 next days), you choose the date from the small calendar on the top left and it appears on the 6th column(Green). In the portal just under the Booking Grid, you can see all the reservations of the date that is in the green rectangle, and if you click on the blue round on the left of each record you it send you to the Booking Details Layout so you can change some details about the selected reservation. My Big Problem is that I have 150 different relations just for the Booking Grid (above the portal) and it make it very slo…

Is there a way to establish a relationship based upon a calculation? I would like the criteria for a "match" to be if a quantity field is greater than (1). If, for example, the quantity of drawers in a cabinet is greater than zero the match field criteria would validate and a portal would display. In this example the quantity of drawers could be 1,2, 3 or 4. Is this possible? Jarvis

Hello folksm, quick question I have a parent table Incident Report with two child tables: sanction and charge How can i design my layout so that I can add multiple charges and/or sanction for each incident number? I want to be able to add in 1 or more charges and sanctions, then use a portal to view all related records for an incident number.

Hi, I'm new to filemaker from mysql and am having trouble translating my knowledge. I have two tables (people and emails) in a one to many relationship. I have a primary_email field in the people table to identify which email is their primary one (duh), but can't figure out how to show only this email address in a people-centric layout. In MySQL I would select the entry from email table that has email_ID=primary_email. Is there a way to mimic this in filemaker? Thanks.

I am trying to write an application that processes sequential timestamps. Each record in the file has a sequence number and timestamp. I am trying to create a relationship over the file that compares each record with the previous one and returns the time difference betwen each one. Should I do this purely with relationships or through lookups etc? Example records; Seq Timestamp 001 28/05/2007 16:45:00 002 28/05/2007 17:00:00 (difference = 00:15:00) 003 28/05/2007 21:30:00 (difference = 04:30:00) 004 29/05/2007 03:00:00 (difference = 05:30:00) etc.... Can anyone give me a clue how to go about this? Thanks, Graham

It is a very basic question, i guest, but it is puzzling me. I have employee and employee ID fields I am able to writhe the employee name and see its ID (I achieved that through self-join) but cant figure it out how to get the Name when I type the ID. In other words i want to type ether in ID or Name field and get the responding field automatically. Any help will be appreciated.

Hi everyone, Have this Database working with invoices. I have a membership table that pulls values from a separate Database that holds the item number and description. So I now have a valuelist that pulls all the values from the item DB and shows them as a pop-up list. I want to have the list only show the values that are marked as Active in the item DB. I had the tables linked through item number, which displayed everything. Then, I tried linking the relationship through a global field = Active to the field in the item DB marked as active or dormant. This change is still pulling everything, so I figure its something wrong with the value list set up. Does…

Hi, Building a very simple call back system and having a bit of trouble trying to find the records that meet the check, todays date with a N call back complete flag. What I have is this: Table one: Company (txt field) First Name (txt field) Last Name (txt field) Table two: call back date (date field) call back by (txt field with drop list) call back notes (txt field) call back comp (txt field with drop down two option Y/N) In the main table layout I simply have the "company, first and last name" then a "portal" that shows me the four field from table 2, this is sorted by the date (newest first) I have set up the tables and relationsh…

Hello. When I create a new record in one table, I would like FileMaker to automatically create the same record in another table. Is this possible? Any ideas how to do this without using a script? Any help would be very much appreciated. Thanks Greg

I am wondering how other developers have implemented a generalization hierarchy structure in FileMaker. I am referring to supertype/subtype generalization which is often used in object-oriented systems and also known as classes. There are normally three methods to map this in SQL: 1) Use one table for the supertype with separate tables for the subtypes (AKA Separate tables method - a compromise method, which can be complex to comprehend) 2) One single table with fields for both subtypes, where the fields are null for some subtypes without certain subtype attributes (AKA Single table null fields method - probably easiest but messy due to all the nulls) …

I'm still getting used to the way FM uses "table occurances" which is very different from other database packages I've worked with. Seems a little strange to me that the same table can be in there multiple times with different names...but that's not really what this post is for. What I'm trying to do is relate tables with an OR statement instead of AND. I can do this very easily via ODBC with web scripting such as ASP. However, within FM this doesn't seem to be possible unless I'm just missing it, which I hope is the case. An example is as follows: I have a table called Employees that houses basic data for each employee here. I've created another table in FM…

I need to scan for a part_ID in PO file to extract all occurrences of "qty not rec'd." to populate a "BackOrdered" field in Product DB for the Part_ID. I also need to scan all Work Orders for Part_ID * Quantity of each to remove the total number from "stock on hand" field in Products. Anyone know how I can do this, either via script, key field, etc?

In an inventory system that we are working on each item has its record and each of those items has a reorder quantity. Similarly each of record has a calculation that establishes a quantity on hand. I'd like to set up a portal that will display any record where the reorder level is greater than the on hand quantity. This is problematic because the on hand quantity is calculated by summerizing debits to inventory (through a relationship) and subtracting from additions to inventory (also through a relationship). I'm thinking this is a pretty common problem. Any suggestions?

OK, just need some clarification here if possible on the variations of what I can and need to do with some relationships: Problem #1... My 'main' table (the one I primarily view everything from) is called Events. In one instance I have a relationship between it and another table called Guests. The relationship is pk_EventID (Events) to fk_EventID (Guests). Using a portal I can thus create a guest list for a particular event, then print it, sort by last name etc. That works great assuming each list of names is unique to one event. Now, what just came to me is this: what if I already have a record of someone's name in the Guests table and wanted to reuse it f…

i am pretty much a novice to filemaker. i have a database with contact info and i'm trying to add donation categories to track how often one person has donated in the past years. i understand that this would probably be defined as a one to many relationship? i have created a donor table but i'm not sure how to relate it to the contact table. contactid = donorid? also i would like to be able to insert a portal on the contact form so that we can display donor info. i have attempted to do this, but when i try to view the info from the portal, it only displays info from the first line of the portal. it isn't detecting additional lines from the portal. why is this? …

Has anyone read the FMP White Paper Doc? If so, I am having troule wrapping my head around the portal-relationhip description on page 18-19.... a way to add records without using portals (not setting portals to add records through portals). Could someone give me a hand? LJ

I am new to FileMaker, I want to create an inventory management system for my own warehouse. Since every layout can just only related to one table occurrences. How can I update another table (CurrentStockLevel ) while inserting record in another table (Transaction) Actually, I want to decide an database with following entity, - Product (PID, product info) - Vendor (VID, Vendor info) - Client (CID, Client info) - Warehouse (WID, Warehouse info) [Every Vendor and Client has his own WID] - Invoice Master (Invoice No., invoice info, Price) - Invoice Detail ( Invoice No, PID, Quantity) - Delivery Note Master (DN No., DN info) - Delivery Note Detail (D…

Hi New to this FM7 game but enjoying it - sort of! Can anyone help? I have developed a job management system for my design company. It has a 'customer' table with the client details in. From this layout we then create a Job Sheet (a new table) and from there we can create Print Entries (new table) and separate Time Entries (new table) for each job. Each Job Sheet is given a unique serial number which is then copied (using copy and paste??!!) to each new Print Job, or Time Entry and used as the relationship. It works well, except for when it comes to deleting any separate elements of a job file. For example if we delete a print entry for a job it deletes …

Dropdown menu that I want to show records from a related table does not react to focus or click. Created two tables, Name and Department. Here's description from Define Database window: Table: Name Field Name| Type | Options name_ID | Number | Auto-enter Serial first | Text last | Text dept_ID| Number | Indexed Table: Department Field Name Type Options dept_ID Number | Indexed |Auto-enter Serial deptname | Text|Indexed, By Value List, Existing, Allow Override description |Text In the Relationships window I dragged a line from department.dept_ID field to name.dept_ID. I checked the "Allow creation of records in this…

Could someone please see if they can duplicate this problem? Go to the contact tab and create a new contact. For company name choose one of the existing companies in clienttracker. Now go to the projects tab and pull up one of the existing projects. Make sure the company name matches that of the new contact you created. Now choose your dummy client from the drop down list. So far so good, right? Now click the little blue arrow next to the contact name while still in the project window. It takes you to the original contact, not the new one you specified, right? How do I fix this? Thanks, Evan

Hiya, I have two tables, one called "Events" and once called "Attendees", that link through a join table called "Registrations". So far, they are set as many to many in the relationship graph, as many attendees attend an event, and attendees attend many events. That's all working great. I receive a list of Attendees from an Organizer (value list), and import those contacts into the database. I would like to create a layout, and a script to automatically assign the Event they attended to the group of imported Attendees. How do I go about building the Table Occurrence Group on the RG. My primary keys are Attendee ID No, and Event ID No. So far I've not u…

This is such a basic question but I am not sure of the answer to it. Whats the best way to add a related record in filemaker ? eg if I have a contacts table and I wanted to add a related sale to that contact, I could 1) create a new sales record and type in the contact id 2) list related sales in a portal from the contacts layout and allow "creation of records via the relationship" - and type in the last row of the portal. ok - but if I want to automate this process using a script, whats the best method ? Why doesnt Filemaker have an "add related record" script step ? Thank you Daniel

I'm trying to create a database to handling scheduling of classes for a summer camp... So I have a table of Staff info, a table of class info, and a table of time periods... there are six periods, each staff member teaches a class each period, there are 20 classes and 30 staff... how do I relate the tables?

I want to create a simple job tracking system. I have created 4 tables called "Job (job_id as PK)", "Customer (customer_id as PK)" "Employee (Employee_id as PK)" and "LineItem (LineItem_id as PK, Job_id(FK), Employee_id(FK), Customer_id(FK))". Is this the way to go? Are all the tables simply joined to the LineItem table via Foreign Key? Please help. Thank you kindly.

Hello, I am trying to get the relationship thing down, and one thing I don't understand is how to keep two tables in one database, when you want to create new records for each table one at a time. One of the most often used examples when trying to explain how to build relationships between tables is that of students and classes. I understand that you'd want one table for students containing all the relevant student id (unique id #, name, address, etc.), one for classes (all relevant class info like the title, time it takes place, as well as the unique id) and a join table (like enrollment or something). Well if they are all in the same database, on different…

How can i make a relation between repeating fields. The relation is working only for the first repetition of the repeating field. ((i can't use the "Extend()" function in the table relationship)).

Hello to all, Concerning my data model I have the following question: My data model consists of 1 object schools and 2 other objects teachers and courses. The schools and the teachers as well as the schools and the courses are each connected through a 1-to-n relationship: So 1 school has many teachers and 1 school has many courses. (I now that here a n-to-n relationship would have been better, but for the beginning it is ok like that). As matching field I used a serial key, which means that the school is linked with teachers and courses through a key number. Thus one school has one key. In the school file this key is generated automatically as serial key …

Hi, I built a database with 20 questions. I need to set up a way of checking the amount of Occurrences in each field. I have set up a count and a self join to do that. But there are a few things I don't know how to handle. If there are multiple entries in one field separated by a carriage return, filemaker will pick up each Occurrence and count it. But will it display each one seperately. For instance if the question is Favorite Designer and they answer 1) Armani 2) Armani Gucci The program will count Armani 2 and Gucci 1 but wont display Gucci separately in the portal. Is there a way to seperate this out? Secondly I am t…

Hallo all together, I just cannot figure out, whether it is possible to insert several values from a value list into one field. If there a many courses in value list and one pupil is enrolled in many of them, is there a way to choose more than one of those courses from the pop up list or the pop up menu. Thanks Ralf from Uganda

Hallo to all, I have created a database with about 5 objects. For my main object, which is "school", I would like to set a serial key, as every school is unique and occurs only one time and has different related objects (like teachers, courses etc.) Therefore my question: Is it possible, that FM generates in one column automatically one serial key for every school in the "school"-file? And that key should not erasalble or changeable. As every school is unique, there just has to be one key number for one record in the school object. Then I would like to use those key numbers as matching file to link my school file to other objects through realtionships. …

Hi, I am desiging a relational data modell in FM. Most of my objects need two attributes as a sufficient key, e.g. I cannot only use the name but have additionally to use the age of a person. so those two attributes should serve as one key. My question is, how I can do that in FileMaker. Does FM offer Multiple Keys (and a Multiple Key has not to be mistaken for a multi-key field, which is something different). Thank You Ralf from Kampala / Uganda

I'm creating a database to track student info, courses, grades, etc. In designing the tables, it appears a have three tables that relate to each other: students, courses (course & semester) and grades. Each student can have multiple courses and vice versa. Each student can have multiple grades, one for each course. I think I need a join table for the student - course many to many relationship, but am not sure what to do with the grades table. Any ideas?

I'm working on a database for my babysitting business. I'm having a problem showing certain data in a portal, and I think it might be because of relationship problem. I'm working with four basic tables: Sitters, Clients, Jobs, and SitterAvailability. On the job layout I have two portals. One is a self-join that counts the number of jobs that the client and sitter from the selected job have worked together. The other shows the sitters that are available for the current job. I want the availibility portal to count the number of jobs between each available sitter and the client from the selected job. I've tried all sorts of table occurance configurations and can't …

Hi, I've created a database solution on my own for the first time. It seems to work alright, but just wanted to check something. My table is a contact list of various people associated to specific jobs. There are a ton of different positions and some people do only 1, some do many. I have them broken into groups such as product managers, product development, lead, etc... I'm able to create a bunch of cool reports either job specificly or person specific. Everything's seems set up nicely, but I have a question. The backbone of my solution is a large table of numbers, all the serial numbers associated with each entity. Is this normal? Is it a good idea? Typically w…

The problem is this: the summary field (total of all records) doesn’t update because the calc field (total of this record) is unstored. Obviously I need the sum field to be updated the moment the calc field is updated. summaryofcalcfield.fp7.zip

I am creating a database which will accept new orders and look to older, shipped orders and last names to see if the new order is from a prior customer. This will allow me to assign the prior customer ID number rather than a new one. The relationship is order_status="shipped" AND lastname = lastname. Unfortunately, it doesn't work. Only the lastname match seems to work, not the order_status=shipped. Any thoughts? I am using FMP 8.5 on a Mac. InternetVenturesDownloadExp.fp7.zip

I've got two tables: one of plants and one of butterflies. The plant table: Each plant record includes two drop down lists with repetitions. 1. for butterflies that use the plant as a host plant. 2. for butterflies that use the plant for nectar. The butterfly table: I want to display the plants that the butterfly uses as a host plant and nectar plant in separate fields because these are not the same plants in many instances. I've made a relationship between the two tables and am able to display the host plants in a portal. I did this by connecting the field "butterflies that use the plant as a host plant" in the plant table to the "butterfly…

I have a database that holds contact info and donor info. Obviously there is only one contact entry (Jane Doe) with multiple donation entries (supported fundraiser 1, fundraiser 2, fundraiser 3). I've created the tables but want to know how to relate them so that I can click on the person's name and select multiple donor categories. I would like to be able to do this on the form and the table format. I know this is pretty basic question. But how do I do this?;)

I am hoping this is simple for someone. In my insurance company database we have the birthdate of the client, but I would like their age to pop up in another field once their BDate is inputed- any takers?

Hi All, I am trying to create a self-join relationship for a table and am not sure of how to define it. The fields in the table to be used for the join are period number, fiscal year, department head code and account number. I want the join to select all those records whose department head code = 1 and account number != 144006. So I create two calculation fields cField1 = period number & fiscal year & Department Head Code & account number. Now I dont know how to define the second calculation field. A part of it will be cField2 = period number & fiscal year & 1 & what should be here. If I omit the account number fr…

Hi, I have a file called jobs, with different layouts, one of them called tasks and one leadtime. In tasks I have a portal with multiple tasks for that particular job (based on a relationship), this works fine.. In leadtime I have another relationship to tasks (based on a global which I set everytime I launch), this works fine too. Problem is that when I edit the row in tasks, it seems to disappear in leadtime on be disabled. The relationship is based on getting all tasks with the type Get Sound, after that I sort them so I only see the latest date.. Seeing it works fine till I start editing or adding a new get sound task. Does anyone maby know a workaroun…

Hello all, I am quite the beginner with Filemaker. I made a post here once long ago, and was able to work out a solution for our current database problem. Since then, I haven't done any filemaker work. Nowadays, I've been tasked with designing a database for handling new production our lab is undertaking. Its a monstrous task, as the database will be quite complex, and I have only tinkered under the hood on an existing filemaker database, never built one from the ground up. I foresee many difficult upcoming problems, but here is one of the first ones. I shall focus on two related tables, Shuttle Vectors and Peptides. Each record in Shuttle Vectors will refer…

I'm getting an error stating "" could not be opened. (Not Found) when tryinh to open a database from a flash drive. The account I'm using is a managed network account. When I take the drive to another computer I can open the database. I can open Word documents on the flash drive. Is this a permissions error with my account or is it a error in the program? I can open database files from my desktop (network account) and other locations in my home directory. Just not the flash drive.

I would like to be able to create a function that accepts as it's input parameter an Employee No (EmpNo) then have the custom function query a table (ADMIN_EMPLOYEES) to return the related Employee ID (EmpID). I'm doing an upgrade/conversion from FM5.5 and the EmpID is a auto-generated field. My problem is that I don't seem to see a way to process a query/find within a custom function, or am I missing something?

I've inherited a database that uses an external file that is linked in a relationship to fields in the main database. There are 5 fields, A-E. When I enter a value in the field A, the other 4 fields are automatically filled in with values from the external file, based on a lookup of the value entered in field A. How is that done? I see no script or button which says "automatically add the values to the other 4 fields?" If this is the wrong forum, could you point me to the correct one? Thanks.

Hiya, I've got three tables; Table 1 is Events & Table 2 is Participants. Table 3 which links them all is Registrations. Using the Missing Manual book, I created this database as a data tunneling interface... Each have portals on the layout. Layout 1 for Events lists in a portal, the people attending the event. Layout 2 shows the Participant details and then lists all events they've been at. On the Participants layout I go go to the related event, and the script is working fine. On the Event layout, I can't go to the related participant record. It comes up with an error saying it's can't run script as the target is not part of a related table. …

Could anyone be so kind to point me in the right direction, here is what I am looking to do: I have a table called factoryPricing I also have a table called Inventory. These tables are related together by a common field ProductID. The factoryPricing table contains quotes received for a product in inventory from a factory. There may be multiple quotes from a factory on a single item in Inventory. I have a field in Inventory table called Cost that I would like to pull from a field called QuotedPrice from the record related to that product in the . However I would only like it to pull the most recent quoted price. I have a auto filling field in factoryPricing call…

If a user defined date is created for one side of a relationship and the other side is defined as a date field for portal records, portal data is displayed for that user defined date. Is there a way to have a realtionship that returns all records for a range of dates, say, the user defined date through the user defined date + 7 days?

I'm making an order / invoice / inventory system for internal use in my company. Orders and invoices are easy, I'm having difficulty deciding how the inventory part should work. In this case the items are shoe boxes. What I'm doing now is as follows, but I don't like it and would like to do it better. The "inventory" table stores the item numbers (and other details) and a calculated balance. The calculated balance is as follows: Opening balance (based last physical count) + incoming (Sum of incoming qty from inbound file) - outgoing (sum of delivered qty from invoice file) + / - Adjustments = calculated balance. This will give me an accurate (ie sy…

Hi to all, Wondering if anyone can help with an issue I've got with serial numbers. I'm new to databasing so it could be an easy thing... Maybe not! What would the calculation steps be in creating a serial number that included letters as well as numbers, and those letters being the result of information checked in a radio set? For eg... An Australian contact who attended a training event I would like their serial number to be A (for OZ)+ 001 (serial number)+ T (training event). With the result looking like this A001T Thanks... :(

Hi, Is it possible to duplicate a relationship table in relationship graph window so, that also the relationships get copied? If I click the double-cross icon to duplicate a selected relationship table, the resulting copy is only an empty table without realtionships, eg. Table1 (several relationships) gets copied as Table1 2 (but no relationships). Filemaker Help describes the function of the double-cross button as "Duplicate selected tables (and relationships) and selected notes" I must be missing something here? Jari V

I have my solution almost finished and i have created a User settings table but i must admit i have never created a Multi user solution so i am ignorant on how i load these into globals at start up (so i can use them without relationships). Can someone give me a heads up on this or a real simple example. I am stuck and wasting time any help would be appreciated

For some reason I am just blanking on how this should work. Here's the setup: In FileMaker Pro 5.5 (or 6)... I have 3 databases. (1) INVOICE (2) PRODUCT (3) CONSTITUENT Each product may be made up of one or more constituent pieces that have a distinct weight. Each Invoice would be made up of one or more Product that each have a distinct price. What I need is a *lookup* that gets the product name and price from the PRODUCT database and puts it into the INVOICE database. That's the easy part, which I've already done. I also need the product weight... which is derived from the CONSTITUENT parts. The challenge is that I need the PRODUCT wei…

I am trying to find a way to add sub-set identifiers to my primary key, i.e. "123" number field is an auto-entry serial primary key. I need to find a way to create 123-a, 123-b (which is a part of the same record, but has different info. Any suggestions.

I have a relation setup between my inventory table field "sku" and my supplierinventory table field "fk_sku" and it is a one to many. I have a record in the SupplierInventory table: ID: 5632 SupplierSku: 123 fk_sku: rv0365 etc... When I look in the inventory table, there is a portal that shows the item listed with the supplier for sku number rv0365 like it should BUT it ALSO lists it under the 0365 part number for some reason. Any ideas?

Hi Guys, I have an unstored calculation (SUM value of related values) which is displayed in two locations on the same layout. Sometimes, the two locations do not show the same value until the screen is manually refreshed. I understand that a refresh might be required after changing some supporting data... but that on the same layout the same field shows different values... Is this expected behavior ?

Hi, I have a one to many relation where I would need only the last generated record out of the many-table. How can I do that? I always end up with the first dataset. Thank you, Bernhard

Hi, While designning a database using the Anchor-buoy model, I am confronted with two possibilities: (1) TO1_1 (Anchor) = TO Globals (2) TO1_1 (Anchor) x TO Globals = TO1_2 TO1_2 is a second occurrence of the table TO1_1. TO_Globals is a table that holds globals for the whole database. The database consists of only one file. I could connect a global field in TO_Globals to a primary key field in TO1_1 or to TO1_1, thus creating (1) or (2). The final result is the same (notice there is a cartesian operator in option (2)). I can use either relationship to calculate fields, etc, as usual. My questions are: Would it be noticeable faster o…

Hello, I am attempting to create a portal that will list all of the establishments from one table (REP_establishment_distance (TABLE: ESTABLISHMENTS) ) on another layout REP_Representative (TABLE: Sales_Reps). The problem is I only want to list the records that are within x miles of each other which can/is calculated with a Custom Coordinate Function. In the REP_ table, I have a static fields called WithinDistServiceArea. In the Establishments, I have a non-stored calculation field called EstRepDist. The distances must be calculated on the fly because there are 150 different Sales Reps and over 60,000 Establishmnets. Each distance is different for each Sale…

I have a Point of Sale System that has a table of inventory items. I buy these items from different distributors. Sometimes I have 1 item manufactured by 1 company but I cannot buy it from the manufacturer, so I buy it from a distributor. Sometimes I have more than one distributor though How can I relate my Inventory Item to many distributors and the distributors to the one manufacturer item? I thought the simple one to many relationship would work but I feel like there is a better way to do it...

Hi there. Please excuse me if this is a dumb question, I'm outta my depth here. I'm a screenwriter and I'm trying to build a database for working out my stories. Here's what I need to do: Any given story has a number of threads, each thread has a number of scenes and each scene is broken down into a number of beats. I need to be able to keep track of which story-thread-scene I'm working on so that I can tie each level to those above and below it. I'd like to be able to do finds, sorts and print outs for a story, thread or scene without having to go through the whole family tree. It would also be good if I could end up with auto-entered serials for every scene number…

Hi, hoping someone can spot the problem in my design quickly, since i'm way late on producing this for our group..... : i have placed portals on four of the layouts (CD4, Followup, OI, Regimen), and I thought that they would automatically create a new line when i added data to the current record, but they are. hitting the "New Record" button while in the portal fields produces a new parent record, not a new child record in the portal. PEPFAR_ChartReview2.zip

Hi all, File is attached. I think there is a "simple" resolution to this, but doggone it, I don't know how to do it. I have two "sections" (manuscripts and contacts) in my database. When I click on the buttons to either "assign a contact" or "add a manuscript" - I go to the correct page, but when I click on the arrow to add the manuscript/contact, I receive the message "The action cannot be performed because this filed is not modifiable." I click o.k., it takes me back to the form layout, and it has entered the data. It seems to be working, even though it is giving me that message. How can I remove/resolve the message? Thanks so mu…

Hi, Relative newbie question, here. I would like the "Contacts" table - ID, First name, last name, and institution - to populate in the "Author," "Original Submission" and "Resubmit" tabs, accordingly, when I list the "Contact ID" in a portal for each tab. Basically, the "Contacts" table contains authors and reviewers of/for different manuscripts. I would like to create a portal in each correspnding tab, where I can list the "Contact ID" in the portal and it will list the author/reviewer's - first name, last name, and institution. I have attached the file for review. Thanks for any help in the right direction. Regards, Seattleflo ma…

Have split Database A and B into two master files and two data files ie, “A Master”, “A Data”, “B Master”, “B Data”. Master files have only Layouts and scripts and no tables while data files have only one table each and nothing else. Master files use the data files for their data. The files were converted from the days of only one table per DB and it's too hard to join all master files into one. Relationship A to B is one to many. Prior to splitting the two, I had “Go to related record” scripts getting me between the two databases. These no longer work because when I’m in A Master I am related to B data and from B Master I am related to A Data but A Master and B Mast…

I have two files (PO's & Invoices) each with its respective table data (PO_data & INV_data). From within the Invoice portal I would like to be able to enter "line number 1" and have it display the corresponding line number data (i.e Part No., Description, price, etc) from the PO file. Either displayed or copied via a lookup. PO Key = PO_No_id I cannot get the relationship right !!

I have one table with an unstored calculation field (past due amount). I am trying to write a script to go to another table and set field (expense) with past due amount. The expense field is in a portal on the main layout and I cannot get the past due amount to transfer. All other fields transfer without a problem. Please help.

Hi everyone, Thanks for having a forum on filemaker!!!! My question is how do I create a relationship, so I can change information in a record that has already been created. Sample10.fp7 in the Filemaker tutorial is my example. In this example, you will see how you cannot change the company name in a record once it is created. How do you create relationships so the company name can be changed if someone changes jobs? I realize this is a newbie question, but I cannot find the answer from my searches. Thanks!!! Tim

I just began with developing in Filemaker and have some questions of which I didn't find an answer (also by using the search function of the forum). For example, I have 4 tables: 1. Firm-table with FirmID 2. Firm_Heading-table with firmID and HeadingID 3. Heading-table with HeadingID and ThemeID 4. Theme-table with ThemeID Relations are as follows: Firm(FirmID) => Firm_Theme(FirmID) Firm_Theme(HeadingID) => Heading(HeadingID) Heading(ThemeID) => Theme(ThemeID) So the table Firm_Theme is a table with 2 ID's (FirmID and HeadingID The COMBINATION of these 2 values needs to be UNIQUE. One ID can be recorded more than once, beca…

Hi everybody! New around here. I have a database which has a couple of tables, and records that tie in to other tables based on a name field relationship. Problem is, if you change the name on a record (to correct a typo or for whatever other reason) it breaks the relationship with all the records in other tables using that name. To try and work around this problem, I gave each record a unique serial number that automatically gets generated when the record is created. This number does not appear on any layout, so it can't be changed accidentally. Currently this fix makes things less user-friendly, because to assign a record to a related record in another table, …

Okay I am reopsting this...my last post didn't seem to get me anywhere and rereading it, I felt as if it were just a mess. This is my second First Post am I am a real novice at FMP. I can usually figure things out but I can't seem to get the grasp of this. HELP, please. This is just the first step of many. I have about 4 DB I would like to join as one. I need to get the Client info from it's own sepeerate DB into the inventory DB. The info has been imported into the inventory and added as a Layout. Or it can be brought in from a completely sperate DB. What is the best way to make these two come together. I want to be able to add people in the invetory as well a…

I'm new to FM and a bit confused on how to create a relationship that will allow me to compare and do calculations between two records in the same table. Here's what I want - to input an item's physical inventory with a date, and compare a product's instock number to any previous date's instock number for all inventory items. I've created an inventory table that is related to itself by productid and the date in inventory2 is greater than inventory1. This layout lets me calculate instock2 - instock1 = difference. So, for an item I have 3 records in Feb. The dates are 2-28, 2-19, and 2-1. Right now it compares 2-19 with 2-1, and that's it. But I want to be a…

Hi I have set up a project management syestem, but would like to transfer projects whose job status is "A" (archived) - not "O' (Open)- to another layout. Thus seperating the active projects from those that are completed. This would make it easier for users to view the current projects which are listed. Information needs to follow the project to the archived layout. Is this a relationship issue or is there a solution to be sought in calculations to transfer the information across. Not sure where to begin and wondered if someome could help put me on the right path? Many thanks

Hi Problem: When I (auto)create a related record (through a portal -'allow creation' in the PortalRelationship is on-) I want to auto-enter the _IDKeyField in the new related record with a multikey. Field: _CategoryGlobal resides as Globalfield in the realted table auto-enter multikeycalc: (_CategoryGlobal & "¶" & Get(RecIDnr) When create the record by hand on the Lay-out of the related table (Current Table shown) everything works fine but when the record is auto-created through the portal the _IDKeyField has been set with _CategoryGlobal only. It seems Filemaker only auto-calculates in the active (current) Table. Am I right and how can …

Hi, can someone help me about how I can create a relationship with an OR-type criteria? Let's assume I have customers and events. Events can be open, or associated to a set of customers. For each customer, I want to list all the open events, and also all the ones associated with that customer. Is there a way I could do this in one relationship? thx

Hi, I am a little bit lost in the following. I have a Server/Client setup. External authentication. I am trying to use the Anchor-Buoy model. I have created several Primary Table Occurrences (PTO), and some Anchor-buoy functional groups. The following is just an example of the calculation context problem I have. I have a user table, in that table a calculated global field to autoenter the AccountName. In the Users table, I have an additional field for RealUserName. This way, I know for every user that logs, its real name. I intend to pass this real name to the UserName fields in the data table. Every time a record is created, the CreationUser fiel…

For starters, I'm in FMP8.0 on a PC with 1 gig of ram and lots of storage. I know there has to be an easy solution to this, but I'm drawing a blank. I have three databases for a school-type setup: Participants Offerings Enrollments Participants keeps the student information including ParticipantID, LName_FName, phone, and email. There is only one record for each participant. Offerings has the class information, including Offering and Offering_Year_Semester (a single field which with the concatenation is a unique field). There is only one record for each Offering_Year_Semester. Enrollments has the sign-up information including classID, stu…

I have an inventory table and a Price table. The price table stores the date and sell price. The table is join using the Inventory ID. In the price table, user enter records as follow Date (dd/mm/yy) Sell Price 01/03/07 $5 15/03/07 $6 01/04/07 $7 15/04/07 $5 They can enter as many records as they wanted. From the inventory table, I like to be able to get the right selling price from the price table , depending on the current date. For example, today I should get $6 as sell price. Then when 01/04/07 comes, the sell price should automatically change to $7. The when 1…

Greetings! I have created a DB in FMP 8, that has many tables. Two of the tables (product_Sales and product_Inventory), both contain date, quantity, cost, etc fields. How can I bring them together to show all of the inventory added and sales made during a particular time period? In other words, dump records from two diffenent tables into one big table to manipulate the data for reporting. Thanks

Hello, Just learning FM and what I am trying to do is within an order entry system pull client information from a separate database populating the information into the main order entry data base by selecting a client from a drop down list...am I explaining myself clearly? any help would be greatly appreciated. thx G

Hey all you pros. I've been self teaching myself FMP for the last year or so and havn't been able to figure this problem out. I'm building a database to keep track of customer policys and the agents who sell them. My hierarchy goes like this: Agency -Agent --SubAgent Each policy has a main agent, but that agent could be either an "Agent" or "SubAgent". I'd like to be able to run a report listing all agents & subagents and their policys. Also, I'd like for the agent to be selected from a drop down and have their information to fill in automatically for each policy so the user won't have to type in an agent's information for each policy…

I'm a new FileMaker user, and I'm trying to figure out how to make a field that is a list of fields from select records in another table. Maybe a field is not what I need? I basically want to display a list of records from a table that are associated with a single record in the current table. Hope that makes sense. Thanks, Moses

You guys can do this in your sleep - for me - Im in hell and in way over my head. I have 4 tables. Customers,Jobs,Work Orders, and Batches. A customer can have many jobs, and each job can have many work orders, and each work order can have many batches. How do I write a script (or use a calculation field) to create a new job record that is related to that particular customer using some lookup info from Customer to job record? How do I set up the matching fields that form the relationship and transfer that field to the jobs record? So, if at some later date someone changes the customer name it will not effect the relationship. This approach keeps going..…

Hi everyone, I have a question for the pros amongst you. The situation is as follows: I have a layout (1) that displays data from table 1. I have a second table (2) and I want to display data from table 2 through a portal on layout 1. so far so good .. this all works .. but here comes the kicker .. Table 1 has a diver identifier number (e.g. 1-10) Table 2 has records (diving projects) that contain this customer number in multiple fields. For example: diver in charge, dive team member, dive team leader, surface attendant, etc. I want to portal in Layout 1 (that displays data from Table 2) to display ALL records from Table 2 that contain the id…

I hope this is in the right subforum! This is kind a "newbie" question, but I'm curious - are there any detriments to having lots of small related tables rather than one big one? I'm creating a new database that's going to have several pieces of data that may have multiple values. In the past, we just did something like: Area1 Area2 ... Total Area = Area1 + Area2 + etc Now that I've taken over, I've set up things like that in related tables and used portals to show the info and used a SUM function to get the totals we need over into the "proper" table. For the new database I'm setting up, there's going to be lots of fields that will need to be in …

I am fairly new to FMP, this is my first post. I have done a little bit of self taught work but am now undertaking my first large project. I am building a "Project Tracking Database" and I need some help on relationship layout. This database will track individual "Projects". When a project is created, the creator has a list of "Clients" to choose from that the project will work with. Each Client has "Regions" and under that "Areas" and then individual stores in each area.(A Client could have 3 Regions each with 4 Areas, and each area could have 10 Stores, for a total of 120 Stores.) I have a "Client Manager" page where Clients can be added along with Regio…

New member here, I'm looking for some "relationship advice," lol. I have a file, in which I record glucose readings for a diabetic. There is a table, EVENTS. Each EVENT has, among other fields, a "Period". Each EVENT is also linked, through a join table, to an arbitrary number of FOOD ITEMS. The function of this file is for the user to enter in some of the data on his next EVENT, and be presented with data from past EVENTS, filtered and sorted for similarity to the EVENT he's entering now. I've accomplished this by, on the EVENT table, having a series of global fields designed to accept his new data, and once enough fields have been entered in the entry layout i…

Is there a simple way to tell file maker to copy selected fields in one table to selected fields of a second table? How should the relationship be set up between the two tables? I want the fields to be identical for both tables for each record created! Thanks in advance for your help!

I have a database with three files. Tickets, proposals, and proposal line items. A Ticket can have many proposals. a Proposal can have many line items. Withing a proposal, there are categories, which i have created with tabs. I'm using the "isfronttabpanel" to set an unstored calc as the primary key. On one screen in tickets, I show a list of all related proposals, next the the proposal you want you click view, which sets another key, this lists that proposal data. Now you click on a tab within that, and another key gets set based on the tab you're on. The data below in the portal doesn't seem to "refresh" correctly unless I go in and out of the record, or go to layout…

Hi, I have an "invoice line" table which has, amongst others, "Item Code" and "Shop Code" fields. I use another table "Invoice Line Summary" to group my invoice lines per Item Code and Shop Code. So, Invoice Line is linked to Invoice Line Summary via Item Code and Shop Code (with "=" operator). Now I would like to use my "Invoice Line Summary" table to group the Invoice Lines for ALL Shops. So, basically, I would like the "Shop Code" field in my "Invoice Line Summary" to contain "any value". I know I can put "all values" in the Shop Code field (separated by return) and it works fine. But I was hoping I could put some kind of wild card in…

I'm creating a database for my babysitting business. The three main tables are "Sitter", "Client", and "Jobs" with multiple jobs for each client and multiple jobs for each sitter. I think I have the basic relationships correct, but so far in order to add a sitter or client from the Jobs page, I have to type in the sitters ID or the Client ID. I want to be able to select client or sitter from a drop down list instead of having to know their ID numbers. Any suggestions? I've included a simplified version of the database to help things out. Thanks in advance for your help. Sitters.zip

Hi. Apologies in advance for a question which I imagine has been answered several times before: I just couldn't find an answer in a form I understood. I have two tables - Feeds and Stories. Feeds have many stories, stories have one feed. They are linked by numerical primary and foreign keys. They both have title fields. In a Story view, I want a popup menu which allows me to select the Feed (by title) which is to be related. I can create a popup menu populated by the Feed title, but I don't know what to do next. I tried assigning the popup on a Story layout to the related Feed title. This had the effect of overwriting the Feed title. I created a new fi…

Hi, I have what is probably a very basic problem to most of you. I am having a very difficult time wrapping my head around this whole relationship thing. Here's my problem... I have 3 tables and they all have one ID field that is the same in all 3 tables. So in the relationships graph I indicated that the ID field in Table A is the same in table B. And that the ID field in Table B is the same in Table C. This works fine when I have a field in table B looking up information on Table A. But I run into problems when I add a field in table C looking up information on table A. I can't understand where I went wrong. I've added an example of what I'm talking about so you ca…

Hi, I am relatively new to FileMaker Pro. We have a database that manages all our figures built in Access and it's my task to "move" it into FileMaker format. We have 17 different centers, each with a "Center ID Number", and every Monday they send in data from the past week with a "Week Ending Date." All the data is stored in a "Weekly Figures" table. I want a layout where all I see is the 17 records (one from each center) with data from the most recent "Week Ending Date"... I'm thinking Max(Week Ending Date) maybe? And then next week when the centers submit their new numbers, the layout shows those numbers. I hope that makes sense. Any suggestions would be gre…

In a resources table there are two date fields, StartInSeason and EndInSeason. In a second table, housing reservation data there are two other date fields, ReservationStartDate and ReservationEndDate. I'd like a relationship between these two tables that can be used to test to see if any of the dates in the reservation date range fall outside of the InSeason date range. This relationship could then be used in scritpting or calculations to pull the correct set of billing rates and other house keeping tasks. Any suggestions?

Hi all I have been reading about the pros and cons of the separation model and I am thinking about using the separation model in a runtime solution that I have written. I known the reason to do the separation it for easy of upgrading the front end (data entry) without messing with the back end (data storage). But I have a question that been bugging me about the relationship part. You have to have at least a couple of relationships from the front end (data enrty) of the program to the back end (data storage). If that is true, then how do you recreate these relationships to the old back end (data storage) to the new front end (data enrty) when the old front end (data e…

I'm trying to do something very simple: In an invoice, I choose a Product ID from a drop-down list based on another table, and would like the Product Description from that other table to appear automatically for that product, on the same line. Using a Lookup function I was able to set this up correctly. When I add repetitions, however, to be able to list several products on the invoice, the process breaks down and the Product Description does NOT appear. Why is adding repetitions not working in this case? Is there an alternative way to list several products on one invoice? Thank you for your help.