Relationships

Is there a solution to have only one portal : - using the records of my lineitems linked to Proposal N

I have a Wholesale/Retail activity. When I started my activity, I had a 15,000 products catalog, directly bought from Italian Producers. I created a Product DB that has fields Producer Name, Producer Code, Collection and Reference, Public Sell Price,...and a Producer DB linked to Product DB, Purchase Order DB, Main DB for all operations. So far so good... BUT as my business grew up, I had to expand my offers with products that I couldn't buy directly from the Producer (some products have a Wholesaling exclusivity), using other Wholesalers catalog products. They now also sometimes buy from me. So I became a client of my current competitors and my compet…

Is there any way to have a sub-database within a record of a given parent database, so that within record "A" I could add an unlimited amount of sub-records. This may indeed surpass the capabilities of Filemaker.

Relatively new to FM: I am currently trying to re-design our corporate docket system. Basically, each contact in our database is one record. We need though, the ability to create a distribution list with many addresses from many records. Is there a way to compile various records into a list without having to use a relational database ?

This Is my first time so please be gentle ! I have a db for an autoparts catalogue were the prices of the parts are updated on a very reguler basis. I have a db of invoices that uses the autopart id code for the relationship between the two db's I have chosen not to use a portal to import the cost of the parts because as i understand when i change the part cost in the catalogue it would update the part costs in all the previous invoices, which of cause is not what i would want. So i have used a lookup relationship to put the information from the catalogue into the invoices. to do this i have had to create a lot of separate relationships for each lookup of each…

Hi Everyone! Well, it's my day to need help! My boss has just requested a report and I'm not sure how to obtain the required information. I have a Services db. I need to find all Services date with ServiceDate 10/1/2002...10/31/2002. I have a Clients db related to Services on ClientID. AND, I have a Contracts db with multiple contracts per Client, also related to Clients on ClientID. These contracts are date-ranged (FromDate and ToDate) and have a total contract dollar amount. I need to find all contracts matching the related Services -- if there is a Contract within the valid date range of 10/1/2002...10/31/2002. Oh, I hope this makes sense. I then …

