Relationships

I'm having a bit of trouble trying to figure out the design of a database using FMPro 6 that can be used in the following way: When entering data begin by entering personal details in a 'Tutors' database, then enter the course details for the tutor - this will only be a summary of (for example) course start date/time, course name, course venue and min/max places available. Multiple courses can be added to each tutor record. To enter further details or view more course information one would click on a button to be transferred to the related record in a 'Courses' database. OK, I have no trouble with the above and created a portal in the Tutors database to sh…

Is there something I should know about one to many relationships? I have 2 portal windows where from one (my stock table) I copy records into my ordering table. But when attempting to add a third, I am having problems. I have ascript that should move records into the table, which I have tried to modify form another. But I feel I am missing something out that will make the relationship work. I think the relationship needs looking at and also the script. What I am trying to do is: when an item is ordered and then booked in, I can then move the item into another table, say delivery/invoice. Please see the file attached. Fasteners_4.zip

I was hoping someone could shed some light on my little issue. I have two related tables from the job table, milestones + status. Milestones can have multiple records depending on the job, although the value list defines the standard three entries. Status generally has one record (but can have two) and has it's own value list. I'm wanting to show Artwork Approval + Contact Delivery Milestones when in the Status screen. (I know I can see these values in the Jobs screen via the portals) I have set up two fields (ArtworkApproval + ClientDelivery ) in milestones which populate if the MilestoneType meets a set value. I have tried different methods of ta…

I thought I had a handle on relationships but obviously not. I'm trying to make a database to keep up with volunteer hours. I'm at the point now where I have to update all of the flat files that I've made to make input and reporting a little easier. I have 3 tables, volunteers, time cards (the FM stock file) and tasks. I want to use the time card as my parent file, so I have the relationship with the time card and the volunteer table working ok. I'm using lookups to actually get the names. The problem is that I made this button to "add a task to this day" and I want it to make a new record and record the related name and the associated time card. Multiple tasks can…

I have two tables I am trying to link together and I would like to know if it is possible to do the link in a certain way. Table A has a field called CELL CODE which will have one specific code placed in it. Table B has a field called CELL CODE also which I would like to be able to put multiple codes in and have FM search for a match of code. The multiple codes woudl be in the same record. I have tried using a colen to separate the codes but FM is not seeint a match. Is is possible to have FM do this relationship? Thank you for your help WizBang

Hello! Although I'm extremely new to FileMaker I really need help finding an efficient solution: 1] I need to create a custom interface in FileMaker where a user can search a data base based on three parameters [ie: state, name, place] that will result in a a generated list based on their specifications. 2] After that, there should also be a place on the [form?] where they can generate a mailing list/labels. I truly hope someone can point or guide me in the right direction. Thanks, Sara

Hello, I've been away from the forum for awhile attempting to figure things out on my own, but I've run into another roadblock. I have two databases 1) Student Database 2) Schools Database (with an Information tahle and a References table) The student database includes all student information. The schools database includes information on schools our students have come from as well as contacts. When a student applies to our school they require two reference letters. These reference letters are often from teachers. In the student database there is an area where I enter the information of the references (name, school, email, phone, etc.). In t…

I've already posted a question (and had the answer) [Relationship help needed...Before I go mad! (Topic#171277)] - since which time I've continued nurdling on the same db. To summarise, I'm working on a Mileage Database and Itinerary Planner. Currently this consists of two tables... Table 1: Routing Field: rCity [text // match field] [*Should this be a global?] // enter the first four departure city letters Portal: Field 1: RouteCalc [Calc text] Field 2: Miles [number] // Portal row is selectable, highlights and places contents of portal fields into two temp fields for scripted "assign" to Itinerary layout Table 2: Mileages Field: Recor…

I have a contact database with a field for manager and then a portal I want to show all people they manage. I created a relationship and related manager to full name. The portal is now showing the manager of the person, rather than the names of the people that person manages. Can anyone help? Thanks!

I'm trying to write a DB capable of processing information from a Reader Service Card. The card asks people to indicate the companies that they would like to receive additional information from. They can choose from 1 to 300 odd different companies. It also collects contact information and an area of interest. Each company has a unique RSC # and of course the other variable is the magazine issue. I'm basically extending a previous DB that I did that was recording the advertising contracts for each company based on the magazine issue. Meaning one table is all company info a second table is recording the publication issue. I would like to be able to s…

I'm trying to make a relationship, but I don't know how can I do this!!! I have two tables: One is the Hour table, it contains the following fields: - Date - EntranceTime - LunchTime - ReturnTime - ExitTime The other table is the Employee table, that contais the followin fields: - EmployeeName I have to make a relationship with those two tables and make a Layout that I can make a report with the name of an employee (example: Everton) and all his records of the Hour table, that contains the date and time of every worked day. Please someone help me to do this!!! Thanks.

I have two tables (T1,T2) I want to store three fields in T2 (F2.1, F2.2, F2.3) I have multiple fileds in T1, but wish to do a comparison of data in two of these fields with two in T2, say F2.1 & F2.2, with the value of F2.3 being the result which is displayed in a field on T1. All the fields are 'text' I was thinking of using a 'IF" statement or a 'CASE' statement, but I'm having problems in working out the correct syntax. I'd appreciate any assistance, Thank you.

Does anyone know why it is that when I build a layout on a Table Occurrence that filters and sorts my data, the layout loads much slower than if I script the same filter and sort on the base table? I have a clients/services database where I start with a client, click on a button and show the client's services. In the old way, I set a global to my client ID, went to the services file, searched for services with the stored ID, and sorted by date. Clunky in code, but the response was instantaneous. I just tried the new way, using a relationship to filter on the Client ID, and applying the date sort to the relationship. My system has ground to a halt while it s…

