Relationships

hi all, i came accross a strange situation today, it's been taking me quite some time to try to find out why, i hope someone could help.... i'm having a very basic portal that shows records in another db, i've got a date field as well as some other text fields in this portal. i tried to do a range search on the date field which returned correct results the on first search. problems started to happen when i tried to search for other date ranges (i know that they are all valid searches), FM returned my searches with a list of all the records in the portal and not just the found matched records. i tried this with other text fields which gave the same results, ie, full…

hello, Now I have 2 databases, one with members and one with payments. in the payment database I have a dropdown with all the names of the members to make the relation. but when I change a name (to correct a typo), the relationship is broken. I know I should link the two by ID, but i can't figure out how to do this with a dropdown with the names of the members instead of the ID. thx

First off thanks for reading the post. A while back I created a simple invoice system for the family retail business. The system is not designed to handle each transaction, rather it is intended to handle special orders and mail order items. I have learned about a few pitfalls in my original database and now I am looking to redesign the database with some more room to grow. In this solution I am looking to create a few tables including Customer, Inventory, Invoice, Distributor, and Line Item. I still intend for this program to not be used as the primary transaction solution for the retail operation. The business has an very large inventory (say 10,000 items). …

I have created an Inventory and Purchase Order Databases. The PO Database uses the records from the Inventory as a value list. What I would like to do is subtract the overall quantity listed in the inventory as I make purchase orders. So if I order 5 widgets, I subtract 5 widgets from the inventory. My solution ,and I want to check this, is the following: Create a Calculated Field in the Inventory Database that Totals the Sold Quantity from the PO Database. Then add a Calculation to the Quantity Field in the Inventory that would subtract the Initial Quantity from the Sold Quantity. Do you see any hickups, is there an easier way?

Hi everybody, Here is my problem today. Working on Mac OS 8.6 and FM 5.5. Actually I solved part of the problem while making this post, but I do not really undersand the process, so I keep posting the following. I have a Product File that holds 20,000 records in it. This product file holds some records as : Manufacturer Product Category Collection Product Designation Price. A Manufacturer have many collections, A collection can hold many categories. I wanted the user to filter the Product File choosing one of 2 different methods : 1st method : Choose a Manufacturer from list, then choose all categories attached to this manufacturer, t…

While modifying a solution, I discovered a bug that I couldn't chase down I had a relationship between two files via a constant field (iOne) for some reason the parent file couldn't reliably calculate Sum(FileB::iOne) I would have to click in to the field on the layout. I figured out that in the relationship there was a "sort" setup and the field used for the sort no longer existed. This prevented the field from correctly calculating & displaying. It would be nice if I delete a field that it would turn remove it from the sort list in a relationship and if it were the only field in the sort to uncheck the sort option.

What am I doing wrong? I have two files (Work Orders/related, Customer Feedback/master) that I am using over a network with cdml/html based pages. I have tried to create a relationship between them so I can pull the data out of the Work Orders file into the Customer Feedback file with out importing. After going through the steps to create a relationship all seems fine until I try to search for data in the Work Orders file from the Customer Feedback file. Even with the exact text no records are found. Can the guru's out there helpa newbie like myself? I have attached the files. FMP Help Me.zip

I have a relation between two files for example: Realation:Cust2order = Customers --> Orders (CustId <-> Orders::CustId) I want from the customers file to go to a related record in orders file, but a customer have more than one orders. How can I use in the Cust2order relation field key definition and the Orders file match key (OrderId) together with (CustId)so the script command "Go to related record" to have a succesful execution?

Hi people, I just don't understand why this relationship doesn't work when used in a portal and I try to create a new record through it: - myDatabase::cOne = myDatabase_LineItems::cOne cOne in both databases is a calculation that returns number 1. This is used to show all line items in all records. But everyttime I try to create a new record through the portal I get: "The relationship "foobar" is not valid and must be corrected before this field can be modified." I don't know why it's happening, I have a normal primary key field and two other text fields that are edited through the portal - hope someone can show me the light! Thanxs! …

Hi, When I create a new record in a line-item from the main db it will create a record (using a script) but it will not enter data from the masterfile. It does work when I go to another lay-out in the line-db where all the fieds are shown. I realize that i can only paste fields when they are in the layout (I do use setField alot though), but is there a way to work around this? Those line-db's should not be visible and when they are (by user action) I don't want to show the lay-out with all the fields. I tried to hide the lay-out from the menu so users cannot access it but then my script doesnt work : TIA JP

I'm a total newbie to FMP. I'm tackeling it to help out my wife who, over the years, has collected several tables all containing nearly identical information. I'm trying to set it up so she no longer has to keep opening 7 different databases, but just 1. The specifics: 7 databases: each of these are a library of videotapes of music videos. The databases are broken out by clients (Virgin Records, Lava Records, etc). The records contain nearly identical information between each database. And luckily, each videotape is given a unique alphanumeric number that is not duplicated between databases - which seems to me to make it an ideal key. The problem: Not …

Sorry if this message really belongs in one of the other forums. My college-student information tracking system includes (among lots of other categories of information) publications, posters, and oral presentations. For each of these three categories, I need to track such information as title, date, where, etc. The upper limit for the number of publications, posters, or oral presentations for any given student is indeterminate in advance since the number can continue increasing during the student's career. Some students could be quite prolific, depending upon their research project. I am keeping each of the above categories of information in its own, r…

