Relationships

I am trying to make a relationship where I can select a State in a field and then it will prompt in the following field showing only the applicable Counties for that State. I know it should be simple but... StaeCountyCoverageTEST.zip

Hi, i've this problem. I've made an invoice db, with automatic calculation of multi-payment (example, first part after 30 days, second after 60d, third after 90 days). The 3 part calculation is not a problem. What i can't do is a new table, with the list of all my payment, so for the invoice number x with 3 payment, i wanna 3 line(record) in this new table. How can i make an automatic record creation in this new table, when i set the number of payments in my invoice? Thanks in advance

Hi, Just trying to get my head round relationships, have purchased the missing manual, read the white paper and looked at the FM8.5 tutorial. The missing manual is quite specific regarding the use of many to many relationships and suggests that you always use a link table. Curiously the white paper & FM8.5 tutorial both show examples of tables that are linked by many to many relationships. Is there ever a situation when joing two tables together by this method is correct ?. I have a feeling I will be spending a lot of time on these forums ! Cheers

Hello folks, I have a table called Contacts, here I store companys and persons an they are related to eachother trough self-join relationship. I have created a second table called functions. Now I want to add functions. EG: Person A works at company B as a secretary but she has also the function of Director at company C. In my company layout I have a portal showing the empolyees. How can I show the differnt functions of the same empolyee in the different company records? I hope somebody can help .... I'm desperate!

How do I dublicate fields from 1 table to a new blank table?

How do you enforce at the table level uniqueness over tow fields in the same table? The Unique check box under the Validation tab is for the current field. But I need it to be over two fields.