Hi there, I'm a beginner using FMPro, but as the only one in our small company that knows how to use a computer I have been assigned this mission! We sell T shirts in London very successfully, but at present everything is written in books and at the end of the week the data collected and sent to our supplier who prints what we order. I have had some success making the database - there are a number of popups, and a running total of takings works, but I am having real difficulty linking this to a new table that we could send to our suppliers. Ideally I'd like it that when T shirt X is sold in size Y, that is put in a table, so that at the end of the week we could refer…

As you may have guess by my screen name Im in law enforcement. In regards to this I'm trying to develop a more complex type of portal than I'm currently using to check Officers thru a dispatch database. Currently I have a portal setup (which is a self join relationship that basically will show which officers are on a particular call based on either their "Time of Arrival" (Time field) and a "Time Cleared" (Time Field). When the officer is dispatched to a call the call entry in the database is assigned to a particular officer and the Time of arrival is empty thus creating an entry in the portal which shows the location (or destination) the officer is going to and that th…

Hello, I have two tables: Guest Scheduling Guest Full Name Substitute Guest (Value list of all people in Guest) Scheduling Primary Guest (The Fulll Name from Guest) Substitute Guest (This is the Substitute from Guest) In Scheduling I want someone to select the Primary Guest and the field Substitute Guest will only populate people that have the Primary Guest name filled in in the Substitute field of the Guest Table The problem is, it is tricky for me. How should I set this relationship up? I had it one way, but it was inconsistant and changed from time to time on its own (it worked one day and not the other day). …

Hi All, Warning! I have some dumb questions about some probably basic FM concepts, so thanks in advance to all those kind enough to help. I have a law firm client using an ancient OS 9 based time entry/billing program called Timeslips. I have been tasked with recreating (and hopefully improving) it in Filemaker. I have the time entry portion up and running fine. Each record is an entry of billable time tied to a particular client. But now I have to create a printed monthly bill for each client. Seems simple enough, but there are some wrinkles. First, it has to maintain a rolling balance due. Most of the firm's clients have outstanding balances that ar…

Excuse the long winded problem: I am trying to produce a file containing data on loan applications which are paid out. Each day additional deals are paid and this amount is totaled to show the total paid. However i need to create a figures page which can show the amount paid out each day. Creating a relationship to the date is no problem. However i want to make a file which contains 31 records (one for each day of the month), but do not contain the month number, this should be specified by the user to correspond to the month they would like to view. The obvious answer is to get them to enter it into a global field, however in FM6 you cannot create relationships with…

I think I may have a layout problem. I have a table "Order", and a table "OrderParts". When I do a script step to add an OrderPart to Orders it works just fine. I get another OrderPart.My problem is, once I get a new order part, that's all I can do with it. My Layout continues to edit the first OrderPart instead of editing the newly added OrderPart. I have used Portals, some script steps, and have had no luck. If anyone could give me a clue I would be greatful. I think my main problem might be that attacted to my OrdersTable are other tables such as "color" "size" and "style". These are the type of fields that I need the new information inserted in. I have attac…

I am migrating a solution from FM7 to FM8. In 7 I have a Relationship which contains a global in a calculation. gVal & "~" & SerialNumVal This is used as a key on one side of the relationship. While it is not indexed or stored, in FM7 it functions for the relationship just fine. After moving to FM8, it no longer works. Any ideas?

I am new to Filemaker but know SQL and Access....I am in need of help, if anyone would be so kind..... This is for a Printing company. I want to be able to enter 3 different print locations for the same item, and for each of those print locations I want to be able to asign a design. each item will have it's own locations for design and the designs itself. example: I want to choose, for item A Location #4 with Design #65 for item B : Location #5 with Design #12 for item C : Location #4 with Design #62 I have a table that Locations table that contains static information on the print location example: Location# LocationName…

Hello everyone. I hope you can help with a relationship problem that is driving me up the wall. Basically, I have one table ('calendar') that is linked to a 'bookings' table. This 'bookings' table is also linked to a 'people' table. I am using it to 'book' these people on jobs on different days. However, I want to be able to see at a glance the people who have no bookings associated with them on any given day. I can do this via scripts, but it's cumbersome. I'm sure it must be possible via relationships. My original thought was this: - In the 'people' table define 7 calculation fields that referenced the 7 dates in a week from the 'calendar' table (whi…

Here is my problem. I have 2 tables right now that I need to have get a serial number from a 3rd table. I thought I could make a relationship where TableA and TableB could create records in TableC but its not working. Any suggestion on how to get this done. I've tried GetNextSerialValue but it says i have too few parameters set and I have no idea what else to set in the calulation to get it to work. If you need a better description let me know I'll try to explain myself better. thanks in advance.

I finally managed to finish my db with the exception of one field. I have 4 related dbs and all seems to work fine except this field. How's this possible ?

I'll give you guys a senario that im having a hard time with.. I have 2 tables (Table 1 RollCall) (Table 2 Students with information) Im relating the rollcall table to the students table based by a dropmenu valuelist of active students (so by name instead of table ID). I cant figure out a way to relate the tables by ID instead of student names by a valuelist.. Anyone have any idea's? I would keep the relationship by name but what if theres more than one student with the same name then there would be a problem. Thanks

I know that filemaker can handle fields with many tables. I want to split a table into two for ease of design and link them with a 1:1 relationship. I am having a lot of trouble doing this. eg: table 1 personid name table 2 (confidential info) personid sex ssn salary vacation accrued table 3 (disciplinary actions) actionid ssn reason comment penalty I want to link table 1 and table 2 with a 1:1 relationship via person id. then I want to link table 2 and table 3 in a 1:many by ssn. I should be able to show the person's name on disciplinary report via these relationships. Any help appreciated.

I have built a very complex series of tables and relationships to minimise data redundancy but now I cannot get my auto fill fields to work properly. I have got the same principle to work from other more simple 1-1tables relatioships but not my 1-Many-Many. I have attached my file. The table area I am trying to get to work is the Room tables. I store the event details regarding a room in the Event Room table which pulls data out through the Room table, which in turn, turns out value lists etc.. through 2 other tables - Room Cost & Layout. I want the room cost in Event Room to be automatically filled in when the event meal record is created. I can get that bit to w…

I'm trying to set-up a mileages db and I figured that this might be best accomplished with a Portal... The objective is to automate mileage entry in a trip-itinerary Table 1 - Mileages Field 1 - city1 [text] // from Field 2 - city2 [text] // to Field 3 - miles [number] Field 3 - city_city [calc text] (city1 & " to " & city2 & " " miles & " miles") Table 2 - Itinerary Field 1 - city [text] //insert city for match Portal - city_city The relationship is made between Field 1 = Field 1 This works...But I can't get to the next step... By way of example... San Francisco - LA San Francisco - Seattle LA - Seattle …

Am having difficulty wrapping my brain around this one: I have 300 newspapers uploading files to a central archive. The archive sends me a manifest of filenames each day telling me what files they received. I import this manifest into my database and through calculation fields pull apart the filename so i can keep track of what paper sent their files, and when. For example: the Fictionville Fiction uploads a file called "fifi_20050909_A32_32.pdf" and I can pull out the code "fifi" and have a relationship to my newspaper database that says code "fifi" = the Fictionville Fiction The One-to-Many relationship is where the field "NewspaperCode" in the Newspaper Ta…

Hello. This is my first post, and my first question. I am building a database for a small company. I have contact information that I need to record for employees, patients, and patients_other (what i call family members, guardians, etc) all of which i specifically want to keep phone and address information for. Ideally, I was trying to assign a contact_ID to each of these persons. Then the contact_ID was related to a table that contained a Phone_ID and Address_ID. Then, the address_ID related to a table that contained the address for the person. The phone_ID related to the table that contained the phone information. Needless to say, since the Patient t…

I am rewriting a database in v.8 that has evolved through the years up to v.6. I made extensive use of contatenated keys to store similar data in one file, relate it back to the parent in a number of ways while keeping the number of files reasonable. For example, one file called Action was the child of Client and contained faxes, letters and phone call records. All had the foreign key "ClientID" plus a "type" field which was set by script to one of three values... fax, letter, or call. This was combined by a calc with the foreign key and made the one file do the work of three. I used the same tactic in invoice line items to distinguish service from expense line items, etc…

Hi, I am having problem with some relationship problem. I am making a simple accounting ledger and journal transaction. Below is the problem I faced Journal Entry Page I've relate Debit & Credit Field to 1 code only. If I get rid the Credit relation it works. If I put in, it doesn't work. General Ledger Table Should show the transaction but with the description as RENTAL FROM H form. How Do I do that ? H Form Should show as credit $100 and description as agency fees. Any help is highly appreciated. Thanks in advance. journal.zip

I have a transactions table in which each transaction is associated with an invoice number. Each record in the transaction table has a serial ID number. What I would like to do is create a one-to-one self join where a transaction is related to the previous most recent (that is previous highest transaction number) transaction on the same invoice number. I can relate to all previous transactions by matching invoice numbers and using transaction number > transaction number as a second condition. I can think of no way of modifying this, or doing it a different way, to produce a match to the single most recent previous transaction. All suggestions gratefully recei…

I have Filemaker 7, and we have pictures that we have alot of searchwords on. So between the Image table and the Searchwords table we have a Image_Searchwords table. That table contains about 32 000 posts, and its to darn slow to just make a new post in it! Why is that??? And it will be around 510 000 posts in it lately... I have made sevral TOs from Image_Searchwords to see different types of searchwords, does that effect the speed of the Image_searchwords table?? Plz need tips badly! Regards Jocke

Hi, I have a multilanguage database where I collect film information. Is called FilmDB I have another where I have the translation of names in different languages. This is NamesDB My FILMDB has several fields with the international names of actors, directors and other talent. Also I have a "glossary" field with international words for film making such as "cast", or "slate" or "grip" and more My NamesDB has an International names field (where names do not have acccents just plain english text) and several different teranslation in different languages and di9fferent encodings (including Japanese, Chinese, Russian, Swedish and so on). Both database are in …

I'm using a relationship to perform a find based on a date range. How can I return a message in a script when there are no valid relations.

My database is set up to create parent records of Orders with a child table related for Line Items. I'd like to be able to create and track new parent records of a revised Order while retaining the original Line Items of the child records in the new revised parent record. What's the best way to approach this? Is it simply a matter of duplicating the parent record and the original child Line Items will rmain attached?

Well, not REALLY random messages ... reminders, prompts and encouragements by Manaagement to Employees depending upon pre-determined selections (which COULD be random or All). Management would add/modify/delete these records (prompts). Record criteria: Duration Start (date) Duration End (date) OR Ongoing (All) AccountName (multikey or All) Privilege Set (multikey or All) Table (multikey or All) Color (blue or red) Layout (multikey or All) Message (maximum character length reserved on most layouts in 25 px. high, layout-wide space. If a User switches to a specific table/layout, prompt field will 'relate' to Message table and provide direct…

Boy I just think I get relationships down and I run aground again. Can some please look at this example and tell me what I have wrong. I have a table named STORY that is linked to another table called STORYversion through a portal. What is supposed to happen is that when you click in the Story Word field (blue) and enter a word a new record is created in the STORYversion table, along with a unique ID # (left field). But instead of this behavior the duplicate number is entered into the next record field and a error occurs because it must be unique. When I check the serial # in the STORYversion table it is incrementing as expected but not ending up in the fie…

Hi I am trying to create a lending library similar to the one provided by FM. I come from Access database so I have few questions and really hoping somebody could please help. The lending library template has 3 tables but they are not joined. I am confused as why they are not joined like Business Tracker is. Anyway, I have 3 tables, an inventory table with Inventory-Table Inventory ID and other details Customer-Table Customer ID with other customer details History-Table This should show who has hired what and when or/and atleast the items that are on loan.. How would I be able to join all tables together?? I am thinking I need a lineitem t…

How to go about doing this? I am currently trying to develop a Prospect database for my company (Insurance company). And I am not able to find the way to implement this feature into FileMaker 8 Advanced. I’m also new to FileMaker, I took 3 FM 7 classes from CoreSolutions (Intro, Intermediate I and Intermediate II) about a month ago. Let me write the sample data and how it should work. Prospect Table (I’m just using a test db) Field names: ProspectID,Company Name, Telephone, website ProspectID, Company Name, Telephone, Telephone, Website P1, A, 18003453434, www.a.com P2, B, 18881231234, www.b.com P3, AA, 18003453435, www.aa.com P4, AB, 18003453…

After several attempts I have gotten very close, but have been unable to get all my relationships to work correctly. Here is what I am attempting to do: Contract Database: -Each Contract has a unique ID code already assigned -Each Contract has three sections: Purchaser (Contact), Products, Invoice Schedule -Each Contract is related to 1 Company and 1 Contact -Each Company may have more than one Contact -Each Contract may have many line items of Products -Each Product line item includes date fields, number fields and text fields -Each Product is associated with a Supplier -Each Supplier is associated with more than one Product -Each Product line ite…

I've signed up for a class on, you guessed it, database management.. one of our big assignments it to create a database for an existing small business. I know a doctor who has a paper-based practice (and may never fork over the time & $ to invest in a proper practice management system) so I figured I would give a crack at putting together something simple. My goal is to create a system that will track patient appointments, allow the system to send reminder letters, and track information about each appointment after it happens (and is then a visit). On my first attempt, I crashed and burned. I'm attaching an attempt at an ERD and a filemaker file which will…

I have created a course registration database with the following relevant tables: course, students, registrations (the join table). Each course has a maximum capacity that is set by the 'capactity' field in the course table. If a student registers and the capacity has been reached, then the "waiting list" field in the registration table should be checked. Anyone have an idea of how I can accomplish this? Thanks, Moses

I have two FM8 files and I want one to pull info. from a field from one and display it in the other. I could do this in 6 but cannot do it in 8. Why is this? Am I missing something?

I have heavily commented my fields in a Developer/Pro 7.0v1 solution. I now want to access these comments as well as the field, table and file names. I would like it related as data to a Developer vs. 6 file where I keep my own working notes. This sounds nuts ... please bare with me ... I want to view a layout of the file/table as record with the fields as portal records (displaying field name, comment and maybe other stuff like working ideas on enhancements or better calculation concepts to consider - these are my working notes). As I am designing, I want to access this Developer data file (and even make changes to it or add notes) by opening a window. Current…

Hi Guys, Sorry for being thick, but can someone describe what a join table is, and when best to use it. As far as my vague and limited knowledge goes, its a table that "joins" two tables, but does that mean its an additional table in the system that is only used to display data (I take it - no data entry goes into this - it just pulls in related data for viewing). Thanks once again guys, I'm just trying to increase my knowledge, and understand some of the basic concepts.

I'm using FMPro 8 Advanced. My database has got a many-to-many between tables A and B represented by a table AB -- so it's the normal A --B sort of pattern. In my application I have a form layout for A in which I want to allow the user to add a new AB record; i.e., relate an additional B to the specific A. I want to do this by way of a pick list, a new window that opens and presents a portal containing B records that might be added. (Ultimately I'd like this to be a multiple select list, but that's another story.) The trick however is to populate the portal ONLY with B's that aren't already related to A via AB. I'm at a loss as to ho…

I have been developing this database for a year now and have figured out a lot things I needed to get through, but I just don't get the problem with my relationships. Prior to starting this project I never used Filemaker so I really don't know what I'm doing, but I have put a lot of time into trying. I am attaching the file in hopes of finally understanding what I'm missing. In the layout named Interface_manage it would be nice if only related records would display. If anyone can help me please and thank you very much. The user name is TAOps and leave the password blank. Shanon Helbock TAOps.zip

I have a script which deletes related records using the GTRR and DELETE RECORDS script steps. The script seems to work perfectly 99.9% of the time but has on two occasions deleted records in the parent table not the related table. And yes, there is an IF step directly before the GTRR which checks for the presence of related records: a symplified version of the syntax I used is IF[count(related table:related table ID)>0] GTRR Delete Record ELSE Exit Script End IF My question is are there any situations (record locking, multi user, multi window?) in FM7 which would result in a failure of the GTRR step to actually go to related table…

Hi guys; this should be SO easy, but I can't find a solution anywhere. I need to do an inexact relationship match. I want to look up a person's salary in a separate tax table and get the applicable tax rate. e.g. SALARY......RATE 0-300.......0% 301-999.....5% 1000-5000...10% etc In Excel, it's a simple VLOOKUP, and if the exact salary is not found (as will usually be the case) the closest matching value is returned. How do you achieve this in FMP? I have created the second table with the tax brackets and tax rates, but I can't get FMP to do anything but an exact match between the salary and tax bracket values. Even a dumb FMP lookup allo…

I currently have 1 DB with multiple layouts. 1 of those layouts should be a separate DB, but I don't know how to achieve a lookup between the two if I separate them. The way it currently works (and the way I'd like it to continue to work after I make them 2 separate DBs): 1) From my "Main" layout, I run a script that changes to the "Master" layout and runs a find script in a title/artist field. 2) From the records returned I choose one, then run a script which copies the contents of that field, switches back to the "Main" layout and pastes in a corresponding field. 3) The script continues by returning to the "Master" record I'd already chosen, copyin…

