Relationships

I need to be able to compare the data in many fields from one table to the data in many fields from another table and any data that is new or changed needs to be pulled into a 3rd table. I have data listings that are getting huge and cumbersome and so we want to print only what is new or changed instead of everything. I am new at this and have had no training in FM - my background is in Access and varoius CDC databases so please help if you can and walk me through how to do this if it's possible. Thanks!

I am using "Filemaker Pro 10: The Missing Manual" to learn how to relate various tables in databases. I'm developing a database to track students and classes for a community learning program (non-profit org.) Right now, I have a table with student info, including a student ID, a table with the list of classes offered this semester, with a class ID, and a table that lists first, second and third preferences for each student for each hour, as well as final placements in classes (by a value list that is created by the class list table). Hope that makes sense so far. So my question has to do with HOW to relate all these tables. In the book, they disc…

Hi, I'm a filemaker user who have been using for almost 5 years. Now, I'm trying to make data tables and layouts separate into different files. For example: i) "Table A (for Layout Customers)" belongs to "File 1.fp7" "Table B (for Layout Products)" belongs to "File 1.fp7" "Table A" and "Table B" are related by the field called "serial_id" ii) "Table A" is placed in "File 2.fp7" as Table Occurence Group. "Layout Customers" belongs to "File 2.fp7", and shows "Table A" iii) "Table B" is placed in "File 3.fp7" as Table Occurence Group. "Layout Products" belongs to "File 3.fp7", and shows "Table B" Question: When the …

My three remaining brain cells just went AWOL trying to resolve this one: I am building a database for a Boy Scout Troop (I'm the new Treasurer) and have much of it up and running, but can't figure out how to properly relate the following: Many (dozens) of scouts attend many outings each throughout the year. Each outing has many (4-5) scout patrols attending. Each scout patrol at each outing has many scouts (8-12) in it. Many scouts (2-3 out of each group of 8-12) in each patrol buy food for their patrol. All scouts in each patrol pay their fair share of the food costs for their patrol. I know how to do the calcs to divvy up the costs, but fi…

Hello, In the manage database mode I can't seem to copy fields or tables. In the edit menu copy, paste, etc are not highlighted, only "select all". There is no security activated on the database. Any suggestions? Thank you

Hi, I am new to database and I am doing one for my client. If my client wants to do a quotation to her customer and her customer approves it, is there an easy way to transfer the information from quotation to invoice tables? Thanks. Kent

I thought I understood that different table views are simply that - VIEWS into the same table, like when you establish a self-join on a key and FM creates a new view for that table. I also thought that data in a field in a table is the same data, no matter which view I am using to display it. Extending that, if I enter or change data in a field using one view, that field will reflect the change to that field in any other view. Now, I understand the concepts of filtering and lookups that might change WHAT you're viewing or HOW you're viewing - but not the DATA itself. But I have a very strange situation that sure looks like my understanding has …

Hello, it will take some lines to decribe, what my problem is, please stay ... 1. Situation: - I have a very complex database with join tables etc. - I connect Filemaker to an Indesign Plugin (Easycatalog) over ODBC. - I can access individual tables, but I have to recreate the relationships in Indesign to access all the information from all tables. 2. What I want: - I want to manage the complexity in Filemaker. 3. Problem: - I can display related records in a portal, but cannot access it over ODBC (only individual tables, or very comlex SQL operations with JOIN) - I want to combine related records in a calculation field, so I can acce…

Hi all! This seems like it should be easy but for some reason I'm beating my head against the wall with this. I have two tables. Site_records and Contact_records. Site_records is the parent record that holds all of the basic information for a site (address etc). Contact_records is the child record that holds (you guessed it) the contacts for the site. The two tables are joined by the site_records_id field. The Contact_records are viewed through a portal. As it currently works (I originally built this with little knowledge of Filemaker), every time a contact is added to a site, a new record is created in the Contact_records table. This is all well …

I hope I'm just suffering from sleep deprivation because I know this is incredibly easy to do BUT, I can't for the life of me make it work. Here is the situation: I collect my daily travel miles using an iphone app (milebug). At the end of the month I just email myself the report and, up until now, just parse it in Excel and use that to mail merge into a word doc. A lot of steps just to print out a report but I have to have it in the company's format. So, I figured I'd slap a quick FM database together and that way I could could easily keep the information better organized if I need to do something with it later. Anyway, Simple database, 3 tables USER - u…

Using: FMP Adv 11 on a Mac with OS X Lion. I normally consider myself pretty good with FMP, but cannot think through the relationship schema for a current project, which is for a language/dictionary database (for a constructed/artificial language. Nerdy, I know). Although the content of my database may seem strange to some, I think the techniques I am trying to learn could be of value to others. So here goes... My example here uses three tables. I have more, but learning how deal with the third will solve my problems for the remaining ones. TABLE 1 (my starting point) is called ENGLISH, containing SERIAL # (number), ENGLISH WORD (text) and POS (part of …

Help, I need to add a field into a table but can not find it in Manage Databse Table - I see it in the occurence graph but I can't access the invidivual table..... any suggestions??????

Sorry if this was already covered but I already tried searching for this for hours, I swear. And, as you'll soon see, I'm new to FM relationships. Say I have two tables, each with two fields: Doctors: doctorid, doctorname Patients: patientid, patientname It's a many-to-many relationship so I have a join table: DoctorPatient: doctorid, patientid I set it up to create records on the join table from either of the other two tables. Is there a way for the user to add a patientdoctor relationship by choosing a name from the drop-down list and it adds the correct IDs to the join list? I was able to figure out a solution with script trigg…

--

Hi, I am new to filemaker and I have a question. Here is the scenario....I have a field Subscription Type and Renewal. The Subscription type value list are usually Full Year 2011, 3Months Subscription 2011, Full Year 2012, 3 months Subscription 2012 and so on. If the customer ordered last Full Year 2011, and trying the order for 2012.Once I selected Full Year 2012, the Renewal field should automatically put a YES otherwise NO. How can I get the last value from the previous transaction of the customer so that the Renewal field will automatically populate the answer? Note: The subscription is in Text format. Thank you so much. Allan

Hi, I'm a filemaker user who have been using for almost 5 years. Now, I'm trying to make data tables and layouts separate into different files. For example: i) "Table A (for Layout Customers)" belong to "File 1" "Table B (for Layout Products)" belong to "File 1" "Table A" and "Table B" are related by the field called "serial_id" ii) "Table A" is placed in "File 2" as Table Occurence Group. "Layout Customers" belongs to "File 2", and shows "Table A" iii) "Table B" is placed in "File 3" as Table Occurence Group. "Layout Products" belongs to "File 3", and shows "Table B" Question: When the found set of "Table A" is …

