Relationships

Hi All, I feel like a bit of a cheat asking something this basic, but my neurons are just refusing to fire this afternoon, so i thought I'd ask for some help to get me started... I have a property database with a PROPERTY table, a ROOMS table and an IMAGES table. Each property can have any number of rooms and each room can have any number of images attached to it. Here's the question... How do i organize this to show the property details, but also allow the user to add rooms and then to add images to those rooms. I've tried a portal in the property layout to show the rooms per property which works fine, but then i can't figure out how to add images for…

It has occured to me sometimes and I thought I should discuss to get your opinion: Let's suppose one faces the following: 1) One main table with several related at the periphery, 2) many fields per secondary table (3050) 3) sometimes more than one record from each peripheral table related to the main one, 4) difficulties for reporting (sorting, space-saving) 5) flexibility for data export. Do you consider the structuring of data in fiewer tables in the manner of data tagging a viable solution? For example, I guess that for data tagging in a child table, one would need the same amount of different "stationary fields" in the parent main table to produc…

If anyone could help explain or show me an example of how to build a conditional value list where i can click an item from one value list drop down box which then displays information in another field it would be really appreciated. Example: I have option of choosing Lunch1, Lunch2, Lunch3, Lunch 4 from a drop box list. After clicking Lunch1, in a field next to it i want it to say HAMBURGER. if i click Lunch2 - Chicken. if i click Lunch3 - Salad etc. Please help

So I have 1 Table that has many different fields. I'm looking for a way so that when I type into 1 field, they auto fill other fields when the data matches all records to the original field. So I have Field called: Job Number. Lets say when you enter in 319 as the job number, id like it so that the 'client' field and 'product' field match other records that have 319 as the job number. So that every time I make a new record, I can enter in 319 as the job number and the other fields are already complete with the correct information. Sorry for being confusing, I'm assuming there is an easy fix to this. Thanks!

I have created a DB to record employees and their managers as well as accounts that each employee has opened. I have the relationships in place that will allow for two portals on each employee's record to show the accounts that they started(and are linked to) as well as all the employees that they manage (have them listed as their manager). What I would like to be able to do is have a radio button option to select whether the portal shows all accounts that the employee opened or all accounts that they opened and all that the employees they manage opened. I would also like this option for the managers portal to see down-line hierarchy. I have attached a sample file. te…

Anyone know why the four arrange icons in my relationship graph are grayed out?

I have 3 tables "cars" with: carID, driverName "drivers" with driverID, carID, and misc. fields and "deliveries" with orderNumber, fk_carsID I'm trying to get the driver name to pull up when I choose the car ID on the deliveries layout, but it doesn't work. I can use a drop down with the cars ID and driver's name shown second on the deliveries layout, but when I choose one, the driver's name field will not auto-populate on the deliveries layout. I'm sure I'm overlooking something here. Thanks in advance for any help.

