Relationships

I'm at the point where I have 89 unique tables in a database I'm designing for a research study and I'm presented with an interesting conundrum... The information below is borowed from the amazing article White Paper For FMP Novices courtesy of Foundation Design Systems, and after reading it, I'm curious as to the forums opinions about the pros and cons of using multiple table occurrences based on a single original table vs. using multiple tables in a FMP 11 solution. What are the main advantages to doing this? What are the main disadvantages? Are there certain situations where I'd never want to do this? Is using multiple occurrences (in this context) just a ve…

I need to add a basic petty cash module to my current database and would like some advice on how to set it up from others who have more experience in the matter. I'm thinking of just a basic table with ID, Description, Category, Credit and Debit fields and use it as a type of never-ending list. However I'm not sure how I would manage the running total or indeed whether I should have a parent table for each petty cash cycle (time between replenishments) and use the previously mentioned table as Petty Cash Line Items. Has anyone done this before? I'm sure it's quite simple... I just need a shove in the right direction. Thanks!

Hi, I have a table where I store some default values such as charge rates and want to 'LookUp' these values when creating new job records in a job table. I created them as global fields and intially these seemed OK but when I reoponed the database and created new jobs the 'default charge rate' was empty? Has anybody else had this issue? I'm obviously doing something wrong but can think what else to do. I have the same problem when wanting to store an image for an invoice header in the same table as a container? Any advice would be appreciated. Regards, Simon

hello guys, i just new here,luckily i found this forum, currently i create 2 file 1 is personal info file and another 1 is annual leave file in personal staff info file,we will key in name, phone number, and position,positionemployeeid is key in annual leave file, i try to make employeeid as popup menu(which is will select from personal info file)then it will display all name, phone number and position, i already try many solution,but still found dead end.and i new to filemaker, request any help.thank you here i attach my filedatabase.zip

This must be quite easy? I have 2 files. One is a name, company and address file where reach record is classified according to a category e.g. shipper, marketing,agent,organisation,manufacturer etc. My second file is a table of products: cotton t-shirt, lighter, bowl etc. What I want to do is create various fields in this second file which are drop-downs of the company name found in the first file but only by reference to its category e.g coton t-shirt would have a related field for manufacturer which would only include companies which had been checked as manufacturer using the value field in the first file. Clear? Hope so.

I am trying to set up a database for my 8-ball league. Currently, I am doing it in Numbers and that works ok but I am thinking that FMP could automate things a little better for me. We have 16 teams of 5 or more players and we have a 25 week schedule. What I would like do is enter the data for each match of each week and then have FMP calculate all of the appropriate team and individual stats. Each match has 5 players from one team playing against 5 players from another team. We play 7 rounds of 5 games. Spares can go in and replace players as the match progresses. Players can play for other teams when necessary. We use a handicap system where the score for a si…

I know this is simple. I know that I have seen a post about how to do it in my travels around this marvellous resource. I have a database called matters. I have a time record database called timselips. In timeslips I have a drop down list of matters which enables me to easily enter matter names for recording time against them. Eventually matters finish and i have a procedure for marking them inactive. i would like to arrange my drop down list in timeslips so that when a matter becomes inactive it no longer appears in the drop down list.

Hi, I'm creating a print production database and at the 'Job' level I'm confused over what do with the table structure. Each Job has four different procedures - Pre Press Production Finishing Delivery I had thought about keeping all of the these in a single table called 'Job Procedures' related to the 'Job' table by 'jobID', however each of these procedures is slightly different and in the simplest scenario some procedures may have 3 records per job whilst some have just 1 or even none. That being the case I am worried about creating nulls in some fields and therefore thought I should perhaps create separate tables for each procedure? …

Hi folks. I am new to the board and look forward to being a member. This initial problem I have undoubtedly has the simplest of solutions but I am staring at these data files totally lost and going stir crazy. I hope this is the appropriate section for this post. OK... I have a customer database which has a related table for the individual staff members of each respective customer in my database. i.e. the unique serialised 'Contact ID' field in the customer table is linked to the staff table with the same field name. So each customer can have multiple staff members. The email addresses and mobile phone numbers of each staff member are also stored along …

Hi all, another newbie question I'm afraid. I had a look at the databases our lab has set up, and unfortunately they are basically glorified spreadsheets - so for example it looks like this product A ordered by ABC Maria CDF Ron AGG Maria Nova where Maria and Ron are simply entered text. So I see two problems: sometimes the same person is entered under different names (for example with or without family name) or simply misspelled there should be a table "people" and each name should just be a link to the table entry So my questions are: - is there a way to clean up the database? - is there a…

I have two files (not tables) with in the first file a GTRR to the second file. This is working, only, the related records are showing in the second file, where I want them as window on top of the first file. Which script step do I miss or have to use? Now I have to click on the 'Window' in the taskbar.... Tx

I've been looking for this answer all over the forum and i wasn't able to find it, if it is answer previously I'm sorry, my question is very simple, how many characters are allowed in Filemaker 11 for a relationship? For example in Filemaker 6 there is a limit of 20 characters, after that it will give you the same result. If someone knows I would be very appreciated. Thanks in advance, Lucas.