I have a database that is a list of hardware. Computers, printers, iPads etc. About 75 items. 6 or 7 of them are used as loaners. I'm creating a layout to keep track of the loaners. I've added a "Loaner Eligible" checkbox to the hardware layout. In my Loaner layout I'd like to have a drop down list that contains only the hardware that is "Loaner Eligible" I've managed to populate the drop down list with all hardware items but I can't figure out how to show just the "Loaner Eligible" items.. Thanks!

Table 1: List of Parts in Inventory _pKey_PartNumber ( Unique Value) Table 2: List of Part Bin Locations in Inventory Stacks _pKey_BinLocID (Unique Value) Table 3: Union : Stores Transactions about which Bins have been select to ties to specific parts _fKey_PartNumber _fKey_BinLocID _pKey_TransActionID Table 1 [one to many transactions] Table 3 Table 2 [One BinLoc can have many transactions ] Table 3 Scenario: Looking on layout Parts Inventory (Table 1). I want two portals: Portal A will show all the Bin Locations the part has selected may be stored at. Portal B will show all the other Bins that have not been assigned to this par…

I am trying to make Go to related record work with two existing windows, Client List and Client Detail. The GTRR works without issue but the script prompt only offers a new window option to enable one to go from one window to another. In this case, I want to select an existing window (Client Detail) to go to via GTRR as I don't want to generate a ton of new windows to do something as simple going from one view of the same table to another. Is there any way to do this? TIA Bob Moran

I'm designing a system that includes the following. Computer --< MAC Address --< IP address --< DNS name Note: This is only for tracking fixed IP addresses, not DHCP. A network ops center reserves/assigns fixed IP addresses. Usually a DNS name is paired with an IP address, but sometimes IP addresses are handed out without a DNS name (I don't see that as a problem at all here, I'm just mentioning it). Computer records must be unique in the set of records. MAC addresses must be unique in the set of records. The design seems pretty simple at first, but there is an issue I'm looking for feedback on. IP addresses (and usually with a…

Hello, I am developing a solution that tracks printed jobs. Each job may have one or more products of a varying total. Each product can have multiple call-offs. I need to track each call-off in relation to the product and job and track each remaining balance. At present I control this through the following relationships. Jobs (job_id_pk) to Products (job_id_fk) Products (prd_id_pk) to Call Offs (prd_id_fk) Is their a better way? All help/ideas will be appreciated. Regards

Hi all, I am brand new to Filemaker, and this is my first post on the forum. I am trying to wrap my head around what is most likely a simple concept. In brief, I run a subcontracting firm and I am working on a simple Work Order solution. The tables in question follow: Clients Projects DWO (aka Daily Work Orders) Each client can have 1 or Many projects Each Project can have 1 or Many DWO's I have created a Foreign Key in the Projects table (fk_Client_ID) I have also created a Foreign Key in the DWO Table (fk_Project_ID) I have drawn the corresponding relationships. Now I am trying to create a Layout on which the user can create a new…

Sorry if this is posted in the wrong section, I did carry out a search but couldn't quite get the answer I was looking for. What I would like to know or to get tips on, is on planning a database. The part before the computer is even switched on. As any tutorials I've seen deal with the programming and layout, not the overall concept. Please point me in the right direction, I do know that I need a relationship database and not flat. Even though I have plenty of time I would prefer working out what I need and how I should go about it before I start typing. Thank you all in advance. Yours, Charles

Just when I think I am getting it, I try something new and feel like an amateur all over again. I was doing fine with One Customer to Many Homes One Home to Many Jobs Now I want to get Many Employees to Many Jobs so I have Many Employees to One Bridge Many Jobs to One Bridge (the same table) Take a look at my relationship diagram to make sure I did it right. But, now, How do I enter employees for a job? I have a table of jobs and added a field for name of employee, and tried various iterations of value lists based on existing employees, but I can't actually enter any value. Please start with me from the basics. What layo…

Ok, I am probably making this harder than it needs to be, but here goes.. I need to set up a relationship where a user (dealer) can pick a car with a unique serial number and assign one of 4 colors to it. There can only be one car with that serial number of each color. (So if there are 2 cars there are 8 possible combinations. Car1 in Blue, red, green, black and car2 in blue, red green, and black) If that unique car has no colors left, it should not be available to pick from the start. Once selecting a car, only those colors not used would appear in the drop down. Sounds simple..right?

I had previously received some great assistance at FM Forums about creating a table for a summer camp that our organization is administering. We would like to add one more feature to this database which would allow for the camp staff to provide daily reports for each camper through three different "evaluating" systems: 1. A ticket system which penalizes each camper for speaking their native language at camp instead of English. Student starts off each day with 5 tickets and must surrender a ticket if caught speaking their native language. Their overall loss affect their whole team. 2. A positive and negative point system-which is used in the classroom to reward goo…

I was wondering, in terms of performance, which will be best: Say i have a calculation in a field from TableA that needs a value from a field in TableB (tables are related). Would it be best to have a lookup field in TableA that looksup the value from TableB then use it in the calc or would it be best to use the relationship between the two tables to directly reference the value in the calculation? I might be completely wrong (still a FM amateur) but my logic argues that a lookup does the lookup once and thats it in terms of processing, where as using the relationship has to each time process the relationship to get the value and thus creates more overhead etc. But what e…

Hi there I have a working database that I need to add to but can't figure out how to do it. I am a composer and as such I get royalties quarterly from different publishers. My Database lists each track along with the total earned per quarter and the name of the publisher but what I WANT to do is to have a page that shows me the total for that quarter for each particular publisher side by side. All I am able to do is to get it to show me the quarterly total for one track and one publisher and it's driving me mad. Any thoughts or ideas? Many thanks! Chris Blackwell

I have quite an extensive fmp database file that is about 250mb in size, but contains about 40 different tables of data. Most of which have relationship between tables, some complex others simple. I have a dedicated PC (Win XP) that stores the file/s on it on our network, and i have the PC as a start up to open the fmp file in the case of a reboot to ensure the file is always accessible remotely to each client PC owner etc. Each client PC has its own fmp starter file to open remotely the file to gain access. I have found that recently when opening the file there is a slight lag, and also a slight lag in time when viewing/moving a relationship within the m…

Hi, I have an issue with some relationship, not sure how to create the below: Consider 2 tables: 1) services and 2) appointment. In the table SERVICES, I have ServiceName and RRP (recommended price). In the table APPOINTMENTS, for each record I want to be able to make 3 services. I have therefore the following fields: Service1 (drop down list) / Price1 (manually entered) / RRP1 Service2 (drop down list) / Price2 (manually entered) / RRP2 Service3 (drop down list) / Price3 (manually entered) / RRP3 My issue is how to link ServiceName (from table Services) to the 3 Services (in the table Appointments) to have the corresponding RRP displayed ne…

