Relationships

I have attached a database that I've just started working on. It's a flight planning system and involves quite a few calculations for distance time etc. Per the attached, I have a locations table which is where I want to store preset locations along with their Latitude & Longitude. On the flights table when entering the "LegFrom" I want it to populate Sectors:lat1 & Sectors:lon1 with the details from the location table. I can then run some more of my calcs. How do I go about bringing this across? I can have it open a popup box which allows you to select a locations and brings it across, but I can't get it to bring across the lat / lon also. Hope th…

Is it possible to auto-enter data (text) from previous record to new record automatically when new record is created? I need to do this only for one field. Now user must first go to previous record and copy text which changes in every record and then create new record and then paste to new record.

Is there a way to determine the rank position of related records with a calculation field? In other words, I have a parent table and some child records. In the child records, I want to be able to have a field that stores the position of the record as it would appear in a given portal, defined by a specific relationship - which may or may not have a sort order defined. This is so that I can use this data within results returned by the List function, which concatenates records from the child table. And so that I can report the rank of the child records in league tables. Many thanks in advance, W.

It my understanding that an unstored calc can be used only the parent side of a relationship...correct? I am getting zero results in my portal when I do so. If I change that field to regular number field, all works fine. I have checked the calc and it is working fine too.

I have a secondary table that associates, among other things, a name with an ID, and a main table. Users: ID Name MainTable: UserID And the obvious relationship: MainTable::UserID = Users::ID. It's a many-to-one mapping, with many MainTable records matching one User record. I'd like to make a nice layout with subsummaries, but I ran across a problem that just seems too basic to be anything other than a mistake on my part. If I'm in a layout that's viewing MainTable, I can sort by MainTable::UserID. Subsummary layouts work fine. If I try to sort by Users::Name, I get nothing. Well, actually, MainTable gets "unsorted" - exactly as if I pres…

I have a table called Calendar containing a record for each period. The fields are: PeriodID Text EndingDate Date gCurrentPeriodID Text / Global I want to be able to access gCurrentPeriodID and corresponding EndingDate from anywhere in my solution. I think I need to create a relationship that allows me to look up EndingDate where PeriodID = gCurrentPeriodID. I tried that, but could not get it to work. What's the best way to access gCurrentPeriodID and corresponding EndingDate from anywhere in my solution?

I have a database related to another database that I want to sort the total summary of records. Example i have a database that I use to keep track of events that take place that this record participates in. I have the database count the number of events that person is attending. In my primary database I have the summary field on it and it will tell me how many events that the person attended. When I sort with this summary field it does not sort right It will partially sort it and there will be a record or two that is in the wrong place. Is there a way to sort these fields that I am missing. Thanks in Advance.

Hi, I am having a big problem trying to figure out wich way i can make this work, I have here a -contacts table (contatos) -Sales table (vendas) -Purchases Table (compras) -Sales Line items (Lista Vendas) -Purchases Line items (Lista Compras) -Produts Table (Produtos) Now, the img attached shows the relationship, but my need is the inventory, i seem to not get the result i want and i have figured out the problem, i just dont have the answer. 1st try:: What i tried to do: Make a quantity field inside the products table calculating the quantities from the sales list minus (-) the purchases list to get the result. What went wrong:…

I'm working on a database for my cabinetshop. I have two tables. Table 1 contains the calculation fields I use to dimension cabinet parts. Table 2 holds parameters which are used in some of the calculations. I keep all the parametric information in one table so that it is easier to find and manage. An example of a parameter might be how much we want to trim down from a cabinet door after we build it. Today we typically trim 3mm from each side. If technology changes in the future I can change this value on the parameter side. The two tables are joined with a match field that is always set to match. The problem I am having is that when I rev…

Hi, I'm really stuck I have two identical DB's each being used by separate tour offices both on the same server. One of the offices books all the tours for both offices. How can I get all the tour requests to appear in one layout and still be able to communicate though the records (leave notes for the other office etc. - information changes often). For example if we're booking tours to the zoo. 4 could be though office 1 and 2 could be through office 2. All 6 need to be viewed on the same layout to book together as well as always be able to check availability etc. I don't know how to set up the relationships or the layout, any suggestions would be mo…

Hello all: I'm curious as to how others handle the creation and deletion of records in many to many relationships. For instance say you have two tables Invoices and Payments with a join table between them making a many to many relationship. If your creating payment records would you go to a layout from the join table and use drop down value lists and create the record from the join table OR would you go to a layout from the payments table and create the record there and then create the join table record behind the scenes. Deleting payment records I would think you would be better off deleting from the payment table and then delete the join record behind th…

Okay, this has me buffaloed. I thought I knew how to do an "or" relationship, but I just can't seem to get it to work. I have two tables. Client has a "To" field and a "From" field. Event just has a "Client_Name" field. I'd like to create a relationship between the two tables that will show all records related to the Client table in a portal in the Event table. The problem is the client name could be in the "To" Field or the "From" field (but not both). I've tried making a field in the Client table that has both names (To_From field calculation = To & "¶" & From) and joining it to the Event table. I've attached a sample file. Any help is…