Not sure if this can be done but if it can I know someone in this forum can answer. Is there a way to have a dynamic relationship. I want my main file to get related information from a different related file for each record. My main file has a record for each day and I have separate file for each day. The day file have 2 to 3 thousand records for each day, they are named by date yyyymmdd. Thanks Joe Cady

Hi, My problem looks like dynamic allocation of rows and columns for each record using a portal. Is that possible? It is easy to dynamically add new records (in a portal) just by having a relation to the other file. Fine. But can I avoid defining static number of header fields? This is my design. I have a Size Specification database where users will put some numbers for specific points of measurements. Each point of measurement will be in different line of my portal. Fine. Of course these measurements are phisically in second related file (relation one to many). Now there is my problem. There are different ranges of sizes for their measurements, example of portal:…

An FM application is often used more than once. For instance covering a different year, or covering a different subject using the same setup. So a set of files related to eachother can occur more than once on a computer, though stored in different folders with appropriate folder names covering the subject. At least that's what I intend. I am using Developer 5.5. I have some observations, and some questions. At first an observation. When opening a set of files belonging together, or in better words, when opening the master file, the related files are opened also, which can be seen in the Mac-Finder under 'Windows'. (Not the operating system Windows..the menu option..…

I have two databases, USER and CPU, related to each other using a field called CPUID. Here's the basic setup. USER database holds user info(last name, first name, etc.) while CPU databse holds cpu info(Model, speed, etc.). Each CPU record has a unique number which is stored in CPUID field. This is good and works fine. In the USER db I also created a CPUID field which I'm using to connect a user record to a cpu record. So the relationship is from CPUID to CPUID both ways. From the USER db, I go to the user record I want, then enter the unique number for the cpu record I want in the CPUID field, bringing up the information for the related record. In this way I ass…

When I use "open remote" to open a main file on a remote computer the 2 other related files can't be found even if they are stored on the same computer. I get a "file not found" error message, then i select "Host" button and click on the file that is related to the main file. I repeat this step for each related file, how can i correct this, what is wrong wtih my set-up? Thanks --Dan

How to I represent a two-deep one-to-many relationship? A good example is a database storing book information. Assume the relationship between library and book is one-to-many. Assume the relationship between book and chapter is one-to-many. How would I represent this information from the

Hi all, I've got a stinker, that I want to get worked out before I roll out my big DB and cut off my options for deep level changes... Here we go: I've got a database that stores classified ads, and a linked database that stores instances where those ads are to be printed (basically, combinations of date and location). Now, in the Ads database, I need to show a portal with every publication that they've scheduled any dates for. There's my problem. Every publication can from anywhere between 1 and infinite dates for a specific ad, and no 1 date is shared by every publication, so how can I make a portal that links to just the first instance of each publication for…

I could be getting some work soon using Filemaker and I am hoping someone could explain to me the trouble that can occur with Relational Databases. I have seen some posting regarding the issues with Portals and the trouble they cause. But I have not had any issues Pop up. When I created a data entry system for tracking video dubbing orders I created a main database (which contained the main information, title, dates, etc) with three relational databases( for pick-up, duplication, and delivery). My reasoning for doing this was that I needed to create records with several fields in the three different areas(Pickup, Duplication, and Delivery) and t…

I am providing a solution for an athletics club that requires me to enter an achieved time in file1 which will then lookup a corresponding number of points that tim equates to in file2. The problem is that the times in both files in many instances will not be EXACTLY the same. Suppose the following is true Points File (File2) Points Time (sec) 100 25.0 105 25.5 110 26.0 115 etc.. 26.5 ..... I need to enter say a time of 25.7sec and lookup the exact points value or if that does not exist the next higher value in File2 - in this case 110 points I need therefore to show 110 points in file1 But I cannot …

I know from the theory of relational databases, making loops in relations is not a good practice. I need to find our quickly how it is being done in a filemaker. This is what I want to achieve. Let's assume I have just three related databases: 1st with 2nd and 2nd with 3rd. Second file (called Blocks) has defined portal with records from first database (Grades) - one block has many grades. Now third database (Size Spec)will use records from Blocks (similar one to many relation). The thing is I also want to use data from the portal in Blocks. I know I can create a calculation field in Blocks copying a record from first database and then relate that field to third databas…

Greetings, I had to create a new simple db this morning to add to our db system running on FMP Server 5. So I created the file, moved it to the server, opened it on FMP Server, created a relationship from one of our old db to the new one and from the new one to the old db. Everything is fine on my computer but it shows a "File Missing" message in the related fields in our old db for everyone else ! How can I make the link properly so that it works for everybody ? Do I really have to close both files from the server and open them locally ? Can anyone explain exactly how FileMaker keeps track of which file it links to, where the file is and such...?…

My college-student information tracking system includes (among lots of other categories of information) publications, posters, and oral presentations. For each of these three categories, I need to track such information as title, date, where, etc. The upper limit for the number of publications, posters, or oral presentations for any given student is indeterminate in advance since the number can continue increasing during the student's career. Some students could be quite prolific, depending upon their research project. I am thinking of keeping each of the above categories of information in its own, related database so that the master is not overwhelmed with…

Hi Everyone! I'm not sure whether to post my problem here or in portals, because I'm not sure why I'm getting phantoms! Main dB is Services. Services is related to: Client on ClientID Enrolments on ClientID Payer on PayerID Each Enrolment is also assigned a PayerID (multikey). I have an Enrolments portal placed on a form in Services. It contains Enrolment::(various fields), Enrolment::PayerID and a Payer::cPayerName, which is a calc (text), stored. But, even though the Client only has one Enrolment, it shows two cPayerName calcs! The rest of the portal line (all Enrolment information) is blank. I tried deleting a blank row and nada! E…