Hi, I have a database that includes the following: 1 Project table 1 Line1 table 1 Line2 table The relationships are as follows: - Every project has at least one Line1 and one Line2 items, but may have many more than one. - Any given Line1 item can only be part of one project (only one project ID). - Any given Line2 item can only be part of one project (only one project ID). - Line1 items and Line2 items are, therefore, related among themselves by the project ID. - Line1 items are related to Line2 items as a many to many relationship. - The total cost of an specific project is roughly Total Line1 cost (what the client pays for the service…

LineItem table is basically a joint table between Invoice and Inventory table. OK I am happy with that. I know how to link the 3 tables together. What I would like to know is where does the data "price", "quantity", "subtotal", ""date of creation of invoice" "total" from the layout go? Do they go in the LineItem or Invoice Table and why? Does the invoice table only have these fields-> Invoice_ID(PK), Customer_ID(FK) and LineItem_ID(FK)

Hi I am having real trouble with getting my head round how to structure the relationships between tables for the following. Background: Need to implement a purchase order feature. I've created purchase orders, products and suppliers tables. Requirement: The user selects a supplier on the purchase order layout. Then the user selects a product (not all suppliers supply the same products). So far so good, I have managed to implement this. Now when the product is selected, I want the unit price (and other fields) to default to the price that supplies it. The user can type over if they wish, but at least the default price is there. This I have …

I have a script that checks field validations in a portal from a related table. The script needs to check if there are not any related records, so it can skip the validation checks if this is the case I had been using a 'Go To Related Record' script step and checking the LastError result to see if there are not any related records. Is there a more standard way or stable way? Perhaps a field in the master table with a RecordCount of related recs?

I'm a newbie, hopefully posting in the right place... Im a longtime user of FM4, recently upgraded to FM8 and failing completely to make a relationship work. For background, I have a table that is built in the tradition of FM4 with many fields and several layouts. I have tried to create a new table "TALENT" that I want to populate with all the data listed in the original "DATABASE". I went to the graphing tool and made all the fields that are the same "=" and expected "TALENT" to be magically populated like it was another layout. Clearly it wasn't. Then, I made just the field I wanted to be the match field, "Job media" "=" "job Media", tried typing an ex…

I have a pets database. Table: Cats Fields: Owner, Cat1, Cat2 Table: Dogs Fields: Owner, Dog1, Dog2 I have created a relationship between the owner fields of the cats and dogs table. I have a button on Cats layout that when clicked goes to the Dogs layout. Let's say I type "Liz" into the owner field on the cats layout and then "fluffy" and "scruffy" under Cat1 and Cat2 respectively. When I click the button, I would like the Dogs layout to show "Liz" and immediately start entering Liz' dogs. However, all I get is a blank record under dogs. How do I make this so that when I change layout from cats to dogs, the owner field in the cats table is automat…

Here's my problem. I have made a small accounting database. I have tables: customers (which also includes vendors), invoices (with line items), received invoices from vendors (with line items) and table with payments (what I pay to vendors and what customers pay to me). I'm trying to create a report which will show me a list of all invoices, received invoices and payments for a selected customer sorted by date. At the end of report there should be a totals of all coloumns on report (except dates) English is not my main language, so I hope I wrote what was in my mind. Thanks in advance for any kind of help.

I'm trying to input information in a field based on values in 2 different fields (ie. If this and that then set field to this). Each of the fields is in a related table, but not necessarily related to the current record (the information is probably in the database, but may not be in a related record). Any ideas?

I am a novice FMP user building a simple film production database. So far I have most of the database working how I would like. It has tables for CODEBOOK, SHOTCARD, IMAGES, TAPES and SCENES. One function of the database which is not working as it should is the CODEBOOK image lookup. In the CODEBOOK every record has a 4 digit value like such as 0001 (this relates to the shot number and by the end of the shoot we will have numbers up to 0700 or so). e.g Shooting slate 43 would have 0043 in this field. The IMAGES table contains a JPG from that camera setup and has the same 4 digit values. Since the CODEBOOK can contain any number of records with the same 4 di…

Hi, I'm trying to create a count of pages in a footer that displays as "Page x of y" so that if there are 10 pages and page 2 is being previewed, it displays "Page 2 of 10." I've set up a calculation field called "PageNum" to Get(PageNumber) and another calculation field to Count(PageNum) but when I enter preview mode, all the pages display "Page 0 of 1." Any idea how this can be implemented correctlly? Thanks!

Hi, I have the following problem. A database of CLIENTS may have clients in the following situations: Prospectives Active Canceled Void What do you think it is the best way to handle this? 1.- Create a single CLIENT table and have several flag fields to identify each situation. For instance: flag_Prospective flag_Active flag_Cancelled flag_Void 2.- Create the following tables: CLIENT_PROSPECTIVES JOIN_CLIENTS CLIENT_ACTIVE The user will always start by creating a Prospective client. Whenever the client makes an order (or whatever else activating mechanism) then the user must move the client from prospective to active…

Every now and then I have users reporting that they cannot modify a certain record in a table because some other user is modifying it. However that user is not in the same record at all. The layout in which this is happening pulls information from a table that has only one record, and all the fields are global ones. Depending on that one record the rest of the information on the layout is pulled from other tables. I figured that since all fields are global a conflict where "someone elese is modifying this record" would not happen (unless two people are really trying to modify the exact same record). Question is how do I find out what causes the conflict? …

I have probably been thinking about this too long but I'd like some input. I am never sure of how "deep" to go with children; I don't want my data-separation to become too involved. When is too much too much? My current situation is thus: 1. I have three tables: CONTACTS, LOCKERS, and LOCKS. 2. A lock is assigned to a locker---one lock per locker. 3. Lockers can be assigned to people. So the correlation is CONTACTS < LOCKERS < LOCKS. The thing is there can be multiple keys for a lock (in the instance that more than one person is assigned to a locker and each person has their own key). In a situation like this, should I create a new…

I have a layout form, A, with several fields like the following; Restaurant Pax Rate Total 1. 2. . . 10. Each field is individual, meaning there is a total of 40 fields for the above example. The (restaurant field) is a dropdown valuelist from the next table, B. This table allows user to input the rate for each restaurant, so that the rate will be automatically fill-in when the user selects the restaurant in form A. For single record, i can simply relate both tables (restaurant field) and use the (rate field)from table B in form A, but for this case, i can't since each (rate field) in form A is unique, for account purposes. Anyone can enlig…

OK, I don't know if that's quite the way to put my question, but it sums it up. btw, I answered that I'm an "Intermediate" user of FMpro, but really I'm an Intermediate/Adv user of Access who's now switching over. I used FM a few times back when it was version 2 or so, but I've never had the $ to get a real mac til now. Anyhoo... I know how to create a simple database. But what I'm trying to do is more complex - at least for me. Since I think it's easier to work with specifics, this is what I'm trying to create: The main database is single-user to house all my "Outstanding Medical Debt", and I'm going to have the main table be Medical Facility Info (name of…

In my educational database I have the follow relationships between tables with one to many relationship from one table to another. It is like a chain. The Ierarchical tree of tables: Ministry Peripherals Administration Sub-administration Schools Classes Sub-classes Students There are also tables: Attendance School year Periods Teachers The problem I have in this database is that it cannot pass the current school year in all student records at once without to oblige the user to confirm the field in the record. Imagine the extra work someone has to do. Perhaps it needs a new database for every school year. Another p…

Greetings! I have four files related for a purchase order scheme; line items, orders, vendors and products. The whole thing works well except for one thing, and that is the vendor part number. Each vendor's part number is stored in the products file under their own field, and I can't figure out how to get FM (6 Dev. now, upgrading to 8.5 soon) to find the right field for line items file to make the print out and/or export for Excel. The match field in Line Items to Products is the company part number, and is used to get the field "product" (describing what the product is, such as "nail" or "clock"). My problem here is so many vendors and getting is to read from the…

Hey everyone! I've been trying to make this work for about two months no and still no luck. I am having relationship trouble and I mean the FileMaker kind. -P Anyway, I am creating a database to track the knowledge base of our employees. The goal is two have a list of the same software packages for each employee and a unique rating of 1 to 5 for each particular package per employee. Layouts: Software Package Layout: Used for entering new packages as they become available. Employee Layout: Enter employee info and list ALL the software packages from the other layout, then apply a rating to each one (possibly in a portal?) I currently have three tab…

Happy New year all! I've read the whitepaper for novices and went through the FM help & am designing my first database on paper before I create it. I have a table called ShiftSuper that contains the shift supervisors first name, last name and employment anniversary date. I want to be able to create a performance report for shift supervisors that will give them data on their performance in specific areas, such as overtime management. I have a table called OTMonitor that includes field such as pay period ending date, overtime worked by the shift supervisor department (as a whole) during the pay period and overtime worked by line staff for the payperiod. I also …

Right here's the dilemma. I have 3 tables... 1 with customer data in 1 with product data in 1 to log orders on What I need to do is the following.... When a button is pressed on the customer data table it, passes the customer data from that record to the orders table. I have tried several ways todo this, looked up values etc but not of which seem to work! ANybody got any ideas? I've racked my brain for several days over this and it's probably something simple, but I just can't seem to get it to work!

From my understanding relationships do not work correctly. Is this Correct? I'm trying plan for the best development of the db. thanks Nick

How can we relate six tables that have a link relation parent::child5::child4::child3::child2::child1 in order child1 can show directly fields from its parent tables? To explain more what a user write to parent or child 5 or 4 or 3 or 2 to be shown directly in child 1. A form shown elements from six tables. Pascal

For lack of a better term or phrase, I'll call my problem one of "hidden data" When viewing a record, a field may "look" empty but it turns out there is "something" in the field because the record shows up on a "find" when, based on the find criteria I have entered, the record should NOT show up. (example: The "find" criteria calls for every record that has data in "field x" -- but -- some records come up that don't have anything in "field x") When I suspect that the "find" results include a record that doesn't belong, I go back to the record, and even though the field looks empty, I delete all the spaces in the field again. This usually clears out whatever is…

I'm pretty new to working with FileMaker Pro 8.5 and am developing a database to track quality assurance and staff performance data. Examples of tables in the database include one that tracks a supervisor's completion rates on staff performance evaluations, another table that tracks the amount of time a case manager takes to write a service plan, a table for staff overtime, etc. I want to create a layout that generates an individual report for each manager's performance over a specific time frame. How can I take fields from these various tables and place them into one layout? I'm not sure if I would have to build a relationship between each table or not. Any advi…

Hello together, I notice a very strange behavior of Filemaker Pro 8.5 - or maybe I´m doing things I must not do: I store the user preferences in my solution in an own table. In this table every user has its own number. In the main table I define a global stored number field which containes the number of the current user. The relation between main- and user-table is defined over the global stored number (current) and the number of the user. When I try to access fields in the user-table the performance ist really bad. Especial over the Network I get the spinning wheel very often. Why is that like this? Is there an other way to work with user-defined values …

I hope you guys can help me with this. I have two databases, one is the master and the other is an archive database. I need to pull information from the archive database into the master database, however I am working in FileMaker 5.5 I know it is a pretty old system but this is what I am working with. What is the easiest way to link these two databases together?

I'm just wondering if it is possible to join a table from a remote host to a table on the local machine? Any help would be greatly appreciated! Greg

I took a filemaker class the other week, and I asked the teacher how he decided whether one database was appropriate to store any and all of his information (some related, some not) or several would do the trick. For example, in the relationships chapter of the FileMaker Pro 7 Bible, they have an invoices database that has fields that pull data from a seperate customer database and an inventory database through a relationship. In the book the invoices, customers and inventory are all seperate tables in seperate databases, but what is to say that they wouldn't be better off as seperate tables in the same database? Anyway, my teacher said that whenever he anticipated …

Background: My client runs a growing company with currently 3 departments. All departments will be using the solution. There will be more departments in the future. The client wants all the departments to share customer, product and stock data, but he wants them to have separate quote, invoicing and cash register access. He especially wants to ensure that serial numbers are unique and consecutive for each department, which leeds me to having them in separate tables. As it is, I have built some of the solution around 3 separate tables and sets of layouts for the various sections that have variable access and have set up a user authentication scheme to direct users…

Hi! 2 hours without finding an answer with Search. My problem is probably VERY simple, but I´m STUCK. I´m developing a telemarketing applikation for my own company. I need to export records from a file/table with PEOPLE and their ORGANISATION (=job) to a a PROJECT/CAMPAIN file/table which can have the same person many times belonging to different CAMPAINS. Each of my customers can have different CAMPAINs targeting different groups of people. I´ve tried to build this with relations but without success, because each PERSONs record needs a unique ID for the CAMPAIN record (where I keep my notes and keep track of all calls I?ve done), which he/she "belongs t…

Hi there, I have a portal of time events that are sorted earliest first by the portal. The records were created at different times so the record numbers and ids are not in succession. I'd like to create a relationship that is valid if there is more than a certain amount of time between two records. The reason behind this is that the portal is being used to create someone's daily agenda, and we can't have too much time between meetings. I don't want to script it, I want it to be a text field that shows "There is too much free time between these meetings" if the relationship is valid. Is there a way I can compare the end time of one portal row with t…

I am trying to link two files where my Master list file has a field called item # that is automatically filled using a field from another file called Memo labels. Here is how I am doing it: I open Master List and go to define database. I then go to specify table and then on the drop down arrow I click add file reference. I click on the Memo Labels file and then I get back a message that says "'Memo labels' is currently in use and could not be opened. If the file is shared, you can use the open Remote command to open the file on the network. (If you've opened the file before, check the Open Recent menu.)." I am able to open the file on the server, and the…

Hey All. I wanted to create a new DB for managing my companies time. I started by using the example DB Time Billing to get me going. I made a new report that groups the project together with broken down activities & time under each project. This all works fine, but the problem im having is that i want to do it for a certain user and not everyone. I'll do a find for my name and only see those records. When I run the report, I get everyones. Any idea on how to fix this problem? Thanks!

Hi, I am re-building a database originally created in FM5 in FM8.5. In the original file, records were always sorted by a numbering scheme that numerically showed records as being parents and children of each other. For Example (each line is a record, and each set of double digits divided by a "." represented a different type of record.) 01 01.01 01.01.01 01.01.02 01.02 01.02.01 02 02.01 02.01.01 etc. I could then sort by the above numbers and see visually which records belonged to what records by the simple fact of them being stacked like an outline. In my re-build I am taking advantage of multiple tables and creating a table for eac…

I have two tables: Contacts; Addresses When I am adding a new record on a layout from the contact table, I'd like to just tab into some fields from the addresses table. I thought I could use fields from both tables on a single layout, however it isn't working. Is there anything I could do to make an easy and painless data entry process? I could add a button/script that adds an address record, but then I'd have to jump to a new layout. Any suggestions are welcome. Thanks

I have a problem. I have some information by which I am explaining in terms of people. PLEASE REFER TO THE TABLE ATTACHED. My Viewing table has 7 fields seperated as condition X1 condition Y1 condition X2 condition Y2 condition X3 condition Y3 PEOPLE I am viewing X1,Y1 in a portal I am viewing X2,Y2 in a portal I am viewing X3,Y3 in a portal I have three different people listed in each record as per the above. Essentially there are all people and really I need to view them in one portal. How can I do this? Is there a relationship method that will allow me to transfer and view all of these on one portal? Without having to p…

The forums have been a great help to me in the past, I'm hoping they will again. I am creating a multiple table database. I have a table for employees and a table for funding memos. (and several other tables) I want to use information from the employees records to create funding memos for each employee. There will be many memos for each employee. For instance I'd like the name, title and employee number to auto fill on a new record in the memos table. I can't seem to accomplish this and I have been looking through the forums for a couple hours trying to find the solution. Thanks for any help.

Basically, I'm rebuilding the database for our entire company. And we have several offices located in other parts of the world. We are building IWP databases for these offices. But we only want them to see the items in our live inventory that we want them to see. Currently we have a field for example called "EMEA" in the inventory database for each record. This is set to "yes" - we have a variable on the other database that is set to "yes" That works, but as our office grows, this will be a horrible option if you add 5 or 6 more branches (we intend to add at least 1 more branch in the next year) Is there a way to have a field with check-boxes for each …

Yes, I just bought the "Using Filemaker 8" book, and will read the relationship section. But, in general just wanted to know if this is possible since me and a co-work dissagree. 3 different tables/records totally unrelated. 1. Inventory 2. Customer Contacts 3. Employee Time cards BTW, The inventory has nothing to with the customer contacts. I can made 3 different FM databases for these without a problem, but would really like to have one db with different layouts. But cant figure out a relationship to make them work. Having tons of global fields does"nt seem right. Not looking for specific answers...

I have what may be a simple question. I have a pretty complicated dbase going and I have a bunch of Join tables connecting many-to-many relationships. They work fine. In addition to using the 3 fields I need in a join , Join id, Table1 id and table2 id I used a bunch of fields to do lookups to put data in the join table from the other two tables. I then used these fields in portals throughout the dbase... only problem (i learn now) is that they don't change when someone changes data in the first tables. Should I have just used the three primary fields in the join table...and then accessed the other data via relationships? is that best practice? Was there any reason…

I have a couple FM files from pre 7 days. The files are related to each other but there are no portals in either file. What I would like to do is create a script in one file that will take me to the related record in the other file if there *IS* a related record. If there is *NO* related record then I want go just go to the file anyway and browse all records. Here is my script with all of the fluff removed: Set Error Capture On Go To Related Record If Get ( Last Error ) = 101 // no related record Open File ["The Other File"] End If When I run the script, if there is actually a related record it seems to locate it but the window never becomes …

Hi, My apologies in advance for the semi-stupid question. I am confused by FM implementation of "key" relationships. There does not seem to be a way in the relationship graph to create an "OR" relationship. For example: If I create a "many to many" relationship from a social security key and would like to find a related record on social security OR last name ...there does not seem to be a way to do that in the relationship graph. Am I missing something? ..is there a way to "trick" FM into doing OR relationships?...do I have to script this functionality? Thanks in Advanced

Hi, I don't know if this is the correct thing to do or not. It appears to work, but I'm wondering if it is a fragile technique or not. In one of my files I want to access data from table (Forms_Submissions), whilst in another table (Submissions_Release). The data is 2 tables away. I've made a calc field in the 'middle' table (Releases) which is simply = the field I want to access in the 1st table (Forms_Submissions). I then made a third relationship table and put the calc field from the second table, directly onto the layout. The data shows up as desired, but it seems an odd workaround. Is it a good way to do this? Grant

Hello forum, so a question. I have an expense table (z_expense ID) I have an employees table (employees ID) all is working fine i can populate the dropdown list from the other table.and it fills in the (firstame) and (last name) fields now to my problem. i also have a staff table (staff ID) what i would like is when i click expense ID, all the names would be listed (drop down) from both (employees ID) and (staff ID) so i can select my contact. been thinking but not learning fast enough at the moment. : thanks

Hi, I'm creating a database at the moment and can't figure out something...pretty sure it is related to a relationship and value list problem. My business is service oriented and we give the option to a customer to use our services during 1 or several weeks (max of 5 weeks). Each week has a different employee in charge of the customer and this information should be available for the customer. For example: W1, employee is John W2, employee is David W3, employee is Peter I have created a table for this called WEEKS. 2 fields : weeks are in a Refweek field employees are in a employees field I have done a portal in order to add or delet…

I have two seperate databases, "members" and "mailing" that are currently related to one another through a customer id field (this is so that all the contact info put into the mailing database will pop up into members). My coworkers are annoyed that whenever you work in one, you have to have the other open as a result of the relationship, so I want to try to do is merge the two. I was thinking of importing the "members" records into the mailing database and just creating a new layout for members in that database. But because my members use the fields on the mailing table, how do I import so that my members records just show up on my members layout? Does this sound feas…

Let we have three tables, Table one is groups that only have six records display six groups of costumers. There are two more tables one for subgroups and one for customers. One group may have one, two or three subgroups. A costumer belongs to one group and to one subgroup. For example a costumer may belongs to group A and to 1 subgroup called A1... Table 1 GroupID GroupName (there are only, a,b,c,d,e,f) Table 2 SubgroupID SubgroupName GroupForeignID Table 3 CostumerID First name Second name SubgroupForeignID GroupForeignID How can I do the best relationship in order the three tables work together in the right way? …

Ok maybe the solution is super simple, but I am stumped. Here is the situation (working in FMP6) I have 2 files--Stores and Maintenance Certifications of each store. We are tracking the water usage at each store so each store has it's own set of Maint. certs. The store db houses information about the store, manager, hours, dates of install and so on. The Maint. Certs database tracks any visit made to the store--either for monthly billing, emergency or QA visits. (we only bill on data from the Monthly visits) Each record in the "Stores" db has a unique recordID that links the "Maintenance Certs" to the individual store. Currently I have a la…

Filemaker beginner.... wondering if there's a way to easily integrate the functions of two templates into one database. For example, I'd like to use the Event Management template with the Email Campaign template so that the Contacts in Event Management can be accessed for our email campaign. Would also like to add in SeedCode Calendar. Would this all take a lot of serious in-depth design (which I can't do right now) or is there an easier way to merge all these functions into one database?

I have a question. I have a database with a count table, and a catalog table linked by barcode. When I enter a barcode in the count table, the item is looked up in the catalog table and entered (fields are: barcode, item, size, count, bin_number) with the exception of "count" and "barcode" all fields are looked up from the catalog based on the barcode relationship. Once I have entered all the counts, I have a subsummary report, sorted by bin number to give me the total number of items per barcode. Where I am running into trouble, is that I may have several different barcodes that for all intent and purposes, I would like to count the same. For instance, Cho…

I am new to this forum and if I have missed the solution to this issue in my search, I apologize. If you can point me to an existing reply which might help me or if you have ANY input, I would greatly appreciate it! ********** I am trying to create a one page report showing 6 different columns of student names by classroom number. I have a student table(with studentID), a classes table(with class no.) and a students by class table(with both studentID and class no.). I created the appropriate relationships among the tables and self-joins by classroom number within the studentsbyclass table. (Maybe self-joins are not the route I should take??) My problem is: If I p…

I need some help. At my last job I was introduced to a fully functioning 7-year compilation version of filemaker, customized perfectly for the company's needs. At my current job, I'm trying to implement filemaker as a scheduling/organizational tool completely from scratch. The core compontents are Clients, Jobs applied to Clients, and Vendors. Do I create a separate database for each of these and then link tables/fields within them together? I need them to pull information from each other and I'm not sure of the most efficient way to begin. Any help is greatly appreciated! Thanks!

Greetings to all...this is my first post to the FMForum...usual apologies and newbie bowing... I am putting together an archaeological excavation database for use in the field while excavating. So far so good...it is based upon a colleagues database created with MS Access, which, of course, doesnt nearly have the ability to store photos, images, etc. But, my problem right now is adapting a feature of the Access database to Filemaker. One thing that we need to be able to do is to relate particular archaeological features with each other. This is done by saying that feature A is above/below/same as/next to feature B. In Access there is a way to build a sort of…

In the scripts below "; ${...", why does that appear and other times, in similar applications, it doesn't? Set Field [ Trial Balance::Detail Acct; ${Trial Balance JE & Detail}:Detail Acct ] Set Field [ Trial Balance::Detail Amt; ${Trial Balance JE & Detail}:cSummary TB JE Detail ] Thanks for your help, Steve

If someone could please tell me what I'm doing it would be very much appreciated, apparently logic fails me today. The attached picture was what I came up with to try and track down where things were going wrong in my database. In the example I have two portals, the same relationship except one is sorted and the other is not (through relationship, not through the portal). The data in the portal is correct and exactly how it should be. Below the portals are the fields from the portal, like if I wanted to show the first record from the relationship (which is exactly what I want to do) without using a portal. The (first) vendor field in both portals is from the da…

I’d really appreciate some help please. I am starting out on rewriting my 8 year old property business database (diary/contacts/properties/tenancies/etc.) and unlike last time when I just dived in, this time I’ve decided to do it as properly as I can. I’m trying to get my head around the relationships and tables I need for the contacts element to function as I want it to and how contacts will relate to the other parts of the database. I need to relate certain contacts to properties, tenancies and to transactions (I aim to add double entry accountancy but after I’ve succeeded in the first bit). I will have an “individual” table and a “group” table. Groups might be…

Help please. I have two files related by product code. One is called product list and contains details about each product including an inventory total. Related to this is an invoice line items file which records sales. I want to reduce the qty on-hand by the quantity in the line item record, related by product code. I'm sure it's really simple but I can't get my head around it... Thanks

The title may not be accurate, as I may not have this set up optimally, but here goes: I have a workout DB that allows me to enter workouts (workouts contain exercises), meals (meals contain foods), and schedule the workouts and meals in a schedule table (links a meal or a workout to a schedule record with a date and time). An image below illustrates the relationships. Here's where I'm having trouble: I want to track my progress as far as weights and reps. This means a seperate "Instance" table for the workout items. When I add a workout to the schedule, the workout items instance portal should populate with the exercises in that workout simply by e…

Hello All. I Explain in a very simple way hope u understand my problem. (One thing I know all about Relationships And how much Fields Are using by me). Thats why I Tell my Problem in a very simple way So Here it is Let I have A Movie Name Field And Three Records In It. ( Want to send on RENT) A Issued on 1 DEC 2006 B Issued on 2 DEC 2006 C Issued on 3 DEC 2006 OK NOW So The Problems ARE: 1. A is already on rent, So How i control that When End User Again Write A in Field its Show Its already on Rent.(If I perform Unique Value Then Comes Another Porblem That is (See 2. Problem..) 2. The Anohter Problem is When A is Return Then The Previous Recor…

Is there any solution for creating dynamic table view? For example: Only these fields that are on the layout are present in the tables. But, what if I want to give someone a choice with fields to display in table view? List of choices taken from the value list. I thought about script making some fields "active" for table view or maybe deleting and adding fields. First probably is impossible to do, second one is a bad practice. Is there any solution apart from making multiple layouts with tables? Regards, Tom Thanks for response.

I am sure some of you have done this because it seems to be simple, i just don't know how to do it. I have a Contacts Managing template (for customers and their info) and a Purchase Orders Template. I would like to fill in the customer's info on the contacts management template and then somehow be able to select or click a button here and appear there type stuff to get the customer's info on the Purchase Order template. For example... On the Contacts Management template i have John Doe's address, company name and tel #s and I when I make a purchase order for him i would like that info to somehow get there. lol I am VERY confused with all of this stuff. Ca…

Hi, I question for u guys more experienced than me when it comes to filemaker and database. I have a database for which I'd like something fairly easy. For everyone to understand, I'll use an analogy: I have clients going to concerts. I have allocated tickets that cannot be alocated twice a same day. How do I make sure of this when I take my client's order ? Exemple: Concert is on Monday, T, W, TH, F, S and S 10 allocated tickets for each session (1 to 10). On M, 4 customers so allocation of 1,2,3,4 On T, 10 customers so allocation of 1 to 10. I dont want any mystake when allocating tickets (repetition). Hope I am clear. Thank…

I have built a relational database with one main-table and some 1-many and some many-many (via Join-tables) relationships from the main-table to other tables. Deleting a record from the main table works fine and deletes related records as expected. However a delete of one of the relational records can be performed without any warning or protest from Filemaker. You at least want to know if one or more main-table records are related to the record you want to delete. I come from the MSAccess-world and there you are warned if there are active relationships. So my question is: does FM have any mechanisme for auto detect or do I have to build a script to check for acti…

I need to be able to update the status of an order by getting information about all related portal records (basically children) pertaining to that order. Explanation: 1. I have two tables, Orders, and Bins. 2. One "Order" has many "Bin" items assigned to it. 3. Each Bin item has its own status (one of three: "Duplicating, Labeling, Complete"). 4. The Order itself also has a status ("Processing, Warehouse, Shipped"). My relationship OrBin_SerialNumber is: Orders::RECORDSERIAL_Number <===> Bin::RECORDSERIAL_NumberOrder Now, in order to update the status of the Order, I need to check the status of all the related Bin items to see …

Today I discovered one of my customers has a few orphan records. Any idea how they might have achieved this? I have cascading deletes from Customer to Service to (Service Areas A and Service Areas B ) in my main relationships but not in some of the others. (I don't remember why some are not flagged appropriately.) Would this inconsistency make the layout from which the parent is deleted relevant to whether or not the child gets deleted? Trouble with that theory is that the orphans are all new and the parents seem to have been deleted quite recently. Most of the layouts where parents are deleted include a portal using the main relationship and they are fair…

I have 20 related DBs in FM6 which I need to convert to FM8, joining them all into one file. The relationships b/w the different DBs in FM6 were established a variety of ways, usually by IDName, but in FM8 I know I need to create primary keys and foreign keys (arbitrary, auto-entered serial #s). For example - Each person has several related titles, but each title record is only associated to one person. So, in Table:People I have Field:pk_PplID and Field:fk_TitleID and in Table:Titles I have Field:pk_TitleID and Field:fk_PplID. When I join these tables in FM8, by relating People:pk_PplID to Titles:fk_PplID, FM automatically creates a many-to-many relationship and…

Hi everyone. I have stuck with a problem on displaying data in a portal from 4 different tables. This is sample scenario: I have 4 tables (cars) 1.merc, 2.toyota, 3.honda, 4.bmw. I want to group them in "folders". It means you can take specific model and assign to the appropriate folder (another table). It shouldn't be difficult, but I have stuck with it. Moreover, I should be able to add and delete cars to folders over the network. Apparently Replace function is not a good option for network database. Any suggestion very much appreciated. I attached sample file. It's just a quick guide to give an idea. category.fp7.zip

Hi, I am not sure where to post this, so sorry if its wrong. I have a database that have a table for companies, and students. The Students have a portal (from another, third table) which shows what companies they have worked for, the last company on the list is the company they are currently working for. I now want to use the name of the current company they are working for in another layout (a list of students) How do I retrieve the last comapony the student worked for?(Remember that ALL the work history of ALL the students are in one table, but my relationship is set up that only the specific student's working history is displayed in the record) Any ideas?

I am pursuing on a educational database recently. But I have ten days for now that I can not establish the appropriate relation between tables in order to have a layout with all the students and all the classes together, and the grades as well. I have put an attachement to show how I want exactly to be this database. Pascal GradesOfClassesLayout.zip

Hi forum, It is my first post in this forum and I am quite a newbie in Filemaker pro. I am willing to make a database (people) where I’ll be able to link different records from the same kind of layout. Ex: Philippe has 2 children Maureen and Kevin. I would like a table in Philippe’s record where I can add member of his family by pointing to other records (Maureen and Kevin) and having the name and age of the member in the table. Is such a thing possible? Could someone please give me an explanation or a link to a tutorial, manual or a template doing this thing? Thanks for any help Steven

I have two files, our "Master" file and our "Database" which are simplified examples of our database. The "Database" file has three fields: Item #, Design, and Color. The "Master" file has the following fields: Designer, Item #, Design, Color, and Paid. What I would like to happen is that when enter the Item # in the field within the "Master" file, it will auto enter the "Design" and the "Color" within the Master file from the corresponding "Design" and "color" fields in the "database" file. How do I accomplish this? Please see attached. FM.zip

I've been tasked to create a service call management database in which we can store clients data (multi-sites) and log maintenance calls for these sites should there be a problem. I am using a separate table to store the site address / equipment data for each client as each client has between 50 and 500 sites. Im having trouble working out to import data for each site into a call logging report of required, bascially so the database users dont have to type the address time and again. Any ideas - I've looked at using relationships but as yet cant get them to function correctly? Yes - I am a complete noob but we all have to start somewhere .

Hi there, as you will see in the attached example I have a database for a training company with layouts for Clients, Learners, Trainers, Courses etc. In the Client Layout there is a portal under the learners tab to show which learners have been employed by the current client. The same goes with the Learners Layout where there is a portal with the employment history of the learner. This works perfectly. Now I want to add the Courses the Learner attended in the same way. I tried to do it exactly as I did with the employment relationship, but it doesn't work. I would really appreciate it if somebody could check out my problem and tell me what it is I am doing wrong. Th…

Ok I have an invoicing system(with all the usual, lineItem, inventory, vendor, invoice tables) for a parts database that works great! We are part of an insitute so we have many departments, for eg. Graphics Design, Audio, Video etc). All departments have fixed cost for each service thy provide Graphic Design department for eg has the folowing cost: - Color Printing - Scanning - Laminating - etc And Audio department would have something like: - Cassette Digitising - Cassette to Cassette Dubbing - Cassette to CD Dubbing - etc 1) Firstly, Do I create a table for each department and link in to the lineItem table? 2) I want to a user to …

In the "Define Database" dialog box, under the "Relationships" tab, the heading above the graph diagram includes the words: "If a relationship is defined between 2 tables (even through another table), fields from one table can be accessed from the other." I think that's what I need to do but I can't figure out how. I want to show Field C from Table 1 on a layout that shows records from Table 3, without having to add Field C to Table 3. I already have the 2 match field relationships shown above. Is this possible?

I've been working with FMP for awhile now (on a relatively smiple level) but can't quite get my head around the difference or pros/cons of linking tables with direct relationships versus table occurences. Someone have a good explanation of the difference? Thanks.

Is there a way to remove and clean up all of the unused and blank file references I have in a file? I am talking about when I go to File - Define - File References, then when I look there are multiple extra lines with no data, and these represent other instances that should have been deleted. When I select and hit the delete button, nothing happens and these references do not go away. Maybe if I save a copy as compact?

Attached a sample for better clarification. Right now, all my records are entered through 'Amending Initial Booking' layout and into 'Whole Database' table. The 'Amended Initial Bookings Table' is related through the 'Group Reference' field to the 'Whole Database' and used to store the latest amended records by using last function. This is to display only the latest amendments while able to keep a backup incase of any contigency. Using 'Group Reference' as the primary key, there will also be another table called 'Double Entry Database', which will store any 'Group Reference' that will have a second arrival date. So right now, i will like to have a layout, …

I have a trouble to establish any relationship in Filemaker Pro 8.5. I think that I have done everything right. I have checked allow or delete tables via relationship. But the program deny to establish any relatioship in any files. I reinstalled the program but the problem still remains. Pascal

I hope someone has a template file that would get me started with the POS basic structure needed to do simple invoices where orders/invoices are created by calling on a products db. Ultimately I'd like to use barcode scanner to quickly scan all of a customers items into a "field" (type of field I don't know), hit enter and have FM populate all the name and price fields and total it up just like any simple invoice. We have standalone card and check terminals and really only need to try and code up FM for a glorified garage sale at our warehouse. I really hope to avoid hours of wasted time figuring out reinventing the wheel. Heck I'd be open to paying a fe…

[color:gray]Okay first post here hope it is in the correct place. I have FM8.5 and am loving it, i have semi-mastered design and summaries and calculations i am pleased with my self and like my new program. i have looked at the tutorials and bought a big book (using FM8 by love,lane and bowers and i realise i have a lot to learn. [color:blue]So to my problem i go and create a template "purchase orders" i then create another template "inventory" now when i create a new purchase order i would like to populate the price fields,description automatically, i can set the product ID up correctly as a drop down menu and can see all my items. But for the life of me i canno…

I have an invoice database that I suddenly need a lot more portals lines for printing. I can add the rows to the portal until it covers two page breaks but the print out (pdf or hardcopy) only has one page to it and shows only half the portal. help? Am I missing somethign obvious? FMP7 and OSXTiger

Hi Everyone, I've read through many posts on this site which reference the topic I'm about to revive, and the most closely related to my issue is Post#135019. I want to do a similar thing to what Ocean West wants to do, which is define interpersonal relationships, but I also want to know how to display those related people on the main page for just one person. And, I also don't understand the replies posted on that inquiry - too advanced for me. For example, if I'm looking at the record for Peter Brady, I want to see a list of the people who are attached to him in some way, and whatever information from their records that I want to. Since Peter's in a fami…

It's been a while since I used FM and I'm a bit rusty. I couldn't find this question in the forum anywhere, please help. The database I'm creating stores training sessions (including fields; "session ref" a serial No, "session name") Another database called trainers (including fields; "trainer ref" a serial No., "Trainer Name") And another database called Trainers Sessions list (including "session ref" and "trainer name") which holds a list of all the trainers at every session. Eg; Session Ref - Trainer Name. 1 - Cathrine Bell 1 - Simon Bowman 2 - Jason Horton Shows that Cathrine and Simon were at session 1, and Jason was at Session 2. When you …

I have an invoice file, line item file and customer account file. In the customer account file I have an "Account Balance" fieldd which is defined as Sum(Invoice::Balance). If a payment is made on the invoice and the balance changes, my "Account Balance" will not change automatically. It still shows the total prior to the payment. Any ideas what I can be doing wrong?

I need a script that will result in removing the contents of a portal row by clearing the field that created the relationship. I definitely do not want to delete the related record from it's table. I can navigate to the related record and copy the ID but have nowhere to go from there, or I can navigate to the ID field in the same table but I can't figure out how to get it to choose the repetition that corresponds to the record whose data was displayed in that particular row of the portal (self join). I see that the option to go to repetition number by a calculation is there, I just can't figure out what to put in the calculation, or if there's a better way. I imagin…

If there are set of records with field1 populated by a member of a value list. For example: data in field1 can be from a list of colors: blue, white, red, yellow and greeen. There are several records (more than the number of color choices) wherein the instance of any particular color could be more than one: record1, blue record2, blue record3, red, record4, yellow What would the relationship be that provides a portal readout, if you will, of the instance of the colors used - ie. I would like to avoid seeing a list (within the portal) of every instance of a color choice for all records in a found set, etc.: The portal would display: three lines o…

Hello, I have a database that is used for entering student results for various types of assessments/tests. There is one table with student data and 5 different tables with student assessment data. What I was hoping to do was show all results from the various assessment tables in the one layout. I want to know if this possible without using portals because I have no way of knowing how many of each type of assessment teachers will use. The student table has a key Stud_ID to the various assessment Tables. I need to pull out the results from each assessment table and put them on one layout (without portals) Is this possible? Thanks Darrel

I can get a design where adding data to a Portal in Table1 adds the data to Table2. However, Table2 requirements are that data once decided upon is more or less meant to be fixed. Accordingly, this requires creating portal records in a third table where adding data to a Portal in Table1 also adds the data to Table3 (where the data can be modified). I wonder how to best create a technique in terms of relationship structure for accomplishing this simple task assuming: Table1_ID (having a portal ) Table2_ID -- item1 Table3_ID -- item1 ? Thanks again in advance:)

Hello Everyone! I am pretty new to Filemaker- most of my knowledge is in Access- and I am having a difficult time creating a grouped by query which is very easy to do in Access. What I have is 2 tables each with 2 fields. Table: PartNumber Fields: PartNumber, Component Table: Alternates Fields: Component, Alternate I want to show all the records in the PartNumber table along with its components and any Alternates if applicable. For Example: Part1 Component1 Alternate1 Part1 Component1 Alternate2 Part1 Component2 Part2 Component1 Alternate1 Part2 Component2 Part2 Component3 Alternate1 Part2 C…

I am not sure if I've posted on the right forum but I hope someone can help me here. Basically I am trying to set some kind of tag within each record so that the correct price can be picked out based on what quantity the customer gets. Record 1 Description Field: Doll Price Field1: 1.00 Price Field1 unit of measure: EA Price Field2: 10.00 Price Field2 unit of measure: DZ (For this product 'doll', if the customer buys 1 the cost is 1.00, but if the customer buys a dozen the cost is 10.00) Record 2 Description Field: Volleyball Price Field1: 25.00 Price Field1 unit of measure: EA Price Field2: 140.00 Price Field2 unit of measure: 6 …

Hello, I have an invoicing database, with a related customer table. There is also a mailing list-type table that is used to track marketing contacts and send out targeted mailings to customers or potential customers. A third table holds information on authors. Any of the people in the three tables could become a customer that would have an invoice. Do I create duplicate entries in the customer table when people in either of the other tables buy something? If so, how would I keep their contact information up-to-date in both tables? Does it make sense to create one big table with all the fields from all three tables? In the other direction, customers could…

I was wondering if someone could draw an organizational chart for FM that shows how the different entities (tables, records,etc) are related. I am planning to overhaul my companies database and although I have my ideas about what is best, there are a few different approaches to making Files in this program (e.g. constant vs. cartesian) that I want some different perspectives on. I am very visual so I want to see how the ER designs you guys submit compare and could be used for our company ER Design. THANKS!!

I'm trying to print address type labels. I created a Layout using the custom measurement in the New Layout/ Labels dialog box. It looks about right when I use the Preview mode but when I print the doc it only prints the top left label. How do I print an A4 sheet of labels? Any wisdom greatly received..