Does anybody know how I can automatically fill out multiple fields on a layout based on a single lookup? e.g. I want a person's name to be looked up and copied into a person field in another table, but I also want it to copy the data from the person's address fields into corresponding fields in the other table automatically at the same time.

I only wish this post was about my love life. I recently created a complex relationship to allow filtered viewing through a portal. It worked fine for a few days, then magically ceased to work. I made NO CHANGES to the system at all, in that time. Not even in another table or file. I checked the relationship again, and the filter globals that the relationship is based on, and EVERYTHING IS PERFECT. In fact, I compared it to my development copy, and everything is exactly the same (as far as this portal/relationship is concerned), though the development copy works. Also, I can't seem to use GTRR to this table, like I could also do even as recently as yesterday. …

I'm having a tough time trying to organize a potal/realtionship that reflects an order status as part of a customer profile. Essentially I want to be able to view in one portal of all unfilled orders, in another all completed orders, cancelled orders etc.... and have summary data on each such as item counts, total costs total charges etc.... I have been able to do this using compound keys... the customer_id with -1 or -2 etc to separate the orders in the line item table to display in the customer profile view, but this sort of worries me from a schema design perspective (compound keys!!!) and makes me think I'm overlooking a feature of FM 8 that could achieve th…

I have a list of about 4,000 author names (authority list). When a new record is created in related db (book catalog)I would like to check the author name entry against the authority list to see if there is a match. i also need to see any similar names if there is not a match and be able to choose one of those to replace the author name or add the current author name to that list. I assume this is possible with a portal structure but I cant figure out how to list multiple similar names (based on last name). I can only get one portal row to show up using the lookup value [if no match..go next]. I would like to list 5 other possibilities (the next 5 author names)