I have posted a sample file with students who take two different certifications A and B. Each students takes 4 tests for A and 4 tests for B. I want to be able to print out a report for certification A and certification B showing progress of each student. Ie. which student completed 1 or 2 or 3 tests. Students should only be in one category for each certification (ie the maximum test number). Report would be certification a, test 1 student names with total, test 2 student names with total etc. Then certification b , test 1 student names with total etc. studentstest.fp7.zip

Hi to the community. I've searched the forum and have been unable to find a working solution to my question. I'm certain I'm missing something simple. I have 2 tables, "Items" and "Details" The relationship is set up with allow creation checked on in details. Primary key in "items" is linked with and matches foreign key in "details". Two issues to resolve. First, I'm unable to get my "go to related records" script step to work. The foreign key field in "details" isn't populating. if[isEmpty(details::kp_id_details)] Go to Layout ["details"(details)] New Record/Request Else Go to Related Record [show only related records; From ta…

I have 3 Entities: ENTRY, BOOK and DOCUMENT. The ENTRY can only have one type: BOOK or DOCUMENT. How can I achive it. I created the relationships, Primary and Forein keys, but it does not work the way I want.I did try to validate with no results, besides the ugly dialog box pops up! Please see an attachment ( I left a very few attributes to keep database clear.) and help me or at least point me what should I do. note: The structure of tables must be preserved because there will be a huge data imported from a PostgreSQL. Thank you very much! ENTRIES.fp7.zip

Hi, How would you design the relationships between customers, invoices and products where : A customer is member of a customer type. Each customer type can have different prices for different products. The image represents what I think would be a good beginning.

I have taken over creating an inventory database, keeping track of different types of inventory within the company. I have also been tasked to create a page that will show management what an employee has checked out. The first four tables work without a problem. The last table, shows "" in the browse mode. The layout that I am having problems with is "inventory check out", this page pulls information from two different tables. First table is "main", the second is "history". I created a relationship to both the "main" & "history" tables in order to create a relationshp from the "Inventory Check Out" table to the "Equipment Checked Out" table. The "Equipment…

So, I have been working on a volleyball tournament database. In each match, there are 3 teams involved: 1) Home team ; 2) Away team ; 3) Referee team. Each team in any match will at some point be performing each of the roles, i.e. every team is sometimes the Home team, sometimes the Away team and sometimes the Referee team. My database has a Team table and a Match table. When I am designing a layout to show a summary of a match, I have the need to display information about each of the 3 teams. I know that I can achieve this by having 3 TO's of the Team table, each related to the Match table to the 3 team roles. I have no technical problem with this. However…

Hi. Having been a lurker for some time, picking up useful solutions to problems within my own database, I am now up against a simple problem that I can't solve. Using the conditional value lists example that comment put up some time ago, I can happily add relevant data to sections of my database, but I now want to peel of a certain section of info, so to add further data in another table. How do I go about doing this? I have a test example consisting of presently 6 tables: Inventory Postal Items Make Objects Software along with 2 sets of conditional valuelists: One to drill down the Country, County/State, Borough And the other to a…