I have no idea if this is possible or not.... I have several separate databases - 1 for each leasing transaction type. I have to generate status reports of every record from every database. So basically I need a new database which has additional "commentary" fields, and lookups from one to many relationships?? for the matter number (i.e you insert a matter number and it copies the relevant information from the database. The matter number is unique and never used twice. So I guess the question is as follows: I am in the transaction database, I have just created a new record. Is there a way where a record gets automatically added to the status databa…

I'm trying to figure out how to do this one, but i'm stumped ... This is for a list of graphics for a news show. The main file, holds the "story title" which then has a related list of the associated graphics. The "graphic name" field is where the persons name goes in. I have another DB of "graphic names" and "graphic titles". How can i get the title to automatically pop into the title field, but also still have the ability to edit it. Currently i have a calc field that let's me show the title, but no way to edit it from that screen. Thanks.

I have been using a database to record address and contact information for publications in the UK. To make data entry easier, I decided to separate the information out into separate relational databases. I now have four databases - a master database into which I want all data entry and modification to be made, a publications database, a publisher database and an address database. Each contains the following fields: Master - Publication title, Publisher, Address 1, Address 2, Address 3, Address 4, City, County, Post code, Telephone, Fax, Web site (plus other unique fields) Publications database - Publication title, Publisher, Address 1, Address 2, Address 3, Addr…

Sorry for the long post. I want to make it CLEAR In Customer DB Portal A shows a list of Orders with relational Key in Order DB (IdCustomer::iDCustomer). a "Add" button in the corner of the header of the portal create a new order with a script in Order DB. This script with copy/paste function at the end imports the "IdOrder" into my CENTRAL DB. The New Order N

I have two databases that are related: Speakers.db and Workshop.db. They are related in a one-to-many manner as one speaker may give several workshops. I want to send my speakers a letter, reminding them of the date, time, and location of their workshop. In the Workshop.db I have set up a "Letter" layout. The top part is a sub-summary of the speaker's full name ("Full Name" is simply a calculation field of "Title", "First", "Last", "Suffix"). In this sub-summary I have the speakers name, address, etc., the salutaion, and the first paragraph. I then would like to list the workshops information he/she will give. If I put that information in the Body sect…

There are two points I'm really stuck with in Relational DB : How can I avoid any modification to a specific record in a DB (i.e. Invoices) ? Is it possible to keep one set of invoices locked ? How can I keep the old records when I update them ? Customers often changed their minds and modify their orders. I want to keep a "copy" of their first orders in case of contestation. This leads to another question. Is it possible to put some datas in an Archived DB in FM ? Or is there other ways some of you deal with? Thanks

I have a file I'm working on for a talent agency that receives offers from promoters for their musicians. Each offer has venue information. There are currently over 10000 records in the file and most of the venues they work with are already in there (around 3000 unique names). I tried to do a self join so that when they entered the name of the venue, the address and contact information would automatically enter. The relationship is setup as selfbyvenue, with the name of venue field: VENUE as the link. Allow creation of related records is selected. I then placed the fields from the relationship into the address and contact area. It works like a charm if the venue is alread…

Let's say you have a portal, and you "allow creation of related records". What if you wanted the related records (children), to be numbered sequentially FOR EACH parent as you enter them.... eg: each parent's first child will be "1", the second will be "2" etc. I understand that you can display the line number, but I'd like the number to be stored in a field in the related record. I know how to auto-enter a serial number... but this is a little different because it is specific to each parent. -- Jason Wood HeyWoody.com

I have three databases related together and when I enter values into the main database the child databases do not update calculated fields automatically. I have to select something from a menu to update. Why is that?

I currenly do a lot of statistics programs in lotus 123 and excel spreadsheets for bowling organisations. Well I looked at Filemaker and really like the program. Basically i do not have a lot of database experience, but what i want to do is take 3 divisions (mens, ladies, mixed) with 6 teams in each division. the teams each bowl 5 games with wins and losses against the other teams. What i need is a way to set up the databases to not only do team totals for games and wins but do singles results based on sex. I have tried to set up the tables but keep getting confused as to which relationships I should use. I currently use one table for divisions, teams, bowlers an…

Hi, I need to set up a summary field that will collect all of the information from a specific field for all records using defined in the relationship. This field need to omit any repeating values. For example: If I have three records that pertain to a certain relationship. In each record there is a field that describes the colour of the product as follows: Record 1 colour: red Record 2 colour: blue Record 3 colour: blue I want to have a field that will sumarrize the colours to be use for this project as: Colours to be used: red, blue Any suggestion will be greatly appreciated. Thnak you, Anthony

I'm trying to build a button in my 'address book' database that automatically creates a new record and fills in common data (name, address, company name etc.) into my 'jobbook database'. I've been playing around with relationships and field lookups but to no avail. Does anyone have any suggestions as to how to make this work? Thank you. Andy

Is it okay to have multiple sets of related dbs on the same PC--with the dbs in each set are named the same--so long as relative addressing is used in relationships and calls for external scripts? Other threads I've found touch on this question but don't answer it. It's been suggested to me that the answer is 'yes,' and I've tested this possibility successfully. But I know from experience that with earlier versions of FMP you were asking for trouble if you had duplicate copies, and I haven't found any statement by FileMaker saying that duplicate names are okay if relative addressing is used. Let me explain why I care: I have a FMP 5.5 solution using related DBs…

Look at the post link below, the answer provided by falkaholic (please star him) and the final solution. It seems that a portal in a layout makes pop-up lists related appears quicker... http://www.fmforums.com/threads/showflat.php?Cat=&Board=UBB17&Number=49670&page=0&view=collapsed&sb=5&o=&fpart=1

Well, I've gone and done it. I have a database in trouble! Its data was imported from an old AccPac program. Its primary key (ProcedureCode) needs to be changed! It should be easy to correct; however, it is joined to another db Service:ProcedureCode which currently contains 1500+ records and there's another catch! db name: ProcedureCodes Record 1: Code: BA023, Description: Family Therapy Rate $85.00 Record 2: Code: 90855, Description: Family Therapy Rate $85.00 ---the first is a Medicaid or State billing code and the second is it's equivalent insurance billing code -- but they BOTH are the same procedural description and price, so both should exist w…

You guys are driving me mad !! Any search to this forum or post I read makes me feel I'm wrong in the database I develop from two years... My concern is about Inventory. As it is built, my current Database use a single "PRODUCT BASE" for Product List and Inventory. I read in different post that most of those who deal with inventory created 2 specific Database. I must admit that this is the file that give me most concern, and that is why I never really made it work... For the moment, the number of Products in stock is weak, but we planned to develop our company and need a real Inventory tool. As this is my own business and that I personnaly de…

I have two files related via a unique ref field in each giving a simple oneto many relationship. The first file has a layout with a portal showing related records from the "many" file - again very simple!. The files deal with departments (main file) and sales of particular products by that department. Also there will only ever be a maximum of six different products sold altogether across all the departments. My problem is that I simply need to show the sales of each product for each department. My plan is to create six new calculated fields to store these values. The problem I have is how to total the sales of each product from within the portal. Something similar…

I made a database which stores orders from our companys website. However this was also my first DB undertaking, and it is a totally non-relational design. I am now looking into making it relational and have made some test files to see how hard it will be. Right now I am working on having a separate line items file and having the line items be displayed to the user in a portal from the main file. However, I have two scripts which need to operate on specific line items. For example, in my current file I have room for ten line items, and next to each line item are two buttons, which point to 20 different scripts. There are only two different scripts but there are 1…

I have a main file, Items, and 2 related files, Charges and Costs. The Items file is indexed automatically with a record number field. When a new record is created in the Items file I wish to also create a new record in both the Charges and Costs file, and assign the record number from the Items file to the identifiers of the related files, thereby cementing the relationship. How can I do this? Thanks Jeff (The Mad Jammer) And then ... there was nothing

Sorry if this post is so long... I developped a Database using FM Pro 5.5 for my Ceramic Tiles Wholesaling business. This DB is constituted by 20 other single bases (Products, Inventory, Client, Producer, Quotation, Sales Order, Buying Order, Transport, Delivery, Invoice,...) and one "Central Base" that compills all the main informations from the other base. 15251 records are registered in my PRODUCT BASE, all these products having an automatic seria Number. I created that database thinking that my employees would use the Product Number as the main key to import the product specifications (Collection, Designation, Size, Price, Packaging,...) into th…

I work with this problem unsolved for a while, but I only know this forum for 3/4 days. In my database, the "Product N

Dear All, I got a database name A I got field : Manager and employee So I use self-join on Manager to get employees with the same Manager I put : Relation: manager and in the portal I put Relation: employee And i have a script attached to it I write Setfield (Realtion:employee," Bill ") And find it ...it does not work to get many employees with same Manager ...Is it the good way to get it ? or you have another approach? Thanks

I need to track students' attendance at workshops. So, I have a Students file, a Workshops file, and a join file named Workshop_Enrollment. However, I need to make sure I have the specific date of the workshop and whether or not a student was absent, present, tardy, or dismissed. I have students going to different workshops on the same date and the workshops are held on many dates. One of the items I will need to produce is a report identifying the name of the workshop with the list of students attending that workshop and if they were absent on each day the workshop was held. I am not exactly sure how set up the files in order to produce this report. Help is…

I am currently converting data from an old DOS program (AccPac sub-module called MedPac) to FM. Current structure consists of four dbs: Services:Service# (CaptKurt's Primary) > Invoices:Service# (one to many). I have manually matched these two dbs so I now have the Invoice number to work with to establish the relationships in the remaining dbs: Invoices:Invoice# > LineItems:Invoice# (many-to-many) LineItems:Invoice# > Payments:Invoice# (many-to-many) I used Invoice# because that was the only unique identifier in these raw data sets. I am unclear on the BEST (and quickest) approach (as I'm on tight deadline) to complete this connection. I …

When you have multiple databases that are related, how do you get the other db's to open up when you open up one of them? Does this make sense? Thanks

I am experiencing a problem with Filemaker Pro 6 (Unlimited copy)for a Web Server. At the moment I am loading details using a website into a database. I am experiencing a problem only with one area of the website. I currently select,( e.g. a contact name), from a drop down value list and the details,( e.g. the contacts address/phonenumber/faxnumber/emailaddress), are loaded automatically from another database named 'Contacts' using the relationship option into respective fields. This works perfectly and there is no problem with the data contained in each record in the database. On the website all the fields show up correctly. The problem is that when t…

Hi there, I am fairly new to Filemaker and these forums so please go easy on me! :-) I have just created a database that has a main file for the list of clients. I then have a very simple database that lists the products and prices. These are then used in a Portal within the main clients database to show what they have purchased. These purchases are stored within a third file. This is where my problem lies. If I wanted to create an Invoice for each seperate company from within the third file then I could easily do this but what I need to be able to do is to invoice the clients via a third party. Imagine the clients have been sent though via an Agency and then all…

