Relationships

I have a main table that can have multiple related items in another table. That table can also have related items. And, that table can have related items. I have tried a portal inside of a portal inside of a portal with no luck. What is the best way to display this data in FileMaker? I have created an asp.net web app and you can have a Repeater inside of a Repeater inside of another Repeater. It works like a dream. Some of my users need to see the same data in FileMaker. Recommendations?

Need help to build a report for this project: A trucking company has many contract drivers and each driver receive a weekly driver report with the containers they hauled and with subtotal earnings. I created this report by date range and driver and gross income. Here is the challenge: On the bottom of this report I need to deduct for the pay period expense items such as gas, insurance, damages etc. from the earnings for each pay period and each driver that is deducted from the gross pay. I like store the expense information by driver and by pay period in a separate table but do not know how to best access and import the expense info into the report. (Matching…

I have made a table that every time that a record is created it gets a serial number.I have made a relationship with the other main table to display the newest created record.It works as i want till i get to the number 99(I started the autoserial at 00)when i make new records above 99 they dont show to the other table.What can i do to overcome this strange behavior?

Hi I would like to build a relationship to populate a portal where. Customer Invoice Cust_pk = cust_fk g_zero < amount outstanding where g_zero is a global = 0 I cant use a calculation in the relationship because I get the amount ourstanding from another table. Invoice Allocations invoice_pk = invoice_fk amt_outstanding = invoice total - sum(allocations::invoice total) I can make this work by doing the calculation and setting a separate field with fixed results (either the amount outstanding or a calculated cust_key. I am thinking there must be a more elegant way to solve this display of part paid or unpaid invoi…

Hey All, Anyone know how to implement a conditional lookup? Here's the problem: I have three tables: Customer, ContactLineItem, and Proposal. In ContactLineItem, there are two fields: ContactType and Number (ex. data: Job Site, 555-5555) In the Proposals layout, I want to create a lookup field that will look at the related records via customerID and copy from the ContactLineItem the telephone number if the type equals "Job Site". How do I do this?

Okay, slightly confusing in terminology but, I'll try to be as clear as possible. I have a database that records various aspects about patients. Often, we find two two patients will be related, e.g mother and daughter, or siblings. I would not only like to capture this information, but also be able to add other family members in, but not necessarily add them as Patients. So the tables so far are: PATIENTS - all info about patient FAMILIES - info about family as a whole Now what I'm aiming to do is have some sort of a join table that looks a bit like this: JoinID | FamilyID | PatientID_1 | Relationship_1 | PatientID_2 | Relationship_2 …

I am trying to recreate a portion of a database for my company. I am currently using a database where you can enter notes into one notes field and press a save button, which will take the notes from the 1st field and enter it into a non-editable notes area and will also place a date / timestamp / username in front of the notes. The user can also put a check mark in a box to have that same note entered into a main notes area for a client. If this option is checked, it will place the note in a main notes area and will also include the name of the creditor the note is specific to. I am looking for help or input on the best way to re-create this type of process a…

I have a table of info for customers that has a contact field that has 10 repetitions(10 contact names). Each name has a field for each contact Phone number(also 10 reps). My problem is i have a layout that has a relationship between the Customer field in each table. The user selects the Customer, and the field for Address populates with the address from the customer table, as required. I placed a field(contact) on the layout now, and all contact names for the related Customer are listed, as required. But the next field is the contact phone number, where i am having difficulty in have the data displayed. If i select say contact name 3, than i would need…

I have an attendance database with two tables, Student Info & Attendance. Student Info has just that, one record per student with a key field. Attendance has one record per class attended for each student. I have a script driven report that finds for the desired month, then shows the days and total of classes each person has taken. That part is working. The issue is that I now need to pull out the students that have ZERO classes attended for each month. Should be simple. I'm losing my mind. I've tried various sub summary reports and portal configurations, but the best I can get is a listing by student with the months they attended at least once, not a short …

We have a very complex 92-file system in FM9/FM 9 Server which controls all vital business operations. There are 4 files pertaining to Purchasing activities, and they relate in various ways to some of the non-Purchasing files. My goal is to consolidate these 4 files into a single file, with as little down-time as possible since this is a live system in use 7-days a week. I've done a thorough internet search for technical papers, discussions etc. but haven't found anything that outlines the most efficient method to achieve combining of multiple files into one. I've only found info about migration and conversion. Anyone know of any resources out there? I'm going…

Hi All, We have some issues with FileMaker reporting output that I’m explaining it below: In both FileMaker and Access we have the same table structure and records. In MS access I already created report that is called Quary1 that outputs a report very well for our users. But when we tried to run the same report in FileMaker Pro 10 we get a different report output that it is not correct and it is missing some data. Can someone let me know why we can’t get the same report output in FileMaker pro? Here are the link to both FMPRO and ACCESS files: http://www.4shared.com/file/167496742/fc914e4c/FMPRO_ACCESS1.html http://www.4shared.com/file/168448…

Hey guys, I'm trying to create many-to-many relationships between entries of the same table. After running this by a few people and doing research we're not entirely sure this is possible, even though it seems pretty simple. I have set up a join table and a portal to display all related contacts but what happens is that I'm only able to view the relations going towards the second occurrence of the primary table but when I go to the contact related to that contact, I see no connection. To give you a clear example, if I link an agent to a model, I see the model as an associated entry on the agent's page but I don't see the agent on the model's page. I seem to…

Hi, I have a table called "projects" and a layout for it on which there is a portal to a join table that allows the user to select a "role" related to the project and then assign a "talent" to that role and project. From this portal I want to click on a talent name and have a pop up window show a shorter version of that talent's full profile. However, because of the issue with windows (operating system) and pop up windows - how a pop up one resizes your iniital window if it is maximized - I've opted to create a "fake" pop up window by designing a layout that looks like the initial window with a "pop up window" on top of it. On this new layout with the pop…

First, my background. I am using Filemaker Pro 10 Advanced on Windows Vista and Snow Leopard. I am fairly new to Filemaker in that I have created some simple databases and I have worked with Access in the past. Now for my issue. I am trying to create a database to include the following: clients, orders and expenses along with a few other things but I have that part figured out. The clients table is linked with the orders table, since every order will have a client. The expenses table is not linked to a client because the expense may occur with or without a client. Therefore, I would like to create a financial summary of both orders (which would be income), alo…

Hi, I am creating a database for a talent agency and am currently working on assigning ROLES to PROJECTS and then assigning TALENT to ROLES. I have a layout for projects with a portal to the "roles" table for creating roles for the project. I also have a layout for projects with a portal to the join table for roles and talent. So from the portal on the project page you can select a role related to the project and then select a talent to assign to that role. The actual join table layout for role/talent (which ultimately will not appear on the database but which I use to test and make sure records are created properly through the portal) works great. On …

Here is a screen cap related to my question: The Alphabet buttons are scripted to set a field to their respective character (ie. the A button sets "A"). When a button is clicked, it sets a field called CustomerJoin to its respective letter. In my Customers table, I have a field calculation that determines the first letter of Customer Last Name. I have a relationship set up between my customers table and this user interface table: Customer Last Name = CustomerJoin The end result is: when I click on a letter, my portal shows me a list of all the customers in my table whose last name start with that letter. Here's the problem: I want to start …

I have that I missing the obvious feeling. I am working on a PATIENT layout with an APPOINTMENT portal The relationship is main_PATIENTS main_APP_history patientID_pk = patientID_fk The portal populates fine and I can GOTO RELATED RECORD in main_APP_history fine. However if I try to GOTO RELATED RECORD in main_PATIENTS from a main_APP_history layout I get an unrelated table error. What have I missed? Thanks

I am working with 2separate fields[item]&[price] that has 2 repetitions each on a layout(page 1). On another layout(Page 2), i have a repeating field(10)[items available], that lists items that are used in the dropdownlist of [item] on Page 1. There is also a repeating field(10)[cost of items], that list prices for each of the items available. I want the user to select an item from the dropdown list of [item], and the [price] field will pull in the value from the other field in Page 2 that relates to the item selected. How do i do this? I created a relationship between:- [item] - [items available] , but i always get in the [price] field, the v…

I have a database with of names of people and a database with seat locations at tables. I can make the table assignments; however, I would like to be able to graphically represent the table. How can I have one Layout that shows multiple records not in a list view. IE if I have 20 stools at the table, I would like the 20 records around a table. Each could drop down with the names. I can get all of the relationships to work, except I can't get the Layout to graphically look like a table. I can only use the standard list view. Any ideas?

Sometime if I relate to a calculation field the relationship works but at other times it doesn't. I think it is when the field is unstored, but if I try to change the store option FM wont let me (saying it references a related file, or unstored calculation) . Is there a method to get this to work? thanks

Need help with a design problem.. 3 Tables: Accounts, Contacts, Work Orders Accounts contains names of Companies, addresses, etc. A "company type" field has choices (Real Estate, Escrow, Construction, Church, or Other.) via Value List. Contacts contains people and contact info. A "contact type" field has choices (Realtor, Escrow Officer, Manager, etc) via Value List. Accounts & Contacts are linked by "AccountID" Work Orders table is my problem... I need to show this information: 1. A Company & Contact (in this example a Real Estate company & Realtor with their Contact Info) 2. An Escrow Company (and Escrow Officer, Info…

Im migrating from FM5 so the new relationship optins are intriguing me. DO you think it is better to use an AND relationship. Eg. Date = Date AND ID = ID or caluclate DATE & ID = NEWKEY in both tables then relate NEWKEY = NEWKEY I guess my question is, is it better to have more calculated fields or a more complicated relationship chart? What are the advantages of either method. Ive aready got the calulated keys fields from the FM5 application. Thanks John

I have a table that contains case numbers. In my job, each case number could be related to another case number. Example: 08-123456 has the same suspect as 09-555666...so it needs to be noted that they are related. So I wanted a portal with a dropdown list on the main case number layout that I could simply pick one or ten other case numbers that are related. All the case numbers are in the table so they are all available to link to. Then I would simply use a button to go to the related case number. Any suggestions...I have searched the forums and can't figure out how to do this.

Alright, this is a little odd. Someone set this database up to be dictionary-like or even choose-your-own-adventure-like ("For more, please see record..."). So each record can refer to another record within the current table. That being said, you can have a string of child > child > child > parent. Basically some records are a "continuation" or "more info" about the parent record. So what I'm trying to figure out is, how can I collect or snowball data together from the parents and grandparents....on each of the Children records. Preferably without a script! (I'm trying to stick to calculations.) The data structure is set up like this: KEY K…

Hi, Im new to FileMaker as I am a MySQL user. Im using FileMaker Pro 10.0v1 on Windows Vista. I'm trying to complete a practical which is suppose to be very easy ..im stuck on the last "button" task. Can someone please enlighten me? Here is the practical The database will have two tables: Table 1 Field Names: ID Number, First Name, Last Name, Job Title, Service ID Table 2 Fields Names:Service ID, Service Name, Phone Relationships: The two tables will be linked via Service ID. Data There will be six individuals in Table 1 (create their names and job titles yourself) There will be three services in Table 2 (cre…

I am migrating an application from FM5. Ive got tables of patients, doctors, and appointments. I want to show diarys for several docs on the same page. I have used the date and DocID to make a unique whole day keyID and use this to populate 7 portals on screen. This far works fine. I can get the patient IDs into the portal but I want to display the names. In FM 5 I just had on relationship between appointment and patients to populat the patient name in the portal. This is not possibel in FM10. I am using a calculated filed to lookup the surname and inital from the patient file. Any one done this? Any help appreciated. Th…

I would like to create a database of recipes, as follows: first table ING_BASE: 1-ID_IngBase 2-Name of Ingredient 3-perc_fat 4-perc_carbs ... second table ING_SEMIFINISHED 1-ID_SemiFinished 2-Name SemiFinished 3-ID_IngBase 4-Qty_gr third table RECIPES 1-ID_Recipes here my problem.... The recipe can contain many semifinished product and many ING_base ex: the sugar is ING_Base, and the syrup50 is a Semifinished product ,consists of the following ingredients (ING_Base sugar and ING_Base water). The recipe consist: 100gr of Sugar 200gr of Syrup50 how can do that, how can create the relations? please Help …

Hi, (Im not sure if im in the right forum) I have two tables First is Quotes – which has a relationship via Client _ID , and generates Quote_IDs, and Quote_detials The second table is Costing- it has a relationship to Quotes via Client_ID. I then have a value list that lists Quote_IDs that relate to the Client. When I select the Quote_ID, I then want the Quote_details to be automatically populated in a feild on this table, QouteText . I have created a look up fro Quote_Text to lookup Quote_Details in the Quotes table but it will not work? Can someone please explain to me how to resolve. Thanks dean

Hi Everyone, I have been working on a database solution for a large business for several months. Everything is operating great but now that a new addition is required I have been faced with major performance issues and would greatly appreciate some advice. This is part of a much larger solution - but I will merely mention what I feel is necessary for this issue. I have the following tables: Customers Assets (machines) Services Cash Line Items Asset Specific Data My problem is as follows: The Cash table has a cash record for every Asset in every Service for Every Customer. As a result, this table currently has over 150,000 records…

My background: I'm brand new to Filemaker Pro and I just got version 10 from my boss. I'm know HTML and Java but don't know to much about databases. I been playing around now for about 3 days, starting to get the very basics. I'm running it on windows 7. My Problem: I Used the Contact Management template to create a customer registration database and a Hotel List Database. I changed the values so they would fit for my use. Now, in the customer registration database, I've got "current_hotel" and "Hotel_number" as scroll down lists. I manage to link the "current_hotel" to my second database, hotel list, to display all "Hotel_name_sort1" as the options for th…

Hi I have data in a nice list: Asset ; Date ; Feature 1 ; Feature 2 ; Value[Asset, Date, Feature1,Feature 2] Asset ; Date + 1 ; Feature 1 ; Feature 2 ; Value[] Asset ; Date + 2 ; Feature 1 ; Feature 2 ; Value[] ... Asset ; Date ; Feature 1* ; Feature 2 ; Value[] Asset ; Date +1; Feature 1* ; Feature 2 ; Value[] and I am looking to transpose it to Asset ; Feature 1 ; Feature 2 ; Date ; Value[;Date;;;] ; Date +1 ; Value[;Date+1;;] ; Date + 2 ; Value[;Date+2;;;] Asset ; Feature 1* ; Feature 2 ; Date ; Value[;Date;;;] ; Date +1 ; Value[;Date+1;;] ; Date + 2 ; Value[;Date+2;;;] ... Is there a simple way to do this? I have around 3m rows at the moment…

I have built a report based on a table that compile the working time on different projects and another table with data specific to each project. I need to find the Overall average time (MINUTES) worked per linear feet. It goes well except for the average summary field that gives me a wrong number. The project look like this: Working time table project IDfk, dept, task, working time... (This table contain thousands of data from hundreds of projects.) Project table: project Id, linear feet (for each task)... Both tables are related by the the project ID and project Idfk fields. Report layer is based on the working time table. Sub summary Proj…

Hi, running filemaker pro 7 on a mac for the last 8 years or so, just had a clients details disappear, so the many many jobs now just have a blank in the client rather. Obviously I can re-input their details and reinstate their name on all the jobs, but is there an easier way, or is the client actually still there but I cannot see it? thanks sheldon [color:blue]FMS on OS X Information would have been available if the post hadn't been moved.

Hi all, I am quite a novice when it comes to working with Filemaker 10, but I am in the process of learning quite a lot. There is however one problem I have not been able to solve up till now. What I would like to do is use subsummary values from one table, and display that in a field in another table, accompanied by its break field. What it is: three indicators: GHG output, energy use, and waste output one recurring entity: (substituted) end products two subsequent supply chain steps: components per end product, and processes per end product two connect tables where calculations take place (currently only in connect table components) and…

I am a very recent convert to the changes in FMP that came in with FMP 7. I am working with FMP 9 and I am really struggling with concepts. I am trying to construct a data base for a non-prof that sterilizes feral cats. They want me to keep track of several types of persons, and what they are doing. As an example: A person may be a Trapper, a Volunteer, a Donor, or just a person of interest. I set up a table of Contacts that contains names, addresses, phones, etc. of all persons in any category. I then set up a table for trappers, and a table for donors. In each of these I assigned an ID number for each of these (Trapper ID and Donor ID) in each tabl…

i made 2 tables. 1st table with unique id, name age sex location occupation. 2nd table with some fields. now each table is asking new records . if i perform find i must get related records in both table of the same person what must i do big confusion pls help me pls :(

Ok, first time I have come across this one. I have 2 tables: cars and trips Cars simply has one field: pk_cars_ID; Trips has two fields: pk_trip_ID ans fk_carsID; I have the fk_cars_ID setup as a dropdown, showing records from cars::pk_cars_ID; but.. the trips::fk_cars_ID dropdown will only show the records I put into the cars::pk_cars_ID starting with the second record and on..it will not show the very first record that I put in....any clues as to what I am doing wrong? PS..all records show when I use a pop-up, just not a drop-down, weird??

Hi all, LaRetta and Comment both tried to help me with this problem a fair while ago. Events overtook me and I have now started again. I have included a sample based on what I have read on this forum. Explanation. This is a db to manage my Share portfolios and track profits and/or losses. There are Buy contracts and Sell contracts. I cannot Sell unless I own the shares (ie Buy Contracts always precede Sell contracts). I need to know in any one financial year what profit or loss I have made for each Sell contract in that year. (1st July to 30th June). This is calculated when the shares are sold. I may have multiple buy contracts for the same company. I can d…

The title says it all....please bear with me as this could be complicated. As part of a manifest system I have developed, I need to know how many passengers departing each location upon a route. For example Route: A - B - C - D - E Passengers can board at any location, and hop off at any location. My goal is to choose a location, and know how many pax on board departing based upon when they boarded and when they are getting off.. For example, departing C could include pax who are: A-D , B-E, C-D etc As you can see on a longer route there could be quite a few possibilites. I also need to ability to rearrange the route. So.....and ide…

I have 3 tables: 1- A Dashboard table with "search" fields that I use to filter records using portals 2- A Patient table with basic patient info 3- An Appointment table listing all the appointments I am trying to get a portal on the Dashboard to show all the patients whose last scheduled appointment is between two dates I set up a calculation in Patients, "Max (AppointmentInfo::AppointmentDate)" that displays the date of their final appointment, but have hit a dead end since I can't use that calculated field in my relationship with the Dashboard right? I know there is an easy solution somewhere, but can't find it. Any help is greatly ap…

I have a portal that I want to show records where field is less than 0 and not blank. So I made a calculated global field which which always evaluates to 0, and a relationship to records with criteria: field < global But I have learned that a blank field has a value (in relationships) that is lower than any entered value including negative numbers. What is the preferred way around this if I want to show related records that have a value <0 but not blank? Would I just need to make a new global field with an extraordinarily negative number that the related field must always be greater than?

Hi, This is my first post and I'm pulling my hair out on this one! I have a db with 2 tables. one is a recipes table with fields for ingredient 1...ingredient cost, ingredient 2...ingredient cost, etc etc. There are fields for 10 ingredients. The 2nd table details all the ingredients and their costs and other attributes. I want the db, when a user types in the ingredient in 'ingredient 1', to look up the cost for that ingredient and display it in ingredient 1 cost. Then do the same thing for each ingredient. Maybe I'm going completely the wrong way about the structure, but I've tried making a relationship between the 2 tables with each ingredient in the recipes …

Hi, this is my first post & I'm wanting to pull my hair out. I'm brand new with FM, so go easy on me. I've spent 17+ hours on VTC learning this program, but this relationship thing is killing me. Here's my scenario: Tables Contacts Event_Registrations (join table) Events (classes or trips) Event_Fees (trips can have multiple fee levels) I want to have a portal in Events to add Contacts (Event_Registrations) to the event. In that portal I want to choose the Contact's Name, select their Fee Level (Event_Fees) and have it enter the Fee associated with that fee level in the 3rd field. I have the 4 tables related in the order they're listed above wit…

I'm trying to design a database for a business that (let's say) furnishes executive offices at large companies. Each contract is negotiated, so what Company ABC pays for a chair is not necessarily what Company XYZ pays. I'd like the solution to be expandable by the user without getting into the schema, so I'm thinking: CUSTOMERS table with Customer and CustomerID ITEMS table with Item, Price, CompanyID and NegotiatedPrice. LINEITEMS with Item, Price and NegotiatedPrice, the latter 2 as lookups from ITEMS Each time a new contract is signed, the user can add a new record to the CUSTOMERS table. I'd have to script a duplication of all records in th…

Hi, Is it possible to create a report from a 4 (or more) related tables? Thank you

Hi, I am trying to create a relationship between 2 tables. I have created one file, with 2 tables. I have created a relationship between the tables in File->Define Database -> Relationships. I have set up the matching fields and it all looks correct. What I don't understand is how to get a view/layout which shows me the data in the linked tables at the same time. I can only view either table on it's own. Is there something obvious I am doing wrong? Many thanks Mark

It's been a while since I have had a FileMaker project and I just can't figure out how this would work... I have two tables, a main contact table and also a related contacts table. Both of them are linked based off of a primary key in the main contact table. I would like to have an all inclusive search that would find a name in the main and or related contacts but return the main regardless. Example: Main Table: Name: John Doe Related Contacts: John Apple Jane Smith Jane Doe The user would either type the search criteria in a dialog or global search field in find mode. Let say they would search for "John Apple", it would return th…

Can anyone help me on the road? I have 3 tables: ingredients products invoice items Product_ID----=-------ID-----=-----Product_ID fields of ingredients name Quantity/product fields of invoices Quantity products 1 invoice can consist of several products. 1 product consists of several ingredients. 1 ingredient can avoid in several products. 1 product can be 'ordered' by several invoices. I can see how much of each ingredient is needed in total for all invoices. And I can see what ingredients are needed for each single invoice. What I can not see is how much of each i…

Is it possible to use a relationship to collect fields from two records in one DB to be inserted in one field in the other DB, both based on the same relationship? I think the problem is that the match field is not unique in the first DB described here. If this won't work, can you suggest a way to do this? Thanks.

I'm brand spanking new at FM and working on my first app which is to track my budget and expenses. My db has two tables: Budget and Transactions. In the Budget table I have the following fields: Account Name; Account Type; Budgeted Amount; Current Balance; Difference; Budget Month In the Transactions table I have the following fields: Date; Description; Comment; Check Number; Amount; From Account; From Account Type; To Account; To Account Type What I want to do is increment or decrement the Budget::Current Balance field for given Accounts when I enter a transaction. In other words, I'll enter a transaction, say for 15 dollars, from th…

I've got a FM database set up to track orders. Currently, there's a basic one (order) to many (orderline) relationship that's used to create order. I'd like to add shipments into the mix. Each order can have multiple shipments (not all items necessarily ship at once) so, I've created a shipment table that has a one (order) to many (shipment) relationship with the order table. I am able, via a portal, to pull up an invoice number in the shipment record and see all the items (related orderlines). What I'd like to do is this: create a shipment for an order and then be able to select which orderlines/items I'd like to ship for that particular shipment. In other words,…

I have a parts table, purchase order table, and a line items table. I want to be able to display only open purchase order line Items in a portal on the parts table based upon a part number. I have setup keys and a relationship between the parts table and the line items table, but the key in the line items table is based on a calculated result that cannot be stored so it does not work and I'm not sure where to go from here. I have included a sample file. Any help would be greatly appreciated. Parts_File.zip

I currently have a file that contains information about all of our students. I have a separate file that I create called high school and then import all of the high school students from the all students table. Then I use the high school file for whatever I need to do with the high school students (grades, etc.). The same for elementary school students. I would like to get with the times and create one file that has a table of students (and other tables. like grades, etc.) as well as a separate occurance called high school that only refers to high school students. SAme for elementary. SO I want to be able to go to a high school layout and only see high school students. How…

I'm a novice user of filemaker, and had no formal training aside from working with it myself. I reworked a database for a friend which consists of 2 related databases:Customers and Contacts. To put it simply, entries written into 'Customers' are recorded in matching fields in 'Contacts'. This was all working fine over the last few months until my friend decided to import some extra records. He only selected 'Add new records',and neither of the 'Update Options' in the 'Import Field Mapping' window. Yet this resulted in deletion of all his existing records. He restored the 'Customers' database from an older local copy, and his records reappeared, but now the 'Notes' entri…

I have 2 tables: Employee - Department Meaning - One employee belongs to one department. One department can have one or more employees. I would like to make 2 report: * Employe -> department * Department -> emploeyes The firs report is very easy. The second report I cannot find a way to do it When I show the departmens, I am only able to show the first employee belonging to that department. (important, I don't want to use a portal) Thank you

Hi! I started using FM a couple of days ago so sorry if this is a low level q. This is what I want to do: I have 3 different databases: - invoice - customer - article (or you might call it inventory) I have a relation between invoice and customer via customer id, so when I create a new invoice I type the customer number and all related data (name, address...) is filled in. So far so good. I also have a relation between invoice and article via article id field. When I type an article id I get name of article price and so on BUT how do I do if I want several article rows in the invoice ?? Of course I want my customers to buy several articl…

Hi, I have a FM database with one table with 28 columns. 4 of those are for Location Information. That is: Country Region District Municipality What I need is this: When from the field “Country” a particular country is selected, for instance Italy, I would like the field “Region” to show only Region names from Italy, and when I select a Region name, the field “District” only to display District names from the selected Region only. The same for Municipality. How I am to accomplice this? (For the time been I have assigned to every field a Value list names, f.eks. field: “TableName::County”) Thanks in advance

Hello, I have an inventory database that I have created for counting stock of items using a barcode scanner. The basic function is to scan an item, the database matches the barcode against a master catalog, returns the name of that item, and I enter a count that I then tally up on a report. It all works fine, except for one thing. Some items will be counted in different units with different barcodes, although they are the same product. I'm trying to figure out a way to relate them so that when I run my tally report, they are summed together as a total count. For example, if widget A comes 24 to a case, and I count 2 cases (by scanning the case barcode then …

Here's a screenshot of my relationships. All the ID and IDShort numbers are long unique numbers. ID is 48 numbers IIRC, and IDShort is at least 22. When I do a Find, say in a jotts layout, using the jottText relationship, Text field, I get a dialog saying one or more of the relationships are invalid. What am I doing wrong?

Hey everyone, not sure where to post this question, but here it goes. I have several layouts with "new" buttons, to create new records, and "delete" buttons to state the obvious, delete records. In every layout, except for one the "delete" button deletes only the current record, but in the other layout it deletes every record all at once...I'm dumbfounded...any suggestions??? and before anyone asks, yes it is only "delete current record", and not delete ALL records in the script. Thanks for your help.

We need for a teacher to be able to enter the attendance (absent) date and absent reason for all of the students in her different classes. Each teacher has 4 classes and there are only 5 to 8 students in each class. I have the following 4 tables with the following IDs: - Students (includes StudentID_kp, TeacherID_kf) - Teachers (includes TeacherID_kp, StudentID_kf) - Classes (includes ClassID_kp, TeacherID_kf, StudentID_kf) - Attendance (includes AttendanceID_kp, ClassID_kf, TeacherID_kf, StudentID_kf) I managed to put together a process using a listview and some scripting but it failed in a multiuser environment. I think I need to do this with a portal.…

I have two tables of data. One table contains securities i.e. stocks, mutual funds, ETF's etc.. The other table contains securities that our advisers are prohibited from buying. I am trying to build a list or report that excludes data from the restricted table to create an "approved list" of securities. I am not sure how to exclude the data of one table from the other. I have searched the forum but I am not sure I am looking for the correct terms. Any pointers would be greatly appreciated. Thanks

I have a table of 50 records. Sets of five from the 50 records are to be related to single individual records. In other words, participants will select a set of 5 from the 50 records. One or more records in the table of 50 records can therefore be selected by one or more participants. The data in the 50 records will change weekly but the five records from the fifty selected by each participants will not change. I am baffled as to how to set this up. Thanks for your attention.

Hello to everyone I was working very well with my POS solution, I have the normal portal where all payments are made, this portal has a relationship where all the sales are saved, when I want to make a sale, go to the portal (all new and without data) and I start to add my products with my codebars reader, like all POS systems. Well after making another relationships for other scripts suddenly when I go to my portal to make a new sale, a lot of rows appear from another sales made, this information is from the database that has the relationship in the portal. I don´t know why this info is appearing after working very well. I tried and check a lot of things b…

Hi, I think I've seen a similar problem posted recently (subject "Intersections" by eric.p on 10-02-09) but no useful answer. So am trying again, rephrasing the problem, it might help. I'd need a way to show a value list with records from one table that are linked to 2 different tables (it's an intersection indeed). There's no way to do this in the "Value List" interface, hence me posting this in the "Relationships" section. To be more practical, if the description above is too theoretical... I have an "Item" table linked to a "Factory Items" table and also to a "Machine Items" table. These 2 last tables store which Item can be produced in a particu…

I have a Clients table, a WorkOrders Table and an Invoices table in my database. Relationship 1: Invoices::INVCLIENTID = WorkOrders::WOClientID Invoices::INVID "not=" WorkOrders::InvoiceID Example of issue: the invoice layout has two tabs.. INVOICE: allows me to choose the client and shows me an empty portal where the work orders belonging to this invoice will appear after i add them UNBILLED WORK ORDERS: has a list of all work orders that exclude work orders that have (WORKORDERS::INVOICED = "yes") Example: On Invoice005 i go to the "UNBILLED WORK ORDERS" tab and click the button on the portal row with the desired work order that will b…

I think this should be simple but for some reason I have been banging my head today. My database has parent records and child records. Each parent can have multiple children. Each child can have multiple parents. The parent and child records are in the same table. I have 3 table occurrences: Parent table occurrence Join table occurrence Child table occurrence The Join table contains 2 fields, the parent_record_id and the child_record_id. In the Parent table occurrence I need to calculate the total of field 'Number' for all related records in the Child table occurrence. What am I missing today? Thanks, b.

I have what must be a common need when creating reports. I need to be able to list all the items in a job and the hardware that goes with them. Easy enough. However, some of the items have hardware components and some don't. The two tables are Items and Hardware. When I join these two tables and create a report I am only able to retrieve those items that have hardware components. All items that do not have hardware are dropped from the report. It appears that Filemaker joins are all equal (=) joins. How can I replicate the functionality of a left outer join in FileMaker? I need to be able to list ALL items, regardless of whether or not they have a hardware component…

I am building a database to record teams of people making visits to different places. In order to learn how to do this, I have a simplified database to start with that has only two tables on it for now. I have - tblPerson containing records of individuals, and tblVisit containing records of visits to geographical areas. A person may make many visits to different places, and a place may have visits from many different people. Thus a many to many relationship. I have created a join table, tblPersonVisit, but I am now stuck. What I want to do is to create a portal on the Visit layout where I can create relationships between the many people making that …

I can't find a satisfactory way to arrive at an intersection of two sets of records, related to the source record via different relationship paths. I've been developing for a little over a year; it's something I keep running into. I've attached a (very) simplified file showing the latest instance of it, in an simple accounting database. and here's an outline of the basic table/field structure therein: accounts -ID -name transactions -ID -date transaction line items (TLIs) -ID -account ID -transaction ID -amount Now let's say I want to put a calc field in my transaction line items records that shows the resulting balance of …

What is the "Best" way to organize your relationships in the graph? Anyone have examples? I would love to get a cleaner look to my graphs and was curious to see how others do theirs.

This has to be covered somewhere, but I couldn't find it. I'm using FM9 and I want to sort a title field. I need to enter the title with the initial "The" "A" or "An" but I want the sort to ignore those. I want the title The Age of Anxiety to sort it as if it were Age of Anxiety. Many people must have had this problem, but I can't figure out what to do. It's a shame FM doesn't have an automatic sort which will do this, but since it doesn't, can someone suggest how I can do it? Thanks. Mike

Hi Forum-user I've got an tiny problem. I want to create a database with chapter, and subchapters. I must have a view on all chapters and all subchapters. Creating a portal with subchapters, where the subchapters can be created by entering its name is easy. I joined the table with itself using a = relation. Now I want a portal with only the chapter, and just once (not once for every subchapter). Any hint for this basic duty? Thank you for that! Cheers

This is most likely a trivial solution to most of you but I'm perplexed on how to proceed. Normally I create all of my tables in a single instance of a database and get all of my information from the relationships developed in the database. Now, I have three totally independent databases [all FMP] one of which needs to get selective information from the other two. In some respects this is a type of 'dashboard' concept . . highlighting specific information from completely independent databases. I can't seem to find any way to access this information other than through the relationship process, which requires me to import the tables. I've established the file re…

I am a recruiter who place IT professionals with clients. I have three tables in my database (people, companies and job order). I would like to enter the first and last name of the hiring manager in the detail form of the job order form but want it to add to the people table, thus I don't have to enter them once in the job order and once in the people table. All relationship go from the company table to the job order table and to the people table and are one company to many people and many job orders. So what am I doing wrong. I have foreign keys in each of the other tables.

I've toyed with loop less creation of join table record, for years and have repeating calc'fields were up to it unless there should arise a need to make summary reports on the matter later. With the arrival of script triggers, could looping be made with ease, but setting of fields and initialisation would at least be some lines of script, why not put in some kind of OOD'ish creator method instead of putting it in scripting? --sd MakePayments.zip

I need to make a calculation that shows if any related records contain a certain value. How do I do this??

Hi, Here is what I am trying to do: Make a database to manage companies, what products they can buy and how much they pay for them. I have multiple 'lists' of prices, each list is of a certain 'type' and all lists of a particular type have the same products in them. For example: Type A lists give access to products X,Y,Z Type B lists give access to products F,G,H It has been straight forward to relate products to lists using these tables (just bare bones shown): List --------- kp_List kf_ListType Name ListType --------- kp_ListType Name Product --------- kp_Product kf_ListType Name I need to store t…

I'm trying to figure out why a relationship is being flagged as invalid. I'm working locally on an import file. This file is has a remote file as a data source. The local table is related to a remote table. Some of the local fields are unstored calc, drawing from other tables in the remote file. I've got a relationship built on those calcs, which is coming up as invalid when I try to search on a related field. It's fine for viewing data. Am I missing something?

Im ready to give up! I cant get this to work for snot. I have 2 diffrent table, one is 'equipment_types' and the other is 'add_equipment'. I need the Equipment type info [model, manufacturer, equipment type & ET#] to autofill the other columns when i enter the name in 'equipment_type:model' I have a few other thinks to link and would like to know if this is a portal, autofill or relationship type thing. I am very new and have tried for 6-7 hours to do this simple thing. I used to be smart but now im just stuck and ready to give up.

I am really stuck on what is probably something simple. I am trying to create pdf's of student midterm reports and am stuck on getting the records to display on a per student basis. I have two tables in a Progress Report DB, Reports and Students, related via the Student ID. When I create a layout (pulling records from Student) and include a portal with the related records from Reports, all of the data comes fine, except for the display. I cannot figure out how to display the data on a layout when not using a portal. All I get for each student is the first record. So, for example, if Student A has 6 courses and 6 reports, they all appear when using a portal, bu…

Considerer a gymn class Student:: ::ID ::Name ::age ::gender ::Biggest_value (Calculation) ::Biggest_value_date (Calculation) ::Biggest_value_place (Calculation) and Records:: ::ID ::StudentID ::place ::date ::value Ofcourse I can pick Biggest_value using the Max function. I have no clue how to get ::Biggest_value_date. This is improtant to me to know the highest mark of that student, but also where and when he achieved it. Thanks you for your support.

From a local FileMaker file ("local.fp7") it is - of course - possible to relate to tables in a remote FileMaker file ("remote.fp7") through establishing the latter as an External Data Source in the first. However, the reverse is not true - at least to my experience. From the FMP 10 Help file "Creating file paths" description of relative file paths I would expect that something like "file:./local.fp7" would work as External Data source in "remote.fp7", if "local.fp7" is stored in the FileMaker application folder because "If the current database is opened remotely, the path starts from the local FileMaker Pro directory" (citation). But it doesn't work. So there s…

I will try my best to explain this. I have an external database in sql that I am pulling information from. This is working exactly how I want it. Where I am running into issues is that I want to be able to filter the results returned by date (which is a variable in one of the tables) Here is the issue. In FMP i have a product table. I have a relationship to an external table called "Items" and is linked by an item #. Then the external "Items" table Occurence is linked to another external table Occurence called "CMS" by order number (which is in both of those tables, but not my FMP) When I create a Portal for this with variables from both of …

I want to be able to display thumbnails of all images related to a record in a matrix. I thought doing with the web viewer would be the way to go, but don't have a clue how do do it. Anybody got any suggestions and/or can point me in the right direction? Thanks. Bill

I have an Excel file with a list of 2,000 properties dated January 2009 and have a separate list with 2,200 properties dated September 2009. How can I use Filemaker to show the properties that are on both lists and the properties that on one list but not the other? Actually a need to separate the properties into three categories, NoChange, Removed, Added. The fields are Property Ref (unique), Property Name, Property Size and the list date.

I'm building a database collecting data from 29 schools. Each school must send its student roster at the end of every year to the ministry of ed, along with the students grades, whether they passed or failed, and who their teacher was. So far so good. A table for schools, teachers, students. But then I discovered that many schools have the previous years roster too (from '07-'08), and now I'm trying to build a more complex relational database. I think I'm in over my head, but I'd rather here the bad news now before it gets full of records (right now I've only got around 600 kids and 11 teachers). I would very much appreciate someone poking around my relati…

I have 3 tables: Dashboard, patientInfo, and appointmentInfo. I am trying to use portal filtering on the dashboard to display patients who have appointments on a certain date. In patientInfo I have a calculation field called lastAppt that has the calculation: Last (appointmentInfo::appointmentDate). I have the relationship sorting records in ascending order by the appointmentDate field. I am thinking this returns the value of the last appointment date in the related records in the appointmentInfo table (and it looks like it is). I have a field in the Dashboard table called lookupDate which is related to the patientInfo table by a relationship where lookupDa…

Quick question: I will use the analogy of a book, with chapters. I have two tables, one called books, one called chapters. Each record in the books table is a book, and each record in the chapters table is a chapter in a specific book. I want to make sure that there is never more then one chapter with the same name in any given book, but that different books could have the same chapter name, for example: Book 1: "A bedtime story" Chapter 1: Introduction Chapter 2: The End Book 2: "A work story" Chapter 1: Introduction Chapter 2: The middle Chapter 3: The end So far so good. I want to make sure that this entry can not be made in either book: …

I have a situation where I have made a join table in between two TOs of the same table. The table being joined to itself keeps records of cell line Batches. The many to many self join is necessary because sometimes multiple batches of the same cell line might be pooled together. The join table in the middle holds the associated batch numbers, to be assigned and viewed in a portal. The join has two keys, the primary key assigned from the record in which the portal entry is being made, and the second key is the value which is entered in the portal row, the id of the associated batch. I've set up a dwindling value list structure, and that works, but there are probl…

Hi, I'm struggling conceptually to create a relationship between doctors and patients. Two tables: a) Patients; and : Doctors Each has a unique identifier for doctor [D(x)] and patient [P(x)]. I want to create a relationship where a doctor could be linked to multiple patients, and also where a patient could be linked to multiple doctors. I'd like to then be able to display and be able to manipulate these links between doctors and their patients via a portal. I've tried simple methods, but with no luck. Can anyone suggest a way I might accomplish this? Thanks, fmphysio

Hi there, I've just designed a solution made of 9 essential tables to help a friend manage his customers. My friend owns a moving company and wants to be able to track his daily transactions in a database. Here are the main tables: ------------------------- [color:red]VIEW PDF ATTACHMENT FOR BETTER INSIGHT CUSTOMERS, JOBS (ORDER), ORDERLINE, SERVICES, INVOICES, PAYMENTS, PRODUCTS, EXPENSES, REFUNDS One customer can book many jobs (orders), but a job can only be assigned to one customer at a time. An invoice can receive many payments, but a payment can only be applied to one invoice at a time. My questions: ---------------- 1) is my…

Hi, I could do with some help on a little problem. Im currently developing a database for my architectural practice. We work on a Project by Project basis so I have one table as the Job Database (effectively) and I have another table and layout with all our contacts (engineers, surveyors etc) each with their own record per contact. Each Project is a single record in the Job Database table. I have a tab set up in the Job Database table called 'Contacts.' Ideally I would like to be able to add numerous contacts from records kept in the Contacts Table to this tab to allow me to see who is involved in each of our projects. I have managed to do this with just one co…

HI, I have a table called STATE in which following fields are there- 1.STATE_CD (PRIMARY KEY) 2.STATE_NAME There is another table called CITY in which following following fields are there- 1.STATAE_CD (FOREIGN KEY) 2.CITY_CD (PRIMARY KEY) 3.CITY_NAME what i want is that after selecting particular city from the drop down the state associated with this city should be displayed in the edit box. plz tell me how can i do this i m really stuck in this small problem . plz help me. The relationship associated with this shown in the pic. Thanks, John.

Hello. I'm looking for a simple warehouse database with locations/bins and picking. The Ex Biz Tracker 2, is far along what I'm looking for, but it lacks the bin solution (only got stock) and the picking solution. Any ideas on how to implement that?

Sorry for the rather vague subject, I'm not sure how to pose this question... I'm essentially working on a contact management database. There are five different Categories of contacts, and multiple Types of contacts within each Category. Lets say one Category is 'Press & Media'. Within this Category are Types e.g. Journalists, Photographers, News Agencies, Newspapers etc... Another category is 'NGOs'. Within this category are Types e.g. Local Partners, Diaspora Groups, Lobbying Organisations etc... The file will be shared over the network and there are 20+ users. Each user will need to work primarily with a different set of contacts which they …

I’m a self-taught newbie (used Lydia tutorials) who is using Filemaker Pro 10 to organize an evaluation I’m conducting of an NGO’s educational program. It breaks down roughly like this: 1 Table for School Superintendents (11 records: Some are linked to multiple schools, others only 1) 1 Table for Schools: (41 records, all linked to superintendent records) I will soon be collecting daily attendance (daily in its record keeping, but reported to me on a monthly basis) for each school, broken down by grade (7 grades per school). So there will be 287 monthly attendance forms submitted to me (each one with daily absence totals and what a code for the weather outs…

This is a manufactured inventory question. Table 1 has the fields: WidgetName WidgetCost Portal - Table 2::IngredientName IngredientQty Table 2 has the fields: WidgetName IngredientName IngredientQty There is a relationship between the two tables: WidgetName=WidgetName So using the relationship, I am able to get the Portal ingredient list in Table 1 working. What I need to do is calculate the total cost of all the ingredients. To do this, I need to look in Table 1 and find the record where the WidgetName=IngredientName. Then use WidgetCost field and multiply it by the IngredientQty field in Table 2. I can't just use th…

Hello I have a table for accruals (lookup table called Accrual_rate) And I have a table for Emp_accruals (transaction table). If an employee’s MOS_Balance is 75- they should accrue 11.33 vacation hours. What is the relationship of the lookup table to the transaction table? And how do I script the lookup? Sample_Accrual.fp7.zip

Hello, I have a DB with multiple tables which all have specific information, but the one "constant" is the customers. Here is my problem... I create my customer in the customer layout (customer table), then go to a systems layout (system table, related to customer via customer ID) and create a new system. All is good so far...however, if i change the name of the customer back on the customer layout the name of the customer in the system layout does not update. I know that it is the same customer, because i can just change the NAME field to use the name from the customer table (rather than the name field form the related table) while leaving the ID field alone …