Hello there. I have a database with two tables, Employees and Safety (actually it's much bigger than that, but the rest is irrelevant to the issue at hand). Using Relationships and Value Lists, I've been able to assign individual employees for Safety Detail: Fire Brigade (Coordination, Extinguishers, Fire Alarm), Evacuation Brigade (Coordination, Drills, Resume Activities) and First Aid Brigade (Coordination, First Aid Kit, Ambulance Alarm). An employee may perform several duties at once, some employees don't perform any duties at all. So far so good. Now here's where I'm hopelessly stuck: Let's say in the Employee Table, the John Smith record,…

Hi, In one of the database I'm developping. I can't create a record via a relationships. In this problem, there are 3 tables : Borrowers, borrow and files. For each file there can be more than one borrower. A borrower can have more than one file. Borrow is the join table. From my file layout I want to be able to create a borrow in a portal which link to the borrower. I don't want the possibility from file layout to delete directly the borrower since he can also be an owner in the futur. I attached the file, it might be easier to understand. testDB.zip

Which do you use, and why? Do you use both cases situationally? I have not developed a standard on which way I choose to go with these. Sometimes it seems like using text is better to me, but I'm afraid I'm not grasping the full ramifications of each. Please share your ideas!

I have looked through the forum and unfortunately cannot find the answer to this (maybe it is too basic). I have built a Contact Manager which will track not only names, address etc, but also tasks, opportunities and product sales for each contact. However what do I do for husband and wife contacts where they may have joint products (or children!!)? In the past I included all of the 'partner' information in the main table (Clients) by simply duplicting all of the Client fields and re-naming them parnerxxxx. The problem here is that if there is no partner, there are a lot of empty fields on the layout. Then I would mark a product/task etc as owned by eith…

Hi I have searched both on this forum and on the homepage for the program but cannot find any help. I have a very basic problem/situation. 1. I have a stock (with different products) 2. I have invoices/purchase orders. I can make Product Drop Down-list on my Purchase Page interact with my Inventory list. (Good) I can make the product description on my Purchase Page interact with my Inventory list. (Good) But I want to have the correct product count for each item in the Inventory, which means if I have one purchase order where someone buys 2pcs of a product, I want the product amount in the Inventory be deducted by 2 products. I was trying…

I came in this morning and all the windows in my db are empty, at first look it seemed like the data was all gone. The tab under manage database, relationships, it is totally empty. When I open scripts, they show thanks

I have a scenario that which requires at least 4 tables which can be represented as such: Continent (which is a superset of) Country (which is a superset of) State (which is a superset of) City I would like to be able to create relationships, value lists and layouts that enable the user to view the information from the top (Continent) all the way down (to City) (succeeded here already) *AND* from the bottom (City) all the way up (to Continent) (failed here). My failure is not that I cannot figure out how to accomplish the task, but rather that the solutions that I can come up with seem flawed. My instincts tell me that there must be a …

In my database I wish to have a "calendar" view based on dates / times in my one table. What I want is for this to be "dynamic" I guess you could say. I don't want to have to put the "events" into the calendar tables if you will. I would like the calendar to pull that info on the fly from my one table. Is this even possible? I've seen so many different calendars out there I have no idea where to start. I don't even fully understand the calculations part of the calendars yet. I hope this was posted in the correct place.

Hi All, I am in the process of developing a db solution with an aviation focus. I am a novice-intermediate with Filemaker and have had a lot of help from more experienced people in putting this together so far. The basic functionality that exists, is: 1. Create a Flight 2. Create sectors for a particular flight 3. Assign crew to particular sectors of a flight The above functionality currently works. What I would like to do now is add the ability to add crew to a flight as a particular category (ie as a Pilot, Crew or Passenger). I envisage doing this the following way. 1. Select the sector. 2. A portal is shown listing all crew and 3 x Check…

This is possibly the most stupid question here so far, but... I have 2 tables: Calendar and Personnel In table Calendar I have field: cUser Unstored,=Get(AccountName) and in Personnel field tUsername. They are connected in relations table with symbol "=" When I try to fetch information from field Lastname in Personnel to be seen in Calendar -layout, I get only Is this "Unstored" the point I cannot get this to work ? If so, How can I get related information separately to different users logged in ? As I said, this might be the most stupid question here so far... EDIT// Uh oh... Problem solved. I dont't know how, but now it seems to work. I'll g…

I have three external ODBC tables, TOOLS, ATTRIBUTES and TOOL_ATTRIBUTES. ATTRIBUTES is a table of all possible attributes and the TOOL_ATTRIBUTES tables assigns an attribute to a tool and captures the value of the attribute. For example I could have a Hammer with an attribute of claw. Other attributes might be wooden handle and fiberglass handle. Now what I would like to do is build two portals, one to display the tools and the other to display the attributes. But in the second portal I would like to display all possible attributes and then change the text (or highlight the row or some other indicator) on the rows that are in the assigned to the tool. …

Hi, Is it possible to insert a row (record) within two existing rows in a portal? Regards, Garu

Hello, I'm hoping someone can point me in the right direction. The situation is 2 tables, 1 client data the other services. They are related via customer id. Assuming each client has the potential for more than 1 credit card; how would it be setup up in the services table when the client makes a purchase, I could choose from the client data the the appropriate c/c that was used for payment? I'm thinking a lookup field might but work but stuck... Jack

Hi All, Been wrestling with this one ... I've got a layout that has a sub-summary part, that sorts "Jobs" by the client's "name" and within that sub-summary part shows a summary field "total spent" = "Total" of invoices, ie BBC....................spent 500.....no. of jobs3 Queen................spent 250.....no. of jobs4 R comes after Q..spent 800.....no. of jobs2 but what i'd like to see is the clients listed by the quantity of spend, ie R comes after Q..spent 800.....no. of jobs2 BBC....................spent 500.....no. of jobs3 Queen................spent 250.....no. of jobs4 I'm guessing that this isn't possible because the records …

Hi. I'm brand new to this sport. I've just been given the task to help create a database and wondered if anyone would have the time to help set up an initial relationship? This deals with handling workman's comp cases. I'm using FM Pro 10. 1 ---There's people - name (fname and lname), address, phone etc, and notes (maybe a pic). 2 ---There's insurance companies (some people *may* have more than one insurance company. Each person will have a case id number. And it would be great to look up insurance companies and view the different people. 3 ---There's attorney's. With company name, contact name, address, phone, notes, etc... Each person will have another case id …

I am getting the following error when attempting to delete a record: "Operation cannot be performed because one or more of the relationships between these table are invalid" Does anyone know how to find out the offending relationship? If I open my db and go right to the record without invoking any scripts, I can delete the record. Thanks Scott

Hi, I'm looking for some "relationship" advice. A co-worker maintains her own filemaker database (containing contact info for about 800 individuals). I maintain a separate filemaker database with my own contacts, about 200 of whom also appear in my coworker's records. For security reasons we cannot merge and share the two versions. Is there any way that we can create a relationship between them so that when she updates a common record in hers, the changes automatically occur in mine too. (I know I can export her list and import to mine using the update function, but it seems like there is probably a more simple way to approach this long-term problem.) Thanks…

Ok here is the situation: I have two databases, One contains records for my inventory items, the other contains manufacturer names and logos. Currently i have a selection on the inventory page that allows me to choose the manufacturer from a drop down and the corresponding logo will populate. However when i created a report i can not get the information to link properly between the two records. when i pull the report it just displays the first record in the Manuf. Logo database and not the logo that should be associated with that inventory item..

Hi, Did someone found a solution to eliminate screen the screen from flickering when in the relationships while doing DB management? It is totally insane and counter-productive. I attached a screen cast from my FM. Here's my configuration: Dell Vostro E6600 nVidia 8800GT 2 GB DDR2 XP Pro SP2 FMPA10 FMS9A Help! fm_screen_flicker_transcoded.zip

Good morning dear FileMaker Community Once more i am stuck with a problem i thought I have already solved earlier, but I lost the solution (as i mostly work on FM 6). In FM6, I had a table for central data, like logo, current user, bank data, and so on. It was linked to the working tables with fields called "one", which was a calculation field with value 1. In FM9 I could go on working like this, but I'm quite sure there is a faster qay. Can anyone help on this easy issue? Thank you in advance

Hello! My boss wants me to make new database to be his "control panel". He wants to be able to look at one screen and see what's going on with our jobs database, purchase order database, commissions database, etc. [color:blue]Does anyone have any suggestions on how to start this? I would assume that I would do everything through portals, but i'm not sure how to connect them all to this new database since it will essentially be blank. : Thanks so much!

I'm working on a database that schedules classes to rooms, times, and faculty. It's use is not as a calendar, but I'd like to be able to have a week view (Mon-Fri) with what is scheduled either by Faculty or by room. I have a couple of questions. First is my structure correct... I have a class table and it has the foreign key for the Faculty, as well as a foreign key for a linking table. The linking table has a foreign key for Days (5 records Mon-Fri) and a foreign key for Rooms (set number of rooms). Am I close for structure? The big question for me is how does time relate to everything? Does it go in the linking table as a field, or as another table, or should it …

Hi All, I'm stuck in my thinking... there must be an easy way to show all related records in the parent table as a found set, based on a relationship? I have two categories: Groups and Members, and there's a Join table linking the two (many to many relationship). I have a portal on the Group layout showing a listing with all (related) Members. How can I show these related Members (and only these) as a found set in a list view in the Members Table of the database? Thanks for any tips!!

I want to create a relational link to a field in a table hosted on an Oracle server. I have read-only ODBC access to the Oracle database so cannot make a calculated lower-case field at that end to connect to, but the key field I'm using is an email address with unpredictably mixed-case characters. The relational link only works if the case matches for both fields so I appear to be stuck. Any helpful suggestions would be much appreciated. Colin

I have created fields "formula id" and "formula name" which are each calculations (concatenation) of two other fields. On my layout I have these two fields and then a portal in which I am trying to add records (from two different drop down value lists). My drop down list shows...but when I try to select one of the choices, I get the error: This action cannot be performed because this field is not modifiable. From previous posts, I can see that the problem is somehow related to my calculation fields but I don't know how to fix the issue. Also...I do not know how to attach a copy of the file or any screen shots to this forum so someone can see what I…

Hi..i have a relationship with 4 AND matches in it...one of these matches follws the format P4526.468 now does the point (.) make a difference because im getting all P4526's match.. its like its not taking notice of whats after the "."

I want to send 30 emails with individual messages. I am using Send Mail options “Multiple emails (one for each record in found set)” When I use this scrip, I get 30 emails one my desktop. Could you help me to send these emails automatically with out clicking each individual send button.

I have been struggling over something that I am sure has an easy solution. I am trying to create a filtered value list for the employees table. I have a table with active and inactive employees, some of whom are salespeople and some are not. I have created a calculated field to determine which employees are active salespeople and created a self-join in the employees table based on the calculated field on one side and the primary key on the other. I want the value list to show only those active salespeople, and I want to record the primary key for the employee, but only show the full name on the value list. I have tried every possible combination, including global fie…

for a table with not very many records, i have a portal on a layout that contains the main text field from all records using a selfjoin relationship with a constant as a match field. now all records show up nicely in the portal. each row is clickable to take me to the proper record. this lets me quickly move among records without leaving the details view. so far so good. now i want to highlite certain text fields in the portal based on context. for example, the text data in the main field for some records might be might be red, green, blue, and for some others table, chair, lamp. if i click either of the portal rows that say red, green, or blue, i want the relat…

If I figured out a question for myself...how do I mark the question DONE or SOLVED on this FORUM ?

I have attached my file because I am not sure how to explain my issue without someone seeing how my relationships are set up. Based on the attached file... When I create a new record on the LAYOUT "Forumlae" it adds a record to the TABLE "FormulasItems", but I need a new record (or records) added to FB Combo. It seems like it should happen...but it does not ? herbalsolutions2-27-09__1.fp7.zip

Say I have a database with three tables, Cats, Vets, and Operations: Cats: Name (PK) Breed Color Vets: Name (PK) Age Operations: Performed by (can only be one of Vets::Name) Performed on (can only be one of Cats::Name) Age of Vet Color of Cat I want to enter "performed by" and "performed on" into the Operations table, and have "age of vet" and "color of cat" filled in automatically for me. Simple, I know, but I can't work out how to do it!

How do I create an archive table within a file that will allow me to import records from a single similar table at the end of the year? Both tables have the same fields. The purpose of the archive table is only to be a stand-alone annual repository of the records from each year so I’ve included a (flag) year field. I know how to import matching records from table to table, but I’m not sure how to set up these two tables in the relationship graph. I hope you can help. TIA lpm FM9Adv WinXP

Hi All, I guess I'm just rusty from not doing this for a while, but I'm trying to figure out how to do what should be a fairly simple validation. I have a table structure as follows: Purchase Order -> Line Items -> Products -> Suppliers There can only be one supplier per PO, and by selecting that supplier, the supplier info area filters a selection portal to only show items for that supplier. That selection portal is then used to populate the line items. The problem arises if the user changes the supplier after there are already line items in the order. I could easily validate against changing the supplier ID field if there are existing r…

This is probabbly very simple to sort out... but anyway... see attached file.. what im trying to do is a Go to related record but I only want to go to the related record that I click on in the portal row. Check the attached file it explains it better. testrelated.zip

Another newbie question: I have a data set of product IDs - these product IDs then have a subset of color IDs. I'm then subsummaries to group records by product ID. The question I have is, how do you get count info on the number of unique product IDs and separate out the display of global product ID attributes from color specific ones?

I hope this is under the correct topic. I have a company and contact table, not all companies have contacts. CompanyA Contact1 CompanyA Contact2 CompanyB CompanyC Contact1 CompanyC Contact3 CompanyC Contact4 CompanyD etc... (This is essentially a partial join of Company to Contact). What's the best way to print a report on all companies with their contacts (if any)? Using sliding portals is awkward and limited.

I feel like an idiot asking this, as it seems so simple, but I just can't figure it out. This is dealing with 2 tables. I'll also include the fields that matter... Table One: Employees Fields: employeeID, fullName (calculated/concatenated field) Table Two: Meeting Notes Fields: employeeIDfk (foreign key from Employees table), mnNotes What I'm trying to do... On the Meeting Notes data entry screen, I'd like to be able to select present users with the fullName field (from Employees) in a dropdown list, but have the database actually populate with the employeeID field (Primary Key from Employees) value. Does that make sense? I fear it …

I'm doing a lookup to pull information from a Contacts Database into the current database. The matching fields for the relationship are the contact names. When I enter a contact name it pulls in the persons email, mobile, phone and fax numbers. Everything works fine for the most part but when there are a number of contact names the exact same (e.g two John Smiths) the lookup always picks the first John Smith it encounters in the Contact names database and pulls in their information. How can I get the lookup to pull in the information from the correct record in the Contact Names Database

I have a database that tracks home inspections, there are technicians, dates, times and duration. I have a calendar that I created in Filemaker that through relationships and portals displays the services on the right days. It has a monthly view and a weekly view. The weekly view has 7 portals for example and 7 different relationships, so it can link the date to date that tracks our inspections and display the information on the right day of the week. The monthly view has many more portals and relationships (40). The problem is a daily calendar with times in 30 minute increments (e.g. 7am, 7:30am, 8am etc...) to have it display all the appointments at the right times. …

I created the starter solution "Home Contact Management". I then I created the starter solution "Business Time Billing". I then copied all the fields from Time Billing and pasted them into the Contact Management. I then copied the Layout and pasted it to a New layout. When I look at the Time Billing layout all the fields say "" Is there an easy way to fix this or can someone explain the proper way to add these 2 starter solutions together? Thanks Ben

Hi, Multiline-key are great but they used as logical OR, which is usefull for a lot of situation. But now I need a logical AND. I've a database of products, Table Products ProductA ProductB and a table of product attributes ProductA Attribute1 ProductA Attribute2 ProductA Attribute3 ProductB Attribute3 ProductB Attribute5 ProductB Attribute6 I wish to go (with a go to relationship step) to all products who have both Attribute2 and Attribute3 so in that case I would get A It's as If I had a car database and I want the user to be able to get all the cars that have manual geard and are blue A multiline key, popul…

This is a noob question I imagine, but how does one combine records from multiple tables into a new table? Specifically, I have three excel reports that report on different attributes of a list of products. Not every product ID is on each report, but there is overlap. So what I am wanting to create is a table that all the product IDs from each source consolidated and filtered to remove the duplicates. While I could do that manually in excel, these reports are updated multiple times a day and I'd love to be able to have this automated. Any suggestions?

Hi Everyone, I'm stuck. I don't know if I'm setting the proper relationships or if this is a portal sorting issue I have four tables. (1) Users - UID - Fullname calculation (from First Name, Last Name fields) (2) Screening Sheet - UID - each invoice has portals to two other tables. one to show an itemized list of songs used. another to show who the Producer(s), Editor(s), and Production Associate(s) were. (3) Screening Sheet Line Items - UID - Song IDs (populated from a separate Song database) - fk_UID - copied over screening sheet UID - these line items are generated from a portal on the Screening Sheet table/layout. (4) U…

Hi all, I'm a total newbie to filemaker but anxious to learn. I've been using the Starter Solution "Contact Management" and what I want to do is have the field for State trigger a drop down field to show a value list containing all the colleges in that state. I've read http://www.filemaker.com/help/html/non_toc.46.32.html#1029041 but I'm not sure if that's what I'm looking for. Any suggestions? Thanks!

What's the best way to implement a unique dual key index (SQL terms) in FMPro? For example, a company_contact table: with 2 key fields: CompanyID and ContactID CompanyID ContactID ------------------- COMP-001 CONT-001 COMP-001 CONT-002 COMP-001 CONT-002 not allowed (duuplicate) COMP-002 CONT-001 COMP-002 CONT-004 COMP-003 CONT-005 COMP-004 CONT-002 etc. - Jansen

I've asked this before but was wondering if the answer changed now that fm10 is out. If I have an application consisting of 7 files interrelated, can I easily transform that into one file with all the relationships built in. Mike

I have setup two different tables: Invoice Contacts What I want to do is to be able to click on the invoice Contact name and it bring up a separate Contacts list. When they click on a contact in the list it imports that data into the invoice. This allows the client to create invoices for returning costumers without having to re-enter the information. Can someone help me set this up. I was able to do it with portals, but I am unable to carry that information over to a field. Thanks in advance!

I have a table ("registrations") that has a number of fields, including a DATE field that's automatically populated with the creation date. When a new registration happens the current date is automatically populated there. I also have another table ("shows") that has a relationship to the "registrations" table based on the show ID number. As it is now, there are 55 registrations 'visible' from the "shows" table. What I need is, on the "shows" table, a list/breakdown of registrations by date. For example, let's say that there were 10 registrations per day for the past five days, plus five registrations today. What I need is a portal on the "shows" page that conta…

cRemaining = Length - Sum ( Usage::Retuned_Roll_1 ) This is the formula i use to update my inventory from other fields. But i have a question. I set up my job page layout "Where I subtract how much i use of a roll of paper) so that I can select many rolls on one job which is normal. What happens when you have Returned_Roll_1, Returned_Roll_2, Returned_Roll_3 as fields that I need for reports but updating one specific field on Inventory based off which roll I select to start with from drop down? Basically i need to know when I use a specific roll if it was the first second or third roll used on the job. That is the reasoning behind me doing it this way.

I have a list of bank transactions Payments and receipts. When I go to the Bank I group the list of receipts together under one Lodgement date and code. What I want to do is get a list similar to my bank statement which shows each payment but instead of showing each receipt it just shows the total lodgement. I can do this in a summary report but I need to do this in normal browse mode. I have setup a self join relationship based on lodgement date and put the totals from the portal on the layout but I can't seem to stop all the other lodgement transactions from appearing. I enclose a rough if my layout and as you can see the first 4 records are copies of e…

hey guys, I have a relationship question. How do you separate quantity into records. IE: I order 10 computers and want to be able to put them into inventory each with there own serial numbers. I use orderform to order the computers and input the serial numbers in there, which i want to be able to have added to the inventory as soon as you put the serial number in. let me know if this isn't clear enough i'm not sure how to even describe what i want to do. thanks a bunch!

This doesn't make any sense to me at all and I've been building filemaker databases for a long time. Anyways, I have two tables. Lets call one Company and the other Patient. Within both databases they have a field called "code". So I set them both up as text field. In the relationship I drag CODE in COmpany to CODE in Patient. I set up a portal in a form for Patient. When I see a person with CODE of B001, in the portal for Company I'm getting: A001 B001 CD01 D001 and so on So why isn't the text of "B001" ONLY mapping to the text in the other table of "B001"??? Why is it getting the A001 and so on? I could see this happening if they were…

Some time ago I had problem with one filemaker program I have made. I have solved problem so I did it from scratch. But now again, same problem. Records are disappearing. I enter something in program, check few hours later and there is nothing entered ... how is that possible? What the hell I am doing wrong. La rhetta checked my program and said it is ok ... I removed all "allow deletion " in relationships ... But problem is not only in portals, if I check records in other layout that displays records from that table, they are gone??? Help me, I am going crazy.

I have a simple 2 table parent child relationship. I store the parent primary key value (a unique ID) in a foreign key field in the child table. No matter what I do, FM continues to allow the deletion of parent records with dependent children. I don't think that should be possible in a relational database. How do you maintain referential integrity like that? I'm a newbie, so I may be missing something. thanks in advance! Hal

Hi I create a Layout to record my week stock balance, My problem here is how to bring my stock balance for this week to next week and next week to following week? I have year, month and week field in place to show the record belong to which year month and week. Thanks gregory

Hello Any help on this would be greatly appreciated I am sure it is something simple but I cant get it. Basically I have 2 tables, clients and jobs with a relationship set up between id codes which makes a job specific to a client. The problem I am trying to solve is that I want a portal on the jobs table which shows all of the clients listed I just cant work out how to setup the relationship. I want to use this method rather than a dropdown list because I want to be able to have more freedom over formatting. Thanks again James

I want to be able to maintain a record of mailshots sent to different groups of customers. I also want to enable users to create mailshot templates (well at least the content for those mailshots). Anyway, I thought following some previous advice that I would get a found set of contacts and then do a single report (body per record etc). (Mainly because of an inherited structure) I have the record of "communications" with contacts in a separate file to that containing the contact records. I have been trying for a little while with the attached file - which corresponds to the above ideas - but there is something screwy in the final relationship which I don't …

Hi all...I have not worked with Filemaker for about 12 years now...and I am trying to build a new database which seems simple on the surface...but I've already run into my first problem. I have a File (PHARM) which contains (2) fields: PHARMACOLOGICAL ACTION (text) HERB NAMES (text, repeating 15) Please note: one ACTION can be performed by many HERBS I have another File (HERB) which contains (2) fields: HERB NAME (text) PHARMACOLOGICAL ACTIONS (text, repeating 15) Please note: one HERB can perform many ACTIONS I intent to populate PHARM and then want HERB to be able to lookup all of the possible PHARMACOLOGICAL ACTIONS matching from th…

Hi All, I am trying to create a baseball database that will allow the following: - Track players position by inning, batter order per game, pitch count per inning I have a file setup with players names and contact information...where should I go from here? Thanks, dmg

Hi I have 2 identical databases being used by 2 different departments. (Don't ask that's what they want) For explanation purposes both DB's have an identical table named PROGRAM Is it possible to display the records from both PROGRAM tables in the same layout in one of the DB's? Thanks Again

I need to re-serialize (using Replace Field Contents) a few of my primary key serial IDs (Indexed, Auto-Enter Serial, Can’t Modify Auto). Each primary key has a foreign key equivalent in another table. If I re-serialize the primary keys, will the foreign keys automatically be filled in, or is there some process/command that I can use to fill them in so that I don’t have to do it manually? I guess I want to re-serialize the primary keys and somehow sync their respective foreign keys so they match up. Not sure if I’m asking this the right way. I hope you can help. TIA lpm WinXP FM9Adv

Hi there, Could use any advise or pointers: I have three tables [media info] [project] list of projects, project #1, #2, etc [project track] details of each project, containing multiple media info through portal Basically each Project track contains a list of media (50-100). My problem is that media info contains ~2000 records, and I want to search for specific criteria and then add those to project track. The way I have it set up now is that [Project track] creates a new record based on the [Project List] record number and a unique identifier. How can I sort through the records in Media Info, check them all (or some), add them to Project…

Hi, I'm looking to build a product selector database. If I choose a size, i want it to only show the product in that size. (i have this part working). When I select the product, I want to then only show the sub items related to that product, and again for the next level down. I've tried various methods to get it to work, but I'm stumped! I've attached a sample of what I'm trying to do. Thanks for your help in advance, Skip Test_Product_Cat.fp7.zip

Hello, I'm stuck and need some pointers please. Here's an outline... I have two tables. Table 1 details Tax rules and percentages, by country. Table 2 is a series of records detailing income and on which some calcs are performed on the basis of table 1 info. The tables are related by a short-form selectable country-code; table 1 country-code is defined as unique, so the relationship is one-to-many. [Table 1 code = data specific to this code] [Table 2 = includes multiple records based on occasionally the same codes, utilising the same rules.] Two problems - at least... 1) I need to store the country-code specific formulae in text form [ta…

Newbie here with a build issue. I am attempting to build my first true related DB, and think I am overanalyzing something easy. I run a rent-to-own store, where we have customers (sometimes joint apps) fill out a form with their info, and then someone verifies their info. Things that we verify are their address through the landlord, their employment through the employer, and their references. I am wanting to move this verification process to a DB, so that we can move to a paperless storage of the information given, as well as a standardized verification procedure. The problem that I am having is what exact tables I should create, what keys, and the proper rel…

Please can someone help my understanding of a simple relationships issue. The attached illustrates the problem. I have a single table, with an ID and Parent ID to relate the tables occurrences together - 4 tocs are included each related in the same way. I have a simple calculation which looks at the ID of the child toc. If the field is empty, it assigns "empty", if not it assigns "not empty" - ie simply detecting the presence or otherwise of a related record. When I show the Portals for the 4 tocs in the layout, they display "Empty", "Not Empty" or nothing (if no record there) as expected. However, if I show the associated field values, they always sho…

We need a report based upon the many side (Invoices) to the one side (Purchase Orders). But it is a bit more complex than that. Tables are: Contracts: ContractID PurchaseOrders: POID ContractID Invoices: InvoiceID POID ContractID Why do we have the ContractID in Invoices? Because sometimes invoices come through and we know which contract they belong to, but we haven't matched them to their specific PO yet. On the other side, we have Purchase Orders who have no invoices yet and THEY must reside in the report as well. Theoretically we should stand in the Invoices table for the report but to do so will mean we can't include POs without In…

I have a table with STUDENTS and I want to set up relationships between some of them. We have a mentoring program and in any particular academic year some students (upperclassmen) will be "Mentors" for new majors "Mentees". There will be new Mentor-Mentee pairs in successive academic years. I want to keep track of this. So, I want a unique identifier for a Mentor-Mentee relationship and I want to keep track of who's the mentor and who's the mentee in each relationship. So it seems like I need a "Mentor-Mentee" Table that has a unique record key, but I'm not sure what kind of foreign key to use to link students in. Should I have a mentor_fk and a mentee_fk, and should ea…

Ok I know theres a way round this but i need help quickly!.. see the attached file.... I'm basically trying to make a portal in the orders table to display customer address to choose from based on EITHER the postcode OR the surname... i can make it work with one on its own .. or the two combined but I cant have it look at the postcode OR the surname OR the two combined! Any help will be much appreciated. test_customerss.zip

I want to keep track of student enrollment in courses. I have three tables: STUDENT, JOIN, and COURSES. I have a portal in a STUDENT layout so I can see what they've taken. I want to eventually do statistics on the student enrollments to determine when they take certain courses. Currently lines 1-7 of the portal contain Fall Freshmen courses, lines 8-14 contain Spring Freshmen courses, etc. The find function lets me find students who have taken certain courses (say, Chemistry). But the find returns ALL records where any of the portal rows contain Chemistry. What I want to know is whether portal rows 1-7 or 8-14 contain a Chemistry entry. Can I do finds for …

Hello I am having some problems with a relationship and I just can´t seem to get it to work any help would be greatly appreciated. I have attached a filemaker file so that you can see what I am talking about. Basically on the jobs table I have 3 drop down lists: client name- this looks up the name from the client table this in turn fills in the client address, contact name – this looks up the contacts from the contacts table specific to that job and in turn fills in the telephone number, delivery address – this looks up the delivery address from the addresses table. The problem I have is with the delivery address, when you select the client I want the d…

I have a db where I completed the initial build in FM9 Advanced. I have to publish it on FM7 Server Advanced (which is not ideal, but I don't have any other option) and had set up a portal based on a relationship with a match field to a list function calculation (which lists multiple keys so I can have a many to many relationship). This works well in FM9, but though the List function still works on FM7 Server, the relationship fails. Is there an alternate FM7-friendly solution? To go into more detail, I have three related tables, Pages (parent), Services (child) and Tasks(g-child). Each page can contain many services and a service can be attributed to multiple pa…

Hi Everyone, I'm in the middle of creating a large database but am relatively new to filemaker. I have performers and am wanting to assign genres to each performer. So far I have three tables: Performers Genres PerformerGenres The Performers table has PerformerID, PerformerName, Performer address etc. The Genres table has GenreID, GenreName and ParentID (for example Jazz would come under Music) PerformerGenres has PerformerGenresID, PerformerID and GenreID What I'm wanting to do is have one layout whereby I can add a genre on the fly but with the ability to have layers. e.g: Music - Classical - Jazz - Rock Comedy etc...…

Hi all, Am I missing something? Is there not a simple way to create a record in a related table when a record is created in the main table, without using scripts So for example... Main table is called Base 2nd table called Year1_Data 3rd Table called Year2_Data Base is related to Year1_Data by StockID Base is related to Year2_Data by StockID When a user creates a new record in Base, can I automatically create a new record in Year1_Data and Year2_Data using the StockID field without using a script, just using the relationship? (New records will be blank apart from stockID but that's perfect) Many thanks Ed

I have two tables A and B with the following fields in each: TABLE A Record ID A (auto enter serial number) type key 1 (a global text value) type key 2 (a global text value) TABLE B: ID tag (created from relationship, =Record ID A) type (created from relationship, so always =type key 1 or type key 2) date (entered manually) And the following two relationships: RELATIONSHIP 1 between A and B: Record ID A=ID tag type=type key 1 RELATIONSHIP 2 between A and B: Record ID A=ID tag type=type key 2 I placed 2 portals on table A layout: one for relationship 1 holding date from relationship 1 and one for relationship 2 holding date from…

I have several databases that are related, and the key field relating the different db's is the Organization Name. I know it is not the best setup to use a natural key, and would be open to suggestions to change that as well if it is the best solution to my problem. (I already have an id# field that auto-generates in the contacts db, but have not yet made it into a primary key field, as we are mainly a text driven organization and the people working in these databases link things based on organization or individual name. The contacts database also grew organically, which means that sometimes we have a record with an organization name, but no individual contact name.) …

Hi, I have a table called purchases that tracks merchant purchases. I created a drop down value list of all entered merchants in the merchant field. I want to be able to avoid redundant data entry, (web address, email, address, phone etc). I created another purchase table occurrence attempting a self join (merchant = merchant relationship). I used the purchase table occurrence fields (web, email, address, phone, etc) in the purchases table and if I selected a merchant from my drop down value list of previously entered merchants, it seemed to work as far as filling in those data fields, but it was somehow generating 2 records within the purchases table. What …

Hi All, I have a table customer, and invoices. invoice layout having a portal to show the customer names. If I'm going to search the A(starting letter) customers in customer layout the records will show in fine.. But the invoice layout customer portal will show the all records. But i need to know how we can show the customer names based on search criteria. Example If i find the A Names in customer layout means the portal should be shown in A names only If i find the S Names in customer layout means the portal should be shown in S names only Could any one please suggest me to solve this solution. Thanks in advance...

I have a Condominium Management application that keeps giving me problems with relationships. Basically I have 5 tables (In a separation Model). Units - There are 241 units and 2 buildings. Owners - There can be many owners to one or many units. Renters - There can be one renter record to one unit. Vehicles - there can be 2 vehicles per unit Special Entry - there can be many SP's per unit I have the 2 vehicles showing in a portal on the Owner layout as well as the renter layout. These units, for the most part, are rented short term (2 weeks - 3 months) therefore we want to keep the owners vehicles active at all times. So I set t…

Hi All, I have a members & memberships database, there is 2 tables: 1- tbl_Members: Memberid MemberName MemberStatus some other members data (e.g. date of birth, telephone) 2- tbl_Memberships: Membershipid MembershipPeriod Membership Payment Memberid(FK) These two tables are related members(One) membeships (many) as one member can have more than one membership specially when membership expired and he renewed for another membership. What I am trying to do is to build a report to show each member with all the memberships he has whether it's one or more, this is w…