Hi...sorry for my English (I'm italian...), ok, my problem is: 3 files: RawMaterials (RMID, RMName, RMPrice) Products (PrID, PrName, PrPrice, RMID, RMAmount) Orders (OrID, CustomerID, PrID, PrAmount) These 3 DB are in a relationship many-to-many with 2 DB linking by portals: LineItems RM/Product LineItems Products/Order If I've many Orders that contains same Product, I want to know how many Amount of RawMaterial I need to produce and deliver the Orders containing that Product (in Italian is FABISOGNO, I don't know in English!!!) All the ralationships works great, but I've no idea like get at, may be to have a calculated field, wich arran…

I've got three files: states, test, and portalTest. In states, people create a record with the name of a state and a city to go along with that. I've created a relationship between states and Test such that, when you click on the "states" field and choose a states, only the cities related to that state appear in the cities pop-up list. Great, that works just fine. PortalTest has a relationship with Test and hopes to show its (tests) records in a portal that allows creation of records in the related file (test, in this case). However, I can't get those fields to work. The key field between PortalTest and Test is an arbitrary field called "state." I don't act…

Hi, The current situation

In order to supply data to a client in his required format I need to take data from my master database and create a new file in which each piece of data is a separate record. So in the master database lets say there are 100 records each made up of field 1, field 2, field 3, field 4. In the separate file I need to create four new records for each field in each record in the master file, so I will end up with 400 records. I would appreciate help Regards, Chris

Hello I'm setting up a database system where each file is a separate country. I want to link up all the databases together so that information can be shared between them all. Some countries have been easy. Most of Europe went together pretty easily (except perhaps for Switzerland and some of the Baltic states). I cannot work out a relationship to link Israel and Palestine; strangly any links from Israel to other countries have to go through USA first. USA is an interesting one. All relationships from USA to other countries by default have the "delete" option always enabled, consequently the deletion of any record in USA can cause the unexpected removal…

ok.. So i made a pretty solid database a few months back and it has been performing well. problem is my lack of forsight. One of the fields in this database is a container field containing images. Originally there was only going to be very low res files in here, so I was willing to leave it as is, but now it seems the image files are getting increasingly larger in size, causing slow browsing through records as well as large DB file size. So, i want to move the image field off to a new database. My original idea was to make a new database that was pretty much just for the image container field, then in the old DB, replace the previous container field with a link to th…

I'm not at all sure where this question belongs, so I'm going to try relationships. I have 2 files: Reps Districts I need to do a monthly import of revenue data from an Excel file containing three fields: RepID, DistrictID, and Revenue. Most line items on this file will contain both a District ID and a Rep ID, but a few line items will only contain one or the other. I need that data to be available to both the Reps file and the Districts file, and I need to be able to summarize revenue for both Reps and Districts, based on Last Year's Total, and Current Year to Date. I created a third file, called Rev_LI (revenue line items) into which I import t…

Hello, I have 3 databases, which have the following structure (image of the structure is attached as JPG to this post): Database 1: ID (primary key) Data (some data) Database 2: ID (primary key) Database1 (foreign key to Database 1) Data (some data) Database 3: ID (primary key) Database 2 (foreign key to Database 2) Data (some data) As you can see, I have a one-to-many relationship between Database 1 and Database 2 one-to-many relationship between Database 2 and Database 3 Because of this structure, I should be able to go to the related record in Database 1 from Database 3. How do I view the data from Database 1 in…

I'm currently using a simple "Auto enter Serial number", but would like to use a more secure PrimaryID.(As in the article by captkurt!). How can I replace the old ID and still keep the relationships. Peter

I am using FileMaker 6.0 and am new to FileMaker. I have three tables in a many-to-many relationship. (Vendors -< Ven_to_Prod >- Products) I want to be able to select three vendors for a product on the product page and select one as a primary vendor (check box). I also only want to show the vendor name in a pop-up list (not the vendor id). I can't seem to figure out a way to use the vendor name and have the vendor id entered into Ven_to_Prod. I am currently using a portal on the Products Layout to enter this information. Thanks for your help. Pulse (a.k.a josh)

How can I make a gif become a hyperlink to a record in a database for example. I want to layout a pictorial of arranged gifs denoting a computer lab setup. When the gif is clicked it would bring you to the the records of that computer(hardware address, ip number, problems and resolutions, etc.) Then I would like to use iwp to allow the intranet to have access to those records when they are experiencing problems with a specific computer. I do not know what to use in order to create a link to information from the gif thanks for your help in advance.

Grrr! I have a database that stores virtual information for paginating a newspaper. The structure goes like this: One Issue to Many Pages One Page to Many Foreign Items (info on stuff that was put on the page before the database examines it) One Page to Many Gaps (Blank spaces on the page) Now, here's the problem. In the Issue DB, if I delete a page, it deletes all of its foreign items records, but not all its gaps. The two are related in the same fashion, and the relationship to Gaps is valid. Both have the 'delete related records' box checked in relationships. Nonetheless, no error, nothin, just a whole bunch of orphan Gaps. Thoughts? Feelings?

Dear All, I would like to know why sometimes my links are not connected . Anybody haas taht problems and has to redo the link? (ralationship with other database or redo the link to value list ) Thanks

FMP 5.5 Mac OS X: I have a database of advertisers, a database of magazine editions, and a join database that matches up the two, such that one advertiser can be in multiple editions. On my input form, I have defined a value list of the editions as a lookup into the editions database. This value list is expressed as checkboxes. Each field is set to create related records. When I add a new record in the advertisers database, I get a new record in the join table. The problem comes when I have more than one edition to that advertiser. Instead of getting two records in the join table, as I would expect, I get one, with both values in the field. This is not optimal at all. How…

I have a related orders database and customers database. The customer databse populates the order database with the customers address, etc based on a relationship of first name, last name, and email address. What I want a script to do is this: Go to a related found set. So, for example, I have a found set of a hundred orders in the orders database. I want my script to jump me to a found set of the customers in the customer database.

Here is another relationship question on a dynamic variation. Main file: Jobs.fp5 Related file: Contacts.fp5 Existing relationship: User ID=:User ID Currently this works as a new record is created in Jobs.fp5, I can put a User ID number in a field, and the other fields for address, phone number, fax number, ect. are then automatically populated from Contacts.fp5. Pretty straight forward. Now I want to add a portal. I have used portals a lot, but I want this one to be a bit different, and I dont quite know how. Here is how I want the portal to work. Select a job in Jobs.fp5. Go to the portal, Select a User ID and have that EXISTING contact from t…

I came across a sample contact database that I would love to figure out how to do. Contacts are entered in one of 2 layouts. One layout for companies, one layout for personal. View database as list view and see all contacts, both company and personal. Simple so far. Go to a company contact record. There is a window at the bottom that looks to be a portal. you hit a add button and can add personal contacts associated with that particular company. These personal contacts that are listed are not from a related seperate databse. If I view all the records is list mode, I can see the personal contact as each individual records. I am not quite sure how in the same data…

Hi everyone. About three weeks ago, I posted a question on "basic structure concerns." Everyone helped me and it was determined that I needed a separate Invoice db because I have a many-to-many situation. I have done that. But last night, reviewing back through 'Finds', Kennedy responded to a gal with an Invoice file, NOT TO EXPORT to create her invoice file ... it was a bad idea. And that's exactly what I have done. Help! If I do not export to create my invoice file, how do I populate it? I originally created an Invoice db with Invoice#, Service# (joined to Service db). Then I just placed the fields from Service db on the Invoice --- problem is, what is invoice…

Hello all I have a problem, I am new to FM. I am developing a DB for an internal storage system. I have a unique ID for

i have a file that holds sales in certain countries and until now the region the coutry was in was calculated, but this meant that no one could add new countries without me changing the calculation. so i now have a file called coutry which contains a list of the countries and the region thay are in however i had a self relation in the old version that showed in a portal all of the other sales in that region when you were on a record. now i cannot create the self relation region::region and cannot get this portal how can i get this to work i have many other similar problems to this as we have added a product table, to specify which group and sub type a …

I am trying to set up a database using a auto number as the primary key. I then want that primary key to be used in another database to link the information, but I don't know how to! Can anyone help me?! Thank you

There's a section on this in the fmp5.5 user's guide, but I think their example's a little off so I'm going to have to ask for help here. I'm modifying the fmpro solution framework, and trying to make my own print layout in the lineitems file. Everything is working great now, except for two of my merge fields. The merge fields, which look something like this <<People_Cust Number::CompanyName>> worked just fine in the Invoice file because it had a relationship with the People file with the customer ID number as the match field. But the line items file has no such relationship. The user's guide says to create a new field in lineitems, a calculat…

Here's my problem: We gotta family db related to parents and students. Their all bound together by a unique serialization family id number. So when you add a student or a parent, (through portals) it automatically assigns the family id, add removes siblings or parents, etc... Now our Parent Assoc. (we're a school) wants to access the parent db of our database, but I don't want them to be messing around with the real parent db. Is there any way "mirror" the parent db so they can have their own parent db in which all the info. will be updated as our office updates the "real" parent db? I was thinking about putting a portal within the parent portal but I don't think t…

I'm new to FMP and have been struggling for days. Someone please help me. It looks pretty easy to calculate summary data using a self-join; however, if I try to multiplying data across sequential records to get a cumulative result over time, I get lost. Here what I'm trying to do: I have 5 different investment portfolios (portfolios.fp5), each has its own monthly performance history (performance.fp5), creating a one-to-many relationship. In the peformance.fp5 file, I'm attempting to calculate cumulative performance by keeping a running index which starts at 100. In other words, the index = 100 and if the first record shows a 5% return, then the new index is 105.…

Hey all. As much as I don't like to repost, I'm afraid my last one got buried under a complex thread, so I'm breaking out my response into a new topic. The basic problem is 8 sets of DBs, all with internal relationships, with each set of DBs have the same names (eg, Central, Medical, Intl, etc) Each set has many individual fxns and is housed indepedently at 8 unrelated sites. One master DB aggregates the data from all DBs, remotely. Am I going to have trouble with the internal relationships and grabbing data from 8 different DBs, all named the same thing, but with different IPs? Do I have to rename the 8 DBs with unique names for each site? While it would be easie…

Hello everyone. I was wondering if someone can help me. I hope im placing in this in the right section of the forum. Im new to the forum and FM. I gotta membership base that has some simple fields like Name, Address, Phone and things like that. What i have is 2 fields im trying to tweak actually 1 but you'll see what i mean. I got 1 field simply called Name and below it i have a field call Sortname. I want to be able enter a name into the the field name example: John E. Jones but automatically put in the sortname field Jones, John E. Does anyone know if this is possible? Thanks for anyhelp you can give?

Dear All, I have a problem which I could not figure it out how to solve it. I have a database named A with unique identification Iden_A (calculation (projecno,year) = 1202 I have a database named B with unique identification Iden_B (text) = 1202 Before I go from A to B , I set Iden_b = Iden_A then go to related record since in B there is no Iden_B = 1202 . Is it the correct way to do? So when I duplicate a record in A ...and I duplicate record in B and when I change A , I can go from A to B which is a duplicate. Let's say : I duplicate A 1202 and change it to 1204 when I go to B, in B there is 1204 ( which is a duplicate of 1202 of Sorry my Engli…

Can anyone help me figure out the fastest way to store the contents of some related records into one text field? Essentially, a portal works, but I have to export the data to a text file so I need to squish the data into one text field. Background: I have a bunch of people participating in multiple events. A self relationship based on participant gives me the unique set I need to export but I need a way to put all the events into one field, seperated by a paragraph return. Sort of a summary. I'm probably overlooking something simple but I'm brain fried. Thanks.

Hi there. I have this relationship. It is pretty easy to understand, but it wont work because my calculation field cannot be indexed because it references a field from another db. Here is the relationship: - Client Database::Contact_Calc - REL - Contact_History_DB::Contact_Calc2 Client Database::Contact_Calc field Contacted_Date_Sort & " " & Originator_Sort Contact_History_DB::Contact_Calc2 field Contact_Date & " " & case(Client_Database_ Rel::Originator_Sort = "All"; "All"; Originator) As you can see, the Contact_Calc2 field cannot be indexed because it uses a relationship field. Basically, if the Originator_Sort field h…

I'm new to FM, but have used Access for years. I'm making a database that tracks responses to a questionair. I've made the following databases (tables): employee (key field employeeid), questionair info (Key field questionaireid: contains information about the particular questionair filled out by the employee), questions (Key field questionid: contains all the possible questions), questionaire results (Related fields employeeid and questionairid; contains all the results from the filled out forms). I can't figure out how to do much of anything with this though. For instance, I don't know which of these I should use as the "Main" database Which db should I use as the main …

I have created a value list from another file that show the values from two seperate files (example Firstname, Lastname) I now would like to choose that combo to be enter into my new field. It looks like it will work, but I pick the name, but it only uses the first part (example: John smith shows up in the value list but when I pick it its only puts the John, into the field.) How can I get both parts? RET

Hello. I have a MAIN file which references a few OTHER files. The MAIN file has thousands of records, but I would like to only see those records that actually reference a least one OTHER file. I figure I'll make a script that on the file's opening, it will find all records whose Reference Count is greater than Zero. but I can't figure out how I count the references. Thank you.

I have a Services db. This contains therapeutic services performed on clients (ServiceDate, Client ID, TherapistID, etc.). I have a Clients db, a Therapist db, and a Procedures db (containing ProcedureCode and Rate). I have a Contracts db which contains agreements from several

Can anyone tell me which is the best type of field to use for a relationship field. I know that in a lot of databases it is best to use a numereic ID. I have also heard it said that in filemaker it is fine to use Either number or text. What are the real facts about this. Also why should we avoid using calculation fields?

When I open a local copy of my main DB it automatically opens related DB's through the network (shared folder) instead of from FM Server... The fisrt time I opened the local copy it actually asked me where it could find the related DB's and I showed it the files through the network instead of using the Hosts button... Now that I realize my stupid mistake, I want to change it to the FM server DB's... How can I do that ? I tried closing all the related DB's and opening them manually from the Hosts but when I opened the main DB it still went trough the network and opened a second copy... Thanks

Dear All, I have a big problems which I could not figure out. I have 2 database Number One ( A) and number Two ( in A , I have Year (Text) : nil, No. (text) : 12 . Unique key : nil12 In B, I have YearB( Text): nil, No.B(text) :12 Unique key: YearB & No.B So when I go from A to B , I create a record in B, so Unique key in B will be nil12 So when I change in A Year: 12 and no. = 12 so it will go to B and change B as unique key Year : 12 and No.B = 12 When I duplicate in A as Year = 12 and No. = 12 , I would like to duplicate in B also as YearB = 12 and No.B = 12 when I change in A No. = 20 ..so Unique key will be : 1220 How can I go to…

In my master file I have a layout that is all fields from a related table, but the fields are not in a portal. I don't want to display all related records I just want to add new ones. The relationship is a One to Many using the A Serial Number Field. I am tring to add multiple records to the related table. If I enter data into the related fields it stores the record but how do I create a second record for the same Serial Number with out leaving the Master file. Do I need a portal?

I am using a portal to display a chronological order of events. I have the relationship set to sort by time, but I need 1am to come after 11pm. I tried to used the custom order based on a value list, but it doesn't seem to have any effect.

Hello, I am new to FM, and I have a question about a db I am trying to create. Should be pretty simple - but it's not coming to me. Here it is: I am creating a recipe DB, and so far aI want to use 3 tables. 1) a recipe table (main browse/recipe entry form here). 2) an ingredients table. And 3) an Amounts table. Here is how 'd like it to work: Each recipe should have several ingredients and each ingredient will have an amount that is unique to that (recipe and ingredient). I hope that makes sense. I've tried several things unsuccessfully. Basically the ingredients will list ingredients that all recipes will use - only once of course. The Amounts table wi…

I have a need to filter a (40,000 records)mailing list down to all records that are within a certain distance. The distance of course will change depending upon the mailing. I found a program that will provide a list of all the zip codes within whatever distance I give it. The list can then be exported in a tab delimited list. How do I make a relationship that will use this list of zip codes to filter out my list. The zip codes may be within a large number of different ranges, so just doing a find that way is too difficult.

Howdy! I'm new to relationships and could not do this on my own or find any examples of it already done (I thought it'd be simple)... I want a "stripped down" version of a more detailed database for web publishing. FM suggests something like this on page 9 of the FMP Web Publishing Security Guidelines (URL below) and mentions relationships and calculations. If database A has all my fields, and "stripped down" database W has only those fields I want to publish on the web... how do I make it so any new records created in A are also automatically created in W? A is the main DB users update. W needs to be as fully searchable as though searching A, just wit…

Hi all, I forget, is there a way to identify the layout on which the fields from specific related file are located. Can it be done with Database Design Report Tool? If I change the relationship, I want to avoid going through all the layouts, and searching for <missing file> fields. Thanks

I have two databases. One has a unique serial number field. The other has the serial number field, but it is not unique. Thus a one to many relationship. I then have a machine number field in the second database. The Serial number and the machine number combine to make a unique record. How can I use both fields to return only the single record I am looking for? I was thinking if the user finds the serial number first then the relationship portal could return say 5 records, but if I then prompt for the machine number I could filter out the 4 records I don't want. I just can't help thinking there is an easier way than to script all that. I do in other database all the time…

The manual states several times: an index is determined by up to the first 20 characters of each word for a total of up to 60 characters. But the manual never answers: "What is a word?" And I wonder if the word delimiters change with language... for example, maybe words are broken only by spaces in English, but by hyphens and spaces in ASCII. How about commas? Does punctuation break words? Anyone know what's a word in English? in ASCII? (I am using 14-digit keys in all my files. English vs. ASCII doesn't matter there (other than ASCII being a tad faster), nor do the character limits. However, when I need compound keys, I clearly need to make su…

When using the auto enter option to create unique record id's, there seem to be some disadvantages to that. For instance, that when re-importing the data the value is auto-entered again. Anyhow, I have to get into this more, but my general question is, do you outthere use the auto enter to make the ID's, or do you do it in a scripted way, with the creating and duplicating or records attached to buttons doing more then only creating a new record. Or might it be possible to trick the auto enter with a calculation that catches up with the situation that an auto entered record ID is already present to leave it as it is. (which seems fine when re-importing records, but not…

Hi friends, long time no visit, and this site gets better and better! Reckon my brain addled over the summer as I can't work this one out: I have a database in which the records are people. There are two number fields, Area and Position. Data in Area can be any number between 1 and 100, and data in Position can be any number between 1 and 10, though not unique (as each Area has positions 1 thru 10). As I work, I need to know which numbers have not yet been allocated in Position. OK, through a relationship I can show in a portal that e.g. numbers 1,3,4,6 and 9 have already been used and exist in the database. But how can I show that 2,5,7,8 and 10 don't yet exist…

This is pretty easy but my brain has just died! I have a database with for example car makes. I have a field called 'make' which is a value list of different makes (with me so far). I also have a field called 'model'. What I want is when I select ford I only want ford cars to appear in the field model. Likewise if I select Fiat I only want Fiats to appear!?!?!?

When things do not work, you look at it again, look at the manual again, set it up again from scratch, and some way or another. You overlooked something obvious, or it is a combination of other things. At last it will almost always work. Exploring (at last) all about relationships I encounter something I can not solve, and give up trying to understand why it does NOT work. It's simple: I have a master and a slave. In the master I have a portal showing data from the slave. I created a many-to-many relationship with as a matchfield a calculation number field #Key=1 on both sides. The field in the slave is set to indexed. #Key=::#Key. In the 'edit relationship' sectio…

Hi, Have a (beginners) question. I have a database file 'vendors' and another one 'types' One vendor can be more types and one type can have more vendor. What I am looking for is the following, When you go to vendor, the aim is to see all the types that exist and then just click in a check box if which types the vendor belongs to. So if i am not mistaking i will need 3 files, : vendor- join- (this table has the primary keys of the other two) types I am not sure how to accomplish this, in which file i do this best , any ideas are welcome, Thanks, Thierry ++++++++++++ 1+1 =1

Hi All.. make sure is this the right category for my problem: Inside my db, tbl_invoice_items i have: prod_code | monthOrder | invoiceNum | quantity| remarks 111 | Jan | ref001 | 50 | 112 | Jan | ref001 | 20 | 111 | Feb | ref002 | 10 | 113 | Feb | ref003 | 40 | ... and so on... I need to find out the top 10 best selling products. I have done a field to calculate the total unit sold. I know i can use subsummary to preview the top 10... but i need the effect of a table view and able to enter the data (remark…

Created 2 files that had 1 field (text, indexed). Related these 2 files on their 1 field. Each file has 1 record. File 1 had the data in its 1 field of 111222333444555666771 The 2nd file had data in its 1 field of 111222333444555666772 Notice that the last character is different, yet FileMaker believes that these records are related. After testing, FileMaker only checks the first 20 characters of a relationship. This problem is also true for checking for duplicates as well. Is anyone aware of this? Is this documented anywhere?

Hi, I just discovered multi-key relationships (where a relationship is based on a text field containing a list of values). I can't believe I've worked for so long with FMP without knowing about this ! I though that the only way of creating a many-to-many relationship was to create a join file! So before I go ahead and implement this rather simple technique into my development, I would like to have your opinion and some answers What are the advantages/disadvantages of join files ? Are there limitations with multi-key relationships ? Why doesn't FileMaker mention multi-key when it comes to many-to-many relationships ? Is there anything I should be awa…

I've designed my fields and relational structure. I've designed my basic tabbed navigation that will be common across all layouts. Now, what's the right way to set this up such that it is easy to manage?? Is there any notion of a template or master layout, such that if you make changes to it, all the other layouts based on it get those changes automatically? Or do I need to just be prepared to cut-n-paste to each file? If the latter, are there any key things to know regarding cut-n-paste? For example, if the buttons and scripts are assuming certain field and layout names, do I need to make sure those each exist prior to cut-n-paste of the layout elemen…

Greetings FM Gurus, As I continue my journey in the deep and abiding mysteries of FileMaker, I find my traditional skills and experience challenged at every step. My question has to do with complex relationships and bringing in information from a "3rd layer" relationship into a form. This example demonstrates how multiple tables can contribute to a central form that is displaying comprehensive information. In other words I need this form to display information from the other tables simply based on related fields. See the DB structures below: There are 3 tables from which I need information in my 4th Table called HuntingSpots. This database is for a sp…

On a new discovered Belgium forum about Filemaker (www.clarify.net dutch language..) I found this link: http://www.onegasoft.com/articles/smartranges/index.shtml about intelligent relationships. Seems worth looking at. (The link is in ENGLISH) Harryk

I want to set up a many to many database and I have followed the example in the help to set one up. My situation is slightly more complicated and I wanted to find out if it is possible. I have a number of animation SHOTS that I am working on from a particular project. Each shot will be sent to the client several times during its refinement. Approximately once a week a tape is prepared containing the shots that have been updated since the last tape. These shots are labeled by the version number that increases each time it is compiled on a tape. So each SHOT can appear on multiple TAPES (with a different version number) and each TAPE can contain mul…

The more i think about this the easier it should be but here goes anyway cause my mind is blank. I have two files: One is list of club members. The other is called Best performances. Both are linked via a unique id field. The main file is the club members file. Some of the club members will appear in the personal best file and some will not. Also members will appear several times in the personal best file in differnt events. OK so here is the question. All I want to do is show records in table/list form of records from the personal bests file + the name from the members file. Simple BUT.... The file just keeps showing only the first record from the club members …

I've been doing Object-Oriented Program for some 15 years. I've designed some large, successful object models... each geared to mostly be run in-memory. Now I am using FrameMaker and designing a medium-sized relational database. My questions for you who are more experienced than I: 1) When making the choice between N fields, a repeating field, or a separate related file, how does that differ from the similar choice in OOP? Are there costs associated with separate files that are bigger than with separate object types? Are the costs of indexing and doing finds very different? 2) In OOP, objects can refer to others very directly and very cheaply (via an o…

Help! Please! I'm relatively new to FilePro 5.5, and kinda under the gun with a very big project. I was thrown into this position with no training, and no one in the office is able to help! I have a master db (I think), and another related one that I would like to remain "static", for lack of a better word (not quite awake yet). I need to input the data in the related db and have it come up in the master db; the way it is now, as soon as I enter a number in the master to pull up the data from the related, it changes everything around in the related and doesn't come up properly in the master. I am in dire need of assistance!

Hi! I've read elsewhere in this forum how to avoid this rather annoying problem. However, following the advice not to use repeating fields with relations won't help me; that would mean creating a nightmarish mesh of up to or above a hundred extra fields. Yikes! My problem relates to a couple of projects. I'll use the easiest one for example. I have a multi part movie database, to keep account of my collection. The main db has (amongst others) 2 layouts with repeating fields linked to different related db-files. One is a role/character and actor list; (Rep.field Role/Character) (Rep.field Actor) [Role/Character] [A…

Mac FM4 1 Quotes File with Repeating Fields including Product Code and Product Details. Product Code has Pop Up List to show Product Code and Product Details which of course come from Charges File. Quotes and Charges Files related via Product Code and set to sort in Product Code order. Problem: The Pop Up List always sorts in Product Details order instead of Product Code order. If the Value List is set to display Product Code only, the sort order is correct. What am I doing wrong? Thanks for any help.

Hello, I have three related files: Art Invoice Photo invoice Invoice Report Both Art Invoice and Photo invoice have portals to Invoice Report. I want one field in Invoice Report to be able to display either ART or PHOTO in the record, depending on which file it came from. I've been stumbling around this seemingly simple problem for days, and can't find the answer. Any ideas? Aaron

I have realtionships that I would like to teminate after a specified date. Once a certain date is trigerred I reference a local field and no longer need the Related File to be referenced. Is there a way to do this? Thanks in advance!

Hi. Im trying to make an quality and inventory system for food manufacturer. The workflow is as follows: Reciving of raw material (lot no./date/prod.code/quantity/price) Then some of the raw material is sent into production in period of some days. (I use two files, rec_raw_material and raw_material_in_prod), related by the lot no. I do this to track what raw material was used on each day of production. The producer manufactures some products from this raw material which was sent into production, (I use three files, production (date, item numer) which is related to raw_material_in_prod by the date, production_items, (date, item number, quantity, etc.), whi…

I have two related databases that allow for items to be checked in and out. One database holds the item inventory and the other is a user list. As items are checked out they are added to the users list of current items out and theusers name is added to the item inventory. They work great but when an item is Checked In it is deleted from the users account. I would like to create a history file that would list all of the items that the user has ever taken out. I cannot seem to do this without the information disappearing when items are checked in. I have tried various relationships and editing the script to set a field in a different database to a value when the item is ch…

hello, i have 2 db's ,the main one has all my data in it and the second one stores my found sets. What i am now trying to achieve is a way of making my (sound FX database) "project" based .ie the user enters a new or existing project at the beginning of the session and using a conditional value list only shows the saved found sets of that project. i have sorted out the conditonal value list but what i can't figure out is how to link the "project" fields between the 2 databases . in an ideal world i would like to have the current project in a field in the HEADER of the main database so the user knows what "project" they are currently working on ,when the user w…

I