I am attempting to connect an event Database to a contacts database. I am familiar with filemaker but newer to creating databases. Here is my issue... I need to have multiple contacts for one record. I would not like to use portals if possible. Currently I have tried Event::EventId = Event_contacts::Event / Event_contacts::Contacts = Contact::ContactId I then would like to select the contact from a pop-up menu if existing or create a new record if not existing within the events database. I need to have multiple contacts for one event and multiple events for one contact. Any help would be great! I am open to suggestions. Thank you!

First and foremost, I just want to say what an awesome and helpful community you have going here and want to apologize if this is the wrong section to go about posting my questions, but seeing as how I am relatively new to the program... I'm not sure what other subforum it would be belong in. I was recently hired as a Junior Specialist involved with research. Recently, my boss has asked me to learn FileMaker 11 Advanced to create a database (with a questionnaire implemented into it) for all our patients information. We are trying to move from away from "paper" records and questionnaires and towards digital. So here I am trying to create what she is looking for in a w…

Background: I am presently working with a database with 5 tables: Donations, People, Families, FamilyMembers (a join table between People and Families), and Companies. Donations can come from People, Families, or Companies (henceforth collectively referred to as "donors"). The same folks listed in People can be associated with specific Families (hence the FamilyMembers join), and for each donation by a Family, a specific party must be selected as responsible. For example, if the Smith family is making a donation, either Jane Smith or John Smith must be selected as the primary contact for that transaction. Ideally, at some point in the near future, a Person record wil…

Hello all! I am looking for an opinion from anyone who has one. I use FileMaker to track our Fixed Asset Inventory. When I first took over the task, the equipment we had disposed of lived with all of the active equipment. Someone higher above me made the decision that the disposed files should be in a totally separate file (this person has limited to no knowledge of FileMaker). Because of accounting needs we need to keep track of all the equipment regardless of active rotation or disposal. I think it would be better to have all of this information together but in separate tables. In your opinion which would be the better option regarding functionality? Please let me know …

I am having in a little difficulty understanding how to us a self-join. I was told that this would be the solution but having a problem grasping the concept or just seeing how it would work. What I have is a inventory database about my inventory of computers monitor routers switches etc. The problem that I am having is how to handle a warranty replacement. I have tag numbers for the equipment that is linked to a PO. Example: Cisco Router dies and SmartNet replaces the router and the serial number changes, but the tag number would stay the same. I would like to keep the original serial number and the date installed in case I was asked what happened to the equipment an…

Hello, I am a bit of a newbie so any help on this would be greatly appreciated as I am in a bit of a mess. Basically I have 2 tables, a clients table and a jobs table. The clients table has a portal to hold multiple contacts. What I am trying to do on the jobs table is look up a variety of data from the clients table via 2 drop down lists. Eg. when you start a new job, you have a drop down list for clients name which once you select the client name it looks up the related address etc then once the client is chosen you have another drop down list for contact name which only shows the relevant contacts for that client and again when selected it looks up that clients co…