I am new to Filemaker and am currently trying to build a database that integrates my purchase orders with my inventory. I would like to be able to click on a drop down menu on my purchase order find a certain item (in this case a Belt Style# which i placed in the inventory) click it and have the belt description a (which was imported into the inventory) come up in my purchase order. For example - In my inventory i have: Belt# 3412 with the description: 14mm brown bond belt Now in my purchase order i have a dropdown field marked BELT STYLE # and a field directly under it marked BELT DESCRIPTION. How do i set up a relationship in which i can scroll down the drop…

I have 3 tables: Clients, Loans, Payments I have related these as follows: Clients to Loans and Loans to Payments Client Loans Payments --------- -------- ------- kp_client -> kf_client kp_loans - > kf_loans kp_payments I have just one occurrence called Clients and the clients are entered on one layout and the loans are entered on another layout. The problem is, when I enter a loan (there is a button on the client's layout that will take you to the Add Loan layout), the kf_client foreign key is not getting added to the table. I tried addi…

So I have database A and database B. I set up a basic relationship and portal and linked the two databases via a button. When traveling from records in database A to their corresponding records in database B, everything works fine. However, when trying to travel from records in database B to their corresponding records in database A, it does not take me to the proper record. Does anybody know how I can fix this?

I would like to be able to correlate records in a table in such a way that when I manually assign a relative item to another item both of them get updated and show each other as "relatives". Can anyone suggest a structure for that? For example, in an ITEMS table, I would have records, where ItemName would be: Screw Washer Bolt Hammer ScrewDriver Key How could I manually set relatives to each other and be able to see each item's (excluding itself) "relatives" in a portal?

I have a database that tracks cast and crew members on a TV show. It contains the following tables: Table 1 - cast and crew Table 2 - episode numbers Table 3 - every scene in every episode I've made a value list of all the cast members from Table 1 and turned that into a checkbox field on a Table 3 layout. I then use this to check off which actors appear in each scene. This works great. My production would now like a way to list all the cast and crew members that participated in each episode. For cast, this is based on their appearance in a scene (hence why I created the checkbox value list above). For crew, I need the ability to select which episodes t…

I will start with a novice apology. I am wanting to create an employee records DB and each employee will have a manager listed. That being said I would like to be able to list all employees that a employee (manager) has under them in a portal (see attached file). I have played with _pk and _fk to no avail. What am I doing wrong? I'm thinking this is easily achieved with a self-join but maybe I need an join table? test.fp7.zip

Hi everybody, I am building a relational database that will be used for linguistic research. I want to have a many-to-many relationship between 'Sentences' (which is a table full of the different sentences that have to be analyzed) and 'Glossers' (which is a table in which I want to place all the different people that are working on a sentence). Its many-to-many because the same person can analyze different sentences and a single sentence may have looked at by different people. So far I have used a join table, 'Join', which holds two foreign keys '_FK_Sentence' and '_FK_Glosser'. The primary keys are in respecively the Sentences and Glossers table. I think I ha…

I am fairly new to Filemaker, but not new to programming. I have an solution that generates a report that can be of varying length depending on the data that is entered. The records for the table that will be used to generate the report will need to be reused because the data used to generate the table will probably change often. I have a couple of questions. 1. I am assuming that I need a table (occurrence) in order to generate the report? 2. What do I do with the table after the report is generated since I will no longer need to keep retain the data in the table? Do I delete all the records or delete the table? 3. Should I be compacting the dat…

Hi folks, I've attached a solution I am working on which is used for theatre auditions. I have two records at a time on screen as I see two auditionees at a time. The problem I am having is I was helped with the basic solution by the wonderful BruceR on this forum, and have spent the months since he sent me a working version of what I was trying to create, trying to work out how what he did works (I am pretty green!) I have since added a bunch of fields to the solution, and cannot for the life of me figure out how to make the related fields show up on the second open record. The basic information is imported into the Camper Info layout from another solution, and the…

Please forgive me as I'm rather new to FMP. I am wanting to create a database that would allow contacts to be sorted like a family tree. Kind of like network marketing. Each contact could have multiple contacts(children) underneath them and I would like to be able to show all info for each contact (child) under then in say a portal. Please enlighten me.

Not sure where to start. Ok. I have a DB that I have successfully made a field that has a Value List of company names from a related DB. Once you choose a company name, subsequent field auto populate with Address, City, State, etc. based on the company name value. Do to an oversight when I originally created the DB, I am forced to be more specific on my look ups. So now, I have made a new field that pulls company name and an Oracle Acct Number from that other DB. Once you choose which company you want, this field populates with the 2nd field value from the value list (oracle Acct Number). Now.. The auto calculations I have on the company name is: …

I have an address book with a variety of types of contacts, e.g. students, teachers, volunteers, etc. I want to keep those tables, but I would like to make a comprehensive list of all of my contacts together, only including their names and what other tables they exist in. Is there a way to take the "Name" field from every other table and make it into ONE field in a new table? Thanks in advance!

I am having difficulty reconciling the results of the attached file. My ultimate purpose is to simply keep two tables in synch for a data separation model. The attached file is an extract to show the problem I am having. I am using a calculation c_current ID's to generate a list of the current ID's in the target table. I've added a "null" value in the first record so that it still works when no data is present. That works fine. I use that calculation with a not equal to relationship to show those records in the source table which are missing. But I get very variable results. Adding a new record to the target table and filling in an ID results in the c…

I am trying to decide whether to have a single join table with a field in each record describing what tables it joins or separate tables for each kind of join relationship. Normally I would do something like this: Class (table) Class_Enrollment (join table) Students (table) Books (table) Books_checked_out (join table) Students (table) In the database I am currently designing, there are numerous kinds of join relationships, so it seems like it would be a cleaner design, if instead of having multiple join tables, I simply created one join table with two keys and a field that describes what kind of relationship they keys have. Join_Table …

I have just read a tutorial about defining relationships at this address: Link The author states: Is this not bad database design? I ask this as I am making some changes to tables right now and would like to know if this is sound advise. In my scenario I have a table of Artists where I hold artist's details. Dependent whether I choose to use these artists or not I hold different details on them e.g if rejected, reason for rejection or if I choose to aprove their work, I hold their commission rate amongst other specific details. It seems bad design to have fields in a table that are not used. It makes more sense to hold specif…

I would love some general feedback / advice on planning a database I am currently building e.g. Tables required - relationships etc. The first thing I need to plan is how to allocate leads to salespeople. It is for a small callcentre up to 12 users but may grow to 20+ We are running filemaker Server Advanced 9. (Could be upgraded to 11 if required) Most users are running FMPRO10 or IWP at the moment I have various seperate inbound and outbound campaigns. I have a large number of leads / records. I have multiple campaigns (specific product or service being sold) I have multiple salespeople who could be exclusively selling on just one c…

Obviously doing something wrong.. I have three tables: Inventory,Transactions and Line_Items Relationships: Inventory:ItemID::Line_Items:ItemID Transactions:TransID::Line_Items:TransID I have a script that is triggered to create a new record within the Transactions table. It then displays a dialog box to enter the ItemID and an authorized name (any initials). Once entered, it will lookup the name of the item and the Quantity on Hand. This seems to work fine. However, the last step is supposed to subtract one from inventory (qtyOnhand= qtyonHand-1). I consistently get a dialog box that states that the field is not modifiable... Just as a test, …

I have a text file that is a list of cross-referenced index terms generated by a program that has ordered the terms hierarchically -like the list below (finance and accounting is a broader term of management accounting which is a broader term of finance - which has narrower terms of corporate finances, [etc ...] public finance. Public finance itself has narrower terms, and some of these terms also have even narrower terms). All the index terms are set out in this way through the list (so they are repeated in different ways). FINANCE BT1: MANAGEMENT ACCOUNTING BT2: FINANCE AND ACCOUNTING NT1: CORPORATE FINANCES NT1: CORPORATE TAXATION NT1:…

Still struggling, thought this was simple but cant work it out.. I have a list of students and I want to do reports which will include a comment. I have set up the table of students and a table of comments. I have set up a drop down list with the values from the comment table... All okay.. what I want is for a new comment written in the class list to automatically make a new record in the comments table so that it is then available ... Sorry for another newbie Q.

I am a self-taught FM user. I have developed my first database, with lots of scripting, and am about to start sharing it. In my scripts, I often have to switch temporarily to an unrelated table on the relationship graph. So I switch to a new layout, perform an operation, and then return to my original layout. To ensure that I get back to where I was, I make sure first to save the record number and portal number. I go back to the same record number and portal number when I return to the layout I had started from (not necessarily using Return to Original Layout, because I might not have started from the layout active at the very start of the script). Working alone, I h…

Hi, sorry to post such a newbie question... I'm using FMP11 Let's say I have a table for shipping charges, three fields and a bunch of records, let's say: Object name/Shipping charge within UK/Shipping charge worldwide Object1/50£/100£ Object2/100£/200£ Object3/150£/300£ Now I'd like to create a form/table/portal/whatever to cross-index my customer's needs, so that I insert the object he wants and where he lives and I get the shipping charge. Sorry again for the naive question, but I don't have a clue about what to look for. Thank you. Alessandro

Hi, I a trying to replace my excel gradebook with a FM solution. What I am really struggling with is how to do the equivalent of a look up. so students have a series of grades marked A-G in excel I give each grade a numeric so G=1, F=2 etc I can then highlight students who are at less than their target grade through conditional formatting. I am sure I am being thick but I cant get this to work. Help for newbie gratfully recd..

Hello. Fist post. Just learning how to work with filemaker by taking some tutorials on VTC this past month. Very neat stuff and Im excited to learn it. I am a Biochemist so this is new for me... That being said I am making a database to track inventory in my lab. As reagent comes it needs to be tracked, as well as when it is used in testing. Reagent can be uniquely identified by its barcode. There can however be many different lot numbers of each type of reagent. For state regulations I need to have a record of when reagents arrive, their lot and expiration dates. I also need to be document when reagents are used and by whom. So for example 100 kits of Reagent …

Trying to set up a relationship between three tables that have some overlap... PAGES WORDS LETTERS In this simplified example, these are the rules : PAGES could be made up of WORDS and LETTERS but also sometimes could just be made up of LETTERS Also, WORDS are always made up of LETTERS. I need to track the "production-line status" of LETTERS, WORDS, and PAGES individually : 1. I need to be able to see how far along the LETTERS are on the "assembly line", so I can know when to expect the WORDS and PAGES will be complete. 2. Also, I need to be able to see which LETTERS are still needed to complete the WORD I am unclear on how to …

Hi, I'm trying to create a table [Table A] such that each record in table A relates to a particular record in Table B. I would also like displayed (via portal, i assume) in Table A, information from one or more different records in Table B, but i haven't been able to get it to work. Basically, each record in Table A contains information about a unique contract. Table B contains information about our sub-contractors. While each sub-contractor will have a unique contract for each program, several sub-contractors may work on one program (each requiring their own contract), and i would like the contracted individual to see who it is they will be working with. …

Hey guys, I have a very simple solution made of 3 main tables: EMPLOYEES, PUNCHCARD and PAYMENTS. The PUCHCARD table keeps track of employees worked hours and the PAYMENTS one is responsible for recording payments owed or made to a given an employee based on worked hours. I have a one->many relationship between the EMPLOYEES table and the PUNCHCARD table and a one->many relation between the PUNCHCARD table and the PAYMENTS table. When I assign a punchcard record to an employee ID, I wanna be able to retrieve some of that employee's related data (name, phone, ...) from the EMPLOYEES table and display it in a report sheet that reads from the …

I have a large solution file that I would like to use as the starting point for a new solution file and will be used in parallel. This is to be able to operate two similar but separate divisions of the same company. One table in the original file (Contacts), however, must be shared with the second file but all other tables should remain in their respective files. My first thought was to take the table to be shared and create it as it's own file that can be brought into each solution as an external file, but what about the relationships, fields and scripts that are already coded in the other solutions? Is there any way to do this without going through each calculation f…

Puzzled here... Is this the correct and expected behavior... First I created a recursive one-to-many table that builds a hierarchy. Since hierarchy implies ownership and destroying the parent record should destroy children records - I ENABLED creation AND deletion of related records in the relationship options. This work as expected: Delete record and its children records are deleted. So far - so good. Then, I wanted to create an Aggregation structure. Since Aggregation implies no ownership and destroying the parent record should NOT destroy children records - I ENABLED creation but DISABLED deletion of related records in the relationship options. This "appears …

I have an excel spreadsheet with aunts uncles brothers etc all the relatives you can think and their relation to each person. So for example I have Jon Doe in cell A1 and all the way under relative would be Jane Smith (listed as an aunt or sister or whatever). I imported all these relationships into my filemaker file. So under one contact you can click on related contacts and there would be Brother1, 2, 3, Sister 1, 2, 3. Aunts uncles father mother all that. I want to be able to have the related contacts self update so if say Jon Doe is the Brother of Jim Doe and he adds a sister Jane Doe, then Jim Does sister field would automatically contain Jane Doe as well and he woul…

Is it possible to build a single relationship that always returns a list of the lowest level children in a one-to-many aggregation? For simplicity, I have one table that has three fields: "account_type_pk", "account_type_fk_parent", and "account_type_name" fields. They relate to each other as expected and works fine. I have been able to build additional TOs and their respective relationships that show a variety of information, such as: Show all records, Show all children of current, Show all children of the children, Show all children or children of children, etc. The same can be done going up the parent. I understand the number of levels in either …

I have a table relationship for a school system: Campus -> Class-> Enrollment (a join table including date, classID, studentID) -> Students I'm trying to calculate for a given time-range, the number of students who were enrolled by campus. I don't want to double-count students, so I need the # of unique students. I would like to run this report as calculations from within the campus table. This can be done by setting up a predicate relationship (with a start and end date) between the Campus and Student table. Then simply use Count(Student::ID) to get the # of students. Works very nicely. However -- there's a case where I need to do …

I have two tables with the relevant parts defined as follows: Event conferenceID - Number eventDate - Date Conference ownerID - Number lastDay - Date calculation (unstored) defined as: Max (Events::eventDate) Owner earliestDayToShow - Date In the relationship graph, I have the following table instances: Conferences - Conference Events - Event Owners - Owner OwnerConferences - Conference I have these relationships: between Conferences and Events: Conferences::id = Events::conferenceID between Owners and OwnerConferences: Owners::id = OwnerConferences::ownerID AND Owners::earliestDayToShow ≤ OwnerConferences::las…

I am new to this- but I have created a DB with a container field, gone to>insert>file>iPhoto and selected a photo, it shows in the container field 1001.jpeg, but clicking on it nothing happens. I see the it in the field, I am expecting it to open when clicked on, Any help would be appreciated Mike

I have a fixed structure of related tables, forming some kind of hierarchy. Is it possible to have a one-time default-created child together with every first child of every parent? For example, the two last tables of my structure are SHELVES However, because sometimes items may be stored directly on shelves and not boxes, I thought I should better solve this by storing a "dumb" box named as 0 or Shelf so it can be distinguished as a virtual box containing items on shelf level. What I need to know is if I can have this item automatically created for every shelf I create. Or to put it in another way, automatically create a "placed-directly-on-shelf" BOX child insid…

Hi All, I’m in the planning stage of extending my company’s database system to include bookkeeping. I will need 4 basic fields, 1) Bills, 2) Payment to Supplier, 3) Receivables, 4) Customer Payment. My question is, should I have all these fields in two tables 1 for the supplier side (Bills & Payment) and 1 for the customer (Receivables & payment), or should I have an individual table for each of these field? I’d love to hear your suggestions especially if you have some experience in Bookkeeping in FileMaker, Thanks Amram Chayim Eirinberg