HI All, I'm trying to build a very simple complex database. My goal is: -Making a list of items, taken from another database, with their prices added automatically. Each Item has a price in the "items database". In the other I have an empty list that I'd like to fill with the data in the other file, let call it "calc_menu" So I linked the 2 files, only with theis common "item" field. I've created 10 field (es item 1 to 10) in the "calc_menu". With a Drop menu you can choose each item in the "Items_DB" file on every line. Belong the fiels "Item" i've put "Prices", that should take their value from the Item_DB The relationship works only on the first line an…

Hi everyone! I've been over this issue for a few days now and I run out of ideias. I was hoping someone could give me some tips. I have a scheduling database. The user sets a start time and an end time. A global field stores all numbers in the range and compares them to the start and end times on the events table. So far everything works just fine. Here's my issue: I'd like to dynamically change the end time according to current the account name (Meaning, logged user is the one that scheduled the event; event time-end does not change/ logged user did not schedule this event; time-end + 45 min.) The problem is that I cannot make it a calculation (it cannot …

Hello, I have worked through how to create a portal that displays records that are restricted by a self join. BUT ... is it possible to do this using a layout as follows: I have a table that has jobs, and these are either quoted or live jobs. I have a field called 'Status' that is populated from a value list with either "Job" or "Quote". I want to be able to create a layout that only shows 'Quotes' as a result of using a self join. I've temporarily solved using a script trigger that forces a find on 'layout enter' but it's not the best solution. I'm sure I'm just missing something simple as I worked out how to do it using a portal ... but now I'…

PROBLEM: GIVEN 2 tables with the indicated fields: Table "Articles" - ArticleID - Price Table "RentalPeriods" - RentalPeriodID - PricingFactor All Articles in table "Articles" can be rented for the periods listed in table "RentalPeriods". Rental price for a rental period is calculated by Articles::Price * RentalPeriods::PricingFactor. WANTED: A portal in the article layout that lists ALL rental periods PLUS corresponding rental prices (as defined in the last sentence) for a selected article. EXAMPLE: GIVEN: Article (1 record): ArticleID = 1, Price = 100$ Rental periods (3 records): RentalPeriodID = 1, PricingFact…

Filemaker Pro 10 Advanced. We're having a problem with portal autosort I have a repeating field list on the left and a matching portal on the right that pulls corresponding data from a related table. The user types a word on the left and the related word appears in the right-hand column (the portal). So far, so good. The correct data appears. But... the portal is sorting it (I don't have any sort values set in portal preferences). This is NOT what we want. We want the portal values on the right to line up with the corresponding values on the left, exactly as they are entered, not for the list to reshuffle itself. There's no way to pre…

I'm new to the site so I'm not entirely sure if this is the best area for this question, but here goes. I have a fairly basic database I'm putting together, but I'm seeing some weird behavior from portals when trying to view related records. In this case, I'm using portals to create an excel type grid view of the related data. I have several fields that are testing the data for one thing or another, all very simple yes/no type scenarios. Those fields are then summarized using both Totals and Averages. The result is a snapshot view when using a portal. The problem I'm having is that in one of my portals, I'm seeing only about half the data I should. The test…

Dear All, I'm trying to track changes in a particular part of a database. Table: TableBooks Fields: - BookID - BookStatus (In/Out only) - BookName I need to create a table which records when each book is taken out and returned. I had some success in creating a table BookStatus: - BookStatusID - BookID - BookChange (timestamp, new on creation) I've linked the tables: BookID -- BookStatusID I've created a little script, which is triggered on BookStatus change: Go to Layout ["BookStatus"] New Record/Request This creates a new record, and the requisite timestamp. However I need to also import from the Book table the BookI…

Hello! I'm getting into FM and can't manage to solve a simple (at least in Access and Excel it was) task. I've got a table: ID_Transaction Some other fields... Profit_or_Loss (calculated 1 or 0) I need to calculate the Sum() and Count() of Profit_or_Loss in this table to find a proft/loss ratio. As I understood, it should be possible to make with a self-join relation and portal of the same table. But I can't place such portal on layout. All fields and options in portal's dialog box are locked. Could somebody help me, please? Sergey

Very stuck on this one, appreciate any help. Example File attached. I have a table called SERVICES which contains the names of various services. In turn there is a related table called COSTS, which has a breakdown of costs for each service in the SERVICES table So for example: Services table has the following records (the number of records is not limited, though they will have different names): Catering, Cleaning, Training Costs table has (each an individual record): Sandwiches £5, Drinks £2, Hot Food £10 – related to CATERING in the services table Staff £10, Equipment £30, Fluid £3 – related to CLEANING in the services table Tra…

I'm trying to create related records in a filtered portal but having some trouble. I get the error: "This operation cannot be performed because one or more required related records are not available and cannot be created." I have a table of Tutors (1:1 to a People table). I want to show all those Tutors on a layout for a Tutoring table and allow the user to select a Tutor to see and add Contact Info. I want to use a filtered portal but am having trouble. A picture is worth a thousand words...a file is worth more. Please see attached. (The actual file is much more complicated, so there are apparently extraneous fields in this file, but they do have a p…