I am sorry for having to ask, but I did try to help myself first, but I am not sure I really know what to terms to use to search. Background: I have a DB that I am developing that tracks requirements for individuals. Each individual is assigned a auto serial key in a parent table. Each individual has satisfy many training requirements and has to file personnel information; there are many training and personnel categories. Each major category has an individual table and an auto serial key assigned to it and I think I have them attached to the primary key in the parent table. So the individuals table is the parent and the remaining tables are the children. Th…

I have question and it could be pretty easy but as a novice I am struggling with putting it all together. Scenario: I have 3 companies that each have a listing of positions. example: Director, manager, technician, trainer etc.. and the positions are identical for the 3 companies. So I have made a table for each company and the fields are the titles of the positions with info like: entry, days, amount etc.... They are also only able to access the table that is for their company. A person from each company will enter in the data for each position and update it once a year if anything changes. I want another table to have the director position listed on it …

I am starting a new flat Filemaker data base which is based on a very old multiple relationship data base. I'm using the info found in one data base which has all the info from the other data bases. My match field, the record ID, only gets me to the 1st found matching record. Is there a script or calculation that I could create to get the info found in all related records? I'm sure one of you magicians out there has the answer!

So I have been on a crash course with FM and have finally hit a bump in the road I'm unable to solve. Scenario: I have two tables with an ID field and relationships are established. What I would like is that when the fax button is clicked which opens another layout to the fax table i would like the ID to be carried over from the record that was being viewed in the other layout and table. What I currently have been doing is running a script that is in a drop down list to populate the ID and person’s name, meaning I have to select from the drop down and then the info is carried over. I'm looking to automate this with the above statement. Attached is a link to …

I have over a hundred fields of data. I put together a custom function/calculation that will convert all values with 5 or more characters into scientific notation. Right now, I'm just going one by one, converting all the fields into calculations, and plugging in this function and replacing the "number" parameter with the name of the field. Is there a quicker way to accomplish this? Obviously if I compiled all the data into one field i woudl only have to run the function once.... I have it separated into diff fields according to categories, but I suppose if I just keep a record/key of the order of the records and the corresponding serial ID# for each category, I …

I have a table of individuals, and each individual gets a catalog number (Individuals:CatalogID). Each individual has multiple images (parent-child relationship), and I flag the best photo for that individual (Images:PrimaryPhoto = "yes"). When a new individual is added to the table, I match its primary photo to the rest of the table to look for matches. If a match is found, it gets the same CatalogID and the best primary photo gets assigned the catalog photo (Images:CatalogPhoto = "yes"). I would like to create a grandfather table that shows only each unique catalog number and only the catalog photo. I know how to show just the primary photo in an individuals table (par…

I have a parent-child relationship. The parent table is Individuals with a primary key of IndivdiualID (auto serial). The child table is IndividualSightings, related to its parent through the primary key IndividualID. Each individual has a full name and its easier for me to work the database using the full names, so the two tables are also related by non-primary key fields, FullName. When I sight an individual, and I know who they are, I simply type their name and through the FullName relationship, automatically assign the proper primary key field. If its a name not in the database (new individual), then I allow the creation of new records via that relationship so that …

Hi Two questions regarding relationships: 1. After doing a many-to-many relationship between table (MOVIE) and table (ACTOR) through the in-between table (LINE_MOVIE_ACTOR) the field in the portal in table (MOVIE) only shows me 1 actor name choice, why? 2. How do I make a relationship between a new table (DISTRIBUTOR) that both table (MOVIE) and table (ACTOR) should be able to show? Do I need two in-between tables? Thanks Fernando actor.fp7.zip

Hello, My first post on this forum. Is it possible to access (check) the contents of a field in a related table using a script without leaving a layout? Have a three table relationship. Members, loans and items. The loan table joins the members and items table (many to many). I wish to check the loan status of an item before committing to an action from the loans layout. I would prefer not to this using a find method as it is cumbersome and time consuming. I have seen some references to [Relationship::tbl] but I am unsure what this feature offers and how to use it if it relates. many thanks

Apologies; I believe I've answered my own question (the way I envision this working, I don't have to worry). In any case, since I can't delete this post, I'll leave it here. I have line items that will have a 1 : 0, 1, or many relationship between an order line item table and an asset table (the latter contains much more information about the item, after acquisition), i.e., if I order a hammer, there will be a field in the line item table where I can choose (via a pulldown) hardware, or software, etc., as a line item type. The line item then have a 1 : 1 or many relationship to the record in the appropriate 'type' table. If the line item table has a 1 : 0, 1, or…

I perform surveys by taking photographs of all the persons I encounter but each person can only be encountered once per survey. The survey table has a nested table with the individuals encountered, but each individual has a nested table listing all the photographs taken during that encounter. I want to flag one photograph from each encounter (the best for that individual). Then, from the best photo of each encounter, I want to flag the best overall photo for that individual. I've attached a sample of the database I'm using and how I am trying to make it work. I am trying to use the "set field" to flag the best image filename and add it to the parent table, then using the …

I have two tables of data. One is a list of customers, and the other is a list of stores. I have the Latitude and Longitude of everything and know how to calculate the distance between any two items. I'm looking to create a portal in the customer table that shows the 3 closest stores. I think I can accomplish this by creating a sorted portal, but I am unsure on how to sort a portal in this way. Any ideas to get me going in the right direction would be greatly appreciated. Thanks in advance.

