Relationships

Hi, I have a file that is used specifically for data entry of customer orders. Several users have a copy of this master file, and do only data entry (some offsite, some in the office) for a client. After the d/e is completed, the files are sent to other users who have copies of several databases containing information that is used for lookups based on date & name fields within the d/e files. (The lookups are then performed via script & relookups). Eventually, all data is imported to a master file for future reference & record-keeping. For the users who have no copies of the "lookup databases", when they enter the date and names, they get prompted to open…

Short question: Is there any way to change the order of check-boxes in a value list from alphabetical to some custom order? If not, read on ... Long question: 1) A part of my database consists of a many-to-many relationship between two files: -a "companies" file -a hierarchical "products" file. I'm using the join file structure between the two for reporting purposes. In the "companies" file, I want an entry screen showning a hierarchical display of the data in the "products" file (through a portal), with some form of checkboxes beside the entry options to display data already entered, and to act as buttons for entry. Any suggestions? 2) Also, I was …

I have two files related by a Location field. I am not able to get to the records that have more than 20 characters in the location field if there are other records that have the same first 20 characters in the Location field, For example: Details in the record with location "GigabitEthernetBBone" is always shown for records "GigabitEthernetBBone_london", "GigabitEthernetBBone_paris" or "GigabitEthernetBBone_tokyo". Does anyone share the same exprience. Any work arounds? Thanks.