I am creating a database that will be use to track changes in the status of a company. Status changes are triggered by actions of agents (and I need to be able to track which agents caused which change in status). I managed to link the company and agents table together using a seperate table; then, I figure that to relate a specific company's status to an agent, I will need to create a multikey relationship (as seen in the screenshot below) between the CA.CompanyID/CA.AgentID and Status.CompanyID/Status.AgentID. I've also designed the value list in the status table such that only existing AgentID (corresponding to the selected CompanyID) will appear. The proble…

I would like to be able to connect two similar tables together for the purpose of viewing the data in a common portal. Background: I have a main table that houses customer data. (i.e. Name, Address, Phone Number, Primary Key) There is a second table that houses service call data. (1.e. Date of call, type of call, work performed, tech, Foreign Key) This table connects to the main table via Primary key from Main table. There is a third table that houses Weekly maintenance data. (i.e. Date of maintenance, Type of maintenance, tech, Foreign Key) This table also ties back to the main table via the same primary key. I would like to link the two tables tog…

I have a database that contains student information. One of the items I attempted to create is a schedule. This was a bit tricky for me, as it involved creating a fairly deep cascade of buoy TOs in order to show all the data desired. Originally, it worked fine to create the schedules. However, now that I'm going back and trying to print schedules on a one-off basis as changes are made, it sometimes works and sometimes doesn't! When it doesn't, I get a page full of blank portals, as if the global student ID field were left blank (though it isn't). Here's the structure: In the data file I have tables w/ the following fields (non-relevant fields excluded for…

Hello there Forum I've searched high and low to answer my own question and suspect I may not be using the right keywords. I have a Human Resources database in the making and trying to work out how to drag across the "Name" and "Personnel ID" fields into a separate table that houses Employment Contract records. The workflow I'm trying to achieve is: Personnel Record layout > "Add Contract" - at this point the New Contract layout appears with the "Name" and "Personnel ID" dragged across from the Personnel Record layout. I'd like these two fields to be live so when the Personnel Record is updated, it is reflected in the Contract layout. I want to creat…

I have to redo one of my database exports for our student lunch program due to the fact the Student Information System is moving the F/R fields. Everything has been going so well and I'm happy about this move. However, now when I pull this data I will get duplicate students with a start and end date (good thing). I only want the most current meal status for each student. What is the best way to tell the database to find the duplicate student and keep only the most current start Date? Usually you can find something that is so close to what you are doing, but this one I'm totally lost on how to tell it to get the most current date for each duplicate record. I wan…

In my business (travel agency), I book mostly group hotel stays. Each hotel gets a rooming list that shows the guest's name, room preference, and arrival/departure dates. Here is my problem; Lets assume there are ten guests in a group. Five are arriving on January-10 for 2 nights and the other five are arriving on January-11 for one night with all departing on January-12. Or for another scenario, all arrive and depart on the same days but two of the guests get suites and the remainder get regular rooms. I need to be able to place all these names on a single rooming list (hence the layout challenge) for both the benefit of both the hotel and the guests. Here is what…

I'd like to place sticky notes on top of sticky notes but my top stickies disappear as soon as click on them. Is there a way to send the top sticky to the back?

I am trying to add a payment for invoices. Similar to the way quickbooks handles applying new payments for invoices. Many times I receive One Check for Several Invoices. I want to open a layout and: (Select) One Customer (Enter) Payment Amount and see List of invoices for this one customer Attached is my layout. Can someone who has done this direct me and let me know if I am heading in the right direction. Do I need any other fields or tables or additional relationships?....Any input would be great. Thanks. Jay. Payment Receive Setup.zip

Hi, I'm building a new database and I've it set up the primary key fields of my tables in a way that I think is effective, but I want to check with the forum before I began the next stage of testing/development. I've created a schema below that shows a part of my database and the way that I have my primary field concatenation calculations defined. They are all fields that are populated via a calculation set to replace existing values. All are also required to be unique. Is this an acceptable way to create compound keys? Should I have all of the columns from each of the "previous" tables in each of the later tables instead of just making the foreign key part of t…

Hello! I have this very stupid issue that I can't solve. I have portal (one to many relationship), and well all the new records I add to the portal do not get added to the table for some reason. I have a other project where they do get added. All the data however DOES update if I change anything in the portal, just can't create new records (or if I have a button creating new record, it sill just stay empty in the table while I can add as much data to the portal as I want.) What is up here? There must be some option that I am not seeing. I have looked at 2 different projects like this and they seem exactly the same, yet there is a big difference... to be …

Good morning!! I have a problem I can't figure out. One of the guys here programmed a file for the local United Way office to track their pledges. He is no longer here unfortunately. They were running FM6 on Windows XP. Their file consisted of five separate tables that opened when the master file was opened. On the main screen was a "Total Pledged" field that was from the "Pledge History" table. It would show a grand total of the pledges for that particular year. For example, this year was roughly $600k. They are now running Windows 7 and FM 8.5 and I opened the files with 8.5 and it converted the files over. Everything works ok, but now that field only sho…

On the enclosed table of newspaper issues (twice a month, 22 times a year), I need to pick out the next issue number based on today's date. I can get the date to return, but I can't figure out how to get the issue number of that particular date into other calculations throughout the db (invoices, running ads again, etc.) I think I am missing a relationship configuration, but it continues to elude me. Any help will be appreciated. The relevant file is Issues, but you can see how I'm trying to use it in the Classified section where I want to include an ad in the next issue via button/script. I'm using FMP 6 on an intel mac with snow leopard. -- and yes, it gets sl…