I'm trying to find three things. Some really good filemaker pro example databases (ideally with populated layouts so I can see they're laid out) that display implementations of some of the different relational diagram techniques discussed in the (comprehensive and hilarious) article Approaches to Graph Modeling A book or website that has lots comprehensive example relational graphs specifically designed in the filemaker relational graph (like a http://www.databasea...org/data_models but for filemaker specifically) more amazing websites like http://www.databasea...org/data_models if anyone has any suggestions, they'd be much appreciated, thanks!

I've recently started working on a new companies existing system. In my personal opinion the system is a complete mess and the architecture is BAD, but management begs to differ. Im from a SQL background and I'm thus not sure if things work differently in Filemaker so my question: On average, what would you say is a reasonable amount of tables to have and more importantly, how many fields on average per table? How many fields is too much? Currently they have 50+ tables, some of them with an excess of 1000 fields! Surely this cannot possibly be? What role does normalization play in Filemaker? Thanks a million!

I have a fairly large database that I am building for an insurance company. I am trying to summarize a group commission statements that we get from different insurance carriers. The problem I am running into is I have to keep all the statements in separate tables in the DB. As you can imagine I need to total up each statement and total them out based off insurance agents. I basically am looking to summarize all carrier commissions into one table, but I am lost on how to do this. Here is an example of my DB setup. Agent Table - Agent ID Number - Agent Name Insurance Company 1 -Agent ID number - Commission - Premium - date Insu…

I have three tables, Customers, who can have multiple Homes, that can have multiple Jobs. For a new customer, I would enter all their Customer info, their Home info, and the info for the specific Job we are doing. But, after that, if they call me back, I want to enter a new job, either for the same Home or possibly for a new Home for the same Customer. Should I be using a layout for Customer? or for Job? and when I enter the new Job info, how can I make sure to relate that record to the right Customer and Home? If I type it in, it will make a new duplicate record for those first two tables. Is there a way to search and link to the existing records? Again, I woul…

Hi everyone: I was able to integrate the seedcode calendar into my solution and I must say I am very pleased. However My solution is a multi-user environment and the login users are based on a users table. When a user logs in, a series of scripts control the records that the user can see.. which are only the ones they are the owner of based on a User field. My problem is that the calendar displays off the events file and I must admit I am not that strong with relationships but it seems to me I need a way to make the events table only display related records, but even if I constrain it to a found set of the user's records, the calendar still displays all events …

Hello, I have a customer/product database with a layout setup in form view that has customer’s information including address & image. I would like to have some type of feature set up when a new record is selected, and I enter the customer’s first and last name, their address & image will auto fill in the appropriate fields, if this information existed prior. Could anyone tell me how to accomplish this task with detailed instructions?

I am having some difficulties with relationships in the FM Pro 11. Currently, I have imported excel from online registration form into Table A. (with about 180 records in it). I need to create the mailing list and I do not want to add fields into the big table, but to create separate table B, where i can use related records from Table A and add 'subscriberID' and 'active' fields in the Table B. I have set up the relationship between both tables of the fields 'firstname', 'surname' and 'emailaddress' but the new table B doesnt show any records from Table A. I suspect that scripting is needed to ensure the records is shown in the new table without duplic…

hello, First of all thanks for reading this post. I'm a teacher and I've always needed a solution which I could use to manage all the info related to teaching test, attendance etc. so I bought Filemaker pro and started to learn how to use it and began the task of buildng my own solution, It all went well when until the design of my solution became too complicated so I need your help. My solution has some fundamental tables: School Year ---=Courses ----= Students Courses is also related to Classes (english, chemestry etc) and each class is related to term (each term needs a grade) up to the grades part it all went well but here is we where the…

I'm in the beginning stages of adding Calendar like features to a database I'm currently managing, but I'm having a heck of a time wrapping my head around the basic structure of such a thing. The basic record will have fields that include entered date, assigned date (to outside contractor), receive date (completed order from contractor), and completed date (to our client). We have established turn times of a week for each order and I thus would like to setup a calendar that lets me know which orders I should be expecting to be completed on a given day (Entered date + 7). I'd also like to have the calendar show me how many orders I've received (Entered Date) per…

I have two tables (Table 1 and Table 2) that need to be related . The match field in Table 1 is a concatenated calculation which uses a field from a third table (Table 3) which is linked to Table 1 with a cartesian relationship. Table3 xxxxxxx Table1 ===== Table2. Table 3 is a single record field so the field mimics a global, but one which is common to all users. The relationship T1 to T2 fails as it cant unstore the T3 field. Is there another way to do this?

I have recently met File Maker and I'm using File Maker 11 Pro Advanced. I have two problems and I will be very glad if you help me solving them. My first question: I have a related table and data automatically comes to this related table. I want this data to come automatically (from related table) and also I want to modify/add this field (manually). How can I do that? My second question: I have two diffirent layouts. I want to connect two layout in one layout. (for example one layout consist of 1000 records and the other layout consist of 500 records. The combined layout will consist of 1500 records) How will I do that? With replace field content /…

I have a customer that I build an ad for. Each ad has 6-10 pages and has 10-20 products on each page. This customer has many stores (identified by a unique store number) and each store could run a special price on any of those products. Typically it's a major project like Coke or Pepsi that changes price, (or product), but it could be any product. I need to build a database that will help me identify through data how many versions of each page I need to make and what the differences are. Here's an example: The beverage lady sends a spreadsheet that says this list of stores gets PEPSI on page 1 at $2.99/12PK, and this other list of stores get's COKE on pa…

Im having a total brain cramp. I know how to connect a portal to show direct one to one relations... however, my situation is this: I have a table called LOCATIONS. In it is locID, locName, locDescrip I have several other tables - a Food table, a Water table, and a Utensils table. Each of those three tables use the Locations table in a drop down to set where each record item is located. On the layout used to define locations, Id like a list of ALL items - water, food, utensils - that use that specific location record. Not sure how to build a portal that looks at all three tables to see if it has the loc used and return it to the location la…

hi guys, i'm using fm11pro and i have my inventory database, I would love to have an check mark for the sold items and dissapear from the available items. i will include a picture of my base, can somebody tell me how i have to do to make item dessapear if i mark SOLD ? thanks in advance.

Hi Friends! Well, I am developing a database, and I'm showing related records in a portal, but when I configure the portal, it have to provide the number of related records. Can you configure the portal so that it could hold a dynamic number of related records? And, of course, without the "Show vertical scroll Bar" option. Thank you very much!!!

I hope this is the correct category, my apologizes if not! I have created a popup window to use as a data entry dialog. I would like to be able to create a new record in two different tables to ensure that the primary ID of both tables and a field called "name" are the same in both tables. Can this be done? More information if needed... The two tables contain settings for printer profiles. We have different paper types that can go on one of two different printers, the paper needs to have a profile but that profile may be a little different depending on which machine it goes on. I would like to keep table called "paperTypes" which has a relationship to…

Hi everyone- I have been combing the boards and learning a TON, but I haven't yet figured out how to do something that is likely very simple. I have a simple database - only 1 table. I run a small research lab and am maintaining records about participants. I basically want to be able to see related records of the siblings of the participant (if applicable). I used a template to guide me in creating the database and the template includes a tab that includes a portal of "related records." Well, the portal *almost* works and it shows me records that share the same last name (helpful), but then it shows me records that match based on the mother's first name (not so …

Does anyone know what is the best way to do that? Thanks, Michael

Hello I have two related tables in the same file. In table Product I have a field A and in table Feature I have a field A1. The Field A contains a code of the product and thanks to the relation it gets its features from the table Feature. There could be from hundred to thousands records in the table Product which does have the same value in the field A. But in the Table Feature there is only one record with the value in A1, and it is restricted to have a unique value by definition. My question is, is it possible to create a new record in the Table Feature, when in the table Product is imported a record with new value in field A which does not exist …

I would think this should be easy but I just don't have the File Maker experience to make it work. I'm building a relational database. The parent Table is Clients, Related child Table is medications. I want to input each Client's medications from the Clients Layout. I have no problem doing this from a portal, unfortunately there are too many long fields to make a portal practical without a log of horizontal scrolling. I'd like to be able to lay it out in standard form format (i.e. field over field) but I can't figure out how to script a button add or brows a child record. Any suggestions? Thanks Bruce

Hello, I've been using FMP since 1999, although in a very limited way (single table databases only). I've enjoyed just jumping in and trying to make things work, and have been fine with the fact that my solutions are not elegant, but they work. I'm up against it now, though, as I've been tasked with the following situation: A business has about 400 products � Many can come in a variety of colors. � some items can be sold as a unit or sold by the foot or both � each product can be manufactured by up to 4 different suppliers � they pay each supplier a different amount, and therefore 1 part number can cost them 4 different amounts �

Hi All - Hope you don't mind a newbie landing here. Looking for a pointer in the right direction for the best place to learn about tables and relationships in filemaker so I can press on with a couple of projects. The internet is littered with FM learning areas - can you recommend any in particular? I'm very familiar with IT and logic but not so much databases and in particular, filemaker. Aiming to build a couple of databases in my spare time so downloaded a trial of FM Pro 11. The first database is for my football team; I want to build a table to record information on football matches for my team throughout the season: -basic info on match: ie date, tim…

Hi to all, I have 2 tables. the relation between these two is one to many. how can I duplicate a record with it's relationships? Thanks

Hi to everyone. I am kind of newbie in FM so a thank you all in advance for your help.. I am going to describe my situation the best way possible. I work for a Air Ambulance charting company. When a client ask for a quote we do very simple calculations that right now we are using excel but we need to use something better like FM to store quotes and be able to print them or mail them. In excel I have a datasheet with distances between cities and I use Vlookup to retrieve that distance. Lets put a simple example.. Lets say the distance between Laredo, TX and New Orleans is 600 miles.. A simple trip would be Laredo, tx -- New Orleans (600 miles) and then Ne…

Hi, I'm creating my first database solution, using Filemaker. I have set up a quote/estimate system, where the products are ordered using the PRODUCTS table to feed product info to the quote line items. That's all fine and preparing the quote is all done. However here is the problem. The nature of the business is that one product may be entered multiple times on one quote. It's an audio-visual company, and they might have one TV for the reception of a project they're working on, and another listed for in the boardroom, for example. So instad of having it listed once, with a quantity of 2...it is listed twice, with a quantity of 1. Now here is where i'm…

Hi, I am modifying an external database with the help of Filemaker (11 Advanced), and thus often need to have a related one-to-one table so that I can make new fields, especially calculation fields. I do not have a problem creating a new record on a layout showing also fields from the related table. I do, however, have a problem keeping an updated set of the related FM-tables when they only exist of calculations (+ the foreign key/ID). The external database is also edited from other users and machines, so that new records do not necessarily come from within a FM-solution. Until now, I have updated these tables by exporting data from an external table, a…

I'm an MS Access developer that is attempting to switch to FileMaker for cross-platform support. I've been reading and viewing as much material as possible, but have come up to a stumbling block that I hope everyone here will help me with. Specifically, I've been asked to review an existing database in order to update the reports to include additional breakdowns of information. Originally, there is a table that contains Client information and another table that contains Residency information. Client Example: ID: 1, First Name: john, Last Name: smith, Residency: 1 Residency Example: ID: 1, Type: over 3 to 5 years, Abbrev: 4-5, FixedValue: 1 …

Hello, I have the following tables: Parent Child Child Item GrandChild GrandChild Item The table relationships(Parent -> Child; One -> Many)are the following: Parent -> Child -> GrandChild Child -> Child Item GrandChild -> GrandChild Item In the "real" world each table represents a type of document. For each table (type of document) there are different tasks (document appraisal; document users; document points of use; document storage; etc) carried out by people from the organization. So, for a record of any of these tables the same person can be doing different tasks as: docu…

Hey everyone. I am trying to create a relationship between a table and another "audit" table. The first table has fields containing various chemicals, amounts and procedures related to a certain chemical "recipe". The purpose of the second "audit" table is to ensure that the chemicals, their amounts and procedures found in the first table are followed correctly. This audit table will then have to be given a unique lot # and filed independently of original table. Any instruction in the right direction is appreciated!

Hey Everyone, i need to define a field that is unique to each new record that is created. Ideally i would like to incorporate the first three letters of record title along along with the date the record was created. This field is meant to act as a "lot number" which allows each record to be unique and traceable. Does anyone know how i can do this?

Hi, I have a billing system running on filemaker. Each bill has a serial number generated on creation. Now, I need to add the possibility of making estimates. I would like to use the same layout. I'm a little confused about the best way to do this ; I don't want to duplicate the layout, as I don't wan't to maintain and modify 2 layouts in future. Ideally, I would like to use the same table, but then estimates would take up serial numbers which is not possible. Estimates need their own, independant set of serial numbers. So should I use 2 tables, 'bills' and 'estimates' linked through a third table ? In that case, how would I integrate the creation …

OK... I have an employee table that stores first and last names, hire date, and termination date. It's good for keeping a historical record of all employees, past and present. What I want to be able to do is filter by whether the employee is current. More often than not, a user of this database will want to only view the current employees. I thought I could do this using a self-join, so I set up two additional fields: CurrentlyEmployed(Calculation) = if(isempty(TerminationDate); "Yes"; "No") CurrentlyEmployedMatch(Text; Global) = "Yes" Now I created a second TO of Employee and joined using the criterion CurrentlyEmployedMatch = CurrentlyEmployed. (I may also b…

Hi Guys, I have been racking my brain and searching for a solution, basically I need to import images into filemaker (reference only) and link them to data also imported via a .csv file. This I have accomplished however, initially I made the relationship through the image name which worked fine until there were multiple images with the same name. Then I thought if I created a field in the .csv with the image path on the server I could do it that way and use 'GetAsText (picture)' to match it, the problem I found is that it still only grabs the file name at the end. GetAsText (picture) returns with "M:/2011/Pre-Production/Jones/Raws/STAFF-A10049.JPG" …

Hi, Thanks for all your help so far. Everything is working but the nurses have asked whether they can have a separate layout that shows all the dead residents! I showed them the find function but they said they would like a layout that included the reason they left or deceased. should I create a new table, create a relationship between the two and use a lookup? any help would be gratefully received, Thanks

I have a portal that shows records based on a global field - all works well... except when you first open filemaker. I need to create a new record before the global field is populated. Once the new record is created all the related records show as expected. So none of the related records show in the portal until after I have created a new record. Then all the expected records show. Any suggestions?

Hi, i'm really sorry at asking this but I'm going mad trying to find a solution. I have a list of residents at an old peoples home. They are in one table and their details. I want to click onto a button and go to another table that has their account. I can do this!! But their account changes every month. I want to be able to have a list of all their past spending and obviously create new ones each month. Everytime I do this I can create new months but then when I look back its the same for each resident! Any help would be gratefully received, thanks, Jon

I've been fiddling with FileMaker since version 5 but one thing that has always bugged me is why lookup functions based on relationships work for months, then stop working. Most of the time I haven't altered anything. I spend hours fiddling around and end up having to half rebuild the file. Sometimes if I reconnect a lookup it doesn't always refresh the data on every change. I can test four matches to Name > ID, three will refresh and one won't, then when I repeat half won't refresh. Is there a particular way to troubleshoot lookups based on relationships to ensure everything is nulled or refreshed?

okay, this might be long and drawn out, but it's hard to explain in writing... I think my problem can be solved with relationships, so here goes... I have a simple database that keeps track of (or will keep track of!) the different softwares (50 or so) installed on various computers in my group (19 computers). I have a few different tables, Computer_Table (computer name, operating system, computer location, owner, etc...), Software_Table (product name, version, license info, etc...), and Master_Table (Computer name, software installed, etc...) which I hope will be able to combine the two others. Software_Table: [Company] | [Product] | [Version] …

I am trying to set up a database for my family's hard cider business to track product costs. Cider is an assembled product. A base cider contains items like juice and yeast. A final CiderA in the bottle might have base cider, sugar, bottle, cork, label etc. Some items are used in multiple products. Some products are used in other products (like a gift basket) I'm trying to figure out how to set up a schema to handle assembly of items that might refer to each other. Jonathan Stark had a good article up on this topic, but it seems to have disappeared in the last couple months (before I could digest it). An example CiderA contains BaseCider1 sugar …

Hey there. I'm stuck, hopefully someone can help me out. Three Tables are involved: Parent, 1 and 2. From data in Table 2, there's three sequential Fields with Value Lists that work perfectly in Table 1. I've created a NumberID Field in Parent Table (Auto-enter serial, Indexing All) and in Table 1 (Indexing All) then joined them via Relationship, checked the boxes that allow creation and deletion of records in Table 1. A Layout in the Parent Table has a Portal with the three Fields from Table 1, with the goal to create records in Table 1. This should work, and I can see the available options in the first pop-up Value List within the Portal, but when I sel…

This is Filemaker 11 on MacOS X.6 My client has a table of items that we are using as a lookup table to populate some value lists The fields in this table are: item_key - primary key for each item, a sequential number item_category - the category of the item, a text field item - the item, a text field How the lookup works is they first choose a category, then they choose the items within that category. The relationships to make that happen look like this: and I have two value lists set up using those two instances of the tables to fill in the popup menus This all works great... Except... Now my client wants to be able to disable ent…

First, let me give you the premise for why I'm trying to do what I'm trying to do, because there very well may be a completely better way to do this. I have a database (call it the main database) that is connected to another external filemaker database. The main DB will be used to plan travel itineraries for my work (among other things) which involves visiting high schools, attending college fairs, and holding special info sessions. It has a table called Trips that has the general trip information (Trip Name, start and end dates, traveler, etc.). It is related to a High School Visits table by the Trip Name field, and contains detailed info about each individual…

I'm building a somewhat complex inventory tracking database. The database tracks customers, vendors, orders, purchase orders and inventory. Inventory is built or depleted based on orders (deplete inventory) and purchase orders (increase inventory). I have built a successful model using the following tables (--||> signifies the direction of the one to many relationship in the tables): Customer --||> Order--||> LineItems<\\--Inventory--||>PurchaseOrderLineItems<||--PurchaseOrder<||--Vendors In the system I've built, PurchaseOrderLineItems properly increase inventory and LineItems properly decrease. Everything works on a one to one basis. The prod…

I was hired by an international fellowship grant program to 1) consolidate all of its grants/grantee records 2) locate current contact information for each grantees/alumni member, and 3)create and print an alumni directory listing alumni grant profile and current contact information. At the time, I had a very tight deadline to do all this and a limited knowledge of FMP, I had last worked with FMP when it was at version 7 and mostly entered data. Although, I managed to complete my duties in the allotted time, I am now interested in re-building this current database into something that is more sustainable and efficient and can, 1) be accessible and easy to use by othe…