Description: Hello, I am having difficulties creating a db that will allow people to sigh up for different courses. It looks really simple - one field where you put your name and one drop-down menu with the courses. I cannot figure out how I can make a course unavailable after a certain number of people sigh up for it. Do you have any ideas or do you have something ready that I can adapt? Thank you!

Hello FM Community, My name is will and I've recently switched to FM Pro 8, I work in marketing and need to manage thousands of contacts and emails. I made a contact databse and then found the free Email Campaign database on the Filemaker Pro website. I've attempted to combine my work with theirs and have accomplished everything except one last detail: In the section of the database called "Mail Room", in the "Groups" tab, what I need to be able to do when adding members to new group is pull data from "A Search" in the "Contacts" table. I want to be able to Perfrom a search for specific contacts and when I go the add contacts to a group it displays only the c…

I somehow had copied my table and the new on was named Ante 2.. How can I revert back to my original copy calle Ante? Thanks

It is clear that I still do not get the relationship thing (or I don't get the way FileMaker Pro does them). It is a simple finances database. It has a primary expenses table and 2 other tables that are connected to it: vendors and accounts. Everything works ok except the relationship between the expenses and accounts tables. When a new expense is created an account has to be selected from a popup menu (accounts table) this indicates where the money came from for the expense. The account numbers are not very intuitive so I included the name of the institution as the second field in the popup, this is where the trouble comes in. Some of the accounts are from…

Greetings all, I am brand spank'n new to FM, I have searched the forums, done some reading etc, and have not found an answer to my question. I'm quite sure its a simple problem and I am missing something very basic, but I'm missing it nonetheless. Sorry if I'm asking a stupid question or posting to the wrong forum. I am setting up a small equipment rental system for a TV production company. The database includes several types of equipment: camera, lights, tripods etc. I am trying to set up a database in which I can add a show's production ID number in a field for the gear in question, search by that ID number and print out an inventory report of the equipment …

Its ironic the second I get half to work the other half craps out on me....ehhhhhhhh Used to have three tables related by last names: Main : Last Name Properties Last Name DailyRequest : Last Name Ran into a snag with multiple similar last names So I added some more stuff and another table Main : _ClientID OWNERSHIP : __OwnershipID Properties : _EiderID DailyRequest : _DRID My main & prop. tables are referecned through the ownership table using a script. Eliminating the last name relationship has ruined two things for me: 1) the daily requests has to be chosen by last name - client calls in its the first thing you hear -…