I am using Filemaker Pro 11 I need help with creating a script which will allow me to populate a portal with the results following a search. For simplicity purpose, say I have a database with the following fields: Name, Age, and Gender. I can perform a search using an Age range and Gender to find the relevant Names, but do not know how to populate automatically a portal with the results from this find. All advise would be greatly appreciated.

One table matches to another table via 5 keys. Entry takes FOREVER into the related table. Does # of keys make a difference with data entry? How do I fix this? On data entry about half the time I'm also getting error "ID requires unique value" about PARENT record ID (which IS unique and not being created in relationship). I'm very puzzled.

I have a database with an overly complicated relationship graph. Is there a way to find which one without clicking on the zillion tables one by one? I am freaking out...

Hello all, I have a question regarding a database which I have built on a friend's computer. Part of this database is a number of many-to-many relationships. Example: Table_Books includes the following fields Book_ID (primary key) Book_title Book_author Book_topic Table_Topics has only two fields: Topic_ID Topic_Name Finally there is Table_Book_Topic, which includes: Join_ID (auto generated) Book_ID (from table_Book) Topic_ID (from table_Topic) Hence, one topic is covered in more than one book, and one book can touch on more than one topic. So far so good. I now wanted to create a query for returning a list of all books…

Hello, My job is to re-make an entire database for a conference organizer. Currently, the file structure is: -4 tables per Conference: REGISTRATION,SCIENTIFIC_SESSIONS,SCIENTIFIC_LECTURES, SALES This is a good solution but it was not exactly designed to be used for this many years. Consequently, it has been used for 8years => there are 32 tables, and it's going to keep on growing. My idea is to create one table for each department (sales, registration, scientific) and insert a field called "conference" in which i will enter the name of the conference (e.g. "lasvegas2011") How can i create occurences for each conference, based on the same ta…

I have an Excel spreadsheet that is converted from a PDF. I am trying to figure out how to get a student's schedule into their layout. When I import them one student ends up having 7records. Do I fix it with a portal or is it the way I have my tables set up. I am attaching the Excel file that shows one record, there are 2400 of them. I am looking at linking the tables as such. Student (Table) Course-Section (table) StudentID (Primary Key) StudentID (Foreign Key) LastName FirstName Period (Not sure how to relate the periods) I want to be able to input a studentID, the student's record comes up and I immediately see a layout like I se…

Not sure how to set up the tables for this EXCEL sheet. I was thinking of using a portal so when you go to a student's ID number or name it will show me everything that I have on the spreadsheet, his courses, the periods, the room number and the teacher all on one layout. How do I relate the table and can I do it with the way the EXCEL spreadsheet is setup. I have attached it. Practice.zip

Hi all, I will admit, relationships are not my fortay. : (heck nothing appears to be) /pity party Can someone please assist in showing me the correct way to set up these sorts of relationships. I have dodgied it up before where I created a script to create the record (relationship) and so the value list will work. It's annoying, not to mention wrong. The scenario is: You have an event You add an artist to that event You select your artwork which is filtered by the artist selected above. I can get everything above to work (v.nice filter) EXCEPT the record (aka the key) is not there until the record is created. i.e on a new portal row the drop …

Hi, I've created a self-join relationship so that I can use a portal on a layout of the current table to display a subset of records in the current table... I can view the subset of records successfully, but I want to be able to navigate to the record displayed through the portal by clicking on it and have it display in the current layout. I'm trying to use the GTRR option (which may be the wrong way to do it) to navigate the subset of records displayed through the portal but after setting up the button, when I click the GTRR button nothing happens...any thoughts? I'm basically trying to make a simple Nav panel that allows me to navigate through a subset of r…

Amateur FileMaker developer here, working on a solution for a medical reporting database. Simply, I have patients, and those patients have reports that belong to them. Now, there are numerous report types, 15-20 of them, each with different criteria (fields). My questions are these: Do empty fields take up space in the file? Meaning, if I use ONE table to create ALL the report types, creating 400-500 fields in this table and using a tabbed interface to display the fields for each report type, will it drastically increase my file size after several thousand reports when most of those fields will be empty for each record? The only reason I have it set up t…