I need some help with a system I am working on for a construction company. They have lots of workers and they have to post up daily logs of what they will be doing that day. As things progressed, I made a "response" area where other employees could add feedback on that employees log. Well, things are moving a long and there are so many posts a day that they can't keep up with additional posts to past logs. I am wondering if I should just make 3 fields, DateNotified, logReplies and a flag boolean that flags a 1 when logReplies and the Sum() of logReplies are not equal. Then make a script that runs on the server and simply finds that flag. If a record is found that has more…

First time using FM Pro. I have an equipment, package and package link table. Equipment is list of equipment, package is list of equipment packages i have and package link table allows one package record to assocaite with multiple equip. records. I want to create a form that lists all the equipment in a single package, however I can only get a form to show the package and then each piece of equipment one at a time. Using a portal but can't figure out how to simply have the form display ALL of the equipment in a list on one form and the use the package name field to select which package to display. Tried to attach fm file, but wouldn't let me. I have included…

I'm trying to create a portal on my "incoming mail" layout that includes all "active" jobs related to the current email of "[email protected]" It's trickier than it sounds at first glance... Here are the 3 tables I'm working with. -Client Emails = [email protected] (each email may be associated with multiple clients; example: John Doe & Jane Doe) -Clients = John Doe (each client may have multiple emails; Example: [email protected] & [email protected]) -Jobs = "active" or "inactive" (each Job relates to a single client)