Okay I've got ONE database with the following fields: [Theatre] [MovieName] [Date] [Gross] I would like to create a report that looks like this: [Theatre] [MovieName] [FRIGross] [satGross] [sunGross].. (etc) There's multiple theatres, so the report should group it by that first, then there's mutiple MovieNames and the corresponding days and grosses. Here's some real data so you can see: [FRI] [sAT] [sUN] ...etc [sUNSET 5 theatre] [batman Begins] [$125] [$450] [$325] ...etc [The Thing] [$45] [$55] [$100] ...etc [MONICA 4 theatre] [Capone] [$152] [$789] [$999]...etc [Men…

Attempting to do the following and not sure how to proceed. I have several 2-way radios that are programmed differently. Channel 1 may be Dispatch on one radio where the other may be channel 2 for dispatch. Each radio has over 50 channels. I would like to use tabs to show channels 1-25 in one tab and 26-50 in the other. I have a table with the frequencies I will be using. Another table has the radio information (name, serial # etc). Obviously there is no common link between the two tables. Is there a way, in the tab, I can click on a filed called channel 1 and the list of frequencies pops up in a menu or list to be selected, then go to channel 2 and …

Hey there, I work in the entertainment industry. I've got a database setup with a Client Table, which books shows in a show Table. This relationship is well established, no problem there. Now, I have a new company under our umbrella. I would like to set up a Client Table of it's own, so as to not mix up each others clients. That being said, all operations are done by us now, so I don't want to have two show tables. How can I get both Client tables filtering into one Show Table? I.e. New Client table creating new records in the show table Any suggestions would be much appreciated!!!

I have a rellationship to show a field in a value list from only some of all records in a table. If I tell the valuelist to show all values (7400) it goes fast, if I show the values from a limited rellationship then it goes dead slow, slow like we can go and take a coffe break wile we wait, and thats is just 3800 values! How can I make this faster?? Plz need help!! Regards Jocke

Hello, I created a separate database that lists job information. It has a General Industry field, a Sub Industry field and a job title field. I use this to create value lists which change according to the previous value. So if you choose Music as your general industry, the sub industry only shows the sub industries for music, etc. When I use it in a relationship as a separate file, it works but inconsistently and sometimes doesn't show the right values, or shows more than the right values. Frankly it just goes wonky. When I import the data into the main database and have the data internal it works fine. Any ideas? Additionally, if I have two d…

Hi, Is there a way to automatically enter data in fields of a related table when you create a new record in a table it's linked to? I have two tables--the main "data" table, and its related "lock" table with fields for whether the data record is new, imported, exported, or locked. When I create a new record in the data table, I want a new record created in the lock table at the same time, with initial values of locked being false. I have the locked field placed on the data layout, but the value is empty when a new record is created. It's a one-to-one relationship, and "allow creation of records" is checked. Any ideas? Thanks, Matt masterlock.zip

I am trying to build a database which stores information about three levels of items used in our company... Raw Components - These can be used in multiple Assembled Parts - This layout needs to include a portal to display a list of Assembled Parts which use this Raw Component Assembled Parts - These can be used in multiple Packages - These can include multiple Raw Components - This layout needs to include a portal to allow data entry of the Raw Components used in this Assembled Part - This layout needs to include a portal to display a list of Packages which use this Assembled Part Packages - These can include multiple Assembled Parts …

Hello all. So, I was told in the define fields forum that I was crazy to try and do what I need to do without using relationships. What I am trying to do: I have an evaluation database that records survey results for differrent projects, pre and post completion. I foolishly created the survey answer fields in words, so I have now made calculation fields defining the words as numbers. What I need to do now is the entire point of the database, and I can't figure it out. My director is breathing down my neck to get reports of the data- finding sets of records and doing percentages of the found set for each response. How do I do this? I was told I need t…

I have a dream that one day, my place of business will have a fabulous scheduling database that will do most of the work for them. Unfortunately, I'm not sure how to accomplish this task. Here's my vision: There's a table that keeps employee availability. In this table, there are fourteen variables: one for am and one for pm for each day of the week. And these fields hold "Yes" if the employee is available, and, of course, "no" if the employee is not. Then there's another table that handles rotations of running shifts, patios shifts, etc. But I'm not even close to worrying about that right now. The actual schedule, I was thinking, would have a list down the left side…

I don't know exactly what's going on,but I made a portal just so I could make sure things are going the way I'd like them to, and it's showing related records that do not exist. Ex: On a layout showing values from table A, there is a portal showing related values from B. These are B1, B2, and B3. But when I look on The layout showing B's values, they don't exist. I even deleted all records from table B, and they still show up. Then i put a butotn in the potal that says "Go to Related Record", and it pulls up table B's layout with nothing in it, showing 0 records. What on Earth is going on here?!?!?! Amber

I want to concatenate name fields from related records into a single field. I have a table named “client” and a related table name “relations”. In the client table I want to concatenate names from related records in the relations table. For example I have 3 related records from the relation table: first record is Bill Smith, second record is Jim Jones, and third record is Steve Jackson. I want a field in the client table to read: Bill Smith; Jim Jones; Steve Jackson. I can get the first record in the relationship to display but how do I get the formula to look at the second, then the third etc?

I would like to know if ther is a faster way to get to the last field in a TO. I am setting up relationship with fields that fall at the bottom of the list. Scrolling down thru 500+ field names is a killer! Sorting by name, creation order, etc. doesn't change the location by much. The little up/down arrows move very slowly. Anyone come up with a faster way to get there?

Greetings All! For a few years now I've worked with a database that included photos of the part numbers, and in this case I use a filenaming software to batch change the item names before importing so that they would not have the file extensions (like .jpg), so that the db would link the photo and display it in the layouts. Now I have a new set of images (new file) to import into an entirely new field, and was wondering if I could link (relate) the photos and still keep the file extensions... My first try at a solution was to create a field to relate to that was a calculation that removed the extension from the filename, but what I'm running into is that some of…

Hi, I have a simple problem, that I just can't find out where I'm going wrong. I have a working portal on a layout, which has a Global field matching a Calc-Field, which returns the first letter of a name. I set a letter of the alphabet from a drop-down on the Global and all my contacts whose name starts with that letter show up in the Portal. Cool. Now I want to change it. I have made a second global field, into which I put, via a script, a value of 1,2 or 3. I have changed my old Calc Field and depending on whether the value of the new GlobalField is 1,2 or 3, it will now return either the first letter of the first-name, second-name or Company…

I still don't fully understand the relationship setups and a couple of things are giving me fits! I'm creating a performance assessment database. Aside from self assessments, each employee may have assessments from their Team Lead(s), Peer(s) and Supervisor. For the supervisor relationship, I used a self-join to the Employees table (straight out of Filemaker 7 Special Edition:-). That works fine; I can show the employee's supervisor on his record and I can also show all of the people the employee supervises via a portal. The problem is with the Team Leads(TL) and Peers (many to many). The supervisor looks at each of his personnel and decides if peer inputs and tea…

I have people and organizations, and I need to be able to pull up a person and find out what orgs they're with and pull up the orgs to find out what people are members. The part I can't figure out easily: I want to be able to directly add organizational affiliations to individuals, by name, from organizations previously listed in the DB. Also vicea versa, adding directories of individual members to records of organizations. It'd be great to have a value list displayed when trying to add an affiliation to the portal (i.e., pre-existing choices of orgs and individuals) too!

Hi, I have a huge (2000) international and asian film database in english. My information comes several sources in different languages set. It gives me most of the data I need. Example TITLE, DIRECTOR, RUNTIME, ecc. ecc. I created another database for the various talents involved in the production. (Creative, Actors, Crew). This second database has the following fields: International Names (without the variants for vowels and or consonants) Original Name (as it is spelled in the Language of the artists) Korean translation Japanese translation Traditional Chinese translation Simplified Chinese translation This becauase people's name ar…

Hi there all, Let me start by saying that I am VERY new to FM. I am trying to get a database working for my company but I am having a few troubles. I have uploaded the database for you all to have a look at. (I had to remove graphics and nice buttons to make it into an upload-able size). My main problem comes when trying to calculate values from two tables. I have a 'company expenses' table and a 'contact management' table (the latter actually houses all the clients invoice details too). what I need to do is deduct the total of the 'company expenses' table from a total figure that is summed up in the 'contact management table'. It seems…

This is my first post. I hope I am sending it to the right forum. Please excuse me if not and direct me to the correct one. I am upgrading from 5.5 to 8 and I am a little bit lost (I have no experience with version 7 or 8). My 5.5 database consist of 45 files, and quite a few relationships, fields and scripts. I have decided to rewrite it from scratch to version 8, given the complexity of my current database. I use a Windows version, windows domain, 30+ network users. Main database files are: Companies (the headquarter or heading company) Clients Contacts Address Phone (includes email phone, fax, etc.) Tender Client line items Project I…

Hi, I have 2 tables with the following relationship: globals::cities = camps::cities If I put 'springfield' in globals::cities, all the camps that are located in springfield appear in a 'camps' portal, which is beautiful. The problem is this: a lot of camps records don't have a city entered. The field is left blank. How can I make those records appear in the portal? Thanks, Moses

I have a PROJECTS db that contains layouts for every type of document I use during a project, particularly contracts & change orders. Now, each contractor in a project has one CONTRACT record in the CONTRACT layout, and then will have multiple CHANGE ORDER records afterwards in the CHANGE ORDER layout. So, on my home screen when I select NEW CHANGE ORDER I am taken to the CHANGE ORDER layout and a CONSTRAIN FOUND SET script step isolates all records with rec_type CHANGE ORDER. Now, when I select a contractor in a new CHANGE ORDER record I would like the contractor's CONTRACT data (from another record that is omitted via the constrain script step) to be auto-enter…

I'm learning about data modeling right now and was wondering, Could any of you share with me... what is your real-world approach to normalization and de-normalization ? When do you know you need to normalize and when is it not worth the effort ? And, can anyone give me an example of when they might need to denormalize : thanks in advance for any real-world insight you can offer.

Stock Inventory database Inventory Table Purchase Order Items Table Purcahse Order Table When I create a Purchase Order to add items to our inventory, I want the Unit Price to update our Inventory Unit price with a Lookup. The reason for the Lookup is so we can manually change the inventory price if we want to. The Lookup by its nature takes the first occurrance, which is NOT what I want, since I want the latest price reflected. So, in FileMaker Pro 8, I have a relationship based on Inventory ID and Date. Inventory Table - Req Item Table InventoryID = InventoryID AND Date Today = Purchase Date is there something about the…

This is my first post, so, please be kind. I have a FMP 7 database codebook layout which has 2000 records each containing six fields. I want to create a new layout where I have only 5 records which are lists of SOME of the data from about 20 of the codebook records. So, the new layout would have a single record (the list) where I can enter a field value which identifies one of the 2000 codebook records and then it would display other related field values from that codebook record. And then, I want to do the same thing again with a DIFFERENT codebook record but still in the same new LIST record. I thought I needed a portal to do this, but I can only …

I know I've posted here alot in the last couple of days, and so i just thought I'd post this screen shot of the relationship graph for the project I've been working on. Keep in mind, this is only 3/4 of the way done. : Amber

Hi, I have a database with two tables called "patients" and "sequences" which are indirectly related via another table called "visits". Patients may make one or more visits, and provide one or more sequences on each visit. Each sequence is unique and can only come from one patient. Sequences can be of types A, B and C. Problem: I would like to have a standing field in the patients database that keeps track of how many sequences of each type this patient has in the sequence table. A user would then be able to quickly scan through patients and see at a glance that a given patient has 10 As, 5 Bs, and 0 Cs, for example. I originally posted this problem in t…

Hi. I'm trying to model something and haven't been able to find discussions about similar situations to study. Maybe I don't know enough terminology to realize that, so would appreciate anything that points me in the right direction. The best way to describe my problem is "six degrees of seperation" where one person knows another person, who knows another person, and so on. Person A and Person N are seemingly unrelated, other than the 1:1 relationships of a large number of intermediate people in between. My prototype solution involves a script that searches for the individual links and assigns "string IDs" to the individual person records. I can go into more de…

Relationships are a new concept for me, and I have just returned my FM 7 book in hopes of an FM8 release so, please bear with me... I have created a database for guest lecturers. I have 4 tables: Guest Lecturers (Contact Information) Seminars Articles Correspondence It is a one to many relationship, so one guest lecuturer can have many seminars, many articles and many correspondences. I just noticed that when I search for a guest lecturer, the seminars, articles, and correspondences do not match or change depending on the search. Would it be possible for the search to be conducted over all layouts (tables), so that when I search for a…

Hi all, This is probably not too tricky but I just can't get my head around how to do it. I have a database that contains stock quantities and sales data for many different products over about 25 different branches of a company. The fields are Product ID, Branch, Quantity in stock etc. The problem is its quite unwieldy to look at like this - I want an overview of the different stock levels in the different branches. So - I want a new table that only has one line per product rather than 25 lines per product (for each branch). Across the screen will be stock levels etc. in each branch. The way I've tried it (and it didn't work!), I've created a product-code-branch key i…

Hello! I have a table Searchword that have Searchwords in Swedish and English (they posts are marked with "1" for Swedish and "2" for English) I whant to show only the Swedish Searchwords, how do I do that? The word on Swedish and English have the same idnr so that I can change language easy so the idnr is no help.. I guess some kind of self join? I tryed with a global variable that held the language id I wanted to show but I couldnt make it work.. In SQL I would say: SELECT * FROM Searchword WERE LanguageID = "1" Soooo simple but not in Filemaker?? Any tips? Regards Jocke

what's the easiest way to make a relational database that can work on OR instead of just AND?? ex: am = am OR pm = pm is this possible? thanks! amber

I am upgrading from FMP 6 to 8, and have not used tables before, just related files. What is the criteria for deciding just how many files to create, and how many tables within them ? I am re-designing my solution (which I have used for years) from scratch, and want to make it right from the get-go.

I have an appt program that was recently upgraded from FMP6 to FMP7. Since the upgrade, the calculation of Initial and Follow-up appt times does not work. in FMP6, whenever an Initial or Follow-up appointment is scheduled, FileMaker is creating an entry in either the Iappointments or Fappointments table. Part of the data in each of these records is the InitialDate or FollowDate, along with the PatientRecord number. IN FMP7, the unit of this program that seems to be failing is the MatchFieldForShowInfo.c field. A patient whose record# is “10-3157” and schedules an appointment on “08-09-2005” should have a resulting MatchFieldForShowInfo.c field calculated…

I'm having a little difficulty trying to get something to work here. What I want to do is to perform a Find on a certain table and then switch to a different related table, and constrain that found set of records further. The problem is that when I switch to the other table, it shows all records, and not just the ones in my found set from the first table. Does anyone know of a quick and easy way to switch layouts to a new table and only view the records in your current found set? Thanks!

Hello Is there an (easy) way of transferring records from a non-relational database to a relational database? In the old database I have Inquiries, Applicants & Enrollments all in one database on one layout. In the new database I have Inquiries & Applicants as one and Enrollments as another. I have transferred the Inquiries & Applicants, as well I have transferred the Inquiry information for the enrollments, but now I need to get the extra information into the enrollments layout and have it match the inquiries layout. How can I do this? When I import it doesn't allow me to match related fields? Thanks!

I'd like to build in some functionality that will automatically generate a follow-up letter to clients based on a follow-up date. The amount of days after the date in question will vary. In some cases the follow-ups must happen at regular intervals, (i.e. once a month or once a year). Could someone recommend an elegant & simple way to implement something like this ? It's possible I might want some printed and some emailed.. but if that makes it too complicated, I could live with them all being printed, since many of the clients are older and don't yet use email. (but if it's easy to implement, I'd be all for it) I'm especially wondering how the n…

Hi there, I'm pretty new to relational databases, and databases in general, so bear with me. I'm attempting to create a relational database that deals with the progression from prospective student to student. I have one table for inquiries and another for enrollment. I'm currently running off a database that has no relational aspects so all inquiries have all the information and layout pages that enrollments get and I don't wish this to be so. I've figured out how to do one to many relationships via a book, but it said one to one relationships are rare and therefore didn't go into detail on them. I need a one to one because one inquiry cannot hav…

I studied Matt Petrowskies video on his filemaker magazine site Contacts,Companies, Details. I want to use his internal separation model to build a contact manager but am not sure this changes things with the new Filemaker 8.0 Advanced. He has a separate interface table, with tables for companies, contacts, companyDetails and contactDetails in his sample file. As I understand this video, the reason for the interface table is to control the menu options that a user has available to him in other words get rid of all menu options especially delete and replace. If this is the case, then with Filemaker 8.0 advanced does that mean you no longer need a separate int…