I am in-charge of creating a DB for an insurance company. We have commission statements that come in for several different carriers. Each statement needs to be imported into the database each month but into separate tables, (so each company gets it's own commission table) due to the fact that each companies statement is different. There are however 6 records from each statement that are similar (but often have different names). Example of similar records from multiple commission statements - Commissions Amount Premium Amount Agent Number Client Policy Number Transaction Date In my DB I also have the following Tables Insurance AgentTB - Agent…

Forgive these basic questions (not basic to me)as I am struggling to understand a simple relationship. Cooking is my main hobby and I have a notebook of different recipes and lists of ingredients for each recipe. So as I am now retired with time on my hands I thought I would try a file to hold this information. I just know the basics of Filemaker and have created 2 tables one containg the recipes the other the ingredients. I want each recipe record to relate to its corresponding ingredient record. So my first question is: Would this be a one to one relationship as each recipe only relates to one set of ingredients? I am a little confused on this fore…

I am setting up a purchase order and we purchase from different companies and of course that means different price lists. I have set this up similar to invoicing sample from "comment"'s layout out. Which is 4 tables: Customer Info Invoice(renamed to purchase order) Linitems Products Is their a way to do a look up on different price lists from each vendor? Thanks. Jay

Hi everyone, I am sorry if this is not in the correct topic, but I am new having just bought FMP Advanced. I have a huge contact database which is currently on Address Book, and exporting it over to FMP is not a problem. Many of the contacts I have are people who do charity work like myself, but some are just friends and a few others my tenants for my property business. I was wondering if it is better to have one big database with all the contacts in and create layouts etc, or should I have a separate file - one for properties, one for friends and the other for the charity-related people? I am learning this as I am profoundly deaf, so telephone calls a…

I salute everyone I'm sorry for missing English. Tables 1.Customers Customer id Customer Name Factory id Factory Name 2. Suppliers Supplier id Supplier Name Car Sticker 3. Routes Route id Route Name Route price 4. Line Items Scenario as follows: 1. Employees or workers moving to the workplace. 2. Suppliers carries. 3. Moving with the bus or minibus. 4. Moving predetermined routes. 5. The customer can be more than one factory. 6. Price particular route 7. Monthly report is given to the customer. 8. How the relationship should be here? 9. Factories how customers should be separated…

I have created a summary field which collects the number of records (sales) attached to a particular product. e.g. Shirts, blue, size L - summary field adds up all the quantities of each shirt. I want create a report which lists the products by the largest no. of quantity. I can produce a report, using a self-join relationship, which automatically fills in a field with the quantities I need so I can see for each product sorted by product name or attribute the quantities for each. But, how do I produce a report sorted by the quantity in the summary field? If I try and do this the summary field is always greyed out? And.. I would like to have a field which shows which …

I am a new FileMaker user - Version 12. I am creating a database to handle employee HR for a transportation company. The constant, or "key file" is k_EmpID. When an employee’s file is complete, it will have records in multiple tables (Personal (1 record); Payroll (multiple records); Drivers File (1 record); Verification File (multiple records) etc.). When working with the files, it is very common to jump from one table to another. When I move from table to table, I would like the system to show records for the “same employee”, unless I ask for a different individual. However, as the tables and their relationships are set up now, there is no guarantee of land…

Hi, I am a novice in database design. In the relationships table in filemaker, is it the best way to create id numbers for each table and connect to each other and then get the related field names if needed? Thanks. Do you create a built-in primary key even though there is a unique key available? K.C.

Hi, I'm very new to filemaker and I'm trying to develop a medical db. I have a table containing all of the consultations. each consultation can 1 to 4 different types and each type will have one or more diagnostic. I'd like to display a portal with all the diagnostics but can't seem to do it. I have a consultations table (consultations) related to 4 tables for each type (epaule, hanche, pied, genoux) and each type is related to the same diagnostic table (rel_pat_diagnotic) that appears as 4 dif table occurrences (rel_pat_diag_epaule, rel_pat_diag_hanche, rela_pat_diag_pied, rel_pat_diag_genoux). My problem is that I can't seem to show all the rec…

Hello all, I'll start of with a basic schema of what I'm using then I'll describe my problem : so I have 6 tables: Event Donations 1:N (occurrence of the event table) Donations N:1 -_DonationID -_EventID -_PeopleID (who donated it) People 1:N -_PeopleID TeamMembers N:1 (bridge table) -_PeopleID -_TeamID -MemberTotalDonations (calculated field of sum()) Teams 1:N -_TeamID EventTeams N:1 (bridge table) -_TeamID -_EventID Event -_EventID where each cardinality applies to the table bellow (ex. 1 people can have many donations) I have a layout based on the EventTeams layout with a portal currently based on TeamMembers. What …

Hello, I am just a little confused about the logic of the following and how the tables should be related for this to work. I have 3 tables (Purchase, Purchase Lines, and Delivery). The logic is the the following: "each Purchase can have many Purchase Lines and each Purchase Line can have many Deliveries". This is all being done on one layout of the (Purchase) table, which contains a portal that is displaying the data from the related tables. I have allowed the user to be able to add or delete data on the portal onto the other tables through the relationship. Issue: When trying to enter a two different delivery dates and quantities, both date and quant…

Hi just read a database book about solution to many to many relationship. One of them is "self referencing". I am just wondering if it is possible in FileMaker. If it is not, what other solution is available? Thanks. K.C.

I have a donation table with over 22000 different donations. I also have a contacts table that has all the contacts corresponding to the donations. I have another table that i want to use for donation searching. For example I would want to search for all donations between two dates and between two amounts. I know how to set up a range field that auto calculates two fields and sets it up as a range so if i put 1 in the low amnount field and 30 in the high amount field the amount range field auto calculates to be 1...30. How can i set up a portal in the field to show all the donations that match the dates and amounts criteria i enter into the fields. So if for example I en…

Hi all I have created a self-join relationship using the same field in both TOC's to create the (=) relationship. This is to enable me to check for uniqueness of a field using Count ( TOC::Field) > 0 The problem is that only the first line in the field is being taken into consideration. If a carriage return is entered into the field, any text following is ignored. Does anybody know why this is the case? Thanks in advance Lee

Hi, I'm very new to managing more than 1 table. I have 3 tables. I want them to be connected by an ID I've started with a FM template and modified to suit my purposes. Here is a picture of that relationship graph. I've started a new database and copied the relationships between the tables as in the template. But my relationship graph doesn't have the "trident" symbol. Where does that come from and what does it mean? Thanks, jim

Hi, not sure if this is right section, apologies if not. I have in excess of 125 databases and of course all of them cannot be open at the same time on FM Server. What I would like to know is, how to amalgamate databases, bringing across the table and its layouts etc? Nearly all of the databases at present are just one table (originating from Filemaker 5) and tend to relate to another database. Cheers Chris

I would like to be able to create multiple new related records (letters) based upon a found set in (dwellings) using key field (license #.) I know how to do this one at a time with scripting (create new record and copy/paste the key field) - can this be done with many records at a time? Not sure how to get the right number of records created, and how to insert the key fields.... Any help - MUCH appreciated!

Hi, I am trying to use radio as section and drop down menu as subsection so I could some how make the list shorter. But I encounter a problem when I choose the section and in the subsection it still list all the values instead of just the values of the section. Is there a different way I can do it?..Thanks. K.C.

Hi All, I have a database with: Customers, Invoices Invoice Line Items,History, History LineItems to which I added "Vendors" (or Brokers). What I need to know is, what kind of relationship should connect the Vendors (Cllaborateurs) on Fig. Contracts DB and the Contacts table. Do I have to implement a Vendor Line Item. Sorry I'm kind of new here and I don't really get the relationship thing. Attached Contracts DB

I have a students that are given group assignments. If I create an "assignments completed" table,I could place a portal in a layout where I would find each student's record, and create a record in the assignments completed table. For data entry, I would have to move from student to student to enter that "assignment so-and-so" was completed and the grade earned. I would like to have an "assignments given" table, where I would create a new record for each assignment, then create a layout based on "assignments given" with a portal from "assignments completed" but also linked to "students". When I have a stack of turned-in assignments, I would do a find of the re…

Dear All, I have a question regarding of transferring selected records from check box from one portal to another. If the left portal is the main database and right portal is the quotation portal. How can I make the selected items in the check box(a field in a table) and transfer them to the right portal? And at the same time, if other users are using the same main database and select their items, the field which contains the selected items won't be confused with 2 users' quote.. Thanks in advance. Kent P.S. Can the following link help me to solve this problem? Thanks. http://sixfriedrice.com/wp/using-global-variables-for-list-selecti…

Hi, Is there a reason why sometime the keyboard shortcuts in the relationships diagram won't work? It is quite annoying when you have multiple relationships that overlap and you can't use the Ctrl+O shortcut. This problem is intermittent and occurs under XP and 7. Thanks

Hello everybody, Can't get rid with the following question I created a database for composers ::Composers ::Nationality When adding Beethoven to Composers table, I realised he had two nationalities, German and Austrian. I have no idea how to relate my record to the national table. Please help. Cori

I have an issue I cannot quite find the answer for. I have 2 tables. SCHEDULE (ID,DATE, TIME,LOCATION,HOME_TEAM, AWAY_TEAM) TEAMS (ID,TEAM_NAME) I want to create a Layout for the TEAM and have a portal that shows the schedule for that team in Chronological order but I cannot get the relationship link right.  I can obviously link team to home team and the portal shows all home games or link it to away team and it will show all away games. How do I link it to show ALL games?

Hi all, I'm converting a spreadsheet into a nice relational filemaker pro DB, and was wondering if there was a better way to do it than the one I've come up with. My main concerns are the key fields for the company and employee tables. Each company has 1-100 employees and each row of the spread sheet has a company name and an employee name. Here is what I'm doing to convert it: 1) Set up CompanyID and EmployeeID fields to auto enter serial numbers and import the spreadsheet to a temporary conversion database. 2) Find and sort duplicates based on Company Name, then in list mode manually copy and paste the CompanyIDs so all the duplicates have the same se…

I'll try to make things as clear as possible. Here's my setup. Table 1 [color:gray][Field A] - Account id, same value across several records [Field B] - unique record id [Field C] - numeric value Table 2 [color:gray][Field D] - Account id, auto-enter value based on variable value at the time of import script [Field E] - record id directly imported from Table 1 [Field F] - calc field that retrieves the value of [Table 1::Field C] based on the following relationship Relationship [color:gray][Table 1::Field A] = [Table 2::Field D] [Table 1::Field B] = [Table 2::Field E] My script imports records from [color:gray]Table 1 into [color:gray]Ta…

i inherited a database that i have been adding features to for quite some time. i'm looking for insight as to what to look for that could be slowing things down. recently, we have seen the server "freeze" and occasionally crash. typically it freezes only once a day, and will go for many days without freezing at all. this freezing started, perhaps coincidentally, when five users were added and 11 relationships to the task table were added. we moved the database to a new machine and location to eliminate those variables. today, it froze again, took about 5 minutes to come back to life, but didn't kick any users. we have jobs in the database and tasks a…

In this example you will see 2 portals. The one on the left shows all the contact records. The one on the right will show the filtered values. I would like the one on the right to show all records if the filter value is empty. I've been racking my brain on how to get this to work for a while but have had no luck. Any suggestion would be greatly appreciated. contacts_2.fp7.zip

I have an invoice table related to a line item table. When In the invoice table I create a new line items in a portal. I'm trying to figure out why it takes so long (10-12 seconds) to add a line item to the invoice via the relationship. There are about 50,000 records in the line item table. Any help would be much appreciated, I have tried everything I can think of.

So I posted a question a while ago about timers, and in that same thread I mentioned the project I was working on. It involves keeping track of the progress of a film shoot, where each time the camera begins to record, I am adding information about what the camera sees. I will do my best to keep the technical jargon of filmmaking out of this description, but if I lose you at any point, let me know. I have a couple organization-related questions that I was hoping I could discuss with you all. Since I began working on the project I have tried to create my database in a few different ways using several tables and relationships, but nothing seems to be perfect yet. Hopef…

Greetings. I have a main table for collecting data (event records), which has relationships built to 4 iterations of a data table (Model Records). Certain Models may appear in the event record table as Model 1, Model 2, Model 3, or Model 4 as they are assigned to a given event. I would like to be able to report on each models hours served, regardless of the position they held (1,2,3,4). Is there a script i can generate in a field that will combine any positioning in that event record table.

Hi Thank you for all your previous response to my questions. You guys are great help to me. Thanks! I have another question. Is it possible to toggle one more more items from one table or in the records from the portal to another portal(product list) in my quotation template? Thanks. Kent

Hi, I finally decided to update an old FP5 application to FM11. This application consists of 45 files (some interface files, rest data files). I have got most conversion bugs ironed out and the application works fine in FM11 except for start up. In FM6 it took about 1 minute to open the application (opening all files, transferring user ID). Now it takes about 3 minutes. I realize I am not using an ideal design by having 45 tables in 45 files. However, rewriting the application was not an option. I tried to delay opening some files until a user accesses that part of the application. Unfortunately, this creates unacceptable delays during the actual use of the app…

Hi, If I want to make the products of related product line appear in the portal in quotation details, how do I do it? If I use the product line number, I know it can be done. However, if i use the product line name, the products would not appear. ...Attached is the file. Many thanks. KEnt Product_Info_2010.fp7.zip

I have a donations table that has over 21000 donations in it. I have a contacts table that has a portal in it connected to the donations table that will show any donations by the current contact I am on. It is linked together by a field Keyname. I just imported another 10000 donations into the donations table but they are uniquely identified by a field called constituent ID. I want to be able to show both of these donations for a contact in the portal. The problem is I can only show on or the other not both. I want both the donations that are linked to the table and portal by keyname and the donations that are linked to the table and portal by constituent ID in the same p…

Hi all Have tax tables that need to be looked up off a total in a sub summary when sorted by name. Wont let me relate with a summary field. How do i get the daily wages to total other than using a summary field so that i can relate to the field in the sub summary or how do i relate the summary field that totals the daily wages to look up the tax tables as filemaker wont let me do it.

Hi all. I have been trying to pull a percentage using values from 2 different tables across a TO group. It seems like it shouldn't be too complicated but I have run into a snag. I am looking for the pecent of sales a salesperson sold from a group of leads called. I have provided an image of the TO group in question. It has been stripped down to just the TOs in question. From a layout based on the Employees 2 TO I have been able to pull in the number of leads a salesman called with a field in the Leads table that gets the found Count, filtered through the relationship from the Leads 2 TO. In the same way I have been able to get a found count of sales from the Tickets…

Hi all, I'm looking for a way to dynamically flag which record is the first in a portal. I have a portal showing records from a related table. I can sort the record in the portal. Now I need to know which record is the first one after sorting, because I need that record showing up in an other portal. In that portal I need to show all the records flagged as 'first'. Any hint? TIA

I have two Tables. One is Fields (as in farm), and another is Invoices. I have the two tables shown in portals on one layout so I can select multiples of each at the same time. I have to be able to do this to apply a certain amount of fertilizer or chemical to several fields all at once. My problem is that I cannot report all the information. It either shows the first line of the invoice and all the fields or the first line of the fields and all the invoice. the relationship is an automatic serial number.

Hi, Is there a way for me to add a new row using button in portal. Many Thanks. Kent

I have two related tables; Patients and Physicians. I need to see the list of physicians names from the patient's table to choose one (value list), and to be able to add a new physician name in the Patient's table, if that specific physician is not listed, without going to the physician table to add a new physician's name over there. Thanks

Basic question from a Nube. I have a simple PO system set up, only 3 tables. I have PO, customers and locations. On the PO form ( the main one) after I chose a customer I want to select the location from a list of locations that have a relationship to the customer selected. To do this I used a Pop up menu and set it up to pull data from the locations table. The issue is when I call up the table it only shows one location. If I save it, close it then open it up again all the locations will show up in the list. Since Im new to FM I not sure how to have this data refresh without have to close the data base. In Access I would just use a simple…

Hi If my laptop cannot access internet and I still want to use my filemaker file which should be connected the the database in the filemaker server, is there a way I can still use my filemaker file offline and when I can go online, new records can update my current database in my laptop? Any help is appreciate. Thanks Kent

Hi all, Im facing this little puzzle and I wonder if anyone can help. I have a Cases table and a CaseIndividuals Table. What I would like to do is when creating a child in the CaseIndividuals to automatically assign an arbitrary name (i.e. Individual_2) to the individual before completing FirstName, Lastname etc. (Name Fields may never be completed) So far, the only solution I have found is to use a self join for CaseIndividuals in order to count the same CaseID occurences. Using that I can suffix the "Individual_" with the total number of records for the same case. The problem however arises if I delete one of the individuals in the meantime and then c…

Hi, Is it possible for multiple user to login to a centrailize filemaker file and then create individual quotation and save it to their computer and also keep a copy in the database so I can create a report? If it can be done, can anyone give me direction on how to do it. Many Thanks. Kent

Hi everyone, I have a database that tracks Takes from a film shoot. Each take has many different fields: scene #, take #, camera roll, starting TC, ending TC, duration, etc. etc. etc. Each take can also be recorded on many different formats. For instance, a take can be shot on film (which is one format), transferred to videotape (which is another format) and then digitzed for editing (which is yet another format). For each take, I want to track what formats it lives on, so I'll know formats are available for viewing. I have a Formats table in the database that lists all the characteristics of the different formats we're using: camera type, aspect ratio, re…

I have a database that has many relationships. The problem I am facing is that the data I am bringing into my database has a different primary keys that the ones already made in filemaker. I want to rerun all my contacts so that the Id's match the ones in the database I am extracting from. How can I do this and not lose the functionality of my relationships? Thanks Again, Erik

Hi, I've just recently come back to doing some development work in FileMaker Pro (prior to 11, the last version I owned was 6!). I'm running into a snag in the design of a database for a music festival. Currently, paper forms come in with entry name and the class ID of the competition the person (or group) wants to enter. One person may show up on many forms, since a form (and fee) must be submitted separately for each entrant or group of entrants. Many individuals (up to 10 names in the case of a group) show up on a single form. I have tables for entries, participants, class, accompanist, teacher, and compositions. (In my diagram, I've also planned for sch…

I have about 10 related tables all set up the same way, and one of them is not working when all the other ones are working fine. I am totally stumped. I can't just create a new table from scratch as this particular table is referenced all over the place.(I had created it earlier and decided to change the way it is related to match those other tables that work fine). What is happening: when I type entry into related field, the value disappears as soon as I click off the field. If I create a calculation = that related value, it shows... However, "go to related records" does nothing, so clearly the relationship is "broken" in that direction. When I go to the rela…

Hello, I'm developing a database where I allocate work to a number of contractors based upon their skill i.e. carpenter, electrician, plumber etc. Some of them may have multiple skills. Question 1: Should I set up a table that lists the skills for each contractor i.e. one to many relationship if so, understand how to do that but then getting stuck on the process of how to us this information to allocate the 'job' The 'job' table has a field that corresponds to carpenter, plumber, electrician etc. Not sure how to link these? Thank you in advance. Regards, Simon

Hello all. New to Filemaker here and slowly figuring out how to use the program. I do have a question about a database I was looking at (trying to understand how things work by looking at a working database). When looking at the database relationship diagram (see pic below) I see that for the blue and purple colored items, that they are all the same tables/fields, just that the tables are renamed. My question is this: If I look at, for instance, the invoices table, it points to two copies of the customers table (renamed invoice billing customer and invoice shipping customer). Is there any reason the relationship couldn't just have been drawn back to the …

I'm not sure if this is the right area for this question. I'm setting up a db that has the usual first name - last name but also has partner first name - partner last name. How do I set an ID to relate to a table that contains other family last names that are not the same? i.e. if last name is Smith and the partner last name is Jones how do I set the relationship ID? The second table holds a list of family members and other private details. Thanks for any help.

I have an 'index' database which acts as a top level navigation page to allow users to open other databases. If a user logs into the index using his user name and password, he can gain access to all the databases in which he has an account with the same user details without a further login request. I've added a new database so have given it a new button which runs the following script: Allow User Abort[Off] Open file["RMA"] Perform Script["Startup" from File "RMA"] The file RMA contains a relationship to another file "Contacts". If a user has an account in "RMA" but not in "Contacts", he is being asked to provide a login to "Contacts" before he is given…

I have a single table employee data file that contains all the usual kind of data including the department each person works in. I have a form layout that displays each employee’s detailed data and also a list view layout that shows the list of employees. I need to restrict access to the records of employees depending on the department they are in and also whether they are currently employed or not. I have set up layouts (the form and the list) that show all employee records and the alternate layouts that show only the filtered records I think I can determine access to the unrestricted layouts and restricted ones via security privileges. My problem is filtering the record…

I'm not sure which section is best for this question...but it deals with relationships...so here it goes: I have a FileMaker solution out there in the marketplace. I have another solution I'm preparing to release. Both solutions have employee tables. I'm looking for an elegant way of having my new solution utilize the employee table of my current solution IF the customer has the first solution...otherwise it should use its own employee table. Anyone have any experience with this sort of situation?

Hi there, I have been asked to assist in creating a database for a group in my city. The organization is a hub for children called best start. Currently there are 3 hubs in the city. You can visit any of them on any day providing they are available. Each time you visit, you have to fill out your name, address (namely postal code) and the names of your children, d.o.b's, the date, time etc... I find this redundant info once you've done it. Would it be easy to setup a database to for instance, have the parent register once with all pertinant info, then when they visit a hub, they select the hub which would be set as a default location and then date, times, whether the chil…