I'd like to set a date field to the current date when the data in a field changes to specified criteria. For example: I have a text field called Record Status. Sample data for Record Status would be: "Not Yet Started", "Started", "On Hold", "Not Resolved", "Resolved". When records are imported into my FMP db, Record Status is set to "Not Yet Started". I want to set up a date field which will pull the current date when Record Status is changed from "Not Yet Started" to any other disposition. The problem I've had is when Record Status is changed more than one time (i.e. if Record Status is changed from Not Yet Started to Started on 11/1/01 and then on 11/2/01 it is ch…

We currently have a file "Contacts". Which contains all sale contacts, with multiple fileds for the normal "sales analysis type stuff". We now need to add an event field with an independant date field and an event type field (quote, catalog etc) all following the date field. We would like to be able to track multiple events for each contact - in one entry screen and then track the activity for each contact on a separate summary sheet showing activity by month. So far we have used a repeating field for the date/event in the entry form, however, when we try to take this information to the summary form (where we first do a find on the entry form for the appropriate date …

I have a layout which is used to track art items that have been ordered by customers. The layout shows a single item, where it is in production, etc. In this layout, I need to show all of the other items that were ordered with the piece shown. However, I don't want the item shown to appear in that list (that would be confusing to my users). I easily created a portal with a self-join that shows all of the pieces in the order. How would I modify the self-join relationship so that it shows all of the other peices in the order, EXCLUDING the currently viewed item? Thanks. -wald

Dear FM Pros, This is probably a known problem: How to keep an Auto-Number nice and sequential even after deleting records? I have this number as Primary Key in one of the files. After I delete a record, the number doesn't "slide", but keeps incrementing. So, if I deleted 1000 now the order is 999, 1001. That's uncool! I can write a replace script everytime I delete a record, but am SCARED to mess with the Primary Key!! Is there any other way to do this? Thank you, and keep your bikes well oiled.

I have a database that contains the fields 'Application' , 'Classification' and 'Equipment'. Each Application contains many different Classifications, and each Classification contains many different Equipment. Using self-relationships I could create a value list that when a certain Application was chosen, the list of Classifications inside the specified Application were displayed. I also created a value list that would display a list of available Equipment for a specified Classification the same way. I use these lists in a "Detailed Search" where the user chooses the Application they want from a value list based on the field 'Application'. After the Application is ch…

Hello, My platform is pc and I am having a problem trying to lookup data. Presently, I have a file named clinic fee schedule, Encounter(master). On the master encounter I have three fields titled Fee ID, Fee name, Fee amount. In the clinic fee schedule I have approximatly 18 different clinics. Within these clinic there are generic services approx 20 the clinics perform these services for our office, but we pay each clinic a different fee amount for the service they provide. Example, Physical, EKG,Drug collection are the generic names but each of these have different fee amounts according to clinic. I have all this information in the clinic fee schedule file. These are…

Any ideas how to make such a calculated key? I tried playing with Status(CurrentRecordID. I made a field called FM_Static_RecordID to store the RecordID. Then I made the relationship calculated key: If( FM_Static_RecordID=Status(CurrentRecordID),"",order_ID) But this doesn't work. Any ideas? Thanks. -wald

As per (Professor) LiveOak suggestions I revisted my assumptions of what I thought was Structure and what was Data. So now I have a simple DB that has: Employee_Name,Date,Hours _Worked and Daily_Detail. I can use a calculation to find week# and total hours per day etc My Dilemma is that I want a field for a weekly summary. Not a report on each weeks daily_detail, but a separate field for summary. It will be faxed off to a Human Resource admin who doesn't want the daily detail that the manager wants. Now if each record is basically based on that each date is a new record. How do I have a summary field available to add/enter text into for every daily entry, but only w…

I have had FM since v.3, but have never had to do anything comlex, I'm in the midst of my most complex database yet, and I've run up against the limit to my understanding. For a long time I could not understand Portals. The Invoice With Portals tutorial on http://www.maclane.com/ was a tremendous help, and got me started. Based on its recommendations, I have an invoicing/order management system set up with a Customers File, an Inventory File, an Invoice File and a Line Item File. This is all great, and I love the way the files all communicate. Next I added a Registrations File to store the data we retrieve when people register our software. I had no problem tying t…

Hiho, I seem to grasp the basics of portals and relationships between files. Here is my dilemma. What I want to do is... There are 7 employees. Every employee will fill out a weekly report, consisting of the number of hours worked each day and detailing what was accomplished each day, as well as summary of what was done each week. So Employee#1 on Week #1 did 8 hours monday and detailed what they did, same for every day for the rest of the week. At end of week there is a summary of what Empoyee#1 did on Week#1. This is twhat each employee does and is repeated for week#2 and etc.. Is one way of doing this to make a unique_id based on Employee_Id&Week_No? This wa…

I haven't worked in FileMaker in a long time. I'm creating a database of contacts and have created a relationship to categories for each contact. I will use a portal as each contact may be under several categories. Now...when I attempt to enter into the related field I get the message "This field cannot be modified until "category" is given a valid value." This is likely something very simple that I'm just forgeting to do but I'm gettin' very, very frustrated trying to figure it out. Can someone help? I'm using FM5.0. Thanks much, Nick 2

Hello - At my museum, I have created three identical databases (A1, A2, A3) that collect different data (about specific artifacts) in the same format from different users in different locations (three different conservation labs). These three databases are connected (one-to many) to another database (, which has information about exhibits (pulled into each artifact record in A1,A2,A3 by a pop-up and simple lookups). I need to create a fifth database © for the manager which collates and summarizes the information from all three labs (A1,A2,A3). If possible, I’d like to perform finds from C across all three dbs (A1,A2,A3), showing summary information and detail. I’d like …

Hi: I've got a main file (File A) and a subfile (File . On one layout in File A I have 2 portals into File B. I want to display the first 25 records in the left portal, and the second 25 records in the right portal. Each record in File B has a sequential # from 1 to 50. The left side works fine (it displays all the records, but since I don't have scroll bars displayed, only the first 25 rows are visible. I tried the following calc in file B: cRightPortal= Case(RecordNum > 25,1,0) I set up a relationship in File A where the File A constant 1 = File B::cRightPortal I'm using FM5 on Win 98. Thanks.

Sometimes, related files appear in script steps with an IP address in parentheses and other times not. I've tried authoring the file with the network protocol turned off in the preferences, but to no avail. I can't even figure out where it is getting the IP address. I am definitely NOT specifiying the related file by pressing the HOST button, although on other machines I have see that doing so results in an asterisk alongside the file name. I have had no luck finding anything about this subject in the FM documentation, and I just wish I knew where it was coming from and how to edit in once it is there.

Hello World, I have setup a ProjectView.fp5 file to give a listed overview of all the records in my Project.fp5 file. I have created a sorted relationship and am viewing through a portal on the ProjectView.fp5 file. Everything is cool, it works fine. Now, I wanted the option for alternate sorting through the portal. I thought the best way to this was to duplicate that layout and replace the portal relationship with an alternate, giving it different sorting properties. My problem is it just not working. When I duplicate the relationship, change the title name, modify the sort order and apply the new relationship to the portal, the only record that displays is the fi…

This must be simple to do but I just can't figure it out.I have 2 realted files related by Id.Its one to many realtionship.A has got one product code and B has got many products with that product code.I want to set a field from the last product of B into a field in Product A.How do I do this. Thank You

I am creating a database of companies we deal with and there contacts. I am working with 3 files. Organisation (Org_id, name, type, etc, Address_ID) Address(Address_ID, Street, town, county, etc) Contact (Con_ID, Name, Org_ID,Address_ID) I am trying to create another file (layouts (Org_id, Address_id, Con_id))to hold all my layouts in. I am working on linking this file via the fields with the same name in the other three files. My problem is will this work and how do I go about creating records in the other files without creating a record in the layout file. Thanks for any help I need it

computers come in and i enter their info into a file. right now the only thing unique in the files are auto assigned/auto incremented (by 1) record numbers. i have another file with records of people (individuals). again the records are auto number. it's easy enough to match the record number fields of the two files to get a view of who has which computer or vice versa (which is also important -using a search). my problem is this: some people have more than one computer. how do solve this? i want the db to operate as it does now, i.e., i can find computers or i can find people (don't ask me -all i know is it works now and i want to add the capability of more tha…

You are telling Filemaker to use the "File1::Name to File2::Name" relationship to lookup the value for Name. So Filemaker does not know the Name and cannot lookup the name, because it does not know the name. See the problem? What are you trying to accomplish? Just explain it in plain english or pseudo-code and either you will see the solution or we might be able to point you in the correct direction.

What is the best way to walk into an existing system and understand all the database schema that currently exists. Is there a documentation feature in FM 5.5 - I don't see one listed in the user guide or help? This particular system has about 50 tables and many interrelationships and I'd like to get a jump start. Where should I look? Thanks!

Hi, I have two databases: one for designs and another for fabric numbers. Each fabric can be used by more than one style. If I enter the fabric number in the designs database for each style, I can use a portal in the fabric db in order to show the styles that are using certain fabric. But my question is: when I first assign a new fabric, is there any way to enter, in a field or portal, the style numbers that are going to use it, so this fabric number shows in the fabric field (in the designs db) for each style that is using it? (without having the need to enter the fabric for each style in the design database). Please advise! Thank you in advance

Hi! I have a 4 file database I'm working on for my church. It is fairly straightforward, but I have at least 1 design issue. Here's the layout... I have a Family file, Person file, Tithe file, & TitheDetail file. The relations are simple with all relations to Family based on a FID field, all relations to Person based on a PID field, and all relations to Tithe based on a TID field. The difficulty I'm having is that the Family file contains the address info for the family unit, but one of my tithe reports is in the TitheDetail file which requires address info. I've created a PID field in TitheDetail that looks up the value from the TID relationship back to Tithe. …

With extensive work arounds (not for the user) I created multiple to multiple related databases (FM 4.1). We recently ordered FM 5. Will the new version make it easier to create this type of database? I'd also like to know if anyone else has created a mutliple to multiple, it would be educational to see other styles & ideas. Thank You Jessica

I am more familiar with Access... Is there a way with FM to build a front-end that links to a back-end(that would have all my tables) I want to be able to update my front-end design without changing the data in the back-end portion. Thanks for the push in the right direction --Dan

My guess is that a calculated field such as Company & " - " & CustomerFirst & " - " & City in each file would satisfy your requirement, assuming that this becomes unique enough. How many guys named "Joe" work as Acme, Inc in Los Angeles?

I am using the trial version and trying to decide if this is the appropriate DBF platform for a company I'm doing work for. Just a couple questions before I can get my hands on the manual. 1. How do you create logical (Y/N) fields? 2. In a parent child relationship, how do you tell the layout that you want a scrollable region of children within the parent record? For example for each reciever number, multiple items are being recieved. Already have a reciever dbf and an item dbf and I believe I set the relationship correctly. I can put the children on the form, but can't control them. 3. Most importantly, does the manual that comes with the program help you get s…

Here goes I have created a database which holds all of the users information including what software they have. For the software i have created a seperate database. For each bit of software i have created a new record. Each record has a number, e.g 001,002 and so on. This is the field i am using to link the 2 databases. So in the user information database i have a field which is linked to the field in the software database, and once this number is tapped in it will drag all the other information from that database e.g. name of software, version etc.I have created at least 10 of these fields all linking to this one field in the software database. So whn i do a search in t…

First of all I apologize if this is posted in the wrong area... Second, this is my delema: These are not really the names of my files. I have one file called name_and_address and another called jobs. Every time I do a new job for a person, I would like to create a new record in "jobs" without overwriting the previous record for that customer. In name_and_address I would like to enter a new record for that customer but maintain her/his customer id. My problem is every time I create a new record I somehow overwrite the record in "jobs". I have a relationship set up that uses the customer id from both name_and_adress and jobs. I would like to keep a history of the jobs f…

Hi, I was trying at my first database, and came up with a need I do not know if is supported by Filemaker. The database is basically about bios of several people, and as such has the following fields: first name, last name, id (serial), biographical text (long). In some of the biographies the name of other people who's own record exist in the database are named. That is why I wanted to use their names in the bio text as an hiperlink towards their own record inside the database, the same way hyperlinks work in hypertext. Some webpage addresses appear in those texts too, so a link for those pages which would open the default browser and go to that page would be desirable t…

Hi all, I need to know how to resolve a relationship/interface problem. STRUCTURE: I have 3 Db's that need to be related to one another, where: A=one to B=many B=one to C=many Db A: ID-A (serial) Name-A (user input) Db B: ID-A (value thru relationship from Db A) Desc-B (user input) cID-A.Desc-B (calc=ID-A&Desc-: Db C: ID-A.Desc-B (value thru relationship from Db B; cID.Desc- Desc C (user input) FORMS: Db A uses a form to enter records AND a portal to enter Db B records. That works fine. Here's the issue - I need the user to enter records into Db C that are related to Db B, on the same form. SCENARIO: 1. User enters a record for Db A …

The aplication I'm developing is formed by 18 DB. One of them is related to others through a field. The Relationship is working good for all except just one in which the data is not shown, in spite of its settings are good. I don't understand what it could be the error here. The FM version I'm developing on is FM Pro 5. Can anybody help me???. Thanks a lot in advance!!!. Mr. YMJ Caracas, Venezuela

I have built a series of databases that work together to make one complete solution. The application has 3 different contact rolodexes as well as tape database. I want to have the clients name from one of the rolodexes go into a pulldown menu in the tape database. Is this possible, and if so how do you do this.

Hi, Is it possible for the result of one relationship to become the master for another relationship? I wish to be able to select a client from a list, that then limits the choices I have for contacts. This much I have working. Now I want the individual contact information to be displayed relating to the contact name, not the client. Can I make multi-level relationships?

I have purchased several FM Pro books but I can't seem to find the answer that I'm looking for ANYwhere. I currently have two databases - Database A (student) Database B (school records) I have successfully linked the TWO together but now want to link a THIRD database - Database C (contact database). For some reason I can't seem to figure it out. In the student database I have personal info...name, school, etc. Whenever I enter a new record it automatically appears in the Database B (school records). Now I want the same info to automatically appear in the third database but can't find a way to do it....is it possible? I also have to generate reports and the i…

i've got two files: one of people, one of computers. no problem causing the (as in "one") computer's information to show up in the master file -people is the master. i have laid out the related fields in my layouts/report. my problem is: (and i guess there are several "looks", but i just need one good example)... how should i relate and put/insert the related fields in the case of one person having several computers. (i guess this like a one-to-many, no?) i can't believe that of all these books i've got they only have either only 2 files, which i can do or they have a whole bunch. i really really need rthat intermediary step GIVEN to me. ./rant if anyone wants to…

I have been given the project to take the existing system and make it more efficient. I need to do this without loosing the existing data. Right now there are over 23,000 records. The system is a related group of 3 databases, Customers, Inventory, and Orders. Some of fields are in all 3 databases, some are in 2 of them and some in just one. Right now each database has the fields set-up separately and they use importing and exporting to change the data. Is there a way to link the connected fields so that changes could be made without importing constantly and without loosing the data. For example: In inverntory there is information on books. Inventory status, dat…

Hello, I have a number field which is validate by a self join relationship that prevent user to key in a number greater than another number field in another record in same database. However, when the related record number field is 500, it should allow enter 500 or under, but indeed it only allow to enter 250 or under, I don't know why? Please help! Damon

I wonder if someone could help in design. I am creating two related files. One will be individual people, the other will be events (each record will be a different event. The individuals will be related to the events in that they will be attending them. I want to keep track of who attended what event, what the event charge was and whether or not the individual has paid for it. They will be paying in several payments. I want to be able to go to the event file and see who is attending, what owed and who owes it. Somehow in the individual file (or in the event file), I want to be able to enter the payments as they come, perhaps three or four payments, ie, the charge is 100, …

I searched this dbase, but not much posted on these 2 Win script steps. Can someone post or send me a small example on how to load(open) a small Bat file, and have that BAT file execute. Thanks, Dale

I a setting up a many to one relationship but I am having a problem. How can I set it up so when I add a record in one of the many, the 'one' is updated automatically? I've tried portals but they aren't updated automatically. Any help on this much appreciated. Rich

Hi everyone, I cannot create one relationship because filemaker says one of my related field cannot be indexed, and the reason for my field cannot be indexed is my calculation field that calculate a value from another relationship. So, what can i do? Any suggestion will be much appreciated.

We have 2 files: one products, one prices. The prices file is populated using a download from our main company datatbase (for simplicity, all the prices are donwloaded). The Products file does not contain all the company products. So the prices records are not necessarily matched with an existing/active product. When looking at the prices, I would like to list only the records that have a match in the products file... I hope this is soooo trivial, that you will have a laugh but willingly reply... thanks

i tried to create a script (scriptmaker) that would automatically open (actually bring into view) the second of 2 files that make up my database. i couldn't get it to do it.. would someone "walk" me through it? and one more, when i have both files (related, same as above) open, can i make the second file flip records as i flip through them in the first file? also, i'm usinf "Record ID, auto-serial by 1" for my match field for the 2 files -is there anything wrong with relating them both to each other? and how can i cause the secong file to create a new record when create a new record in the first -and give it the same number? whew! Thanks.

i've noticed that in lots of tutorial FMP databases that when you open the 'first' (or master) file all the "related" and necessary others files also open -automatically, underneath, IIRC. how does this occur? i.e., how do i make this happen? Thanks.

FMP 5.5.v2 Mac OS X. i have also bought almost $100. in books to try to learn some of this... but i want to go "relational" right away and though the books are huge they often don't just GIVE me the direct answer i need without a struggle to find it in some weird section. ok, here's what i want to do -that is kinda of important to my self-esteem - i have a file of people -names, room number, email (for now let's say i have no "IDs" for them) just their full name in one field. ok that's one file. i have a second file that contains: Desktop Macs... their ID number, a notes field, serviced by field and one of those automatic "last modified" fields that shows when the …

Hi all, I have two file for record students grade that contain these fields Student File: Student ID (text) Grades File: S/N (text, auto entered) Student ID (text) Subject (text) Grade (text) Comment (text) Performance Score (text) I build a relationship between the student file and the grades file using the Student ID field as the key field (match field).Now I create a layout in the student file to print a report for each student with all the subjects and the grades that she/he achieved. From what I read in the manual, that the way to display all related records in the master file is to use a portal. Is there any way I can print all the related …

I have 3 DB files. A) Order, Contract Rate Table, and C) Color Rate Table. I've built a relationship between A to B using a calculated field built from 3 unique fields in each database to pull a Contract Rate from DB B into a Contract Rate field (Looked-up Value field) in DB A. I did the same between A to C to pull a Color Rate from DB C into a Color Rate field (Looked-up Value field) in DB A. I'm using a looked-up value solution so that the user can override the lookup rate and input a quoted rate. The solution works perfectly in the A to B relationship. However, the A to C relationship will not pull in without some hassle. I've checked the relationship calculation…

Create a relationship in [FILE A.FP5] to [FILE B.FP5] the key fields would be the unique product code. Product Code = Product Code Then in [FILE A.FP5] Create some fields: Product Description, Product Price, etc... In the field opitons of the newly defined fields check lookup option then select the relationship you wish to link to, then from the list of fields in the related file choose the corresponding fields. In pratice once you type in the product code it will instantly lookup the description and price, that data will remain static until you clear the product code field and retype in the same or another code or use the re-lookup command, or import records wit…

Can you relate 2 fields in the same record. Specifically how can I have input for Field one (Home Address), automatically transfer to a Field Two (Emergency Contact Address) without input into Field Two transfering back to Field One if the information is different?

I want to connect a unique product code and its description in one file with my customer invoice in another file. Ideally, that product code would also trigger unit prices.

I am trying to publish (in book form) data I have collected in three related databases. db1. list of 17 bulbs db2. the 40 divisions of the above 17 bulbs db3. the varieties in each of the above 40 divisions The database works great for etering and searching but I am now trying to export and merge the data. What is happening is that I can't figure out a way to export the related fields in the the approptiate order Dahlia Div 1 Art Deco van Gogh Div 2 Big Red Big Blue etc. What I am getting is the first related variety in each division and the rest of them at the end like: Dahlia …

I've not been this frustrated at a project in a long time. . . the jist is this: File A ---> File B (line items) Allow creation, based on Item Ref# File B ---> File A: Based on Item Ref# to allow for lookups; calcs. etc. File A has a field 'Item Name" that needs to either auto-calculate to = File A::Item Name or auto-Lookup the same field, i don't care which, either works for me. . . IT"S NOT WORKING! It doesn't do either the lookup or auto-calc. I have another pair of files that does the exact same thing and it works. I've compared each of the referenced fields, all are set up the exact same. . . any troubleshooting suggestions? Possiblities i might've ove…

We are creating a new application for a police department to track Crime Reports. The reports have several sections Administrative, Offense, Victim, Offender, Property, and Narrative. We want to start with the administrative section of the report and attach the related offenses, victims, offenders, property, and narrative to the Administrative section using the case number as the key. We want the case number to be inserted and saved into the related sections as they are created. If the Administrative section of a report is deleted we want each record associated with the report deleted. We are new to Filemaker Pro. How do we relate and insert the case numbers into other se…

Can you relate 2 fields in the same record. Specifically how can I have input for Field one (Home Address), automatically transfer to a Field Two (Emergency Contact Address) without input into Field Two transfering back to Field One if the information is different?

I'm working on a solution where I am tracking the scores of a number of different tests for a number of different students and I'm not sure if I need a join file...here are the details: Students take the Iowa Basics test in Reading and Math, a Title 1 test in Reading and Math, State Standards tests in Reading, Math, and Writing, and QRI in the Spring and Fall. I want to be able to compare a student's score on a particular test over the course of their elementary years. (The test scores are never compared to a different test. In other words, I will only compare their Iowa Basic Reading score from one year with their Iowa Basic Reading score from a different year). I have…

Another logical twister for y'all: Still working on a Donor and Donations database for a foundation. I have a Donor DB with unique IDs for each record and a Gifts DB which is related to it using the IDs as a match. I am happily using portals in both DBs (as well as in others that don't enter into this problem), but I'm finding myself stumped at how to calculate some specific Gift info. Specifically, in Donors I have a portal listing all Gifts made by that donor by date. Below that I have calculation fields to show Average, Highest, and Lifetime donation amounts for that donor. Beautiful. HOWEVER, I also want to show total donations over the last 18 months, over the last…

Please see the thread entitled "Rolling Total Amounts?" and post a reply if you can. The "reply" that's listed so far is just me updating the question and begging for help. THANKS!

I am using a looping script through a portal to create records in a related file using relationship/set field function: loop setfield [...] "nextportal row, exit afterlast" End Loop The only way that seems to work is to use [status(currentportalrow)& ...] in an unstored calculation for the key in the relationship. Is there any problem with doing this and are there any better methods? Thanks for the help.. [ August 22, 2001: Message edited by: Greg B ]

Hello All! I am in a mess! I have volunteered to help a school principal friend of mine develop a relational database to track school discipline. I think I have a good grasp on the fundamentals but am not sure. I have created 5 files (students,teachers,rules,punishment, and referral) Obviously the student and teacher file holds all the student/teacher demographics as well as the rules hold the rule_id, rule_name, Rule_descript. The referral table/file is what I would call the main/master file (where most of the action takes place). It is where the principal will record a "referral" from a teacher that a student has broken a rule. I have linked the tables/files by st…

Hello! I have a FileMaker Pro 5 database for membership and contributions to a non-profit organization. There are two databases: one with a single record for each member, and one with an entry for each contribution from the members. I used a single field called "ID" as the related field. We use the member's first initial, last initial and phone number as a unique and EASY to remember identifier for each member. So, my ID would be tl3285555 When a user enters the member id into the ID field of the contributions database, the name, address, etc. is filled in from the membership database. My problem is that the relationship is only matching the numeric portion of t…

I have created a db (Field Defns.FP5) to document the definitions of the billions of fields I have in a large relational db. Records cover data details regarding the fields in my big app. I would like to relate the records to each other so I can quickly look up what is affected when I deal with any field. E.g. Imagine I am working with the field "A" in my big app. I look it up in my documenting db "Field Defns.FP5" and hope to see on its info screen, among other things: calculation = W+X "feeder fields" portal lists W, X, Y and Z because in addition to W and X contributing, Y*Z=X (so I know where all the data originates) "contributes to" portal lists J, K and L be…

THESE IS A POST I MADE DAYS AGO AND STILL HAVENT GOT ANY ANSWER. PLEASE CHECK IT OUT: ehhh, I think I didnt explain my self enogh because the problem is not so dificult. maybe it isnt a filemaker feature, so its going to be solve using a loop script or something. ok. i have 2 db's. the first one with a bunch of ids and each id have a number related to it in a field called..... "acount". the second db have the same fields (id and acount) but id marks the relationship bewten the two dbs, so when I insert an id it automaticlly ask the first db for the "acount" value. What I want its to make a sumatory of all the "acount" values IN THE SECOND DATABASE, but when I create …

My database consist of 4 files; reservations, house, guest and calendar. I want a portal showing how many rooms (of 22) from the house file are available. My reservations file contains arrival and departure date fields and a some calc fields that create a date range and room info. I currently enter a date into a global date field that's related to the reservations file(many dates and rooms)which gives inaccurate results and can list the same room more that once. I think I needed another line items type file but I'm not sure. Hope I've given enough info. Can anyone help?

I know and understand the reasons that global fields will revert themselves in a shared environment, my solution to that problem was creating a related file with regular fields and having one record in that database. well I realised that that really didn't work either, because if two people had the database open, they would both be on that one record, and unless you exited in the right order you wouldn't update that record... my solution to that was to create a second record and have a script that on the opening of that database made sure that it was not on the first record... which leads me to the question... is there a good way to have a variable which anyone can u…

I have a database that imports a text report listing every bit of software installed on a computer. There is a report of each computer on the network. Each software title may have duplicate records in this database because it may be installed on multiple machines. (ie the only difference between these records is the Computer ID field. I have created calculated match field that concatenate the Software Title field with the Version field. This alows me to differenciate between older and newer versions of the same software.This works fine for counting numbers of users, etc. with each version of a software title. Where I come to a mental blank is when I want to create a la…

I have a field in a database that comes from a relationship. I want to make a total out of all the foun records but when I choose the total field as sumary, related field doesnt apear. PLease help me, or tell me if you need a bigger explanation about it thANKS

I have a one->many relation. I want to have a calculated text field in my "main file" that is a concatenation of the values of a text field in the related records. If the fields were numbers this would be like the "Sum" aggregation function. Possible?? I am tearing my hair out on this one. One may be wondering why I want to do this. The text field of interest in the many file is calculated by an custom external based on the contents of the other fields in the same record. This calculated field from all the related records needs to be concatenated together into one field in the one file, which will finally be the basis for an e-mail. [ August 13, 2001: Messag…

I am running a 4.1 filemaker database that has a master record for each student and it has a related file that consists of a daily log which contains records for the students daily visits to the nurse. We have a problem where records from the daily log file are somehow beeing deleted. Most recently, the master file name was changed and I should just have had to relink the daily log file, but when I did, about 700 of the 780 records were gone. Does anyone know of any known problems with loosing related records? I do have it set up to delete the related records in the daily log if you delete the master record in the master file,but that should not cause 90% of the records…

Hi, I have a container field where I store references to pictures. I would like to include a bottom that opens the file that I previously used as reference for the container. That is, after including a reference to a Photoshop file in a container, I would like to click in a bottom that opens Photoshop, and that particular file, in order to modifie the picture if I need to. Is it possible to make this script? I appreciate any help. Thanks in advance.

Hello - I'm designing a new donor database for a foundation. I'm starting with the following related DBs: Donors, Donations, and Campaigns. I want to add a Correspondence DB that tracks tax letters, postcards, and catalogs sent out to donors. So I created a Correspondence DB and a DCJoin DB that is the join database for the many-to-many relationship between Donors and Correspondence (many donors receiving many letters...). I have portals in both the Donors and Correspondence DBs so I can see the letters sent out to each donor or the donors to whom each letter was sent... Whew! What I'd ideally like to do is to automatically add data to the DCJoin DB when a letter (r…

hi folks. i am building a relational master database that will pull data across tcp/ip. the problem is that when the db's are being shared at the host location, no editing (defining fields, etc.) can take place. does fm pro server fix this? i am currently using fm pro 5. thanks, ez

I have a database with a job number, a page number and a price for each record. So there might be multiple records with the same job number but different page numbers. I want to be able to set up either a new layout or a related database which will show only unique job number and the total price for all records for that job number. Thanks [ August 06, 2001: Message edited by: clmoss ]

hi folks, i'm a newbie, so please forgive me if this has been covered. i'm trying to build a relational db over tcp sharing. i have several regional centers which i would like to pull specific info from into my local master db. i've got it to work (in a simple manner so far) BUT for some reason the db's can't be open in two locations at the same time. this is a problem cuz the regional centers will need these db's open at all times. i keep getting an error message regarding file locking. the db's are in multi-user mode, all the plug-in's are active, and i am stumped. can i not see the trees for the forest? your humble grommet, icefield

I am currently using a single db file to collect information about music students. Among the fields are time and day for each student's lesson. I want to create a layout that is essentially a table with time along the left side and day along the top. I tried to make 6 global fields (text, repeating 23) to insert the names of each student for the appropriate day. The file was sorted by day and time prior to looping through the records. My plan was to have the global fields arranged on the layout from monday to saturday and the students name would be added to the field by the script. But I can't seem to figure out how to enter information into a…

Here is my situation. I need a database to do 2 things. Firstly, I need 4 pull down bars. Each successive bar is created by the progressive bar chosen. ie. You have a movie database, and you first select Genre, which creates a list of movies, etc... After that is done, i need one more thing. When the 4'th pulldown has been selected I need to have a box filled in with a description. Is this possible, and if so, can someone point me in a direction to look, or show me how to do this. I am new to Filemaker, and couldn't figure it out. Thanks for the help...

How do I create a join file? I understand the need for one, but I don't know how to structure it. What types of fields do I need, and where? Please answer this like I was a 5th grader...I need specifics. -) Or is there an article or web site somewhere that spells it out? Thanks for any help...I'm working on 3 solutions that all need one and I'm at a stand-still. Susan

How many characters deep does the relationship go when pulling a referenced field. By this I mean I have a parent field with the content 18-730588-PX5151T730688 which is a calculation of USER ID & DATE & RUN NUMBER (ITEM & DATE DUE) to match up to the log entry(s) for that particular person on specific date(s). The problem I am having is that the parent field matches up to other children that have the same first characters (such as - 18-730588-PX5151T730674 will show up as a child when in fact it is not) but the last few are different so it doesn't pull ONLY those that are related but pulls all children that are similar in the beginning portion of the field.…

Hi.. I have a Master file A, withe one key field, I have several matching records in relational data base B. The problem is I need to list those matches in the file B and list it in Master file A, and be able to print it for faxing. I have no problem listing the matching records in a portal, but as you know portal does not work well for printing if it goes over the lines limit. To show you the example of the problem, the master file A is a fax out database that shows part number, description, quantity, and price. The relational database is a RFQ database with vendor name as the key field with the part number, descripition, quantity, and price, per record. so when i …

I have a DB that has individual records for each user and application on his or her workstation. For example, record 1 is user 1; application A; record 2 is user 1; application b; record3 is user 1 application c. I want to create a new DB that will gather all the info and let me see, in one record, all the software on user 1's machine, reading something like user 1; application a; application b; application c. Any ideas out there?

Hi, I've been having some trouble and wondering if there is any solutions for this. The file has the following relationships: 1: Supplier Name::Supplier Name Itself 2: Invoice No::Invoice No Itself 3: Line Item::Line Item Itself The file also include the following data for each: 1: Sales Amount 2: Purchase Amount I would like to output the data as following: Supplier Name Invoice No Sales Amount Purchase Amount (Each line has the sum of each Line Item's sales amount & purchase amount for one Invoice No.) Subtotal of Sales Amount & Purchase Amount (The sum of sales amount & purchase amount for each supplier) …

I have created two quite polished database applications and there is one problem remaining for me... On a data entry form that uses a master file and a related file, sometimes a person may want to change the data in the key field and have it change in all the related records at the same time. Instead what happens is that the related information disappears because the key field has been changed in the master. It's still there but inaccessible except to manually go in and change the key field of the related file. Other users wouldn't know this. How can I design around this?

Kurt, Your method (described in the FAQ) is so good I kick myself for not figuring it out years ago. You can bet that every new design from now on will use your method. Because for many years I have used the simple auto enter serial number I have many, MANY solutions that I now would like to convert to this scheme. It is worth the effort to me to convert my files to your method, but it appears pretty challenging. I hope you have experience in converting old systems to yours and can offer a good way to do it. The gist of this post is to posit steps for converting legacy systems to this scheme and ask for your comments and improvements. It is a big job for multi-file sys…

I figured out how to navigate throught files that have a related field, but I can't fiend how to make a button that would allowed me to go from the present reccord to the first reccord of any other file, related or not. Thanks

Hello, I have one database (Editor) with no records just global fields used for temporary storage of data for editing. The problem comes up when I am creating new records with data from the Editor in another file. I have a relationship base on a global field in the Editor called "gCompany Name" relating to a field in the Contacts database called "Company Name". I check to see if the company exists using IsValid and warn the user. If not I try to populate the "Company Name" in the Contacts File but FileMaker refuses to enter into the field and spawn a new child. Has anyone ever had this problem? If so how do I get around it? Thanks, Graham Sprague

I am creating a database which contains student contact information for a school (FM 3). When I bring up a specific student I want to be able to show all of that students siblings that go to the school and are in the database. I know this can be done by creating a calculation field that combines the last name and the last 4 digits of the students phone number to create a "Family ID." Then the "Family ID" would be set up in a relationship to pull out the siblings into a portal. The problem I am having is that the way the database is set up is that each student doesn't have his own phone number, but rather there are home phone number fields for each one of his parents…

Okay, this should be simple...if I were in anything but FileMaker. Most of my DB experience revolves around web-database, where I have more control over the interface using SQL..please be gentel with me, this is first time I've tried using FMPRO for anything more then simple one-to-many relationhips. I'll see if I can describe this... I'm developing a fairly robust CRM for a client, whose main functionality reloves around mailings and labels. I have several files: Contacts.fp5 (primary data entry file) Locations.fp5 (child of Contant entity, stores all addresses for a contact) Communicate.fp5 (child of Contact entiry, stores all forms of comm.) History.fp5 (ch…

I have a database (Sales)that has as one of its fields, part number. Its easy to create a second database (Parts) with part number along with part price. The two databases can then be linked/related so as to get part price and determine charge in the database (Sales) based on number of parts needed and cost per part. However part prices change. I don't want the previous cost calculations to change everytime I change a price on a part number. Seems to me I need effective dates to be added to the relationship to make the part price current and corrrect. Is that possible? Anybody out there have a solution to this type of problem?

In an attempt to make this as clear as possible here is what I am trying to do... I have.. - db - Invoices - db - Inventory (338 line Items) - db - Codes (7 common discriptions, 42 possible codes) The INVOICE db has a popup value ilst that inserts discription and unit price from INVENTORY (line items), Somehow i want a field on the INVOICE that will show a 'CODE' (one of 42) when the discription and the unit price add to a 'specified' The pricing calculations are working what i cannot get is the 'code' to come into a designated field.. - Example - ....Discription....Units....Unit price....Amount owed .....Woozle-A.......(1).......$23.00........$23.0…

I was wondering if it was possible to transfer my found set to another related file. Thanks in advance.

OK....I am trying to access a two dimensional table that I created in a second FM program that will go look at the conditions and come back with the appropriate field, but I am having a lot of trouble with it going out and getting the specific field. It might be the calculations of it, I am really struggling to get this. For Example (If Total_MM = Fat_bicep and (age >=16 and <=29)), get that field. This is on a PC too. Any help or insight on this that would point me in the right direction would be greatly appreciated. [ July 08, 2001: Message edited by: bdhooper ] [ July 10, 2001: Message edited by: bdhooper ]

I am trying to make a database where I can route problems easily. I would like someone to specify a location and a category, then based off of another database with this info it will assign a person to handle the request. So here is a little more info. Database 1: Where this new request is created Database 2: Where the information is storied for categories/location/tech is stored. In Database 2 I have a record that has the following information: Location- Home, Category- Printer, Assistant- Bob. Now when you specify "Home" "Printer" I need to return "Bob". I can't get this calculation to work. I have tried If statements to say if Location=Home then run this next …

I'm currently creating a database to keep track of all ongoing projects. I was wondering if it was possible to create a field to store links to all files related to a certain project. Example: I'd like to keep track of all the word and powerpoint documents, in order to do so I could store a link pointing to each of the files to easily launch them directly from filemaker. Like an alias/shortcut inside filemaker. Can this be done?

Hi there, This one might be hard to explain, but I'll try anyway. I have tried for a long time to make a feature in a time billing database, but I just simply can

I have 1 db with all the company names and a portal with the contactpersons from the company. The name, telnr. faxnr, email etc. are stored into a second db, TelFax. Relationship = companyName So in db 1 I can see all the persons related to a company. Into the portal, at each row, a button to go to db Telfax, to see the details from that person.(goto related record) But somewhere I do something wrong, I allways ended up into the first record. Can't figured out how to set up the relationship to work. Thanks to pointing me into the right direction.

I'm developing a database solution where employees store their daily work hours allowing me to create reports on hours spent on different projects during different time spans etc. I'm looking for a template to see how it has been done and to either refine it to meet our needs or more likely create my own solution. I just need a few tips to get started. So if anyone knows any good templates or has a solution ready for me to play around please post a message or E-mail me directly. (BTW. If anyone happens to know any good sites with free quality templates I'd be happy to hear about it) Thanks in advance.

Hey All, Does anybody know a way to remove the current record (i.e. Dick) from the portal when using a self relationship (so we only see Jane and Spot in the portal)?

I have two databases. The 1st which is a Staff Directory that includes the name, extension, and e-mail of everyone in my organization. The 2nd database is used to track correction requests to our website, usually these requests are made by other staff members. The way I want the information listed in the (1) Staff Directory is LastName, FirstName. Then when I enter the employees extension in the (2) Corrections database I want it to automatically pull the employees name into the name field of that database (which I have figured out how to do) I want the name to be FirstName LastName in this database though. As of now its pulling Last Name, FirstName because that is h…