I have to duplicate several files and redirect some of their links to other files. Is there a quicker way of doing this other than clicking on the links and changing them manually? Thanks for any help Oz

How do you set a portal in Clients File to show all Quotes, Orders, Invoices, for this single client? Do I need three portals ? Is there a way to summarize through the line items ? Here is the method, rather complicated I use. In Line Items c_ClientInvoice = Client_ID & Invoice N

Everything's been fine with a relationship I've created -- one file ("Install.fp5") supplies information to a number of files. Now, when I try to open one of the related files, I get the error message "Install.fp5 is currently in use and could not be opened. The file is single-user, or the host could not be found on the network." Install.fp5 is not single user, and resides on my own computer. When I try to select the file, I get the same message over and over. However, if I open the suite of files in a different order, the relationship works. What could be happening? Also, I'm experiencing a script slowdown that I feel might be related to a relationship that is…

I have an Invoice file containing batch numbers. The Invoice.fp5 file might contain: 10 records where BatchID = 5001 16 records where BatchID = 5002 13 records where BatchID = 5003 I'm having a hard time creating a portal in my Main.fp5 that just shows the individual Batch information (in this example just 3 rows in the portal). Any suggestions?

Hi folks, does anyone know how to set fields & relationships for pulling data through multiple tables? A brief example of what I am trying to do is below. Say you have the following tables... Company Contact Orders(ie Invoices) Line_Items ...and each table is related to the next up the chain, for example... Company Table Company_ID Contact Table Contact_ID Company_ID Orders Table Order_ID Contact_ID Line_Items Table Line_Item_ID Order_ID Now, in FileMaker, it is easy to find, say, all contacts at a given company, but what happens if you want to find all line items for the company, p…

I'm an awfully new user of FMPro using 5.5 on a Mac (Os 9.2), and I'm wondering whether I might receive some advice to get my foot in the door -- I think I'm missing something fundamental either about design or FM capabilities, and I would very much appreciate any insights. My question: in one database, I have a series of fields used to identify components in each of four different processes (in this case, primers used in specific polymerase chain reactions; they're analogous to ingredients in a soup). Thus, the user can enter 'onions' for 'Soup 1', 'beef' for 'Soup 2', etc., up to Soup 4. The ingredients are selected by the user from a pulldown menu containing a …