I'd like to thank any and all who take the time to read about my issue and offer to help. I posted another thread on this board about a specific issue, but now I'm backtracking and rethinking my whole design. I am setting up a database to analyze tests. I need to be able to analyze a test question by question. For example, how many students got question #6 correct on test MA7. I also need to be able to calculate a particular student's test score and summarize scores based on team and grade level. I have come up with a design that works; basically, every question on every test for every student is a separate record. I am able to get the information I need, however, w…

Hi Everyone, I have to update an old, rather large database with multiple relationships based on Names. I want to do it right and use Unique serials for the relationships. Can someone recommend some best practices for converting such relationships. Thank you.

Thanks in advance for any help you can offer. Is there an issue with having a relationship based on a field containing text with decimals, such as 'MA.7.G.3.1'. For some reason, the relationship won't work and I'm trying to troubleshoot why.

Good morning Team, This is my first post, so I hope to explain my problem quite well. Right now im designing a db to keep tracking on a corrective and preventive maintenance tasks, so i already have the tables and i'm working on the reports and charts I expected to fm gives me, but I wanna say to fm, that creates me a new record according to a certain logical criteria, let say: I wanna keep track on all the injection molds my toolshop has and evaluate if they need preventive or corrective task,so I have a field to enter the number of shots that every mold gives me after a running, let say 13000 shots, after I input the number of shots, I wanna fm tells me that…

Hello, I'm new to FileMaker and have been modifying the Contact Management starter solution to fit my company's needs. We are trying to keep track of our contacts, and will be assigning "codes" or labels to each contact designating certain characteristics (for example - media, art, entertainment, etc.). Not sure if this is the best way to go about doing this, but I've created a "Code" tables (with Code_Type as the primary key) and have linked it to the Contact Management table (k_ID_Contact as the primary key) with a join table, "Code Assignments." I've inserted a portal on the Contact Management Record Detail layout and used the Code_Type field as a value li…