Hypothetical here: If a database is normalized quite a bit - resulting in many tables to capture the bits of information on say a person, with a table for contacts, table for phones, table for addresses, etc. how difficult is it to import this data when the data comes in the form of an Excel spreadsheet where one row represents all of this information along with multiple instances of some of the entities like phones or addresses (Phone 1, Phone 2, address 1, address 2)? For example, data from Excel spreadsheet, each row contains ALL the information for one contact (First and Last Name, an address or two or three, multiple phones, fax, a couple of emails, web addres…

hello: I know my title may not be right, but basically I want to create a relationship using 2 value list fields. This is the setup: I have a table named EVENTS. It has various events and their dates. There is also a field called "Interests" which is a checkbox with various value list values in it: New Products Seasonal Events Instructional Events just as an example. So let's say I make an event record called "Summer Slam" and I check the "New Products" and "Instructional Events" for it, since those are the Interests associated with this event. There is another table called CUSTOMERS. It also has an "Interests" field with the same values. …

I am trying to create a family tree database i already have all the fields but am wondering if there are any designs or scripts out there that I can use. Thanks. Erik

I realize this is filemaker 101 but I can't seem to find the best way to do this. I have 3 tables that are nested (parent, child, and grandchild). When I create a new parent record, and then wish to add child records to this, what is the best way? When I add new records, I want to be sure it is related to the proper parent ID. Is the portal the best way? If using a portal, I will also want to add new "child" records related to that portal entry (i.e. granchild records). Can someone advise on the best way to set this up? I've attached a simple database with 3 nested tables. I use parent and child as hierarchical meaning not as a family tree. NestedTables.fp7.zip Nest…

I have a table called contact management. In it I have contact fname mname lname and suffix. I also have fields with all of the contacts family information. Brother1FNAME, MNAME, LNAME etc. Brother2FNAME, MNAME, LNAME etc. Parents aunts uncles friends all that. Is there a way to set it up so that if one contacts parents equals another contacts parents they will automatically fill in the other contacts first middle and last name fiels in the brother or sister fields of their record? So that I do not have to manually do it? Any good family tree scripts or plugins out there as well? Thanks! Erik

Hi all once again! Can someone suggest possible solutions to the following: I need to store positions for items. Since I can't yet manage Infinite Hierarchies (No FM Magazine Subscription), I've set up a fixed structure with six levels of position denoting fields (i.e. Area-Building-Row-Column-Shelf-Box) all of which receive the appropriate Value list Items through a dedicated "Registered Positions Table" with appropriate relationships. So far so good. An item is usually stored in a box (uniquely identified by name), BUT may as well be stored by itself on a shelf. What I would like to do is to manage the position of the item in case it is stored inside …

Is it possible to base a relationship from portal to portal? Let me try to explain. Layout from table "Items" Lets say Item name is "Bluetree". Its a tier 4 Item. Tier levels are from 1 -4. It takes 2 or 3 tier 3 items to build a tier 4 items. It takes 2 or 3 tier 2 items to build a tier 3 items. It takes 2 tier 1 item to build a Tier 2 items. What I would like to do is on the layout. Portal 1 shows the tier 3 items I need to build a bluetree. (This I can do. The rest I can't) Portal 2 would list the items you need to build the tier 2 items from portal 1. Portal 3 would list the items you need to build the tier 1 items from portal 2. Hope …

I have created a field that contains a list of foreign keys separated by returns. What I want to do is create a calculated field that takes a value from each of the related records and combines them into a specific format within a record. So, for the field that contains the foreign keys, I have: 115¶ 258¶ 458¶ The Full Name field of foreign key record contains: "Ronald Reagan" "Bill Clinton" "George Bush" So: 115¶ - "Ronald Reagan" 258¶ - "Bill Clinton" 458¶ - "George Bush" I want the calculated field to take the full name values and then output them like this: "Ronald Reagan, Bill Clinton, George Bush" …

I've created a join table of values that represents a set of data unique to a specific employer. I want to validate the names of these values so that they are unique only within a specific employer, not across all employers, and I don't know how to do this.

Ok I know this is probably very simple to do but I still can figure out how to make it work. In a nutshell I have been working on a database to manage leads, sales, commissions, assets, estimates, work orders, etc. This question is really applicable to relating alot of these tables and layouts but simply put what I am attempting to do is this. In my lead management layout we store contact info including name, address, phone, email, etc. As well as some of the estimate details, lead outcome, sale price, marketing info etc. All of that data is in its own table; "Contact Management". The commission layout has its own table; "Commission". This layout is used to calculate…

I am trying to create a simple gradebook in FMP10. I have a table of students and a table of assignments. I have created an intermediate table called grades that is related to students and assignments. It has a fk_studentid and an fk_assignments id as well as fields for category, date, and most importantly grade. My problem is: I would like to create a portal that works like a spreadsheet so that it lists the students down a column on the left and the assignments as the headers for the remaining columns. I think this is sometimes called a crosstab query or a cartesian product. I want to be able to click at any point on the portal and immediate put in a grade for…

Hi there, Here goes my issue: I have two tables - Events and Facilities. The user sets a date and a start time/end time on the events table. From all available choices, he picks a facility. My purpose is to restrict the number of choices accordingly to the room availability. Let's say we have an event set for today at 18h00 to 19h00 on room A. If we schedule a new event for the same period only room B will be available for selection. I'm attaching a sample fp7 so that this looks more clear. Can you give me a hand? Thanks! database.zip

I have a solution (or need one) that takes events with a start and end date and then displays the overlapping (concurrent) events. The relationship to show the concurrent events works as expected, displaying only items where there is an overlap, no problem there. My question is that I want to be able to show the elapsed time between the main (focus) event and the concurrent event. I am uploading a sample database to clarify my question. Within the example database, when viewing the focus item, "Went To Disneyland" in Item View, it correctly displays the concurrent items, "Undergoing Cancer Treatments" and "Age 4". The start dates for the concurrent …

Hi, I am thinking of creating a db that will summarize the spending of my roommate and I in areas that we either need to split the bill (here percentages of splitting may apply) or a loan one to each other. Eventually what i want to deduce from this whole deal is how much we owe one another and keep track of our mutual expenses when they are both of ours. I already have a db with all my financial transactions. I was wondering what the best way to set this up would be, i.e. fields, tables, etc. and how i would go about deducing the info i want to see out of it. Any help is appreciated. Regards, Jimmy

I'm very much new to Filemaker and possible diving in to deep at the start, but could someone review my Tables and relationships as attached. I know I'm going wrong but head hurts ever time i try and simplify it. The idea is: AIRCRAFT_DETAIL contains all the details of each aircraft and will via drop-down list pull data from: ICAO_TYPE_AIRCRAFT OPERATORS COUNTRY_TABLE ICAO_AIRLINE_CODES Also want to try and include images in each record (aircraft_details) as required. But if details to aircraft or photo change move the record to history and keep current updated in aircraft details. Finally have a feature where when an aircraft is seen this is …

Hello, all-- I'm using a return-separated list in a primary key to do portal filtering. It correctly shows matching child records when a word in the foreign key (a concatenated return-separated list) exists in the primary key. Q: Is it possible to do an "AND" or "EXACT" match? iow, if my primary key is this... blue green red ...how can I get the portal to show child records whose foreign key is exactly blue¶green¶red (not blue OR green OR red)? Hope that makes sense. Many thanks--

I am creating tables using the supertype/subtype model. I am wondering what is the standard in terms of which key field I should be using to create the relationships. I have a contacts table which has a key field ContactID. A contact could be a supplier, a customer or both. If a contact is a customer a CustomerID is automatically generated and held in the contacts table. Also if a contact is a supplier a SupplierID is generated in the contacts table. I then have two tables holding details which are specific to customers and suppliers. Which key should I be using to create a relationship between the tables. Should I be using (1) the ContactID or (2) either the Suppli…

Let me introduce my problem: - Product Master info database with a related db of attributes (1 master to N attributes) - Quotation db which links to Product db (M quotes to 1 productmaster) - Quotation db has its own attributes (1quote to N attributes). Right now I lookup the attributes of the related master and export them to file and import them to quotationattributes, but this is ugly and leaves files on the client. Productmaster has an key var ProductID Productattrib is linked via ProductID Quotation has a key var QuotationID and is linked to productmaster via productID Quotationattrib is linked via QuotationID. Is there any way I can sc…

Is using a script the only way to have a new entry in Table 1 trigger a check for an existing record in a Table 2 and if no matching id is found, create a record in the 2nd table?

I am creating a database for an art gallery. The people I will be tracking in my database will be Artists, Potential Artists and Clients. I will be holding different types of data on each of these but some data will be common to all such as contact details, account created etc. I see it as I have two options:- (i) Create three seperate tables one for artists, one for potential artists and one for clients. (ii) Create a Contacts table that will hold clients, artists and potential artists. I would have three table occurences of the contacts table in my relationships graph, one for artists, one for potential artists, one for clients. They would be dertimined …

I'm several years into this rather large database project, it's working well, and I've finally gotten around to documentation. In the process, I'm realizing my relationship graph is a bit of a mess. Early on I tended to favor the "keep the # of TOs down, and relate everything to everything" design philosophy. Perhaps "philosophy" is too strong, I think I hadn't fully understood the "anchor-buoy" method yet, or understood the power of having multiple TOs for a single base table. In any case, I'm now wondering if there is an easy (and safe) way to de-spider my relationship graph? My motivations are (A) easier documentation, and ( easier long term maintenance. …

I have a form for recording a contribution from an individual. The Individuals table has a one-to-many relationship with the Contributions table. When the user has entered the data and clicks "Enter", I want a script to create a new, related Contributions record and fill it in. I know how to do it with portals, but there's no reason to have one here. Every method I've googled up uses a layout in some way. Is there a way to create a related record without using a layout?

Hi all! I' m having a small problem trying to summarize data correctly. Here is the case: Table: People pk Person ID Table: Payment Categories pk Payment Category ID Category Name Table: Payments pk Payment ID fk Person payed fk Person(s) affected ID fk Payment Category ID Payment amount CALC Cost per person (=amount/ no. of person IDs) The tables relate to each other as follows: Every payment is assigned to a person through the "who paid" field, but also through the "who is it for" field, accepting values of person IDs from Table People. Also, Payment Categories Table provides the value list for Payments table. What I…

I have a portal that shows the records from an email table that matches the current record email address. So for example if I am on jon doe i have a portal that shows all the emails sent to his email address. The mathing relationship is email from the contact table matched to the to field of the email table. This shows the records just fine when I have only one email address in the to field for an email. But as soon as I have more than one email in the to field, it does not show in the portal. I think this is because it is looking to match all the email addresses in the to field to one record only. How can I set the relationship so that if an email address in the to field…

Hello all this is my first post. I am making an item database with the goal of copying an online game database as displayed in link below: http://www.shardsofdalaya.com/fomelo/fomelo.php?char=svettoy I have created an item database Items with joining databases that contain many to many relationships between item type and character type. I am now in the process of making an individual character database and am stuck. What I am trying to do is limit items displayed from a value list of items to only usable in that inventory slot by this type of character. The problem I am having is that I am now dealing with joining files which do not provide me with t…

I have a table in a database recording exam marks which links to a table of user details. Currently, all fields in the marks table are displaying ? and cannot be clicked upon. Doing a search in a field does returns the relevant data, but it is meaningless as again all values display ?. Adding a new record in the table works just fine, and previously the records contained all displayed fine. Is there any simple way of fixing this problem? I have tried deleting the relationship and re-establishing but to no avail. Thanks.

Hi! I'm a french literature student trying to understand how to build a database... It's not exactly my cup of tea... so I need a little help: I'm studying a literary magazine throughout time. I have 3 tables: [color:blue]authors, [color:blue]articles and [color:blue]issues. In [color:blue]Issues, there is a field listing the members of the [color:green]editorial committee at the time. I'd like to have a field in my [color:blue]authors table showing the value of another [color:blue]issues field ([color:green]issue's number) for each record where the author's name is found inside the [color:green]editorial committee field. For example: if, for the issue #3 …

I'm stuck on a problem that seems like there should be an easy answer, but I can't seem to get the answer on my own (and didn't find anything searching the forum). The relevant tables and keys look like this: ----- Project Table: ProjectID ContactID TotalPaidToContactForProject ----- Payment Table: ContactID ----- PaymentLine Table: ProjectID AmountPaid I simply want a calculation field in the Project table that calculates the total amount paid to a contact on a particular project (we'll call it TotalPaidToContactForProject) that aggregates/sums the AmountPaid field from PaymentLine based on a combination key relationship of b…

Hi, I have a table of Groups and a table of People. A Group has a number of positions: Treasurer, Chairperson, District Representative, etc. (about 8 in all) - each one of which is a person in the People table. When I display the detailed info about a group, I want to be able to list each of those positions, and the name of the person currently filling it. It seems most obvious to store a Person_ID for each of those 8 positions in the Group record. However, unless I'm confused, this means creating 8 different relationships, and 8 different People TO's. That seems like a lot of clutter and effort. Is there a simpler way to do this, that I'm just not …

I have three tables: Student Info, Required Courses and Courses taken. I have a relationship from student info to required courses and courses taken. From My Student Info screen I have created portals to my other tables. I would like to flag in my required courses portal which courses have not been taken for that particular student based on what has already been taken in the Courses taken portal. I have a relationship between the Courses Taken table and the Courses Required table so I preform a lookup to populate a Course_ID field in the Courses Taken table. A little lost on this one. Any help, ideas or suggestions would be greatly appreciated. Thank Y…

I have 3 tables -Big-Project table -Sub-project table -Worker table 1. A Big-Project record contain multiple Sub-project records. 2. A Sub-project record contain Worker + hour spent. The goal is to show the total of hour Worker spend on each Big-Project (Worker can work on multiple Sub-project) Is there a way to do that? please help project.zip

I have two fields: Field A contains date numbers: 733585 733586 733587 733588 733589 733590 733591 Field B contains date numbers: 733586 733587 733588 733589 733590 I need to be able to identify the number of dates in Field B that are also in Field A. The answer I would be looking for is 5 in this example. Any ideas on how to accompish this?? Relationship??

I have a solution that is attempting to GTRR on found sets to capture all children and flag them for export. I run my script, it works fine, then sometimes it doesn't. I run it with the script debugger and it returns error 101 on the GTRR, but it goes to the related table and has a found set. I scratch my head and check the parent found set and sure enough these are children belonging to the parent found set, yet I get an error. I run this process for a few hours, tweaking a few items to see if it's an error on my part, corrupt data or indexes, so and so on. Then it occurs to me to run the script debugger and on the GTRR script step, nav the parent found set to …

I'm creating a rough and ready budgeting database. I'm downloading my bank statements and what I want is a lookup the matches a keyword wherever it appears in the description field. So if the description field says: Budget Supermarket The match keyword is: Supermarket This won't work in a classic lookup in FMPro 7 as it only likes one word in the description field. So I'd like something like: Franks Auto Repairs = Auto Budget Petrol = Petrol Beer Barn = Beer Any ideas how I would achieve this

I am new here so please be patient. I have been assigned the task of creating a "button" in our quotes database that will open a new record in our invoicing database and autofill the fields with the quote data. In my first attempt, I created a new layout that autofills the information from a layout in the quotes database I titled "invoice"(the quotes database and invoice database are nearly identicle in layout anyway and the fields autofill among those layouts creating what I had thought would be an ideal solution...). After some colorful criticism about following task instructions, My boss had been very clear about that NOT being what he wanted. He then …

I am working on a Auto Dealer software. They have customers that are both buyers and sellers. I created one contact table to hold the buyer/seller/vendor contact information. I created fields in the main Vehicle table to hokd Buyer ID and Seller ID from that contact table. All good so far. Now I want a list of all buyers or all sellers. My list has the buyers and or sellers BUT if they bought or sold multiple times then the list shows them multiple time (ie Buyer "A" sold us 3 cars, he shows up 3 times) Any idea how to get the list to only show that buyer once? I tried multiple layouts with multiple relationships and none seem to prod…

I'm using the Line Item portal technique described in the Filemaker training manual and have a question about how to select values. In the technique it uses a related value list to select the foreign key value of the related line item. I'm trying to replicate this functionality without the requirement of selecting the foreign key value. In other words, I want to use the item description to choose a line item rather than the foreign key value. Is there any tutorial out there how to do this?

I know I've seen this before but *blush* I've forgotten how it's accomplished. I've searched the threads and couldn't find any posts about it. What I'd like to do is have a portal as a User Interface sans a standard Parent/Child relationship; when you create records in the portal they would stay in the same table. I think it's done with making a copy of the parent table in the Relationship graph (e.g., Contacts and Contacts2) connected by a Cartesian (sp?) connection, but I just can't remember. TIA for your help!

I apologize if this is a subject that was already covered by other threads, but time is of the essence! And I don't have time to sift through all of them. I have entry level experience in FileMaker and after working on a "asset" relationship graph the records do not show up on the "layout" view. What ensues is a notification that says "This action can not be performed because the required table is missing." So what I am guessing is that the original "asset" relationship graph was affected, so if this is the case could someone help me piece together the original asset graph if this is at all possible. If any other solution is possible please recommend, I'm all ears!

peşin Yardımlarınız için, size teşekkür ederim. fabrikalar ya da işletmeler, personel taşıma çalışma tablosu aşağıdaki gibidir Ulaştırma Acantası Müşteriler Tedarikçiler Hizmet araçları Siparişler Orders_suppliers Senaryo: ücretleri acantesinden're müşterilerden ücretleri olduğunu söyledi Nakliye ajansı alarak taşımak sonra Tedarikçi özel servis araçları ve müşterilerin işçilerinin günlük fiyatlar ay taşır. Benim ilişkileri nasıl? Diğer bir öneri bir fikir geldi? raporları sağ olsun. Türkiye'den selamlar Nuri Agency.zip

I am getting some erratic behaviour and don't understand why.. I have a feeling that it is my ERD structure that is at fault and some of the fields in question are greyed out, as seen in the attached. Can anyone explain why they might be greyed out? Thx

Good morning, This is my first post on this forum and I am new to the program. I have some experience with Access and have a pretty good grasp on how the program works. I am trying to create a database that effectively tracks our construction operations. I want to present the main goals below: 1) I have to track 3 main categories: Equipment, Employees, and Materials. 2) I want to be able to have a SINGLE costing input screen. I want to be able to have a button/pull-down option that lets me chose between the three main categories mentioned above (Equipment, Employees, or Materials). I know HOW to create the said button or pull-down menu, however, …

I have got myself in a situation where I am trying to create more than one relational path between two tables in a graph. I'm aware that I can't do this but I can't see any other solution to my problem. Basically I am setting up a database for an art gallery where each peice of art has a category. A client can purchase a peice of art so therefore has a relationship with a peice of art. Also I want to enable a client to register an interest in a particular art category so again therefore creating a relationship between the two. This leaves me with the dilemma of more than one relationship between category and client. I have included a screenshot of my relat…

I'm having trouble creating a relationship between 2 tables. 1 table is my customers table, and the other table is my quote table. 1 Customer can have multiple quotes. My problems are the following. I don't know what i need to create and what i need to join between the 2 tables and also. I don't know how i would assign multiple quote #'s to 1 customer (portal maybe). can anybody help. Thanks Happy Friday.

Hi. Over three days last week I was introduced to FileMaker and managed to create a working(ish) database to help me organise my University documents. Although it started as only one table, some of the fields have become tables of their own firstly so that two values (e.g module code and module title) can be shown on the main layout with either being used to choose the module/assessment. This works fine. Secondly I would like to be able to limit the values shown in a drop-down box to only those related to a previously selected value (e.g only staff who teach on module EOE1201 should be able to be selected on the main layout when the module is set as EOE1201. This…

I seem to be at a loss, it has been years since I have had to do anything major to our FMPro database. We have a main menu, contact, invoice, and product databse set up. I can click from contact to make a new invoice, I can type in products and they show up on the invoice - But here is my issue, I had to add 2 fields to my products, a Rack and Vehicle field, and I am trying to get these fields to show up on the invoice when I type in the product ID. I defined the fields in the product database, where to go from here I am at a loss - my Products DB is linked to my line items DB, so if I add those fields in my line item DB, I can see them, but I canno…

I looked at my history on this forum, and noticed that the last time I posted a question, it was 2006! Anyway, I’m back to ask another question for a problem I can’t seem to figure out. I’m using FMP V9 Advanced on a Macintosh. I've developed this database that tracks the television pitches my company has made to TV networks. The database contained two main tables: Ideas and Networks, and I recently added another main table, Personalities (for people whose ideas we rep). The database is working great (hence my long time away form this forum). However, the addition of the Personalities table has caused some issues that I can’t figure out. Or put more specificall…

Hi everyone, I had a newbie question. I am trying to figure out the best way to display a relationship between 2 records on the same table. Basically what I have is a "Parent" table and a "Child" table. I can display all of the parent's children in a portal on the parent screen, but cannot figure out how to display the siblings of the children on the "Child" layout. For instance if John has 2 sons Jake and Jason, both sons appear on John's layout in a portal. How do I get Jake and Jason to appear on each other's layout when they are in the same table? Thanks

I feel this must have been discussed somewhere, but I can't find it... Table A and Table B are connected by a join table. I have a portal showing all entries in B that are joined to (linked to) A. Is there any easy way to show in another portal all entries in B that are NOT joined to A? Thx

I'm trying to create a portal that shows all possible tags for a contact as well as an "x" indicating the current contact's associated tags. To make matters slightly trickier, I'm actually trying to create several portals that each show tags of a certain type. Let's focus on one example. This description is the way the portal SHOULD work. In a portal called "Boards & Committees" the rows include the following tag names: Board of Directors Medical Advisory Board Dinner Committee Business Council In John Doe's contact record, All four of these appear, and "Board of Directors" has an "x" next to it. In Jane's record, All four appear, but none…

Working in FM 10... I have a database with a standard many-to-many relationship orders--products for CD sales, but also a separate database that lists individual songs. Each song record has a UPC code that corresponds to a CD's UPC code found in products. I need to generate a report on sales by song during a certain time period. How do I set up a filter based on order date for a productlineitems portal on a layout based on songs? Or is there a better way to do this? The data needs to be combined with other sales info, so I'd like to avoid a search method.

Hello Everybody, I am downright desperate. I need some "Pro Bono" help regarding a solution I'm working on for my father's company. He owns a Manufacturers' Representative firm, and I am having the hardest time building him a working solution The main problem lies in tracking his rep's commissions, and having the ability to split them up if they share a sale. I have created a work flow type of interface, but the data doesn't always seem go through the relationships. Here is a summary: [Customers]----<[Orders]------<[invoices]>--------[Commissions Recieved]--------<[Commissions Payable] To walk you through the process 1.) A custom…

Ok, My IT department has just purchased File Maker Server. We are wanting to use it for Asset Management, for Computer hardware, Printers, and software. So far I have 3 tables, Computers, Printers, and Software. I've established a relationship across the field of "computer name" and it seems to be working, but I'm having problem with Relationships (I think) I have "Software Titles" show up as a checkbox in the Computer list, I have 3 software titles right now, if I uncheck a software title off all of them, the removes the choice from all the computers, and deletes the entry on the software table. we're wanting to keep license info, purchase order info under this as …

I've created various relationships in the past but I can't seem to find out how to edit one once it has been created? Do I have to start again or can I add another element to the relationship?

Hi all! I'm not sure I'm going to be able to clearly explain what I'm looking for here, but I'm going to give it a try..... I'm looking to replicate the power of using Finds to filter a list of records in a portal. I frequently use a global field in the header of a layout that lists records in a List View Body part to allow my users to enter some text, including partial words, in order to filter that list of records. As they type in that global field I simply run a find that uses exactly what they have typed. Very simple. Very powerful. Even with a partial word typed, FileMaker's search engine will find records that have words in the field being…

Hi I am attempting to create a (GAME) layout for inputting team information. Specifically the home team (Team_home) and the visiting team (Team_visitor). I have my GAME table and TEAM table. I have made 2 table occurrences of the TEAM table (home and visitor) and related the two to the GAME table through their respective primary keys (serial#) to the relating foreign key on the GAME table. I put in the home team and the visitor team is assigned the same info and visa versa. I do not know how to separate the two occurrences of TEAM so that data can be entered independently. Any help would be appreciated. Thanks! /Joe

Hi All, I have a very nice correspondence tracking database that works perfectly. The main report is a subsummary printed report that shows the status of every Letter mailed to a Customer (eg, Sent, Received, etc.). I've now been tasked to get the report to show the last Action taken on each outgoing Letter. The Actions are in a separate table; this table is visible as a portal when the user is in the Letters layout. I now need the only the latest Action taken (each one has a date field that will be populated) on each Letter to appear in the subsummary Letter status report. For example, the report needs to show the status of the letter (eg, Out for R…

Hi All I am new to creating databases and using filemaker. I am creating a database for a shop scenario. I have created a product table which has a field Category. I have created a table for category as I would like to add new categories in the future. Within the category table I have created a field CategoryName and linked CategoryName in the category table to Category in the product table. This has created a many-to-many relationship, but a product can only have one category and categories can have more than one product. I want this to be a one-to-many relationship. How do I go about this? Thanks in advance