Dear All, I have 2 databases A and B, In A fd_A = primary key (Data: "Hello") so I set relationship From A to B ( Fd_A = Fd_ so Data in B is "Hello". Now I want to change data in A , it means now in fd_A = "Hello Mama" how can In B fd_B = "Hello Mama" right away? Since This problem happens many times, is there a way to achieve it so that we don't retype it or we don't lost data??? Thanks All

I have 3 files which are Vehicle, Products and Fees. The first file is the Vehicle file which consists of 5 fields. They are Vehicle ID, Category, Type, Make and Model. They make up a conditional value list in the Products File. Category from this file also populates a field in the Fees file. The Second file is the Products file which includes the conditional value list described above along with the Customer ID number. The Third file is the Fees file which consists of 4 fields which include the Fee Id, Customer ID, Category (which is populated by the Vehicle file), and Fee. Now the Fee is dependent upon the Customer ID and Category. (The Customer I…

I hope my icon expresses frustration! I have a file with a bunch of self-relates. When you are in the current record, there is also a portal below which lists other records from the file that are related. If a row in the portal is clicked, then this should become the current record. I set it up by: Set Field(gSelectedRecord,Portal Rlnship::Record ID) Go to Related Record(Show,"Current Record") where Current Record was defined as gSelectedRecord = Record ID. When clicking the row, sometimes this would work and sometimes it wouldn't. After much troubleshooting, I realized that if I inserted Go to Next Field in between the two lines of code above, it al…

I am new (posting) to this group. You've all helped me for many months and I appreciate you for it. This particular time, I can't see any clear cut info on my question, so I wanted to jump in and ask. Thanks in advance. Simple Setup: Purchase Order.fp5 - Used to enter items we purchase. Inventory.fp5 - Records of those items we purchase (currently placed there through a portal). Question: Many times there is more than one quantity of each item we purchase. Two of this, three of that, etc. How can I take each line of the purchase order and split it into multiple records in the inventory? Example: purchase order.fp5 2 Widgets 24.99 …

I am noticing a problem with our orders database. The customer information in our orders database is pulled from the customers database throuh a relationship based on a concatenated field. The field is the first name, last name, and email address. It seems that the relationship doesn't work if the cases don't match. How can I correct this? Do I need to change the language in the storage options to something other than English? ASCII?

As a brief overview, I have two databases: one is 'products', the other is 'companies'. There's a many-to-many relationship using another database. For one report, I need to have a sorted list of all companies with products in the products database's found set. Possible solutions I can see include putting everything in a temporary database, or flagging all records in the companies database. But I feel like there should be an easier way... Thanx alot

Hi All, I need some help with auto data entry via calculation based on relationships with multiple files. The parent file will have a many to one relationship with three different child files, the dilemma is deciding while relationship to use to pull information from a child file. I have a field with a value list that denotes which child file it should use and I can set up the relationship OK. But I need some help to test the relationships so that data is automatically entered in several fields in the parent file. My thoughts have gone along the lines of an If script... If field a = value a of list, then use relationship 1. If field a = value b …

The Scenario - Our firm has mobile phones and dictaphones which it would like to keep track of. e.g Joe Bloggs borrows mobile phone for 3 day trial. I need to register that the person has it, when it was returned, for what matter and keep it historically so that when the phone bill comes in i can charge the appropriate matter to the bill. I have so far created a main screen which holds standard information (using the asset register fmp template) for the actual phones themselves. I then have a related database which has all the many makes and models of phones. Then i propose to create another database which is purely borrowing information I'm thinking of some…

I am a new/novice user and I need to create a button that links to a pdf file I have somewhere else. So when one clicks on the button it would open adobe acrobat reader and the file specified. I have read most of the user manual and nothing intuitively describes or even mentions that this type of process is available unless I types the whole filepath and paste it into the appropriate text box. PLEASE HELP! I need to figure this out for my new job!

I'm brand new to Filemaker, and I need some help. I'm a lawyer, and I'm trying to create a way to keep track of my clients, their cases, and what's happening on each case. For the clients information, I'll be using all of their general contact information. I figure I can use a 2nd database to keep track of individual cases and what's happening in them. Due to the level of detail I want on the "what's happening" section, I might need a 3rd database just to hold that information. I'm slowly learning how to do the whole relational database thing and how to connect information from one database into another. My big question right now is about the case status …

I have a simple database ordering system at the moment. I am currently implementing the payment side of things. As we generally collect full payment I don't need to worry too much about multiple invoices etc. Basically we accept payment via Credit Card, Cheque or Cash. If our customer pays via credit card we obviously have to store card details. If they pay by cheque we store cheque number and bank details. If they pay by cash then don't really need to store anything. I have a payment method field with these three options (Cash/Cheque/Credit Card). My question is this. If my customer chooses to pay by cheque then it seems a waste of space to have all the …

I'm totally rebuilding my database but the Payments give me such a pain that I'm quite stuck with this. So I would like to have some ideas (or example) on how you reached to establish a Payment File in a Relational design (if you did actually create a file for it) In my business, for both the Customer and the Supplier (of goods and services related to the activity - not Tax collector,...), the Invoicing system is based on shipments. When involving such a system, here is the kind of relationships you will come accross - [color:"red"]One to Many: One Customer order shipped in several times, invoiced in several times - [color:"red"]Many to One: Many Customer o…

I have the following situation and any help/advice would be appreciated. I have a simple product database and I'm creating an ordering table. Problem is this: I will have individual products available for ordering. But I'll also have groups of products available for ordering. I was thinking of creating a 'Packs' tables that had assigned a pack id and consisted of x number of products (via product_id). What I'm after is this: to be able to type in the product id or the pack id on the order page and the rest of the product details/pack details to be sucked through. So in a way I want to product id on the order page to check two different tables ('product' tabl…

Hi All I'm not at all sure I am going to be able to explain what I want to do in writing but I will try so please bear with me. I work in a museum and I have 2 databases to deal with cataloguing our entomology collection. One is the specimen database where information about each pinned specimen is entered and the other contains information on all the names of species found in Britain for many families of insects. In both files the name of the insect is made up of 2 fields 'genus' and 'species'. Is is possible to set the databases up so that when a name is entered in 'genus' in the specimen database the 'species' field only shows the species names linked to t…

I have 3 files as follows: Header to Items; Items to Invtry. For tunneling I have created 2 calculated fields c_WT and c_Dept in Items. The 2 fields are in Invtry. My header form has a portal of Items fields plus these 2 calculated fields. When I perform a find on the Header form, one of my records that matches my find criteria may be on row 3 of the portal but the report only displays the item and amount of the incorrect record on row 1 of the portal, causing my report to be incorrect. How can I have the report use the correct record in the report? Thanks

Hello Here's the setup. I have four files, Orders.fp5 with a relationship to Order_Items.fp5, displayed in a portal and a similar connection between a file Invoices.fp5 with a relationship to Invoice_Items.fp5. What I want to do is, once an order is closed, to move the record over to Invoices and taking all the related records from Order_Items over to Invoice_Items. How would this be done? I've managed to move the main record (from Orders) over to Invoices by script (using the Set Field command). But i don't know how to loop through the related records. I hope this is understandable. Thanks for your help. snc

In my business, a customer order can be delivered in 2 or 3 times, so invoiced in 2 or 3 times. This isn't the unique problem, but this is the start point of the new solution I'm working on. I actually use a unique line item with all my operative modules (quotes, cust order, purchase orders, shipping, invoice,...) relating to it via their respective numbers. As I want my new solution to use a real relational structure, I came with a solution where - each module file use its own Line Item file, - each Line Item file relate to a Main Line Item with a Unique Id That mean I can now easily use the portals in each file either : - to create new related rec…

Hi there - I have a database for 'clients' that I and two other people use to make notes of telephone conversations from clients. Our sales girl works on another database for her sales contacts. Is there anyway I can be in 'my' database and look at some of her fields to see 'notes' that she has written on some clients and also perform a find on one of her 'clients' within my database and layout???

I'm having a problem with my relationships (I think) between two tables. I'll try and explain this as simple as possible. Table "CPS Case" is a table to track all the Child Protective Services cased being brought it. Most of the information in this table is brought from other places, like a "client master table", and "contact info" table, etc. One of the tables it's shows information from is the "perp master" table. On the layout, I list the victim's name and personal information from "client master", as well as the perpetrator information from "perp master". The perps are tracked by a unique id. There can only be one record of them in the perp master. Howev…

Good day all ( I hope!)- A db I inherited is currently set up where I have my main file:Study and Related File: People. There are several relationships between study and people depending upon the roles the people serve (ie PI, Data Contact, etc) Currently for each role the files are related by the person's full name (first name & last name & title). For obvious reasons, I think this sucks, namely if any part of a person's name is changed (they get married, promoted, etc), then all the relationships to the study file break. EEK! I am in the process of switching the relationshis to be based on unique identifier keys (Capt Kurt system) but I do not und…

This post is an extension of a precedent post here where I was looking for a way to show unique "summary records" from my line item - made using selfjoin- in a portal in my Product File. I'll get to the point in a second. Just imagine a "consumption good" and its inventory system (this is the closest example I found) where each set of articles has a unique ID, but a different consomation date. So we have to subcategorize the Inventory on the Date of Consomation for each Product_ID... In my system, the Inventory is kept in the Product File using constant relationships from line items (operational file) to "import" the Totals of goods in stock. This work fine s…

Hi everyone! I have a Client dB which lacks critical information to generate a billing. I want to create a report per Therapist, giving them a *list* of missing information (errors) on each of their Clients, so they can *fill in* the required information on the report, as follows: ClientName Missing Medicaid # ________________ Missing Diagnosis ______________ Invalid Social Security #______________ At first, I started to write a calc, but I don't like the thought of using up resources (particularly adding the "message text" portion (see above), so I instead created a dB called Errors. This Error dB has an ErrorID (1) and ErrorDescription (Missing …

Have you ever stared at a problem so long that you can't see the obvious solution? That's where I am right now.... someone please help! Attached are two DBs that I need to define relationships in. I've followed all the tutorials I've found and CANNOT get the related file to populate the master file. I am at my wits end..... no matter what I do - info doesn't show up in the 2nd file.... Can someone please look at these, EDIT & EMAIL or at least tell me what I'm missing?: I'm not an idiot - but this problem is certainly making me feel like one! Thanks Stephanie www.mcgc.net mcgc.zip

I have been writing new DB for many months and recently learned that when I try to define new relationships in the define window, the new relationship is defined and the number of relationships in incremented by one, but when I close the define window and re-open the window, the newly defined relationship is there, but it has overwritten an already existing relationship. I made a copy of the DB and tried to define "x" number of new relationships, and when I close and re-open the Define Relationship window, there are not "x' new relationships; the new relationships are present, but there are now "x" number of older relationships lost. Can anyone shed any light as…

I have a simple question. I am trying to count the number of times a number occurs in a field. The field (rider number) is imported with another field (times) for a scoring program in a single file. I want to count the number of times a rider is scored (laps). I created a field (number of laps) and am having trouble setting up a self relationship (I think that is the way to go). Any advise would be great, RAD DAD http://sera.dirtrider.net (this is a database for our kid's races)

Hi, A client of mine wants to be able to do the following: Enter a ClientsPO and turn it into multiple VendorsPO. They receive an order from a client with several products. The products are then ordered by my client to several vendors. I have several files setup (VendorsPO, ClientsPO, Products, Vendors, LineItems) and several relationships. I can't find a way to create several VendorsPOs from the ClientsPO with the produts showing from LineItems. I get either all items from the ClientsPO (instead of just the one belonging to that Vendor) or I get only the first product. Also, is there a way to that automatically, meaning by script or scripts. Any help would be appr…

Could someone please help with how to do link tables and multiple table joins? The need is to have a 1-to-infinity relationship between tables, by using the link table. The desire is to achieve an application supported by a fully normalized database behind it. Table Client.fp5 serves solely as a GUI, containing singular field "person_id". Other fields will be "dragged" onto the Client.fp5 layout from related tables using the layout tools. Table Person.fp5 serves to contain the client's "person_id", "firstname", "lastname" fields (i.e. things which most people have only one of); as well as several address_id fields. There is a relationship Client::person_i…

I'm trying to figure out how to create multiple form letters. For instance somebody who isn't very computer / filemaker savy wants to create mulitple form letters, without having to create a new layout for each different letter in my main database. I don't really want them creating a ton of layouts everytime they want to create and save a different letter for a mail merge. A relationship would help here somehow. I have a main database that has all the contacts. In a seperate database could i create a field that they can type the letter in and have merge fields for the contact info from the main db? But i'm not sure how to make that work either. Or do they ha…

I have a online inventory website that has 68 categories and of those it has 197 sub-categories. I have 3 databases. 1 Inventory, 1 Categories, and 1 Sub-Categories. They are setup relational and work properly. (at least I think) Now here is the problem: When I do a search for a item via a value list on the web it find 207 matches which is correct but I only have 3 sub-categories and it displays all 197 of them. How can I make only show the sub-categories that are associated with this category?

I want to be able to see duplicates of same product in a unique invoice. I tried making a calc text Product_ID&Invoice_ID (both txt), and use it in a selfrealtionship in my line items, but showing the result through a portal, it doesn't come to anything. What am I doing wrong ?

I have a pretty dumb relationship question. On my layout so far I have a "casemanager id" field which is a 3-digit number, and a "Staff Name" dropdown that has a valuelist made from a calculated field that takes all the records in the staff database. The relationship that is set up so far is that the "casemanager id" is linked to the "staff id" in the staff database. What will I have to do to make the "casemanager id" populate itself when I select something from the staff dropdown list? Also, I would like it to work in reverse. If they type in the number, the staff field should populate. Thanks guys!

Hi, is there a way to make sure a related db is open, and stays open? I mean, can i abort opening a solution when the preferences.fp5 is missing or misplaced? TIA JP

I have built a DB with multiple fields. Three of which have a relationship. Company, company code, company type. If I click on company, the pop up menu shows my list of companies and I select the one I want and it files in the relationship for that company (company code, and type). All works well. The problem is if I go to the pop up menu for Company type after I have selected the company and select a different company type it shows my new selection but when I go to simple list layout of my companies that reference all my fields for the companies, the company type (in the list layout) is the original company code that was entered when I selected the company. If I go…

I'm working on a reliable inventory system, but I am stuck with this because of the specificity of my business (Ceramic tile wholesaling). You may have experienced it as an individual, if you had some repairs in your house. It is quite impossible to find exactly the same ceramic tile that the one you bought 30 days ago, even with the article code on your invoice. As a mixture of clays and low-iron content kaolins, with temperatue of + 1200

I have 3 databases one named Data Entry, Clerking and Models. I have a field named Model_ID in Data Entry. I have a field in models named make_model_key which is a combination of two individual fields in models which are make and model. In the Data Entry database I have the field model_id setup as a value list and displaying the value make_model_key from models. I then add the related fields make and model from models to the database Data Entry. The result is when I select the Model_ID, the make and model fields are automatically populated. My problem is that in the third database named Clerking I want the same information showing up in the make and model fields of Data …

Hi All File A , 2 fields , Field A1 = 12 Field A2 = nil so unique key= 12nil so first time I go from File A to File B ..I create a record in File and set unique key of File B = 12nil Second time I change Field A1= 12 Field A2 = 123 so uniquekey = 12123 So How can I go From File A to File B which don't create new record in File B but go to the same record of File B (12nil) and change unique key of File B = 12123. Somebody can help me with this since I tried many ways but no idea how to implement that ...since I know that one unique key to one unique can not change like that? I really need your help Thanks All

Hello, I need advice! Help! I have 1 file that must read off of 3 files. When someone checks a checkbox in the related file, and performs a find, I need it to look at one of the 3 files and give me the data that was checked off. My field names match in all 3 files(motion description)- but not the names in the value lists. Is there any way to do this? and if not, what other options do I have to get the 3 files in individually. (my reason: I have different checkbox value list names for each of the 3 files and when someone wants to see a certain group, they can select the group and then have it read from the appropriate files) Should I use a layout…

I cant quite decide which category to post this in, but here goes: I am optimizing an earlier solution for speed in some scripts, and wanted to see if anyone could point out potential problems with this scenario. I want to delete blank invoice items created as result of users clicking on last line of portal (there are several portals on the invoice screen that create different kinds of line items. works great, but results in many blank line items accidently created) 2 files: Invoices and LineItems 4 relevant fields in LineItems: t_InvoiceID (unique ID from Invoices file- identical in all line items for invoice) t_LineItemText (what the invoice will di…

I am sorry if this has been covered, but I couldn't find exactly what I needed. I have two dbs one is networks(client addresses) and the other is an order form. I would like it when you type in the name of the network their other information would then poulate the other remaining fields. Such as I have described below. Network: TWC Phone: XXX-XXX-XXXX Address: XXX Fax: XXX Contact: XXX I have set up a relationship from the network db to the order db and tried to type in the information and see if it would match up, it doesn't do anything, I realize I am doing something wrong, but I am at a dead end, I have set the match field to be network being th…

I have a system with 30 files and I have to change the file names. Is there a simple and practical way to change them and to change the file names in the relationships otherwise the relationships won't work properly. thanks. Luiza

I created a self relationship based on my Record_ID (auto enter serial number). I want my lookups to be based off this self relationship and lookup calculations that I created in the same database. It works great for records already in the db as I just do a relookup. However, when entering new records, these lookups do not behave the same as lookups from outside the db, the lookup remains blank unless I do a relookup. I don't understand why it doesn't populate automatically since I am generating a new Record_ID and the lookup is based on this relationship. What am I doing wrong?

hey guys just wanted to as a quick question. i have a relational databse with one layout setup so i do not have to make multiple records for the same record(thanks andy for that one). i guess its kinda like a sub record but i am sure you guys know what i mean. that relational databse is a weekly progress report with hours completed for each week. i want those hours to be subtracted from a feild in the main DB and then calculated and displayed. to quickly explain. hours required is on the primary DB weekly progress report from relational DB contains how many they have completed for the week. back to main DB to display how …

In the FileMaker Help I ran across this paragraph in the 'match fields used to create a relationship' section. But this seems unheard of to me, read the following (the ending sentances is where it gets interesting) The match field in either (or both) the master file or related file can contain a calculation formula. For example, the formula Unit Price in the match field of the master file establishes a relationship to a record (or records) in the related file whose match field contains the value of the Unit Price field in the current record of the master file. You can also define a calculation formula for the match field to match multiple records. For example, the formu…

Another problem that I constantly run into (as I try to get used to FileMaker vs SQL) is how to display data that's seperated from a record by two or more relationships. Here's a typical scenario. Addresses DB Customer DB Invoice DB Customers can have a main, shipping and billing address (all possibly the same), so there's three address fields with address id's. On a new invoice I want to be able to type in a customer code and have the main and billing addresses fill in. So how's this normally done? Do I need a script? Do I save the address IDs into fields in the Invoice DB and have Invoice->Address relationships based on those? Do I need a scipt for…

I am sort of stuck. I created a contacts database, with lots of functionality, search scripts, automatic mailmerge etc. then they asked to adapt it "a bit"... each record (company name) has 1 up to 4 contact persons, each with their own set of fields. the question is to be able to print labels and letters to 1 or more contact persons from the same company. e.g. company A has contact persons a1 and a2 company B has contact person b1 company C has contact persons c1, c2 and c3 I have markers next to each name, to define if they should receive mail or not. Now I want to mailmerge to persons a2, b1, c1 and c3, so I need following labels: company A …

hello! i am new to this group and new to FileMaker 6 - I think I used it last about 6 years ago... SO, I downloaded a set of databases called "Classmate" which addressed some of the issues I needed to address with a database, but the problem was, it was for a school where one kid is in one class ("grade 4") for an entire year. I teach at a college where 20 kids are in multiple classes that repeat every other month. What I need is for the student's name to be related to the assignments which are related to the class title. So Steve Smith is in 3D Modeling and DVD Authoring. the assignments remain the same every time the class repeats, but of course I need to…

I have a database of teachers and other school services personnel. It is related to a database of school districts the teachers work in. In Teachers.fp5 I have a value list created from the records in Districts.fp5. It is displayed as checkboxes since a teacher can work in more than one district. Many districts have more than one building and I need to indicate in Teachers.fp5 what buildings a teacher works in. I don't want to list all the buildings in all the districts for every teacher...Is there a way I can display in Teachers.fp5 only the buildings that exist in the districts they work in (indicated in the checkboxes)? Thanks for your help. Susan

I've been searching this archive for a few hours now and cannot find anything that directly relates to my question: I have a file with a self-join related twice by two calculated fields using concatenated fields as the key (thanks to this list for the idea!). The idea is to search for a client name and view all projects that are "active" or "complete" for that client only. One relation/calculation handles "active" and the other handles "complete". Now I have two portals side-by-side on one layout, one to display active projects and the other to display completed projects. They both work correctly. Except... Let's say I have a client "ABC" with two active p…

Ive written a database that keeps track of all the TV shows we are editing and mixing. Each Record is the TV show in house and the editor assigned to cut each reel of Dialog and Effects. Here is an example: Show JAG 121 Act1 DIA Dave Act1 EFX Mike Act2 DIA Joe Act2 EFX Mike Act3 DIA Dave Act3 EFX Steve Act4 DIA Sue Act4 EFX Steve I want to be able to search by editor to see how many Acts they are assigned each week. How can I set up a relationship so if I search Steve it would display: Jag 121 Act3 EFX Jag 121 Act4 EFX These fields were set up as repeating fields, but I would change them to make searching easier. Thank you Kevin

I am trying to create a value list based one field depending on the entry in another. I.e. I want to create a value stock list of items by make in a shop. However once the item has been sold it still has to stay in the database. But if the value list is based on the makes field the sold item still comes up. How do I get round this?

I realize this is a very basic question, but I haven't been able to figure this out from the manual or the help program. I have a database of 24k records with 40 fields, one of which is a unique ID. I have a list of 100 unique IDs (in another database) and I want to pull out all the fields for those unique IDs and export them. I haven't been able to figure out an easy way to do this. Can anyone point me in the right direction? thanks

I've set up a series of databases in order to track the paper-filing system here in the office. The idea is that now that I have entered the names of all of the files in the filing cabinets, when we want something, but don't know exactly which file we are looking for, we can search through the database to find the name and location of the file we want. These are reas simple databases. Theres a seperate database for each file cabinet drawer and the only fields are filename and creationdate. In my master database, I have set up a relationship to one of these other databases (i'm starting small with just one until i can get things to actually work). I've also go…

And I'm not certain whether it's a RELATIONSHIPS problem or a DEFINE FIELDS problem or even it it's a problem at all. But so far I haven't been able to either puzzle it out or find the answer by searching the forum... In a db to track attendance at a workshop, I have a summary field to calculate the number of 'yes' responses (totalAttending?). Suppose that 5 people have responded their intent to attend. How do I define/script a button that will show those 5 related records? How do I relate the total attendees with their names? If this is right in front of my face and I'm just not seeing it, I apologize for taking up your time. But I don't see it and [color:…

So I'm trying to run FM Pro 5.5.2 on a Windows 2k Pro machine and it is hitting databases living on a couple of high performance Win 2k servers. When bringing up a particular database my CPU jumps up to 95 -100% useage. My understanding is that the particular file in question contains a bunch or relationships and this might be the root of the lousy performance. The servers don't even blip - they just chug along untainted by whatever is happening on the client side. On a Win98 box this performance problem is hardly even noticeable. On a G3 mac everything seems just fine... This makes no sense to me - I am perplexed to the max - clues are welcome... Pfmon…

Hi everyone! I'm designing a NOTES dB to attach to my Clients dB. I receive frequent emails regarding a specific client and I began by using cut & paste to *capture* an emails contents to insert into a note record. YUK!! Is there an easier way to reference the contents of an email? I'm using FM 6 and Outlook Express. Ideally, I would like to be able to select a link (?) from within my notes dB to open an email's contents directly. Am I dreaming? Any suggestions on how to track these types of data sources would be greatly appreciated LaRetta

I wonder whether you could help me with the follwing. I have three database files; purchases, Sales and master. In the master file I have two portals, the first allows creation of records in the purchases file using a serial lookup, which can contain multiple records. However I want to be able to select only the purchase portal data from the master record into my second portal (SALES) which comprises of two fields supplier & desc., and deduct a sales quantity from the purchase qty providing a balance remaining. I have created the related value lists for this but cannot seem to find a way of getting the purchase quantity to show in the mas…

I am trying to generate a commisions report for salespeople. The Sales are divided into a 52 period system (weeks in a year). I need the commissions database to break down the sales by period AND by agent fields... When i make the relationaships it works great for either the period or the agent but not both. It disregards the other relationship. So i am doing sum of of Units sold....however i want sum of units sold during a specific period.

I just found the forum and have read a number of questions and answers and hope I can get some help. I have created a database for a small repository. I currently have 6 files (and more to come). The one field that appears on all records is an accession number. Each box is assigned an accession number and each object in that box is given the accession number and a unigue catalog number. Example: A Box is assigned an Accession number --1974.01.001 (year, collection #, box#) Each object in that box is given a Catalog number -1974.01.001.001 (year, collection #, box # catalog #) Each accession number has many objects related to it. An object may have a record in…

dilemma - I have my main table which has individual records which include clientID and month of entry (plus several other pieces of data necessary to this project) what I am trying to do is create a dynamic form that has a summary of the number of records each client has by month. The trick is I need it in form format because I am trying create dashboard that will allow me to click on these grouping in the form to perform a find based on the criteria that created these summaries.....must breathe. I have a beautiful report that displays the data perfectly and even summarizes it further by several additional criteria but unfortunately it does not allow for buttons or scr…

Ok Guys, Heres What I Am Looking To Do! I wrote a database in FM to track all my employees information. Name, Address, Social etc... I have invested in a barcode scan system, and have distributed employee cards out to all of my employees. Now what I want to do is be able to track Time In, Time Outs In The Database. I am figuring I have to make a seperate database for Time In, And For Time Out.... But how do I incorporate it so that I can track the times in and out for all employee.... My employee status is constantly fluctuating, do to the type of business I am in. So number of employees change.... ANY IDEAS?: ANY HELP GREATLY APPRECIATED

When making a relationship where all records relate to all records, is it better to use a calculation field that always equals "1", or a global field that you set to "1" for both databases. When you create the relationship with globals, FM says it won't work, but it seems to work fine for the purpose of getting data in global fields from one db to another.

I have a system where records are created for a Hotel.fp5 from within Events.fp5. I use the relationship hotel = ::hotel The fields in Events.fp5 are: Hotel, ::HotelAddress , ::HotelCity, etc. This works great except when you want to edit the hotel name after a record has been created. When you change the hotel name in Events.fp5 it creates a new record in Hotel.fp5 I know there is a basic rule here that I am missing to keep the system easy-to-use, but more flexible.

hi all, i have 3 separate db's, let's say A is Clients db, B is Consulants db & C is XMas cards db. there's a field in both A & B so that if i put a tick in this field, a record would be generated in C using data from A OR B, data should include several fields from A & B. Does anyone have any clue on how i could tackle this efficiently? cheers

I'm trying to link one db's fields with another but cannot. I think it may relate to the fact that the fields I'm trying to link/relate to are also related to another db. I have no problem linking/relating to fields -- db to db -- that are in the 'current file'. How do I do that? thanks, Harry

I am a beginner . I did not find an answer to my problem in the Forums. I have a Person.fp5, Document.fp5 and a Join_Pers_Doc.pdf The Person contains Names and all associated data as well as a ID The Document contains text, date, container, ID ..... I want to create a Document record and later be able to associate a selection of Person records to it (displayed in a Portal in Document). The Document Records associated to the Person are displayed in a portal in Person. I want to make a selection of Person with the usual tools in file maker (sort, find) at a later stage and associate the relation to the Document record. I need to be able to change that …

Sorry if this has already been covered. I have a job tracking solution made up of 2 files: Jobs.fp5 and Job Tasks.fp5. Tasks for each job asks are marked as either active or inactive. The files are related by job number. On one layout of the Job.fp5 file, a portal shows all tasks for the specific job number. This works fine, but I'd also like to create 2 other layouts in the Jobs.fp5 file - one with a portal that shows just active job tasks and another layout with a portal that shows just inactive job tasks I assume I have to create 2 additional relationships between the 2 files and probably some fields, but don't how to go about it. Can anyone help? Than…

I work with FileMaker solutions which have Invoices, Quotes, and Service Orders. They are all set up to share the same line items database, so that Quotes and Services Orders can be quickly made into invoices. Rather than relating the line items by invoice number, quote number, or SRO number, I am using something I call a "Universal ID". Every time an Invoice, Quote, or SRO is created, a new Universal ID is created and assigned to that record. Any line items created then relate through that ID. If you invoice a Quote, the script will use the same Universal ID on the invoice as was created for the Quote (rather than creating a new, unique number). This …

I'm think I'm on the way to find the solution, but I need some help at a start. I'm planning to create a Main Line Item Database, that would have at least 5 child files (Quote Line Item, CustOrder l.i.,Order l.i., Deliveries l.i., Invoices l.i.) linked to their relatives Databases (Quote Db, CustOrder Db,...). A Unique ID will then be adressed to each file that would have same Quote N

Hello, i'm somewhat new to relational design. I just got finished converting my flat database into a relational database with four files, and yesterday I installed it on our small network. There are three Macs all running FMP 6.0v3, peer to peer networking. The problem I ran into is that some of the related files were not automatically opening when a client was doing an activity that required them. The primary files i 'Orders', and the major related file is 'Line Items'. Line Items opens automatically, as desired, since the initial script requires it. Occasionally the user makes a new Line Item which requires a price markup. If this is so, Line Items is …